Lookup To Autopopulate value on a form Access 2010

[bkatsma]

I am struggling to find a solution. I have a table that I log all my customer transactions (tbltransactions). This includes all invoices and payments, each being their own transaction record. When a customer sends me a check, they write the invoice number on it. What I am trying to do is create a lookup field on my tbltransactions form which I can enter the invoice number and it will search the transactions table and auto populate the clients name in the ClientID field on the form.

I can run the code without error. The Me.ClientID will equal the correct ClientID based of the Me.PaymentSearchBox, but ClientID field isn't showing any value in form view.

Private Sub frmtranbtnfindacct_Click()
If IsNull(Me.PaymentSearchBox) = False Then
Me.ClientID = DLookup("[ClientID]", "tbltransactions", "Invoice_no =" & Me.PaymentSearchBox)
Me.PaymentSearchBox = Null
If Me.Recordset.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!txtInvoiceSearchBox = Null
    End If
End If
End Sub

 

[alansidman]

Why not put a search (combo box) on your form to do your lookup. It would then bring up the record with the client name.

Look at this video for a similar situation.

Macromedia Flash (SWF) Movie Created by Camtasia Studio 2

Also, it is not advisable to do lookups directly in tables. Read the following on why

The Access Web - The Evils of Lookup Fields in Tables

 

[bkatsma]

I am not using a combobox since I don't want to bring up the specific record tied to that invoice, just the accountid that is associated with it. Each transaction (debit or credit) will be its own transaction. Thank you for the videos and information on lookups on tables. I may have found a solution (still working through the details...) were I created a query with the data I needed, then I Lookup to the query instead of the table.