Open a form with a filter

[homer2002]

Hi

i'm trying to work out the syntax to open a form with a filter

I have 2 tables that are linked with a 1 to many relationship.

STAFF
STAFFID is autonumber (Primary Key)
NameOfStaff is Text
UserID is Text


STATS
STATID is autonumber (Primary Key)
StaffID is a foreign key to the primary key in table STAFF


i want to use docmd.openform to open a form with just 1 persons stats.


For example somthing like



docmd.openform ("STATS",,***WHERE userID = "APERSON")




but i can't get the syntax right. All i can do is filter on fields that are not linked to another table. which is easy.

 

[Adrianna]

Why don't you just run a query off the two linked tables.....and in the userID criteria use [Enter the user ID that you wish to view]

Or if you only want a certain userID...fill that in the criteria

Or use a combo box to autofill the form and/or subform!

 

[homer2002]

because I don't know how to.

can you give me a quick step by step on how to use a query to filter.


i can make a query with the STATS & STAFF tables but i can't make a form filter using it.

 

[Hayley Baxter]

I use this code to find all the relevant contacts for a company. Company is chosen from the combo then on click of the cmdbutton the contacts for the chosen company is displayed

Private Sub CmdViewContact_Click()
On Error GoTo Err_CmdViewContact_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmProspectsContacts"

stLinkCriteria = "[I D Number]=" & Me![Combo160]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdViewContact_Click:
Exit Sub

Err_CmdViewContact_Click:
MsgBox Err.Description
Resume Exit_CmdViewContact_Click

End Sub

You can use the wizard, open form, find specific data to display then choose your matching fields.

HTH
Hay

 

[homer2002]

i tried using the value of a combo box to return the StaffID value
and tried (taking out using variable and entering in a valid ID

DoCmd.OpenForm "STATS", , , "STAFFID = {BE78E0B3-B4E6-11D6-8B10-0002A5941B7F}"


but i got an error message saying

Malformed GUID in query expression 'STAFFID = {BE78E0B3-B4E6-11D6-8B10-0002A5941B7F}'


the field data type is ReplicationID (in autonumber)which I think is what is screwing me up.

 

[Hayley Baxter]

Here's a little sample for you to see how it works hopefully you will be able to use this. It's in a97 so you can convert if you are using 2000.

Hope this is of some help to you
Regards
Hay