Best way to write data to a table using data from a form

I'm trying to figure out what is the best way (VBA code) to write data to a table that is not the record source of a form using a command button.  But if the record already exists, then overwrite the data.

For example:

Form field -= txtFirstName to be written to the unbound table to a field named FirstName
Form field -= txtLastName to be written to the unbound table to a field named LastName

etc.
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

omgangIT ManagerCommented:
Are you determining record existence strictly by First Name & Last Name?  Meaning, is there only one record for John Smith?  Can there be two?

Your steps will be
1) Does a record exist in the destination table for [FirstName] = txtFirstName AND [LastName] = txtLastName.  You can use a DLookup function, a DAO RecordSet Find method in VBA, etc.

2) If True/Yes, run and update query:  UPDATE MyTable SET Address = txtAddress, City = txtCity, State = txtState, Zip = txtZip, Phone = txtPhone WHERE [FirstName] = txtFirstName AND [LastName] = txtLastName
3) if False/No, run an insert query:  INSERT INTO MyTable FirstName, LastName, Address, City, State, Zip, Phone VALUES (txtFirstName, txtLastName, txtAddress, etc.)

OM Gang
SteveL13Author Commented:
The existence of a previous record would be a RecordID.

What would the code look like for step 1?
omgangIT ManagerCommented:
<<The existence of a previous record would be a RecordID.>>

That makes it a bit trickier.  If your user is entering data into a form you won't have a RecordID to query with.  Unless that form is bound, in some way, to the destination table (which it isn't as stated in your original Q).

But, to the point.

Dim rs As DAO Recordset

Set rs = CurrentDb.OpenRecordset("TheDeistinationTable")
rs.FindFirst "[LastName] = '" & txtLastName & "' AND [FirstName] = '" & txtFirstName & "'"
If not rs.NoMatch Then
     'update query or VBA code to update the recordset record (might as well since you're here)
Else
     'insert query or VBA code to insert a new record into the recordset
End If

Set rs = Nothing


OM Gang
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PatHartmanCommented:
If you are duplicating data, you might consider normalizing your schema so you don't have to write the same data into multiple tables.

In some cases, you can use a query with a left join that would allow you to add data to two related tables at once so you would simply need to populate the name field twice.  Once in table1 and again in table2.

The AfterUpdate event of LastName
Me.DuplicateLastName = Me.LastName
omgangIT ManagerCommented:
I tested this this morning.
I created a table (MyTable) with RecordID, LastName, FirstName, Address, etc.
I created an unbound form (MyForm) with txtLastName, txtFirstName, txtAddress, etc.
I added a command button (cmdUpdate) to the form.  Here's the code for the button.  This works as you want, i.e. it identifies if the a record exists or not.

OM Gang

Option Compare Database
Option Explicit

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

    Dim rs As DAO.Recordset
    Dim strFind As String, strMsg As String

    Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
    
    strFind = "[LastName] = '" & Me.txtLastName & "' AND [FirstName] = '" & Me.txtFirstName & "'"

    rs.FindFirst strFind
    If Not rs.NoMatch Then
        strMsg = "Record exists for " & Me.txtLastName & ", " & Me.txtFirstName & ".  RecordID = " & rs("RecordID")
    Else
        strMsg = "Record does not exist for " & Me.txtLastName & ", " & Me.txtFirstName
    End If
    
    MsgBox strMsg, vbOKOnly, "Person Exists?"

Exit_cmdUpdate_Click:
    'destroy object variable
    Set rs = Nothing
    Exit Sub
    
Err_cmdUpdate_Click:
    MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error in cmdUpdate procedure"
    Resume Exit_cmdUpdate_Click

End Sub

Open in new window

omgangIT ManagerCommented:
I've updated the code to include UPDATE and INSERT functionality as appropriate.
OM Gang

Option Compare Database
Option Explicit

Private Sub cmdUpdate_Click()
On Error GoTo Err_cmdUpdate_Click

    Dim rs As DAO.Recordset
    Dim strFind As String, strMsg As String

    Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)
    
    strFind = "[LastName] = '" & Me.txtLastName & "' AND [FirstName] = '" & Me.txtFirstName & "'"

    rs.FindFirst strFind
    If Not rs.NoMatch Then
        'record exists so we'll update
        rs.Edit
            'only update address fields
            rs("Address") = Me.txtAddress
            rs("City") = Me.txtCity
            rs("State") = Me.txtState
            rs("Zip") = Me.txtZip
        rs.Update
    
        strMsg = "Record exists for " & Me.txtLastName & ", " & Me.txtFirstName & ".  RecordID = " & rs("RecordID") & " has been updated."
    Else
        'record does not exist so lets we'll create a new one
        rs.AddNew
            rs("LastName") = Me.txtLastName
            rs("FirstName") = Me.txtFirstName
            rs("Address") = Me.txtAddress
            rs("City") = Me.txtCity
            rs("State") = Me.txtState
            rs("Zip") = Me.txtZip
        rs.Update
        
        'get id value from newly created record
        rs.FindFirst strFind
    
        strMsg = "Record does not exist for " & Me.txtLastName & ", " & Me.txtFirstName & ".  A new record has been added.  RecordID = " & rs("RecordID")
    End If
    
    MsgBox strMsg, vbOKOnly, "Person Exists?"

Exit_cmdUpdate_Click:
    'destroy object variable
    Set rs = Nothing
    Exit Sub
    
Err_cmdUpdate_Click:
    MsgBox Err.Number & ", " & Err.Description, vbCritical, "Error in cmdUpdate procedure"
    Resume Exit_cmdUpdate_Click

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.