J G
asked on
Add records to a form to a table
I have a 5 field unbound form, I want to be able to click on a "add record" button that will create a record in a table and insert the values from the form into the table as a new record.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The power of Access lies in its bound forms. Why would you want to write code to do something that Access does for free?
ASKER
I was thinking the VB script because I will want to add records to multiple tables at once.
I assume there is a genuine purpose in using unbound form.
DoCmd.RunSQL "insert into tbl(field1, field2, field3, field4, field5) values ('" & txt1 & "','" & txt2 & "','" & txt3 & "','" & txt4 & "','" & txt5 & "')" to multiple tables at once.A justification to my assumption.
DoCmd.RunSQL "insert into tbl1(field1, field2, field3) values ('" & txt1 & "','" & txt2 & "','" & txt3 & "')"
DoCmd.RunSQL "insert into tbl2( field4, field5) values ('" & txt4 & "','" & txt5 & "')"
<I was thinking the VB script because I will want to add records to multiple tables at once. >
then why didn't you mentioned that in your original post?
then why didn't you mentioned that in your original post?
ASKER
I haven't integrated the new tables into the database yet, just thinking ahead.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are talking about duplicating the data loading the same data into multiple tables, that is yet another poor practice. We can just give you bullets and help you load your gun or you can give us some idea of where you are going with this and we might be able to offer some better practice solutions.
You might need to check whether there already is a record in the table, in which case you might need to update it instead of adding a new (duplicate) record. Or delete the old record and then add the new one. Here is some sample code:
'Add new record or update existing record in tblAudits as needed
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblAudits", dbOpenDynaset)
strSSN = Me![SSN]
dteRecord = Me![RecordDate]
blnANJ = Nz(Me![ANJ])
strSystem = Nz(Me![System])
lngID = Me![AuditID]
lngEmployeeID = Me![EmployeeID]
lngManagerID = Me![ManagerID]
strSearch = "[AuditID] = " & lngID
rst.FindFirst strSearch
If rst.NoMatch = True Then
rst.AddNew
Else
rst.Edit
End If
rst![SSN] = strSSN
rst![RecordDate] = dteRecord
rst![ANJ] = blnANJ
rst![System] = strSystem
rst![EmployeeID] = lngEmployeeID
rst![ManagerID] = lngManagerID
rst.Update
rst.Close
line 12 should read rs.update