FindRecord Macro Won't Work

[Kgwill85]

I am trying to run a macro that takes information from 2 separate
fields in one form and then searches a table for the record and then
it populates another form.

The first form only has 2 fields in it. I have one command button
also. For the command button I set the "OnClick" to a macro that
opens up another form and also finds the record that was chosen from
the 1st form.

The FindRecord action is set up like this
Find What:=[PARID]
Match: Whole Field
Match Case: No
Search: All
Search as Formatted: No
Only Current Field: Yes
Find First: Yes

The search form has two combo boxes that display information from my
table. When I click my command button the macro finds the record for
PARID which is what I'm looking for, however I cannot figure out how
to incorporate the other combo box into the macro correctly. I want
the user to be able to search both combo boxes seperately in case they
do not know the correct information for both boxes.

The name of my other combo box is BO_Project_Name. I tried to put
this in the Find What section but it didn't work...
=[PARID]or[BO_Project_Name]

Thanks in advance

 

[Guest]

I don't think you can use a macro to filter on two fields - but I don't use
macros. It is real easy using code. Here is an example - change the names of
the forma and the combo boxes to your names.

The code between the 'xxxxxxxxx lines is what you would paste in the body of
the sub. Your sub name will probably be different.


Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

'xxxxxxxxx
Dim stDocName As String
Dim stLinkCriteria As String

'change this to the name of your results form
stDocName = "ResultsForm"

'check for selection in first combo box
'change COMBO1 to the name of your combo box
If Not IsNull(Me.Combo1) Then
stLinkCriteria = "[PARID] = " & Me.Combo1 & " AND "
End If

'check for selection in second combo box
'change COMBO2 to the name of your combo box
If Not IsNull(Me.Combo2) Then
stLinkCriteria = "[BO_Project_Name] = " & Me.Combo2 & " AND "
End If

'remove the last 5 chars
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria)) - 5
End If

'open form filtered by criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
'xxxxxxxxx

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub


HTH

 

[Kgwill85]

I don't think you can use a macro to filter on two fields - but I don't use
macros. It is real easy using code. Here is an example - change the names of
the forma and the combo boxes to your names.

The code between the 'xxxxxxxxx lines is what you would paste in the body of
the sub. Your sub name will probably be different.

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

'xxxxxxxxx
Dim stDocName As String
Dim stLinkCriteria As String

'change this to the name of your results form
stDocName = "ResultsForm"

'check for selection in first combo box
'change COMBO1 to the name of your combo box
If Not IsNull(Me.Combo1) Then
stLinkCriteria = "[PARID] = " & Me.Combo1 & " AND "
End If

'check for selection in second combo box
'change COMBO2 to the name of your combo box
If Not IsNull(Me.Combo2) Then
stLinkCriteria = "[BO_Project_Name] = " & Me.Combo2 & " AND "
End If

'remove the last 5 chars
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria)) - 5
End If

'open form filtered by criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
'xxxxxxxxx

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I am trying to run a macro that takes information from 2 separate
fields in one form and then searches a table for the record and then
it populates another form.

The first form only has 2 fields in it. I have one command button
also. For the command button I set the "OnClick" to a macro that
opens up another form and also finds the record that was chosen from
the 1st form.

The FindRecord action is set up like this
Find What:=[PARID]
Match: Whole Field
Match Case: No
Search: All
Search as Formatted: No
Only Current Field: Yes
Find First: Yes

The search form has two combo boxes that display information from my
table. When I click my command button the macro finds the record for
PARID which is what I'm looking for, however I cannot figure out how
to incorporate the other combo box into the macro correctly. I want
the user to be able to search both combo boxes seperately in case they
do not know the correct information for both boxes.

The name of my other combo box is BO_Project_Name. I tried to put
this in the Find What section but it didn't work...
=[PARID]or[BO_Project_Name]

Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for the help. I was trying to use a code for this but I didn't
know how to set it up at all. That's why I use macros. I'm going to
try this out and see what happens.

 

[Kgwill85]

I don't think you can use a macro to filter on two fields - but I don't use
macros. It is real easy using code. Here is an example - change the names of
the forma and the combo boxes to your names.

The code between the 'xxxxxxxxx lines is what you would paste in the body of
the sub. Your sub name will probably be different.

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

'xxxxxxxxx
Dim stDocName As String
Dim stLinkCriteria As String

'change this to the name of your results form
stDocName = "ResultsForm"

'check for selection in first combo box
'change COMBO1 to the name of your combo box
If Not IsNull(Me.Combo1) Then
stLinkCriteria = "[PARID] = " & Me.Combo1 & " AND "
End If

'check for selection in second combo box
'change COMBO2 to the name of your combo box
If Not IsNull(Me.Combo2) Then
stLinkCriteria = "[BO_Project_Name] = " & Me.Combo2 & " AND "
End If

'remove the last 5 chars
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria)) - 5
End If

'open form filtered by criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
'xxxxxxxxx

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub

HTH

I am trying to run a macro that takes information from 2 separate
fields in one form and then searches a table for the record and then
it populates another form.
The first form only has 2 fields in it. I have one command button
also. For the command button I set the "OnClick" to a macro that
opens up another form and also finds the record that was chosen from
the 1st form.
The FindRecord action is set up like this
Find What:=[PARID]
Match: Whole Field
Match Case: No
Search: All
Search as Formatted: No
Only Current Field: Yes
Find First: Yes
The search form has two combo boxes that display information from my
table. When I click my command button the macro finds the record for
PARID which is what I'm looking for, however I cannot figure out how
to incorporate the other combo box into the macro correctly. I want
the user to be able to search both combo boxes seperately in case they
do not know the correct information for both boxes.
The name of my other combo box is BO_Project_Name. I tried to put
this in the Find What section but it didn't work...
=[PARID]or[BO_Project_Name]
Thanks in advance- Hide quoted text -

- Show quoted text -

Thanks for the help. I was trying to use a code for this but I didn't
know how to set it up at all. That's why I use macros. I'm going to
try this out and see what happens.- Hide quoted text -

- Show quoted text -

I tried it and I keep getting a type mismatch error. I changed the
combo boxes names correctly and the command button also.

 

[Guest]

Sorry, my bad. The bound field in my combo boxes are long int and (I think)
yours are text which need to be delimited with quotes.

Try this

Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

'xxxxxxxxx
Dim stDocName As String
Dim stLinkCriteria As String

'change this to the name of your results form
stDocName = "ResultsForm"

'check for selection in first combo box
'change COMBO1 to the name of your combo box
If Not IsNull(Me.Combo1) Then
'changed
stLinkCriteria = "[PARID] = '" & Me.Combo1 & "' AND "
End If

'check for selection in second combo box
'change COMBO2 to the name of your combo box
If Not IsNull(Me.Combo2) Then
'changed
stLinkCriteria = "[BO_Project_Name] = '" & Me.Combo2 & "' AND "
End If

'remove the last 5 chars
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = Left(stLinkCriteria, Len(stLinkCriteria)) - 5
End If

'open form filtered by criteria
DoCmd.OpenForm stDocName, , , stLinkCriteria
'xxxxxxxxx

Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click

End Sub