setting record source in a command button

D

DonD

I have a table that is maintained by two users (each has responsibility for
@1/2 of the records). Each would like to only see their records when they
open a form.

I have placed a filter on the form using the stLinkCriteria in the command
button that invokes the form (using the complete table), but the problem is
if they filter on a field and then remove the filter they are back to seeing
all of the records.

As an alternative to filtering, I created two queries to divide the table
into each users records. Question is do I have to create two duplicate forms
(one for each query) OR can I create two command buttons on a switchboard and
set the recordsource in the command buttons and then maintain just a single
form
 
A

Allen Browne

You could use the OpenArgs argument of OpenForm to pass the variable, and
then change the RecordSource of the form in its Open event.

This example assumes a UserID field that is a number, so you would pass a 1
or a 2:

DoCmd.OpenForm "MyForm", OpenArgs:="1"

Private Sub Form_Open(Cancel As Integer)
If Len(Nz(Me.OpenArgs) > 0 Then
Me.RecordSource = "SELECT * FROM MyTable WHERE UserID = " &
Me.OpenArgs & ";"
End If
End Sub


If it is a Text type field, you need extra quotes:
Me.RecordSource = "SELECT * FROM MyTable WHERE UserID = """ &
Me.OpenArgs & """;"
 
Top