Re: Still Struggling...

Hi Beetle,
Where is my recall button when I need it?! I hadn't seen your post before I
sent off my last one. Boy, do I need to make some changes to what I did.

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

Uh-oh...I'm supposed to use Emp. ID here? I saw that when Bruce had it in
his code but I thought I was supposed to put the fields I was interested in
seeing. I'll change that.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) >that your form is based on.

Just ignore my last post...
So this line should be Set rs = Me.tblEmployeesClone?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).

Ok, I looked in tblEmployees. I didn't see anything. I do want both first
and last name to show. I did as Bruce suggested with the SQL he gave me and
it does show what I want.

The bookmarks are only valid for the period of time that the form is >open. If you close and re-open the form, those same records may have >a different bookmark assigned.

Interesting...thanks for the additional info; that was helpful.

There is something a little odd though. I have only input 6 employee >>names using frmEmployees just so I can check to see if things are >>working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry >>shows a #...#14.

When you opened which table?

tblEmployees; I don't understand why that is there since I am not entering
directly into the tables and I use a drop-down menu.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a >report and then use a command button on your form to print (or print >preview) it.

I did print the form and understand what you're saying. I didn't really care
for the way it looked. The only reports I have right now are relationship
diagrams. I don't have any saved macros either. How hard would it be for me
to do this?
Bruce gave me this:
DoCmd.OpenReport "ReportName", acViewPreview

in Macros under Action, I do see OpenReport. Do I select that and then under
comments (?) list the above?
--
Aria W.


"Beetle" wrote:

Several questions to address here, so comments are inline.

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

Yes, but you may need to correct the naming. For example, I believe
you PK field is EmpID (not EmployeeID), and your combo box may
have a different name.

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

No, Access doesn't already know that rs is an abbreviation for
RecordsetClone. In this line;

Dim rs As Object

you are declaring a variable named rs and telling Access what *type* of
variable it is. In this case it is an Object type of variable.

In this line;

Set rs = Me.RecordsetClone

you are telling Access what to assign to the rs variable you just declared.
In this case you are telling it to assign a copy of the recordset (table) that
your form is based on.

You can declare anything you want, as long as you declare it properly so
that Access knows what it is. In other words, you could (theoretically) write;

Dim ClownShoes As Object

Set ClownShoes = Me.RecordsetClone
ClownShoes.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = ClownShoes.Bookmark

but that would be silly <g>

When declaring a recordset variable, rs and rst are more or less universally
recognized (by application designers and such) as representing a recordset
of some type.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

It is a placeholder of sorts, but it has nothing to do with the actual data
that
is stored in your table. Everytime you open a bound form, a unique bookmark
is created for each record in that form's recordset. The bookmarks are only
valid for the period of time that the form is open. If you close and re-open
the form, those same records may have a different bookmark assigned.

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?

You have to look in tblEmployees, because FirstName and LastName don't
exist in any of your other tables. Your query looks fine, unless you want to
show both names in the combo when it is not expanded (dopped down).
A combo box will only display the first visible column in its unexpanded
state, so in that case you would need to concantenate the names in your
query as Bruce suggested. His example query might look like this in
design view;

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:LastFirst:[LastName] & ", " & [FirstName]
Table:
Sort:
Show: Yes

Field: LastName
Table:tblEmployees
Sort: Ascending
Show: No

The last field is not shown because it is only used for sorting.

There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14.

When you opened which table?

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.

Although you *can* print a form in Access, they are not designed to be
printed and usually look like *&%! when you do. You should create a report
and then use a command button on your form to print (or print preview) it.

--
_________

Sean Bailey


"Aria" wrote:

Hi Bruce,
Thanks for responding. It seemed so easy in the example. Why does everything
have to turn into a problem? I'm sorry, I forgot to include column widths. I
thought about that much later.

Column widths =0";0.6459";0.625"
List widths=1.2708"

In this case it should be unbound. You aren't trying to store the value, >but rather to go to the selected record.

I thought it should be unbound but I've been wrong so often that now I
automatically think, "You're wrong. It must be the other way."


Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

Hmmm...programming code. You know where I am as far as skill level. I am
certainly willing to try, if you don't mind the questions that are sure to
come. I'll try not to inundate you with a thousand and one questions.
Can I ask the following:
1. The above code goes into the AfterUpdate event as is?

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable.

