Command Buttons that use 2 link criteria (1 Viewer)

[Aaron Senft]

I am trying to create command buttons that take the user to another form, that use 2 fields to filter the records on the other table.

Example Joe Smith, 25

I want to be able to pull up Joe Smith, 25 and not just all Joe Smiths.

Any suggestions? TIA!

Aaron Senft

 

[Travis]

Here's one:

Try using this approach.

I know it is more common to bind forms to a query, but you can gain more flexibility by removing the RecordSource and adding code similar to this on the Form_Open Event:

Private Sub Form_Open()
Dim stSQL as String
Dim stName as String
Dim intAge as String

stName = [forms]![FrmButtonWasOn].[Name]
intAge = [forms]![FrmButtonWasOn].[Age]

stSQL = "Select * from [tblMyTable] Where Name ='" & stName & "' and Age = " & intAge

Me.RecordSource = stSQL


This works if you have both a finite or infinate amount of filtering. To allow for more add more. You can even build the intire WHERE part of a query before you open the form.

Another method it to pass information via the OpenArgs. You can build the entire WHERE Clause and pass it to the Form:

Private Sub Form_Open()
Dim stSQL as String

stSQL = "Select * from [tblMyTable] Where " & Me.OpenArgs

Me.RecordSource = stSQL


Hope this helps.

 

[Aaron Senft]

I couldn't get those to work. I am not too good at the VB scripting part of Access yet. Looking at the VB that the command button Wizard creates, looks similar, but i need it to open the other form wit 2 criteria instead of just one.
here is what the Wizard created:

stLinkCriteria = "[SR ID]=" & "'" & Me![SR ID] & "'"

i need it to link on SR ID and another field called candidate.

Any other suggestions? Thanks!

Aaron Senft

 

[Pat Hartman]

Try:

stLinkCriteria = "[SR ID]=" & "'" & Me![SR ID] & "' AND [Candidate] = " & "'" & Me![Candidate] & "'