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.

On 6 Nov 2006 13:59:46 -0800, (E-Mail Removed) wrote:

>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]

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.


John Vinson wrote:
> On 6 Nov 2006 13:59:46 -0800, (E-Mail Removed) wrote:
>
> >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]

<(E-Mail Removed)>
??????:(E-Mail Removed)...
> 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.
>
>
> John Vinson wrote:
>> On 6 Nov 2006 13:59:46 -0800, (E-Mail Removed) wrote:
>>
>> >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]
>

On 8 Nov 2006 20:51:23 -0800, (E-Mail Removed) wrote:

>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]