Combo box Selection Criteria

  • Thread starter Thread starter Dane.
  • Start date Start date
D

Dane.

Hi all,

This is a continuation of a thread I posted a little while ago
trying to get a combo box working...

SITUATION:

* I have 2 TABLES (and matching input FORMS) in my database: a LIBRARY
USERS Database and a CARD GENERATOR database.

- The LIBRARY USERS database contains records of people containing
information (columns) like first name, last name, contact number,
etc...

- The CARD database is a table which stores information (input by
the user) which is used to generate an identification card (for as
many people as input by the user, i.e.: stored in this table) via a
REPORT (using a query). This table has only a first ("FirstName") and
last name ("LastName") column (obviously, based on the LIBRARY USERS
TABLE).



PROBLEM:

* What I'm trying to do is make the "FirstName" combo box in the CARD
GENERATOR FORM only display the "First Names" for those people
matching the "LastName" field in this form (from the LIBRARY USERS
table)..

* So, for example; if I have 3 people/records:

1) Joe Smith
2) Mary Smith
3) Bob Jones

When I select "Last Name" SMITH in the CARD GENERATOR FORM, it will
only display JOE and MARY in the "First Name" combo box (first and
last name sourced from the LIBRARY USERS table [and stored in the CARD
GENERATOR table]).


- I have successfully setup a "Last Name" combo box to source its
data from the "LastName" section in my LIBRARY USERS table (using a
query) and save it into to the CARD GENERATOR table (using RowSource =
"SELECT [Library Users Database].LastName, [Library Users
Database].LastName FROM [Library Users Database] ORDER BY [LastName];"
in the "LastName" combo-box properties).

* I have no idea how to setup the "FirstName" combo box. A previous
(and only) suggestion was:

"In the AfterUpdate event for the Last Name combo box, add code
similar to this:

Dim strSQL As String
strSQL = "Select FirstName"
strSQL = strSQL & "From [USERS] "
strSQL = strSQL & "Where LastName = " & Me!LastName
Me!FirstName.RowSourceType = "Table/Query"
Me!FirstName.RowSource = strSQL
End Sub
"

but this didn't work (I kept on getting the error on the "Me!" lines:
"Run-time error '438'. Object doesn't support this property or
method").

Can any brave person help with this problem? I apologise for the
copious amount of info in this email, I hope it makes sense to
someone!

Regards,
Dane.
 
The easiest way to do this would be to create an actual query to list your
first names. Then in the criteria set it to
Forms!NameOfCardGeneratorForm!NameOfComboBoxForLastName.

Add your new combo box for the first name and set the row source to this new
query.

In the afterUpdate event of the last name combo box add

Me.NameOfComboBoxForFirstName.Requery

Thsi will update the list of first names each time you change the last name.

Kelvin

Dane. said:
Hi all,

This is a continuation of a thread I posted a little while ago
trying to get a combo box working...

SITUATION:

* I have 2 TABLES (and matching input FORMS) in my database: a LIBRARY
USERS Database and a CARD GENERATOR database.

- The LIBRARY USERS database contains records of people containing
information (columns) like first name, last name, contact number,
etc...

- The CARD database is a table which stores information (input by
the user) which is used to generate an identification card (for as
many people as input by the user, i.e.: stored in this table) via a
REPORT (using a query). This table has only a first ("FirstName") and
last name ("LastName") column (obviously, based on the LIBRARY USERS
TABLE).



PROBLEM:

* What I'm trying to do is make the "FirstName" combo box in the CARD
GENERATOR FORM only display the "First Names" for those people
matching the "LastName" field in this form (from the LIBRARY USERS
table)..

* So, for example; if I have 3 people/records:

1) Joe Smith
2) Mary Smith
3) Bob Jones

When I select "Last Name" SMITH in the CARD GENERATOR FORM, it will
only display JOE and MARY in the "First Name" combo box (first and
last name sourced from the LIBRARY USERS table [and stored in the CARD
GENERATOR table]).


- I have successfully setup a "Last Name" combo box to source its
data from the "LastName" section in my LIBRARY USERS table (using a
query) and save it into to the CARD GENERATOR table (using RowSource =
"SELECT [Library Users Database].LastName, [Library Users
Database].LastName FROM [Library Users Database] ORDER BY [LastName];"
in the "LastName" combo-box properties).

* I have no idea how to setup the "FirstName" combo box. A previous
(and only) suggestion was:

"In the AfterUpdate event for the Last Name combo box, add code
similar to this:

Dim strSQL As String
strSQL = "Select FirstName"
strSQL = strSQL & "From [USERS] "
strSQL = strSQL & "Where LastName = " & Me!LastName
Me!FirstName.RowSourceType = "Table/Query"
Me!FirstName.RowSource = strSQL
End Sub
"

but this didn't work (I kept on getting the error on the "Me!" lines:
"Run-time error '438'. Object doesn't support this property or
method").

Can any brave person help with this problem? I apologise for the
copious amount of info in this email, I hope it makes sense to
someone!

Regards,
Dane.
Click to expand...
 
Hi Kelvin,

Just in regards to the "Me.NameOfComboBoxForFirstName.Requery"
section: I first pasted that into the afterUpdate box (for LastName)
and got the error "Access can't find the macro Me." when clicking in
the "FirstName" box.

I then went into the code builder section and put the same code into
the corresponding area for the LastName box. Now I get the error "The
record source '[Forms]![Card Generator]![LastName]' specified on this
form does not exist" when clicking in the FirstName box.

I have doublechecked the name, and it is definately correct.

Any ideas?

I have also setup your query to get the FirstName from the LIBRARY
USERS table, used the criteria "[Forms]![Card Generator]![LastName]".
I have both the LIBRARY USERS and the CARD GENERATOR tables in this
query, with a relationship between both first/last names for each
table.

