Unbound Form to update multiple tables

[Presto]

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.Connectio n
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'Check that all fields are filled in
FirstName.SetFo cus
If FirstName.Text = "" Then
err = err + 1
MsgBox "Please fill in the First Name box!" & err
End If

LastName.SetFoc us
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.Connectio n
mydb = "U:\Apps\Office \AddNewMembers. mdb"
strCnn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open Members table.
Set rstMembers = New ADODB.Recordset
rstMembers.Curs orType = adOpenKeyset
rstMembers.Lock Type = adLockOptimisti c
rstMembers.Open "Members", cnn1, , , adCmdTable

'get the new Members data
rstMembers.AddN ew
rstMembers!Firs tName = FirstName
rstMembers!Last Name = LastName
rstMembers.Upda te

' Show the newly added data.
MsgBox "New Members: " & rstMembers!Firs tName & "has been
successfully added"
'close connection to the Members table
rstMembers.Clos e

' *** THIS IS WHERE IT STOPS AND ERRORS OUT AFTER THIS LINE *
' Open ContactInfo table.
Set rstContactInfo = New ADODB.Recordset
rstContactInfo. CursorType = adOpenKeyset
rstContactInfo. LockType = adLockOptimisti c
rstContactInfo. Open "ContactInf o", 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.Curso rType = adOpenKeyset
rstStatus.LockT ype = adLockOptimisti c
rstStatus.Open "Status", cnn1, , , adCmdTable
'get the new Status data
rstStatus.AddNe w
rstStatus!Activ e = Active
rstStatus!Paid = Paid
rstStatus.Close
' Open Payments table.
Set rstPayments = New ADODB.Recordset
rstPayments.Cur sorType = adOpenKeyset
rstPayments.Loc kType = adLockOptimisti c
rstPayments.Ope n "Payments", cnn1, , , adCmdTable
'get the new Payments data
rstPayments.Add New
rstPayments!Pay mentType = PaymentType
rstPayments!Amo untPaid = AmountPaid
rstPayments!Mem bershipType = MembershipType
rstPayments.Clo se

' Open History table.
Set rstHistory = New ADODB.Recordset
rstHistory.Curs orType = adOpenKeyset
rstHistory.Lock Type = adLockOptimisti c
rstHistory.Open "History", cnn1, , , adCmdTable
'get the new History data
rstHistory.AddN ew
rstHistory!Hist oryYear = Year
rstHistory!Spon soredBy = SponsoredBy
rstHistory!Hist oryNotes = HistoryNotes
rstHistory.Clos e

'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

[Tom van Stiphout]

On Sat, 19 Apr 2008 18:23:55 -0400, "Presto" <ju******@prest oweb.net>
wrote:

"It errors out": you probably say that because you get an error
message "An Error has occurred, please check and try again"
but that error handler is HORRIBLE. You as a developer (and us trying
to help) deserve an accurate message, at least Err.Description , and
perhaps also Err.Number.

The only valid reason why the line:
Set rstContactInfo = New ADODB.Recordset
would fail is if the recordset variable rstContactInfo was not
declared, as it appears to be.
Add a line:
Dim rstContactInfo As ADODB.Recordset
to the top of the procedure.

There are many other things not to like about your code, but they are
beyond the scope of your immediate question.

-Tom.

>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.Connectio n
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'Check that all fields are filled in
FirstName.SetF ocus
If FirstName.Text = "" Then
err = err + 1
MsgBox "Please fill in the First Name box!" & err
End If

LastName.SetFo cus
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.Connectio n
mydb = "U:\Apps\Office \AddNewMembers. mdb"
strCnn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open Members table.
Set rstMembers = New ADODB.Recordset
rstMembers.Curs orType = adOpenKeyset
rstMembers.Lock Type = adLockOptimisti c
rstMembers.Open "Members", cnn1, , , adCmdTable

'get the new Members data
rstMembers.Add New
rstMembers!Firs tName = FirstName
rstMembers!Last Name = LastName
rstMembers.Upda te

' Show the newly added data.
MsgBox "New Members: " & rstMembers!Firs tName & "has been
successfully added"
'close connection to the Members table
rstMembers.Clo se

' *** THIS IS WHERE IT STOPS AND ERRORS OUT AFTER THIS LINE *
' Open ContactInfo table.
Set rstContactInfo = New ADODB.Recordset
rstContactInfo. CursorType = adOpenKeyset
rstContactInfo. LockType = adLockOptimisti c
rstContactInfo. Open "ContactInf o", 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.Curso rType = adOpenKeyset
rstStatus.LockT ype = adLockOptimisti c
rstStatus.Open "Status", cnn1, , , adCmdTable
'get the new Status data
rstStatus.AddN ew
rstStatus!Activ e = Active
rstStatus!Paid = Paid
rstStatus.Clos e
' Open Payments table.
Set rstPayments = New ADODB.Recordset
rstPayments.Cur sorType = adOpenKeyset
rstPayments.Loc kType = adLockOptimisti c
rstPayments.Ope n "Payments", cnn1, , , adCmdTable
'get the new Payments data
rstPayments.Ad dNew
rstPayments!Pay mentType = PaymentType
rstPayments!Amo untPaid = AmountPaid
rstPayments!Mem bershipType = MembershipType
rstPayments.Cl ose

