SteveL13
asked on
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.
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.
ASKER
The existence of a previous record would be a RecordID.
What would the code look like for step 1?
What would the code look like for step 1?
<<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("T heDeistina tionTable" )
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
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("T
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
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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