Using combo box for two field search

[chigley3]

Hello there,

I have created a combo box within a form in order to search for a specific manufacturer claim no.

However, the 'job form' can have records that have duplicate manufacturer claim nos within a specific Dealer code.

I have the code to search for the manufacturer claim no

Private Sub Combo196_AfterUpdate()
Dim MyRecSet As Object
Set MyRecSet = Me.Recordset.Clone


MyRecSet.FindFirst "[MANUFACTURER Claim No] = """ & Me.Combo196 & """"


Me.Bookmark = MyRecSet.Bookmark
End Sub

But I need to add a secondary search that will pick up the [dealer code] to then complete the search.

Please Help.

Thanks

 

[TheAceMan1]

How are ya chigley3 . . .

Example assuming [blue]Text[/blue] datatype.

[blue]   Dim rst As DAO.Recordset, Cri As String
   
   Set rst = Me.RecordsetClone
   Cri = "[MANUFACTURER Claim No] = '" & Me.Combo196 & "' AND " & _
         " [dealer code] = '" & Me![dealer code] & "'"
   rst.FindFirst Cri
   
   If rst.NoMatch Then
      [green]'Not Found MsgBox[/green]
   Else
      Me.Bookmark = rst.Bookmark
   End If
   
   Set rst = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chigley3]

Thanks for the code but there still seems to be a problem.

The debugger doesnt like rst.FindFirst Cri.

When I press the combo box, the data comes from a query. For example when I press the combo box I see,

MANUFACTURER Claim No/Dealer Code
248A 10001
249A 75000
250A 75000
248A 75000

The search is to look for the MANUFACTURER Claim No taking into account the Dealer Code. As you see 248A appears twice for two dealers but as it is access only picks the first one it finds. I need to be able to pull up which ever one I want.

I hope this helps, just a bit a code is all I need I'm sure.

Thanks a lot.

C.

 

[chigley3]

please can I get some help on this

 

[TheAceMan1]

chigley3 . . .

Change Cri to:

[blue]   Cri = "[MANUFACTURER Claim No] = '" & Me!Combo196.Column(0) & "' AND " & _
         "[dealer code] = " & Val(Me!Combo196.Column(1))[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chigley3]

hello aceman,

it still doesn't work.

on the debugger, rst.findfirst cri comes up yellow.

It did that with the first code you sent too.

What do you think it is?

thanks a lot

 

[TheAceMan1]

chigley3 . . .

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[chigley3]

I try to check the box and it says

Name conflicts with existing module, project, or object library

I did come up with a long winded solution by associated the search wih the unique field. Once i read the unique field no, I can search that unique field number to pull up the same record.

Just means a little extra work.

Would be nice if this worked though....

Thanks a lot for your help ace