Display information based on another field?

[Dane.]

Hi everyone,

Firstly, my apologies about my terminology use...I'm an Access
beginner and still get quite confused with all the jargon.

OK, here's the situation:

* I have 2 tables in my database: a USERS Database and a CARD
Generator database.

- The USERS database contains records for people and has columns
containing information 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 and last name
column.



* What I'm trying to do is make a FORM which will source it's
information from the USERS table and store it in the CARD table:

- I have successfully setup a "Last Name" combo box to source it's
data from the "Last Name" section in my USERS table and save it into
to the CARD table.

- PROBLEM: How can I now make the "First Name" combo box in the CARD
form only display the "First Names" for those people matching the
"Last Name" field in this form (from the 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 FORM, it will only
display JOE and MARY in the "First Name" combo box (first and last
name sourced from the USERS table [and stored in the CARD table]).

I hope all this makes sense.....I do realise this is has probably
been asked many times, but after spending a long time searching on the
net I still don't know how to do this....

Any help is greatly appreciated.

Regards,
Dane.

 

[mark]

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

Substitute the actual names for your FirstName and
LastName fields. This will create a SQL string that will
be used as the source for the first name combo box.
Hope that helps.

 

[Dane.]

Hi Mark,

Thanks for your reply. I put the code into the AfterUpdate for the
Last Name box, but when I select a name from this box I get the
following error for these 2 lines:

Me!FirstName.RowSourceType = "Table/Query"
Me!FirstName.RowSource = strSQL

The error says: "Run-time error '438'. Object doesn't support this
property or method".

Any ideas?

Thanks,
Dane.

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

Substitute the actual names for your FirstName and
LastName fields. This will create a SQL string that will
be used as the source for the first name combo box.
Hope that helps.

-----Original Message-----
Hi everyone,

Firstly, my apologies about my terminology use...I'm an Access
beginner and still get quite confused with all the jargon.

OK, here's the situation:

* I have 2 tables in my database: a USERS Database and a CARD
Generator database.

- The USERS database contains records for people and has columns
containing information 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 and last name
column.



* What I'm trying to do is make a FORM which will source it's
information from the USERS table and store it in the CARD table:

- I have successfully setup a "Last Name" combo box to source it's
data from the "Last Name" section in my USERS table and save it into
to the CARD table.

- PROBLEM: How can I now make the "First Name" combo box in the CARD
form only display the "First Names" for those people matching the
"Last Name" field in this form (from the 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 FORM, it will only
display JOE and MARY in the "First Name" combo box (first and last
name sourced from the USERS table [and stored in the CARD table]).

I hope all this makes sense.....I do realise this is has probably
been asked many times, but after spending a long time searching on the
net I still don't know how to do this....

Any help is greatly appreciated.

Regards,
Dane.
.