Unbound Form to update multiple tables

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-forms-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

Re: Unbound Form to update multiple tables

On Sat, 19 Apr 2008 18:23:55 -0400, "Presto"
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-forms-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
>

Re: Unbound Form to update multiple tables

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" wrote in message
news:v1el04dltohh583v8jm6r6m2c1laalr4b7@4ax.com...
> On Sat, 19 Apr 2008 18:23:55 -0400, "Presto"
> 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-forms-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
>>