Still Struggling...

The subform for Titles. Finally! I don't know what the problem was. I think I
did the same today as yesterday. I didn't have a problem with the combo box
for Classifications. The Row Source for Classifications seemed very similar
to Titles. The only thing I didn't do was "Order By Classifications" and
that's only because it had already been working before we started discussing
the subform for Titles. In the beginning, both Classifications and Titles
were combo boxes. Should I delete frmTitles? It still has the invalid value
message from yesterday.
--
Aria W.


"Beetle" wrote:

Just wanted you to know that I got it to work!

Do you mean the combo box for Classifications, or the subform for Titles,
or both?
--
_________

Sean Bailey


"Aria" wrote:

Just wanted you to know that I got it to work! I did as you suggested and
pushed the ellipsis button to take a look at the query. It doesn't look any
different from what I saw yesterday. I'm also not getting the message about
the From clause even though the Row Source doesn't appear different to me. I
don't know what the problem was, but I sure am happy it's working. Thank you
all!
--
Aria W.


"Aria" wrote:

Maybe I'm not doing much good by jumping in occasionally, but I'm going to
jump in again anyhow.

Thank you so much for your explanation. It was very helpful. Jump in
whenever you can...

Maybe you mean you are including tblTitles in the query

You're correct. When I did the query, I also pulled in tblTitles.