' Open History table.
Set rstHistory = New ADODB.Recordset
rstHistory.Curs orType = adOpenKeyset
rstHistory.Lock Type = adLockOptimisti c
rstHistory.Open "History", cnn1, , , adCmdTable
'get the new History data
rstHistory.Add New
rstHistory!Hist oryYear = Year
rstHistory!Spon soredBy = SponsoredBy
rstHistory!Hist oryNotes = HistoryNotes
rstHistory.Clo se

'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

[Presto]

Sorry if the code seems "horrible" but as I stated on the first post, this
was borrowed from an example that exists online and I'm trying to convert it
to fit my db. I'm not a code master.... but I am trying to do as much as I
can.
If there is a better way to get all the fields to update to the correct
tables that would be great. Please post a url to an example db..

"Tom van Stiphout" <no************ *@cox.netwrote in message
news:v1******** *************** *********@4ax.c om...

On Sat, 19 Apr 2008 18:23:55 -0400, "Presto" <ju******@prest oweb.net>
wrote:

"It errors out": you probably say that because you get an error
message "An Error has occurred, please check and try again"
but that error handler is HORRIBLE. You as a developer (and us trying
to help) deserve an accurate message, at least Err.Description , and
perhaps also Err.Number.

The only valid reason why the line:
Set rstContactInfo = New ADODB.Recordset
would fail is if the recordset variable rstContactInfo was not
declared, as it appears to be.
Add a line:
Dim rstContactInfo As ADODB.Recordset
to the top of the procedure.

There are many other things not to like about your code, but they are
beyond the scope of your immediate question.

-Tom.

>>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.Connectio n
Dim rstcontact As ADODB.Recordset
Dim strCnn As String

'Check that all fields are filled in
FirstName.Set Focus
If FirstName.Text = "" Then
err = err + 1
MsgBox "Please fill in the First Name box!" & err
End If

LastName.SetF ocus
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.Connectio n
mydb = "U:\Apps\Office \AddNewMembers. mdb"
strCnn = "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & mydb
cnn1.Open strCnn

' Open Members table.
Set rstMembers = New ADODB.Recordset
rstMembers.Curs orType = adOpenKeyset
rstMembers.Lock Type = adLockOptimisti c
rstMembers.Open "Members", cnn1, , , adCmdTable

'get the new Members data
rstMembers.Ad dNew
rstMembers!Firs tName = FirstName
rstMembers!Last Name = LastName
rstMembers.Upda te

' Show the newly added data.
MsgBox "New Members: " & rstMembers!Firs tName & "has been
successfull y added"
'close connection to the Members table
rstMembers.Cl ose

' *** THIS IS WHERE IT STOPS AND ERRORS OUT AFTER THIS LINE *
' Open ContactInfo table.
Set rstContactInfo = New ADODB.Recordset
rstContactInfo. CursorType = adOpenKeyset
rstContactInfo. LockType = adLockOptimisti c
rstContactInfo. Open "ContactInf o", cnn1, , , adCmdTable
'get the new record data
rstContactInf o.AddNew
rstContactInfo! Address1Line1 = Address1Line1
rstContactInfo! Address1Line2 = Address1Line2
rstContactInfo. Update

rstContactInf o.Close

' Open Status table.
Set rstStatus = New ADODB.Recordset
rstStatus.Curso rType = adOpenKeyset
rstStatus.LockT ype = adLockOptimisti c
rstStatus.Open "Status", cnn1, , , adCmdTable
'get the new Status data
rstStatus.Add New
rstStatus!Activ e = Active
rstStatus!Paid = Paid
rstStatus.Clo se
' Open Payments table.
Set rstPayments = New ADODB.Recordset
rstPayments.Cur sorType = adOpenKeyset
rstPayments.Loc kType = adLockOptimisti c
rstPayments.Ope n "Payments", cnn1, , , adCmdTable
'get the new Payments data
rstPayments.A ddNew
rstPayments!Pay mentType = PaymentType
rstPayments!Amo untPaid = AmountPaid
rstPayments!Mem bershipType = MembershipType
rstPayments.C lose

' Open History table.
Set rstHistory = New ADODB.Recordset
rstHistory.Curs orType = adOpenKeyset
rstHistory.Lock Type = adLockOptimisti c
rstHistory.Open "History", cnn1, , , adCmdTable
'get the new History data
rstHistory.Ad dNew
rstHistory!Hist oryYear = Year
rstHistory!Spon soredBy = SponsoredBy
rstHistory!Hist oryNotes = HistoryNotes
rstHistory.Cl ose

'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