How to record unbound control to a database

Question

I have a form control (address) that uses Dlookup to call info from a table "database" but the form is bound to table "Tracker". The dlookup is based on another control on the same form - "Name" I need the form to record this dlookup control, along with other controls that are bound to "tracker" as new recordto the table "tracker."

My failed attempts: Using the default value property to assign the recalled data from the dlookup to another text box which would be bound to "tracker" This simply does not work for some reason. Perhaps I am missing something that tells this control "Address" to update upon selecting the correct "name?"

Code:

Private Sub SubmitReferral_Click()
On Error GoTo Err_SubmitReferral_Click


    DoCmd.GoToRecord , , acNewRec

Exit_SubmitReferral_Click:
    Exit Sub

Err_SubmitReferral_Click:
    MsgBox Err.Description
    Resume Exit_SubmitReferral_Click

End Sub

I also tried this - to assign the data - but the data from the dlookup in control "Address1" is not transferring/copying to control "Address2"

Private Sub Combo276_OnUpdate() OnUpdate ([Address2].Value = [Address1].Value)

End Sub

Help or suggestions?

PS - I have tried to Edit per request to be as specific as possible, and to follow proper board etiquette.

You may need more than just a DLOOKUP. If I understand correctly, you have a form which is bound to table named 'tracker'? and when you select a name on that form, you want it to look up the address for that person in table 'database' and add the address to a table named 'tracker'? What should trigger the adding of data? A button click? A double-click on a control? Simply clicking a name (bad idea)? — Wayne G. Dunn
@Fluidity I'm not using code perse- i can post what I have tomorrow though. — user97804
@WayneG.Dunn I'd like it to be triggered by a button click. That I can do, I think. — user97804
(1) Are there fields for the address on the form?; (2) are they bound to the fields in the 'tracker' table? If Yes and Yes, you could place code in the button_click event to do either a Dlookup, or a query. i.e. Me.AddLine1 = DLookUp(.....), then repeat for city, state, zip, etc. Finally, DoCmd.RunCommand acCmdSaveRecord — Wayne G. Dunn

Wayne G. Dunn Wayne G. Dunn · Accepted Answer · 2016-11-30T19:19:34

Still unsure of your field names, etc., but the following is an example you can modify. Change 'tblEmployee' to 'database'.

I must state that if you are just starting out with developing in Access (or VBA) that you should never use names that are reserved words, or that can be misleading. Your table named 'database' is ok if named 'tblDatabase'.

Option Compare Database
option Explicit

Private Sub cmdInsert_Click()
Dim strSQL  As String
Dim i       As Integer

    Debug.Print "cmdInsert; "
    i = MsgBox("Do you want to add 1 row for Employee ID: " & Me.EmpID & " to table 'tracker'?", vbYesNo, "Confirm Add")
    If i = vbNo Then
        Exit Sub
    End If

    DoCmd.SetWarnings True
    strSQL = "INSERT INTO tracker ( FirstName, LastName, Add1, City, St, Zip ) " & _
        "SELECT tblEmployee.FirstName, tblEmployee.LastName, tblEmployee.Add1, tblEmployee.City, tblEmployee.St, tblEmployee.Zip " & _
        "FROM tblEmployee " & _
        "WHERE (((tblEmployee.EmpID)=" & Me.EmpID & "));"

    DoCmd.RunSQL strSQL


End Sub

How to record unbound control to a database

2 Answers