I suspect a disconnect with the concept of Row Source and Control Source. Think of it this way (don't do it, but rather just picture it; this is a thought experiment only)

You're right. There is a disconnect. I understand the concept but I can't
visualize how this works. Because I can't "see" it, I'm not "getting" it. My
query made sense to me because it pulled in all the information I needed.
Your "picture this" experiment is helpful. I'm going to have to go through it
a few more times to make sure it sinks in. Thank you so much.
--
Aria W.


"BruceM" wrote:

Maybe I'm not doing much good by jumping in occasionally, but I'm going to
jump in again anyhow. These are some observations and comments based on my
reading of the thread.

As I recall, tblTitlesEmps is a junction table comprising TitleID and
EmployeeID fields. A query based on tblTitlesEmps can therefore return only
those fields. A query can only return fields that are in the tables (or
queries) on which the query is based. Maybe I'm stating something you
already know, but there seems to be a disconnect somewhere. I'm not sure
what you mean by "a query based on tblTitlesEmps referencing
TitleDescription". Maybe you mean you are including tblTitles in the query,
but if this is about displaying information on the Titles subform (which is
based on tblTitleEmps), the following about combo boxes may help.
Regarding combo boxes, I suspect a disconnect with the concept of Row Source
and Control Source. Think of it this way (don't do it, but rather just
picture it; this is a thought experiment only): Instead of having a combo
box to select the Title for the Employee you have a text box in which you
input the TitleID (which is a number field, as I recall; the
TitleDescription is a separate field). In order for you to input the
numbers you need a separate list of Titles and their ID numbers. If the
Title is Math Teacher you look on the list and see that the ID for Math
Teacher is 123, so you input 123 into the TitleID text box. In order to see
the text "Math Teacher" in addition to the number 123 you need to add
tblTitles to the form's Record Source query.
Instead of doing all that, your combo box Row Source takes the place of the
printout you needed in order to work with the TitleID text box. The combo
box *stores* TitleID just as happened with the text box, but it *displays*
TitleDescription. The displayed text is a convenience for the user. The
combo box Row Source SQL or stored query means you don't need to include
tblTitles in the subform's Record Source query.

In case you haven't discovered it yet, if your combo box Row Source is
something like:
Select TitleID, TitleDescription From tblTitles Order By TitleDescription
you can click the three dots next to Row Source to view this as a query
design grid. You can also switch to datasheet view to be sure the SQL
produces the expected results. I can't see why the FROM clause isn't
working, but if you switch to design view and datasheet view you may be able
to discover something. BTW, the Row Source SQL doesn't use leading or
closing quotes. I don't know if they are in the postings for clarity or if
they are part of the SQL.

Just to be sure you are clear on the terms SQL and Query, they are the same
thing for purposes of this discussion. SQL is the language behind the
queries. If you are going to use a particular Row Source SQL for combo
boxes on other forms you may want to create and save a query, then used the
named query for the Row Source rather than typing the SQL each time. It
makes no difference to Access which you choose (maybe there's a slight
performance advantage one way or the other, but that is of no concern now).

Again, I may be restating what you already know, but I have lost track of
some of the details of what has been discussed here.

"Aria" wrote in message
...
Well on a bright note; cboClassifications is working well. The only thing
it
doesn't say in the SQL statement is Order By ClassDescription. I think
because I ran a query *before* I created the combo box. I'm afraid to
change
it in case I end up with problems in this one too. Right now, I'm really
frustrated with creating this subform. It isn't working and I don't know
how
to fix it.
These are the steps Ive taken:
1. Recordsource=tblTitlesEmps
2. Deleted text box and label for TitleID.
3. Changed label for EmpID to combo box.
4. Set EmpID as the control source.
5. Set Default view=Continuous
6. Unsure of this step since you stated, "...should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles. I input the statement you provided yesterday, "Select
TitleID, TitleDescription From tblTitles Order By TitleDescription

I believe this is the same statement that is working for
cboClassifications.
I think it returned the same message as before; there is a problem with
the
FROM clause. It also wasn't a continuous form even though I had changed
the
default view. Then I started trying a lot of different things like
changing
it to TitleID as in yesterday's post; same message. Then I tried creating
a
query based on tblTitlesEmps referencing TitleDescription. I didn't get
the
FROM message but it didn't return any values. I know I should post the SQL
statement but I deleted it. I deleted everything that didn't return the
value
I need. I know...it isn't helpful. I going to back away from this right
now
and try again tomorrow.
--
Aria W.


"Beetle" wrote:

I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need

It *should* be a subform in frmEmployees. It's recordsource should be
tblTitlesEmps. It should have a combo box that has EmpID (from
tblTitlesEmps)
as the control source, and the row source of this combo box should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles.

BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.

In that case, then you need a field in tblEmployees for ClassID as a FK,
since
tblEmployees would be the "many" side of the relationship. Then in your
frmEmployees you would just add a combo box that is bound to this field
(the control source), and the row source would be something like;

Select ClassID, ClassDescription From tblClassifications OrderBy
ClassDescription

Bound Column = 1
Column Count = 2
Column Widths = 0,1

You would not need tblEmpClassifications (the junction table) in this
case.


--
_________

Sean Bailey


"Aria" wrote:

frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees

Correct (subs and site staff).

sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff

Correct.

frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?

I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need.

frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?

No, this is not a subform. This is a form. The combo box is part of
tblEmployees. I did not experience the error message that I received
with
cboTitles so I left it alone.

BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.


--
Aria W.


"Beetle" wrote:

OK. I would like to make sure I have a clear understanding of the
forms
you currently have. From what I can tell, you have the following;

frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees

sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff

frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?

frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?

Is this correct?

--
_________

Sean Bailey


"Aria" wrote:

OK, so what you need to do is use subforms for this.I'll use
Titles as an
example. The junction table (tblEmpTitles) stores the data that
defines

If frmTitles is not the subform that you are currently using, then I suppose
you can delete it if it serves no other purpose.

In your last post yesterday you listed the steps you had taken to create
the subform. Some of these steps were wrong, so just to clarify your
current subform should have the following properties;

Record Source = tblTitlesEmps
Default View = Continuous
The link between the main form and this subform should be EmpID

Also, your subform should have a combo box with the following properties;

Control Source = TitleID
Row Source = Select TitleID, TitleDescription From tblTitles
OrderBy TitleDescription
Column Count = 2
Bound Column = 1
Column Widths = 0",1" (or whatever width works best for you)

--
_________

Sean Bailey


"Aria" wrote:

The subform for Titles. Finally! I don't know what the problem was. I think I
did the same today as yesterday. I didn't have a problem with the combo box
for Classifications. The Row Source for Classifications seemed very similar
to Titles. The only thing I didn't do was "Order By Classifications" and
that's only because it had already been working before we started discussing
the subform for Titles. In the beginning, both Classifications and Titles
were combo boxes. Should I delete frmTitles? It still has the invalid value
message from yesterday.
--
Aria W.


"Beetle" wrote:

Just wanted you to know that I got it to work!

Do you mean the combo box for Classifications, or the subform for Titles,
or both?
--
_________

Sean Bailey


"Aria" wrote:

Just wanted you to know that I got it to work! I did as you suggested and
pushed the ellipsis button to take a look at the query. It doesn't look any
different from what I saw yesterday. I'm also not getting the message about
the From clause even though the Row Source doesn't appear different to me. I
don't know what the problem was, but I sure am happy it's working. Thank you
all!
--
Aria W.


"Aria" wrote:

Maybe I'm not doing much good by jumping in occasionally, but I'm going to
jump in again anyhow.

Thank you so much for your explanation. It was very helpful. Jump in
whenever you can...

Maybe you mean you are including tblTitles in the query

You're correct. When I did the query, I also pulled in tblTitles.

I suspect a disconnect with the concept of Row Source and Control Source. Think of it this way (don't do it, but rather just picture it; this is a thought experiment only)

You're right. There is a disconnect. I understand the concept but I can't
visualize how this works. Because I can't "see" it, I'm not "getting" it. My
query made sense to me because it pulled in all the information I needed.
Your "picture this" experiment is helpful. I'm going to have to go through it
a few more times to make sure it sinks in. Thank you so much.
--
Aria W.


"BruceM" wrote:

Maybe I'm not doing much good by jumping in occasionally, but I'm going to
jump in again anyhow. These are some observations and comments based on my
reading of the thread.

As I recall, tblTitlesEmps is a junction table comprising TitleID and
EmployeeID fields. A query based on tblTitlesEmps can therefore return only
those fields. A query can only return fields that are in the tables (or
queries) on which the query is based. Maybe I'm stating something you
already know, but there seems to be a disconnect somewhere. I'm not sure
what you mean by "a query based on tblTitlesEmps referencing
TitleDescription". Maybe you mean you are including tblTitles in the query,
but if this is about displaying information on the Titles subform (which is
based on tblTitleEmps), the following about combo boxes may help.
Regarding combo boxes, I suspect a disconnect with the concept of Row Source
and Control Source. Think of it this way (don't do it, but rather just
picture it; this is a thought experiment only): Instead of having a combo
box to select the Title for the Employee you have a text box in which you
input the TitleID (which is a number field, as I recall; the
TitleDescription is a separate field). In order for you to input the
numbers you need a separate list of Titles and their ID numbers. If the
Title is Math Teacher you look on the list and see that the ID for Math
Teacher is 123, so you input 123 into the TitleID text box. In order to see
the text "Math Teacher" in addition to the number 123 you need to add
tblTitles to the form's Record Source query.
Instead of doing all that, your combo box Row Source takes the place of the
printout you needed in order to work with the TitleID text box. The combo
box *stores* TitleID just as happened with the text box, but it *displays*
TitleDescription. The displayed text is a convenience for the user. The
combo box Row Source SQL or stored query means you don't need to include
tblTitles in the subform's Record Source query.

In case you haven't discovered it yet, if your combo box Row Source is
something like:
Select TitleID, TitleDescription From tblTitles Order By TitleDescription
you can click the three dots next to Row Source to view this as a query
design grid. You can also switch to datasheet view to be sure the SQL
produces the expected results. I can't see why the FROM clause isn't
working, but if you switch to design view and datasheet view you may be able
to discover something. BTW, the Row Source SQL doesn't use leading or
closing quotes. I don't know if they are in the postings for clarity or if
they are part of the SQL.

Just to be sure you are clear on the terms SQL and Query, they are the same
thing for purposes of this discussion. SQL is the language behind the
queries. If you are going to use a particular Row Source SQL for combo
boxes on other forms you may want to create and save a query, then used the
named query for the Row Source rather than typing the SQL each time. It
makes no difference to Access which you choose (maybe there's a slight
performance advantage one way or the other, but that is of no concern now).

Again, I may be restating what you already know, but I have lost track of
some of the details of what has been discussed here.

"Aria" wrote in message
...
Well on a bright note; cboClassifications is working well. The only thing
it
doesn't say in the SQL statement is Order By ClassDescription. I think
because I ran a query *before* I created the combo box. I'm afraid to
change
it in case I end up with problems in this one too. Right now, I'm really
frustrated with creating this subform. It isn't working and I don't know
how
to fix it.
These are the steps Ive taken:
1. Recordsource=tblTitlesEmps
2. Deleted text box and label for TitleID.
3. Changed label for EmpID to combo box.
4. Set EmpID as the control source.
5. Set Default view=Continuous
6. Unsure of this step since you stated, "...should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles. I input the statement you provided yesterday, "Select
TitleID, TitleDescription From tblTitles Order By TitleDescription

I believe this is the same statement that is working for
cboClassifications.
I think it returned the same message as before; there is a problem with
the
FROM clause. It also wasn't a continuous form even though I had changed
the
default view. Then I started trying a lot of different things like
changing
it to TitleID as in yesterday's post; same message. Then I tried creating
a
query based on tblTitlesEmps referencing TitleDescription. I didn't get
the
FROM message but it didn't return any values. I know I should post the SQL
statement but I deleted it. I deleted everything that didn't return the
value
I need. I know...it isn't helpful. I going to back away from this right
now
and try again tomorrow.
--
Aria W.


"Beetle" wrote:

I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need

It *should* be a subform in frmEmployees. It's recordsource should be
tblTitlesEmps. It should have a combo box that has EmpID (from
tblTitlesEmps)
as the control source, and the row source of this combo box should be
a SQL statement like the one I posted previously, which gets its values
from tblTitles.

BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.

In that case, then you need a field in tblEmployees for ClassID as a FK,
since
tblEmployees would be the "many" side of the relationship. Then in your
frmEmployees you would just add a combo box that is bound to this field
(the control source), and the row source would be something like;

Select ClassID, ClassDescription From tblClassifications OrderBy
ClassDescription

Bound Column = 1
Column Count = 2
Column Widths = 0,1

You would not need tblEmpClassifications (the junction table) in this
case.


--
_________

Sean Bailey


"Aria" wrote:

frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees

Correct (subs and site staff).

sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff

Correct.

frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?

I believe this might be where the problem lies. Currently, it's a form.
I
was trying to make a subform based on your post yesterday, but I was
unsure
exactly what you meant. I didn't understand why this wouldn't be a
subform of
tblEmployees. You mentioned Titles (tblTitles? frmTitles?) but since I
didn't
quite understand what you meant, I tried it both ways to see if I
could get
what I think I need.

frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?

No, this is not a subform. This is a form. The combo box is part of
tblEmployees. I did not experience the error message that I received
with
cboTitles so I left it alone.

BTW, I re-read all of our discussions about Titles and Classifications
and
for the life of me cannot figure out why we have this junction table,
EmpsClass. Each employee can only have one classification although many
employees can have the same classification. I think that would be 1:M.
The
only thing I remember discussing concerning dual classifications were
administrators but we were leaving them as is. I'll leave it alone for
now.


--
Aria W.


"Beetle" wrote:

OK. I would like to make sure I have a clear understanding of the
forms
you currently have. From what I can tell, you have the following;

frmEmployees
Record Source = tblEmployees
Default View = Single Form
Purpose = enter general info about all employees

sfrmSiteEmps (a subform of frmEmployees)
Record Source = tblSiteEmps
Default View = Single Form
Purpose = enter address and other info related to full time staff

frmTitles (which I think is a subform in frmEmployees ?)
Record Source = tblTitlesEmps (the junction table)?
Default View = Continuous ?
Purpose = select the appropriate Title(s) for each employee ?

frmClassifications (which I think is also a subform in frmEmployees
?)
Record Source = tblEmpClassifications (the junction table) ?
Default View = Continuous ?
Purpose = select the appropriate Classification(s) for each employee
?

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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the control
source but Access didn't like that so I took it out. The Row Source Type is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've looked at
the query. It looks like what I want. I've also looked at it in datasheet
view. The names are there but it did not sort ascending like I asked. What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've looked
at
the query. It looks like what I want. I've also looked at it in datasheet
view. The names are there but it did not sort ascending like I asked. What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've looked
at
the query. It looks like what I want. I've also looked at it in datasheet
view. The names are there but it did not sort ascending like I asked. What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

"Aria" wrote in message
...
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"


My mistake. It's list width that sets the overall width.

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?

Use your actual control and field names, but other than that it should work
as written.


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.

Here is a simpler example of using a variable. This is a string (text)
variable:

' This declares that when strMsg appears in the code it is to be treated as
a text string
Dim strMsg as String

' This assigns a value to the string variable we have just declared
strMsg = "You are about to change the value"

' The next two lines of code will produce the same message
MsgBox "You are about to change the value"
MsgBox strMsg

If this code is in the Before Update event for a text box the message "You
are about to change the value" will appear before the value is updated.
Using the variable can be tidier, and simplifies things when you need to use
the same text string several times. In the VBA examples in VBA Help all
values are assigned to variables. I can't say I see the benefit of that,
but it may help to understand what's going on with Help.

In my example, Dim rs As Object declares to the After Update event that rs
is an Object. I can't really describe why RecordsetClone is an Object, nor
can I clearly describe what RecordsetClone is. However, this line of code
tells the After Update event that rs is the same as Me.RecordsetClone:

Set rs = Me.RecordsetClone

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...?


For one thing, it's very different from a Bookmark in Word. This
explanation may be a bit sketchy, but it's the best I can do. Form VBA
Help, a bookmark "uniquely identifies a particular record in the form's
underlying table". This:
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
identifies a particular record in the form's record source. A bookmark is
assigned to each record in the Record Source table or query. RecordsetClone
is a copy of the Recordset, so it has the same bookmarks. RecordsetClone
lets you perform actions such as Find that cannot be performed on the form.
Once the particular record you selected is identified in the copy
(RecordsetClone), Me.Bookmark = rs.Bookmark goes to the record with the same
bookmark as the one that was found in the RecordsetClone.
That's the best I can do with that one.

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.

Yes, look in tblEmployees if that contains the records that are not sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup tab
at the bottom of the window will show "Combo Box". I couldn't say what is
going on with #...#14.


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.

You can preview the report with something like this:
DoCmd.OpenReport "ReportName", acViewPreview

The menu bar or toolbar can contain the print command or icon.




--
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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source
Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've
looked
at
the query. It looks like what I want. I've also looked at it in
datasheet
view. The names are there but it did not sort ascending like I asked.
What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've looked
at
the query. It looks like what I want. I've also looked at it in datasheet
view. The names are there but it did not sort ascending like I asked. What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

I've run into a bit of a problem. It didn't work. Please tell me where I'm
going wrong. I input the following into the Afterupdate event:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[LastName, FirstName] = " & Me.cboStaffLookup
Me.Bookmark = rs.Bookmark

or should it say," rs.FindFirst "[LastName], [FirstName] = " &
Me.cboStaffLookup ?

I'm getting the following message:
"Micorsoft Access can't find the macro 'Dim rs As Object
Set rs=Me
The macro (or its macro group) doesn't exist, or the macro is new but hasn't
been save. Note that when you enter the macrogroupname, macroname syntax in
an argument, yu must specify the name the macro's macro group was last saved
under."

So, I went to macros and didn't get beyond "Find Record". I didn't know what
else to do there or if I even needed to be there since it wasn't mentioned.

Yes, look in tblEmployees if that contains the records that are not sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup tab
at the bottom of the window will show "Combo Box".

I didn't see anything unusual other than what I already mentioned about the
#14 listed under title description. There were no drop-down arrows in
datasheet view nor did the Lookup tab show "Combo Box". It said text box for
Class Description, Title Description, LN, FN and MI. Is this a problem? When
I go to frmEmployees, Class Description is a combo box. Title Description is
a subform.

Your explanation was fine. It answered the question I posed. Thanks.

--
Aria W.


"BruceM" wrote:

"Aria" wrote in message
...
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"


My mistake. It's list width that sets the overall width.

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?

Use your actual control and field names, but other than that it should work
as written.


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.

Here is a simpler example of using a variable. This is a string (text)
variable:

' This declares that when strMsg appears in the code it is to be treated as
a text string
Dim strMsg as String

' This assigns a value to the string variable we have just declared
strMsg = "You are about to change the value"

' The next two lines of code will produce the same message
MsgBox "You are about to change the value"
MsgBox strMsg

If this code is in the Before Update event for a text box the message "You
are about to change the value" will appear before the value is updated.
Using the variable can be tidier, and simplifies things when you need to use
the same text string several times. In the VBA examples in VBA Help all
values are assigned to variables. I can't say I see the benefit of that,
but it may help to understand what's going on with Help.

In my example, Dim rs As Object declares to the After Update event that rs
is an Object. I can't really describe why RecordsetClone is an Object, nor
can I clearly describe what RecordsetClone is. However, this line of code
tells the After Update event that rs is the same as Me.RecordsetClone:

Set rs = Me.RecordsetClone

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...?


For one thing, it's very different from a Bookmark in Word. This
explanation may be a bit sketchy, but it's the best I can do. Form VBA
Help, a bookmark "uniquely identifies a particular record in the form's
underlying table". This:
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
identifies a particular record in the form's record source. A bookmark is
assigned to each record in the Record Source table or query. RecordsetClone
is a copy of the Recordset, so it has the same bookmarks. RecordsetClone
lets you perform actions such as Find that cannot be performed on the form.
Once the particular record you selected is identified in the copy
(RecordsetClone), Me.Bookmark = rs.Bookmark goes to the record with the same
bookmark as the one that was found in the RecordsetClone.
That's the best I can do with that one.

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.

Yes, look in tblEmployees if that contains the records that are not sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup tab
at the bottom of the window will show "Combo Box". I couldn't say what is
going on with #...#14.


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.

You can preview the report with something like this:
DoCmd.OpenReport "ReportName", acViewPreview

The menu bar or toolbar can contain the print command or icon.




--
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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as the
control
source but Access didn't like that so I took it out. The Row Source
Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've
looked
at
the query. It looks like what I want. I've also looked at it in
datasheet
view. The names are there but it did not sort ascending like I asked.
What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m relationships
for Employees to Titles and Employees to Classifications, so you have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1

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@uwe...
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].

"Aria" wrote in message
...
I've run into a bit of a problem. It didn't work. Please tell me where I'm
going wrong. I input the following into the Afterupdate event:

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[LastName, FirstName] = " & Me.cboStaffLookup
Me.Bookmark = rs.Bookmark

It should read:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup

This assumes the bound column in cboStaffLookup is EmployeeID (or EmpID, if
that is the name of the field). An unbound combo box still has a bound
column. The bound column is the one that Access "sees" when the combo box
is referenced. The Row Source SQL is:
SELECT [EmpID], [LastName] & ", " & [FirstName] AS LastFirst FROM
tblEmployees ORDER BY [LastName], [FirstName];

If you view the SQL in datasheet view you will see that the first column is
EmpID. Column 1 should be the bound (hidden) column in cboStaffLookup.
This statement:
rs.FindFirst "[EmpID] = " & Me.cboStaffLookup
means "Find the first record in the RecordsetClone in which EmpID is the
same as EmpID in the combo box."

Remember, the combo box drop-down list is the equivalent of your
hypothetical printout matching employees with their ID numbers. When you
select a name from the list you are invisibly selecting their number. You
are then taking this number to the full Employee listing and finding the
record in which EmpID is the same as the selected number. As far as the
user is concerned the name is being selected, but Access is quietly using
the ID number instead.

or should it say," rs.FindFirst "[LastName], [FirstName] = " &
Me.cboStaffLookup ?

I'm getting the following message:
"Micorsoft Access can't find the macro 'Dim rs As Object
Set rs=Me
The macro (or its macro group) doesn't exist, or the macro is new but
hasn't
been save. Note that when you enter the macrogroupname, macroname syntax
in
an argument, yu must specify the name the macro's macro group was last
saved
under."

It isn't a macro, but rather an event procedure. To create the event
procedure, right click the combo box, then select Properties. When the
Properties Sheet (the thing with tabs for Format, Data, etc.) shows up,
click the Events tab and click After Update. Click the three dots at the
right, select Code Builder, then click OK. You should see the VBA editor
with the following:

Private Sub cboStaffLookup_AfterUpdate()

End Sub

The cursor should be blinking between those lines. Add the code. After you
enter:
Dim rs As Object
press the Enter key to go to a new line (or press it twice to create some
space and make the code easier to read). Add:
Set rs = Me.RecordsetClone
Press the Enter key again, and add the next lines of code, pressing the
Enter key after each one.

Scroll to the top of the code window and be sure the words Option Explicit
are under Option Compare Database. Add them if they are not. If they are
not, in the VBA editor click Tools Options. Click the Editor tab, and
check the box Require Variable Declaration.
Still in the editor, click Debug Compile. This will highlight any typos
and other such errors in the code.


So, I went to macros and didn't get beyond "Find Record". I didn't know
what
else to do there or if I even needed to be there since it wasn't
mentioned.

Yes, look in tblEmployees if that contains the records that are not
sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup
tab
at the bottom of the window will show "Combo Box".

I didn't see anything unusual other than what I already mentioned about
the
#14 listed under title description. There were no drop-down arrows in
datasheet view nor did the Lookup tab show "Combo Box". It said text box
for
Class Description, Title Description, LN, FN and MI. Is this a problem?
When
I go to frmEmployees, Class Description is a combo box. Title Description
is
a subform.

Sounds OK. A combo box in a table is *entirely* different from a combo box
on a form. Combo boxes belong on forms, but should not be in tables. I
don't know what is happening with the sort order, but let's not get too many
things cooking on a Friday afternoon.


Your explanation was fine. It answered the question I posed. Thanks.

--
Aria W.


"BruceM" wrote:

"Aria" wrote in message
...
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"


My mistake. It's list width that sets the overall width.

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?

Use your actual control and field names, but other than that it should
work
as written.


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.

Here is a simpler example of using a variable. This is a string (text)
variable:

' This declares that when strMsg appears in the code it is to be treated
as
a text string
Dim strMsg as String

' This assigns a value to the string variable we have just declared
strMsg = "You are about to change the value"

' The next two lines of code will produce the same message
MsgBox "You are about to change the value"
MsgBox strMsg

If this code is in the Before Update event for a text box the message
"You
are about to change the value" will appear before the value is updated.
Using the variable can be tidier, and simplifies things when you need to
use
the same text string several times. In the VBA examples in VBA Help all
values are assigned to variables. I can't say I see the benefit of that,
but it may help to understand what's going on with Help.

In my example, Dim rs As Object declares to the After Update event that
rs
is an Object. I can't really describe why RecordsetClone is an Object,
nor
can I clearly describe what RecordsetClone is. However, this line of
code
tells the After Update event that rs is the same as Me.RecordsetClone:

Set rs = Me.RecordsetClone

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...?


For one thing, it's very different from a Bookmark in Word. This
explanation may be a bit sketchy, but it's the best I can do. Form VBA
Help, a bookmark "uniquely identifies a particular record in the form's
underlying table". This:
rs.FindFirst "[EmployeeID] = " & Me.cboEmployee
identifies a particular record in the form's record source. A bookmark
is
assigned to each record in the Record Source table or query.
RecordsetClone
is a copy of the Recordset, so it has the same bookmarks. RecordsetClone
lets you perform actions such as Find that cannot be performed on the
form.
Once the particular record you selected is identified in the copy
(RecordsetClone), Me.Bookmark = rs.Bookmark goes to the record with the
same
bookmark as the one that was found in the RecordsetClone.
That's the best I can do with that one.

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.

Yes, look in tblEmployees if that contains the records that are not
sorting
properly. Regarding the drop-down arrow, I meant in datasheet view. It
will show up when you click into the field. In Design view, the Lookup
tab
at the bottom of the window will show "Combo Box". I couldn't say what
is
going on with #...#14.


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.

You can preview the report with something like this:
DoCmd.OpenReport "ReportName", acViewPreview

The menu bar or toolbar can contain the print command or icon.




--
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@uwe...
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].
[LastName], [tblEmployees].[FirstName] FROM tblEmployees ORDER BY
[tblEmployees].[LastName], [tblEmployees].[FirstName];

The Record Source is tblEmployees. I tried to put Last name in as
the
control
source but Access didn't like that so I took it out. The Row Source
Type
is
table/query. Column count = 3
Bound Column = 1

I'm sure we've already discussed this but I not catching it. I've
looked
at
the query. It looks like what I want. I've also looked at it in
datasheet
view. The names are there but it did not sort ascending like I
asked.
What
mistakes have I made and how do I fix it?

Beetle284 wrote:
You may need to refresh my memory a bit here. You had m:m
relationships
for Employees to Titles and Employees to Classifications, so you
have
junction
tables for each of them, is that correct?

I was talking specifically about tables, not forms.

[quoted text clipped - 15 lines]
gonna
start :- )


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200807/1