Display records selected by user in a combo box

[Chad_jsu]

I have a simple database with one table. The table will be used to
enter data for multiple users on a weekly basis. One feature that I
want is to have a form with a combo box populated with all of the users
(which is a field in the table) that, when selected, will display all
of the records for that user on the form. I have tried several
different ways of doing this with minimal success. Can anyone tell me
how to do this correctly? Thanks.

 

[John Vinson]

I have a simple database with one table. The table will be used to
enter data for multiple users on a weekly basis. One feature that I
want is to have a form with a combo box populated with all of the users
(which is a field in the table) that, when selected, will display all
of the records for that user on the form. I have tried several
different ways of doing this with minimal success. Can anyone tell me
how to do this correctly? Thanks.

Sounds like you really need some more tables! You should, at the very
least, have a table of Users (which you can populate using a SELECT
DISTINCT append query from your current table).

WHat you can do with the current design is to set the Form's Filter
property in the AfterUpdate event of a combo box. The combo should be
based on a Query like

SELECT DISTINCT UserID, Username
FROM yourtable
ORDER BY Username;

(adjusted of course to your table structure); in the combo's
AfterUpdate event use

Me.Filter = "[UserID] = " & Me.cboSelectUser

or

Me.Filter = "[Username] = '" & Me.cboSelectUser & "'"

depending on whether the user field is number or text, respectively.

John W. Vinson[MVP]

 

[Chad_jsu]

When I put Me.Filter = "[Username] = '" & Me.cboSelectUser & "'" into
the AfterUpdate field I get an error saying that the Macro 'Me' can not
be found.

I have a simple database with one table. The table will be used to
enter data for multiple users on a weekly basis. One feature that I
want is to have a form with a combo box populated with all of the users
(which is a field in the table) that, when selected, will display all
of the records for that user on the form. I have tried several
different ways of doing this with minimal success. Can anyone tell me
how to do this correctly? Thanks.

Sounds like you really need some more tables! You should, at the very
least, have a table of Users (which you can populate using a SELECT
DISTINCT append query from your current table).

WHat you can do with the current design is to set the Form's Filter
property in the AfterUpdate event of a combo box. The combo should be
based on a Query like

SELECT DISTINCT UserID, Username
FROM yourtable
ORDER BY Username;

(adjusted of course to your table structure); in the combo's
AfterUpdate event use

Me.Filter = "[UserID] = " & Me.cboSelectUser

or

Me.Filter = "[Username] = '" & Me.cboSelectUser & "'"

depending on whether the user field is number or text, respectively.

John W. Vinson[MVP]

 

[student huang]

When I put Me.Filter = "[Username] = '" & Me.cboSelectUser & "'" into
the AfterUpdate field I get an error saying that the Macro 'Me' can not
be found.

I have a simple database with one table. The table will be used to
enter data for multiple users on a weekly basis. One feature that I
want is to have a form with a combo box populated with all of the users
(which is a field in the table) that, when selected, will display all
of the records for that user on the form. I have tried several
different ways of doing this with minimal success. Can anyone tell me
how to do this correctly? Thanks.

Sounds like you really need some more tables! You should, at the very
least, have a table of Users (which you can populate using a SELECT
DISTINCT append query from your current table).

WHat you can do with the current design is to set the Form's Filter
property in the AfterUpdate event of a combo box. The combo should be
based on a Query like

SELECT DISTINCT UserID, Username
FROM yourtable
ORDER BY Username;

(adjusted of course to your table structure); in the combo's
AfterUpdate event use

Me.Filter = "[UserID] = " & Me.cboSelectUser

or

Me.Filter = "[Username] = '" & Me.cboSelectUser & "'"

depending on whether the user field is number or text, respectively.

John W. Vinson[MVP]

 

[John Vinson]

When I put Me.Filter = "[Username] = '" & Me.cboSelectUser & "'" into
the AfterUpdate field I get an error saying that the Macro 'Me' can not
be found.

sorry... should have clarified!

Don't put the VBA code in the property itself. Instead, clear the
property; click the ... icon; select Code Builder, and put that line
(changing the field and control names to match those in your database,
which I of course do not know!) between the Sub and End Sub lines that
Access will give you.

John W. Vinson[MVP]