Unbound Form to update multiple tables

**Tom van Stiphout** · Jun 27 '08, 07:22 PM

Re: Unbound Form to update multiple tables

On Sat, 19 Apr 2008 18:23:55 -0400, "Presto" <junkmail@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** · Jun 27 '08, 07:22 PM

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" <no.spam.tom774 4@cox.netwrote in message
news:v1el04dlto hh583v8jm6r6m2c 1laalr4b7@4ax.c om...

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

Unbound Form to update multiple tables

Unbound Form to update multiple tables

Comment

Comment