Database lookup from more than 1 table field

[lowtek]

Hello,

I want to perform a database lookup and return a matching recordset from various fields. However, I need the matching recordset to be based on lookup values from 2 fields of data where the 2 are in the same row (recordset). (I have a form where the user enters both criteria in textboxes, and a commandbutton click will return the results from the matching recordset.)

(Example)
Table name: TRACKING
Field 1 name: FILE# (employee numbers; "7235" etc.)
Field 2 name: PERIOD (dates in mm-yy format; "04-02" etc.)
Fields 1 and 2 both may have duplicate entries in them ("7235" may have more than 1 entry, same with "04-02&quot
However, there will be only 1 entry with BOTH "7235" and "04-02" and that is why I need to do a lookup of 2 fields to get the unique recordset. (Hope I explained that clearly.)

Can anyone help? Thank you in advance.

Rey

 

[Isadore]

Rey --

Couple of minor points. Change your convention of naming from all UPPERCASE (TRACKING) to conventional object names, e.g., "tblTracking" using lower case (better on the eyes!). Secondly, avoid spaces and anything that even hints of machine language (FILE# above) in your fields..to avoid compiling and run time errors, etc... Also you might consider using a prefix for Form objects, e.g., "cboMyComboBox", "cmdButton", etc...

Doing a DLookUp on two fields is generally straightforward and your DLookUp would look something like this (declaration of variable will depend on the type of field which you are looking up, number, etc...)...you can add this code on the Afterupdate event of a textbox or on the OnClick of a button, etc....

Dim MyStr As Variant
MyStr = DLookup("[ID]", "TRACKING", "[Field#]= Forms![MyForm]![txtA] AND [PERIOD] = Forms![MyForm]![txtB]&quot
Me![MyResult] = MyStr
Exit Sub

...good luck..but I'd get rid of the "#" first...

 

[lowtek]

Isadore,

THANK YOU!!! <<Caps used intentionally >>
I did exactly as you recommended and followed conventional naming. I had to tweak the code a bit...well, actually, I used your method to get the ID# for the recordset and used DoCmd.GoToRecord to return the recordset:

Private Sub FIND_BUTTON_Click()
Dim MyStr As Variant
MyStr = DLookup(&quot;[ID]&quot;, &quot;tblTracking&quot;, &quot;[FILE]= Forms![frmTracking]![Fsearch] AND [PERIOD] = Forms![frmTracking]![Psearch]&quot
DoCmd.GoToRecord , , acGoTo, MyStr
End Sub

It did exactly what I wanted. I'm no programmer, just someone trying to figure out how to make my work easier using advanced tools that are pretty much over my head. I am very glad that I am lucky enough to receive assistance from helpful & knowledgable people like yourself. Thanks again for giving me directions!

Rey

 

[Isadore]

Iowtek: I like your &quot;slick&quot; line of code there at the end...you may not have years behind you coding but your logic is well placed...I'm only at the technical level myself with this stuff...we'll be seein ya around!