Thanks for your help!

Dane.


Kelvin said:
The easiest way to do this would be to create an actual query to list your
first names. Then in the criteria set it to
Forms!NameOfCardGeneratorForm!NameOfComboBoxForLastName.

Add your new combo box for the first name and set the row source to this new
query.

In the afterUpdate event of the last name combo box add

Me.NameOfComboBoxForFirstName.Requery

Thsi will update the list of first names each time you change the last name.

Kelvin

Dane. said:
Hi all,

This is a continuation of a thread I posted a little while ago
trying to get a combo box working...

SITUATION:

* I have 2 TABLES (and matching input FORMS) in my database: a LIBRARY
USERS Database and a CARD GENERATOR database.

- The LIBRARY USERS database contains records of people containing
information (columns) like first name, last name, contact number,
etc...

- The CARD database is a table which stores information (input by
the user) which is used to generate an identification card (for as
many people as input by the user, i.e.: stored in this table) via a
REPORT (using a query). This table has only a first ("FirstName") and
last name ("LastName") column (obviously, based on the LIBRARY USERS
TABLE).



PROBLEM:

* What I'm trying to do is make the "FirstName" combo box in the CARD
GENERATOR FORM only display the "First Names" for those people
matching the "LastName" field in this form (from the LIBRARY USERS
table)..

* So, for example; if I have 3 people/records:

1) Joe Smith
2) Mary Smith
3) Bob Jones

When I select "Last Name" SMITH in the CARD GENERATOR FORM, it will
only display JOE and MARY in the "First Name" combo box (first and
last name sourced from the LIBRARY USERS table [and stored in the CARD
GENERATOR table]).


- I have successfully setup a "Last Name" combo box to source its
data from the "LastName" section in my LIBRARY USERS table (using a
query) and save it into to the CARD GENERATOR table (using RowSource =
"SELECT [Library Users Database].LastName, [Library Users
Database].LastName FROM [Library Users Database] ORDER BY [LastName];"
in the "LastName" combo-box properties).

* I have no idea how to setup the "FirstName" combo box. A previous
(and only) suggestion was:

"In the AfterUpdate event for the Last Name combo box, add code
similar to this:

Dim strSQL As String
strSQL = "Select FirstName"
strSQL = strSQL & "From [USERS] "
strSQL = strSQL & "Where LastName = " & Me!LastName
Me!FirstName.RowSourceType = "Table/Query"
Me!FirstName.RowSource = strSQL
End Sub
"

but this didn't work (I kept on getting the error on the "Me!" lines:
"Run-time error '438'. Object doesn't support this property or
method").

Can any brave person help with this problem? I apologise for the
copious amount of info in this email, I hope it makes sense to
someone!

Regards,
Dane.
Click to expand...
Click to expand...
 
Dane,

the problem in in the query. You don't need the tables linekd, all you need
is the User table. In the query, you should have 2 fields, FirstName and
LastName. The criteria should be under LastName. You can uncheck the box
that says show for this field since you don't need to see the last name.

Kelvin
 
You're a champ Kelvin!

It's now all working like a charm. Thank you for your time and help :)

Kind Regards,
Dane.


Kelvin said:
Dane,

the problem in in the query. You don't need the tables linekd, all you need
is the User table. In the query, you should have 2 fields, FirstName and
LastName. The criteria should be under LastName. You can uncheck the box
that says show for this field since you don't need to see the last name.

Kelvin

Dane. said:
Hi Kelvin,

Just in regards to the "Me.NameOfComboBoxForFirstName.Requery"
section: I first pasted that into the afterUpdate box (for LastName)
and got the error "Access can't find the macro Me." when clicking in
the "FirstName" box.

I then went into the code builder section and put the same code into
the corresponding area for the LastName box. Now I get the error "The
record source '[Forms]![Card Generator]![LastName]' specified on this
form does not exist" when clicking in the FirstName box.

I have doublechecked the name, and it is definately correct.

Any ideas?

I have also setup your query to get the FirstName from the LIBRARY
USERS table, used the criteria "[Forms]![Card Generator]![LastName]".
I have both the LIBRARY USERS and the CARD GENERATOR tables in this
query, with a relationship between both first/last names for each
table.

Thanks for your help!

Dane.
Click to expand...
Click to expand...
 
No problem. Glad to be of help.

Kelvin

Dane. said:
You're a champ Kelvin!

It's now all working like a charm. Thank you for your time and help :)

Kind Regards,
Dane.


"Kelvin" <[email protected]> wrote in message
Dane,

the problem in in the query. You don't need the tables linekd, all you need
is the User table. In the query, you should have 2 fields, FirstName and
LastName. The criteria should be under LastName. You can uncheck the box
that says show for this field since you don't need to see the last name.

Kelvin

Dane. said:
Hi Kelvin,

Just in regards to the "Me.NameOfComboBoxForFirstName.Requery"
section: I first pasted that into the afterUpdate box (for LastName)
and got the error "Access can't find the macro Me." when clicking in
the "FirstName" box.

I then went into the code builder section and put the same code into
the corresponding area for the LastName box. Now I get the error "The
record source '[Forms]![Card Generator]![LastName]' specified on this
form does not exist" when clicking in the FirstName box.

I have doublechecked the name, and it is definately correct.

Any ideas?

I have also setup your query to get the FirstName from the LIBRARY
USERS table, used the criteria "[Forms]![Card Generator]![LastName]".
I have both the LIBRARY USERS and the CARD GENERATOR tables in this
query, with a relationship between both first/last names for each
table.

Thanks for your help!

Dane.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top