I am making a front end mdb so users can enter new members data.
I can then import this into the master database on the backend and erase the
existing info on the front end to keep the data reasonably secure.
( I borrowed the code from
http://www.databasedev.co.uk/unbound...-add-data.html example)
I have 4 tables that will be updated from this form:
Members
ContactInfo
Payments
History
I can get it to update the Members table with no problem, but it errors out
when we go beyond that.
I marked the spot where it errors out. The Members table gets updated but
not the rest.
Here's a look at the code:
-----------------------------------------------------------------
Private Sub cmdAddNewMember_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstcontact As ADODB.Recordset
Dim strCnn As String
'Check that all fields are filled in
FirstName.SetFocus
If FirstName.Text = "" Then
err = err + 1
MsgBox "Please fill in the First Name box!" & err
End If
LastName.SetFocus
If LastName.Text = "" Then
err = err + 1
MsgBox "Please fill in the Last Name box!" & err
End If
'if no errors insert data
If err < 1 Then
' Open a connection.
Set cnn1 = New ADODB.Connection
mydb = "U:\Apps\Office\AddNewMembers.mdb"
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
cnn1.Open strCnn
' Open Members table.
Set rstMembers = New ADODB.Recordset
rstMembers.CursorType = adOpenKeyset
rstMembers.LockType = adLockOptimistic
rstMembers.Open "Members", cnn1, , , adCmdTable
'get the new Members data
rstMembers.AddNew
rstMembers!FirstName = FirstName
rstMembers!LastName = LastName
rstMembers.Update
' Show the newly added data.
MsgBox "New Members: " & rstMembers!FirstName & "has been
successfully added"
'close connection to the Members table
rstMembers.Close
' *** THIS IS WHERE IT STOPS AND ERRORS OUT AFTER THIS LINE *
' Open ContactInfo table.
Set rstContactInfo = New ADODB.Recordset
rstContactInfo.CursorType = adOpenKeyset
rstContactInfo.LockType = adLockOptimistic
rstContactInfo.Open "ContactInfo", cnn1, , , adCmdTable
'get the new record data
rstContactInfo.AddNew
rstContactInfo!Address1Line1 = Address1Line1
rstContactInfo!Address1Line2 = Address1Line2
rstContactInfo.Update
rstContactInfo.Close
' Open Status table.
Set rstStatus = New ADODB.Recordset
rstStatus.CursorType = adOpenKeyset
rstStatus.LockType = adLockOptimistic
rstStatus.Open "Status", cnn1, , , adCmdTable
'get the new Status data
rstStatus.AddNew
rstStatus!Active = Active
rstStatus!Paid = Paid
rstStatus.Close
' Open Payments table.
Set rstPayments = New ADODB.Recordset
rstPayments.CursorType = adOpenKeyset
rstPayments.LockType = adLockOptimistic
rstPayments.Open "Payments", cnn1, , , adCmdTable
'get the new Payments data
rstPayments.AddNew
rstPayments!PaymentType = PaymentType
rstPayments!AmountPaid = AmountPaid
rstPayments!MembershipType = MembershipType
rstPayments.Close
' Open History table.
Set rstHistory = New ADODB.Recordset
rstHistory.CursorType = adOpenKeyset
rstHistory.LockType = adLockOptimistic
rstHistory.Open "History", cnn1, , , adCmdTable
'get the new History data
rstHistory.AddNew
rstHistory!HistoryYear = Year
rstHistory!SponsoredBy = SponsoredBy
rstHistory!HistoryNotes = HistoryNotes
rstHistory.Close
'Close connection to the database after all updates to the tables
cnn1.Close
Else
MsgBox "An Error has occurred, please check and try again"
End If
End Sub