Lookup based on multiple boxes

[kilobyte]

I apologize if this has been adressed. I searched but did not find it. Partially I think because it won't let me use the word 'two' in the search. That's kinda important in this case.

I know how to make a lookup combo box. I used the wizard and copied and pasted the code, actually. What I need is the code to base the lookup from two combos.

For example, the records in my table contain information about different districts on certain dates.

I'd like to lookup the record that matches both the Report Date combo and the District combo.

Thanks in advance.

 

[Pat Hartman]

I don't know in what context you are using the selected values but you probably need something that looks like:

"[ReportDate] = #" & Me.[cboReportDate] & "# AND [District] = '" & Me.[cboDistrict] & "'"

The string will be slightly different if the first combo isn't a date and the second one isn't a text field. The above string will evaluate to:
[ReportDate] = #10/26/2005# AND [District] = 'Georgetown'
with the date and district being what was selected from your combos.

 

[kilobyte]

I think I made a mistake. I said 'lookup' when I meant 'find' and I didn't explain myself very well. I am trying to recreate the third option in the combo box wizard 'I want my form to find a record based off of my combo box' or something to that effect, except using multiple selections to find the appropriate record so the user can change the information stored there. Is that what the code you posted is used for?

 

[Smokers]

I'm basically searching for the same.. So thank you very much for the reply..
I also have two combos but I can't get them to work...
Where do i insert this text ? In the 1st combo, in the 2nd combo or in the textbox that is supposed to display the result ?

 

[Smokers]

Never mind, I figured it out.. My mistake was that I had a query and a source selected at the same time..

 

[kilobyte]

I am still hoping for some help.

I need the form to go to a record based off the values in three controls.

This is the code that the wizard comes up with for a lookup from one combo.

    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AreaId] = " & Str(Nz(Me![Combo8], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

So I am trying to do the same thing except using an expression combing the values of the three controls instead of just the one. Something like the following.

    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ReportTypID] & [RepDateID] & [DistID] = " & 
    Str(Nz(Me![ReportTypID], 0)) & Str(Nz(Me![EntryDate], 0)) & 
    Str(Nz(Me![DistList], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Since I am just guessing here I am not surprized that didn't work. I don't understand the FindFirst syntax and I'm having a hard time finding it in the help file. I also am not sure why it uses the Str and Nz functions. I've been working on this for a while and I would be very appreciative if someone could point me in the right direction.

Many thanks,
Kilobyte

 

[Pat Hartman]

You need to create a string that looks like a compound condition:
"fldA = 'zzz' AND fldB = 123 AND fldZ = #1/1/2005#"

rs.FindFirst "[ReportTypID] = " & Me.ReportTypID & " AND [RepDateID] = " & Me.EntryDate & " AND [DistID] = " & Me.DistList

 

[kilobyte]

Thanks for the quick reply, Pat. I tried entering the code just the way you posted it, but it doesn't work. I think that in this case the problem is definately between me and the screen. When I update the combo it's based off of nothing happens.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ReportTypID] = " & Me.ReportTypID & " AND [RepDateID] = " & Me.EntryDate & " AND [DistID] = " & Me.DistList
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Do you have any idea what I am doing wrong?

 

[kilobyte]

Okay, I am an idiot. I had that in the wrong event.

Now that it is in the AfterUpdate event of the entrydate combo, I get an error.

The changes you requested were unsuccesful because they would create duplicate values in the index, primary key, or relationship.

After that, the debugger highlights the last line of code with the me.Bookmark stuff.

I don't have any clue why.

 

[mechwarrior3]

This thread seems to be exactly what I am looking for. However, trying the code you suggested, Pat, still results in an error.

I have a table with several fields. I want to find a record based off of two of these fields, called MfgLotNum and MixLotNum. Both are text fields. This is what I am trying to do but is not working:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim dummy As String
Dim dummy2 As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenTable)
txtMfgLotNum.SetFocus
dummy = txtMfgLotNum.Text
txtMixLotNum.SetFocus
dummy2 = txtMixLotNum.Text
rs.FindFirst "[MfgLotNum] = '" & dummy & "' AND [MixLotNum] = '" & dummy2 & "'"

When I run this code, I get this error:

Operation is not supported for this type of object.

And after clicking the error box, it highlights the rs.FindFirst line. What is wrong?

 

[Pat Hartman]

There is some conflict between the OpenRecordset and the FindFirst. Try removing the dbOpenTable and let it use the default type.

Also - do not use the .text property. It is ONLY available when the control has focus as you have discovered. Use the .value property or omit the property entirely.

either:
dummy = txtMfgLotNum.Value
dummy2 = txtMixLotNum.Value
or
dummy = txtMfgLotNum
dummy2 = txtMixLotNum
will work fine. Get rid of the .setFocus'

 

[mechwarrior3]

First off, I'd like to say thanks, Pat for your quick response.

Unfortunately, those modifications did not work. I still get the same run-time error. I tried it with the .Value property and without. I even switched the data type of the dummy variables to Variant, with no change. I am using Access 2003.

 

[mechwarrior3]

As a head's up, my problem was with the recordset type, like you said, Pat. I tried using a dbOpenDynaset at my .OpenRecordset command instead of dbOpenTable and it worked. Everything is good now. Thanks again for your help, Pat.