A Better way the DLookup?

[Blackelise]

Hi again,

I have the following code:

CustAddr1 = DLookup("[InvTo_Add1]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddr2 = DLookup("[InvTo_Add2]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddr3 = DLookup("[InvTo_Add3]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddr4 = DLookup("[InvTo_Add4]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddrPC = DLookup("[InvTo_PostCode]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddrEmail = DLookup("[InvTo_email]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        CustAddrTel = DLookup("[InvTo_Tel]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])
        PTerms = DLookup("[InvTo_PayTerms]", "tblCustAddress", "[InvTo_ID] =" & [EstOrd_CustName])

I think that it's very poor as there are repeated lookups not to mention the possible hit on system resourses.

I'm having to do this as I need to reference two different names and addresses on the one form so can't do it through the Form record Source...

It there a better/faster way of accessing this info from the table? I'm thinking of being able to read the record in and pick from it via code.

Thans in advance.

 

[pbaldy]

I'd do this if the situation fits:

http://www.baldyweb.com/Autofill.htm

Otherwise I'd open a recordset using the same criteria; that would mean you only made 1 trip to the data instead of 8.

 

[Blackelise]

Thanks pbaldy.

'Autofil' looks neat but it would only seem to work when the user selects something from a list. I will use this for user data entry on record creation. The issue that I see would be displaying the customer information as the user move through the records not only displaying info upon selection.

I had a quick look at recordset in the Access help, to me it's impenetrable...they seem to be reading each record for start to EOF and not selecting a specific record.

I would be grateful for some sample recordset coding to lookup a specific record based on a held value (EstOrd_CustName in my code above) to point me in the right direction.

Thanks again.

 

[pbaldy]

This type of thing:

  Dim strSQL                  As String
  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset

  Set db = CurrentDb()
  strSQL = "SELECT Odometer FROM CarRecords WHERE CarNum = " & Me.cboCarNum
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

That will return any records meeting the criteria, and no others.

 

[pr2-eugin]

Hello Andrew, see if this link will be of help in creating and manipulating Recordsets, it explains a very good step by step process of how to use such objects..

http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners

 

[Blackelise]

Thank you both.

@pbaldy I do seem to owe you an apology! When I implemented your autofil for use upon user selection when I moved back and forward the correct information was displayed much to my surprize! Access clearly reruns the combobox control source each time a record is displayed. one learns something everyday!

Well in this case two things; I do have a need to tidy up some other code where using recordset would seem to fit the bill.

Perfect thanks. Have a good weekend!

 

[pbaldy]

Happy to help!