2. Well, maybe I don't need to know, but I was wondering if Access already
knows that rs is an abbreviation for Me.RecordsetClone because the = sign
isn't used until the second statement. If it's more than I need to know, we
can drop it.

3. Me.Bookmark--I've seen that before and have been dying to ask. What does
that do? I think of a bookmark as a placeholder, but if the value isn't
stored...?

Again, if you don't want to get into it, that's fine.

As far as the SQL statement, I opened it again in Design view and this is
what it shows:

Field: EmpID
Table:tblEmployees
Show: Yes (The box is checked).

Field:Last Name
Table:tblEmployees
Sort: Ascending
Show: Yes

Field: FirstName
Table:tblEmployees
Sort: Ascending
Show: Yes

Just want to make sure we're on the same page...I'm working in frmEmployees
with subform SiteEmps, so the table you want me to look in is tblEmployees?
Just to cover all, I checked both tables. I did not see a drop-down arrow. I
assume you mean in Design View. There is something a little odd though. I
have only input 6 employee names using frmEmployees just so I can check to
see if things are working. When I opened the table, 5 of the employees have a
title description (Admin., teacher, etc.). The sixth and final entry shows a
#...#14. I don't know why. When you open the form, it list the title.

As far as printing the record, I am just trying to circumvent some
anticipated issues; one in particular is staying in my head. As I work
through my issues with the db, I keep thinking about how others will use it.
I can hear some of the conversations already. I did think about making a
report after I created that button. The print button is convenient but I
think I would rather it go to print preview. I don't know, I'm still just
trying to get through all of my other issues.




--
Aria W.


"BruceM" wrote:

I can't figure why it isn't sorting, although I have one idea I will address
later. Try this:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY
[LastName], [FirstName];

This doesn't really address your questions in some ways, but it is an
example of how an alias (LastFirst) can be used. You didn't mention column
widths, but for a three column colmbo box in which the first column is
hidden then would be something like 0";1";1". In my example the column
count would be 2 and the column widths 0";1". From what I can tell the
rightmost visible column will fill the rest of the space in the combo box.
If there is one visible column it will be the width of the combo box unless
its width is greater than the combo box width. If the combo box is 2.5"
wide and the visible columns are 1";1" the rightmost column will actually be
1.5". If the combo box is 1.5" wide and the visible columns are 1";1" the
rightmost column will hang over the edge of the combo box when you click the
down arrow.

Anyhow, back to the combo box. In this case it should be unbound. You
aren't trying to store the value, but rather to go to the selected record.
For this the combo box needs an After Update event something like this
(cboEmployee is the name of the combo box):

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = rs.Bookmark

You could also do it this way:

Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me.cboEmployee
Me.Bookmark = Me.RecordsetClone.Bookmark

The difference is that in the first case rs is used as a sort of
abbreviation for Me.RecordsetClone. Thereafter you use rs in place of
Me.RecordsetClone. Dim rs as Object declares rs as an object variable. I
don't want to get too deeply into this, but when you hear about variable
declaration and use, this is one example.

Back to the sorting in the combo box. Open the SQL in Design View and be
sure it is what it should be. Unless there is a lookup field in the table,
it should work. To see if there is a lookup field, open the table and see
if any of the fields have a drop-down arrow. If they do, report back about
it. It needs to be fixed.

Assuming you get the combo box to work as intended, just what action are you
performing when you print the record? Best choice would be to create a
report (it can be very simple if you like), and to print the report. It is
an easy enough matter to limit printing the report to a single selected
record.

"Aria via AccessMonster.com" <u44643@uwe> wrote in message
news:86f39d41551ee@xxxxxx
Apparently there are problems in the Microsoft forum. I hate to break the
thread (sorry Bruce) but I was wondering if you could help me with
something?
Currently, I'm working with frmEmployees. I want the form to be
user-friendly.
I know there are going to be issues there. There is an example of what I
want
to do in my book and I followed what they said to do, but of course it
isn't
working for me. In frmEmployees I added a combo box. It's unbound; maybe
it
should be bound. I did try that but it again only showed the last name.
What
I'm trying to do is create a drop-down list of employee last name, first
name.
When the user clicks on the name, it goes to that employee record. I added
a
print record button (I don't know if it works because currently, I'm stuck
on
this). I see the names in the drop-down but it isn't going to that record
and
when I click on the name it then only shows the last name.
This is the SQL statement: SELECT [tblEmployees].[EmpID], [tblEmployees].
.