VBA

November 5th, 2009, 05:01 AM

Hi
My previous post about loop through datasets probably was not that clear

What I am trying to do is perform the same action as Excel Lookup function in VBA.

Where a field is searched and an associated field returned.

For example if a text box in a form shows a certain value that value is searched and if found an associated value returned to another text box.

Hope that might be clearer

Regards

allenm · November 9th, 2009, 07:18 PM

Let's assume you want to find [Your_Value] in MatchingField_Name and wish to return ReturnField_Name. You would set the text box Control Source to:
=GetFieldValue([Your_Value],"MatchingField_Name","ReturnField_Name")

Notice that [Your_Value] comes from the current bound table for the form which has a field named "Your_Value". Also notice I'm passing two field names in quotes without the [] because I'm passing the field name itself and not it's value.

Next, place this code in the form's code mod:

Code:

Private Function GetFieldValue(sPassed As String, sSource As String, sTarget As String)

'Retreives first instance in DB of sTarget field where sPassed is value matching sSource in table
  Dim rsTable As Recordset
  Set rsTable = CurrentDb.OpenRecordset("Select * From Table1 Where " & sSource & " = """ _
    & sPassed & """")
  If rsTable.RecordCount > 0 _
    Then GetFieldValue = rsTable(sTarget).Value _
    Else GetFieldValue = ""
  rsTable.Close

End Function

You didn't give any field names or table names so I am using 'Table1' as the lookup table name.

Hope this helps point in the right direction.

SerranoG · November 25th, 2009, 10:33 AM

Have you looked into the DLookUp() function?

November 25th, 2009, 11:56 AM

Hello Greg
My apologies for not getting back sooner, I have been heavily involved in another problem.
I did get the Dllookup to work and it does do what I needed

Thanks very much for you assistance in this. I will have alook at your comments on the other post this evening

Best regards