Form to Update Two Database Tables with One-To-Many Relationship

Hello everyone. I apologize for this newbie question but after searching the
'net I am at a loss. I also posted this request to
microsoft.public.frontpage.programming and hope this is okay.

I have a database with two tables. The first, table 1, is for the user
information and the second, table 2, is for the type of cars they have - so
one user can have multiple cars. These are linked with a one-to-many
relationship by one field:

Table1 - Users
--------
ID (autonumber)
Field1 (text)

Table2 - Cars
--------
TableID (autonumber)
Table1ID (number, linked to ID of table 1)

I have a query that is used to display the records appropriately and this
also works great on the ASP page.

I need assistance creating a submission form that will allow the data to be
entered properly - i.e. one user with multiple cars. Has anyone done
something like this in FrontPage? I assume this would be a custom data entry
form since FrontPage data for wizards will only allow updating one table.
Any code samples would be greatly appreciated.

Thank you in advance.

Re: Form to Update Two Database Tables with One-To-Many Relationship

Here's a code sample from my sample Web database that assumes a user
may have changed the invoice number and thus updates the tblInv
(invoice) fields in the database as well as any related tblInvDetail
(invoice detail) records:

' Begin transaction as updating multiple tables.
objConn.BeginTrans

' Open rs to add.
If InvIDOrig = 0 Then
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblInv "
strSQL = strSQL & "WHERE (1<>1)"

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (1=CursorType of adOpenKeyset in case ever want to get an
autonumber of new rec,
' 3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, 1, 3

' Add new.
objRS.AddNew
Else ' Open rs to edit.
strSQL = "SELECT * "
strSQL = strSQL & "FROM tblInv "
' No quote for numeric fld.
strSQL = strSQL & "WHERE (InvID=" & jpsvbFixSQL(InvIDOrig) & ")"

' Open rs.
Set objRS = Server.CreateObject("ADODB.Recordset")
' (3=LockType of adLockOptimistic because updating.)
objRS.Open strSQL, objConn, , 3

' Move to 1st (and probably only) rec.
objRS.MoveFirst
End If

' Set var.
objRS("InvUserID") = jpsvbBlankToNull(InvUserID)
objRS("InvCustID") = jpsvbBlankToNull(InvCustID)
objRS("InvID") = jpsvbBlankToNull(InvID)
objRS("InvDt") = jpsvbBlankToNull(InvDt)
objRS("InvIsComp") = jpsvbBlankToNull(InvIsComp)
objRS("InvSubtotal") = jpsvbBlankToNull(InvSubtotal)
objRS("InvSalesTax") = jpsvbBlankToNull(InvSalesTax)
objRS("InvShipping") = jpsvbBlankToNull(InvShipping)
objRS("InvTotal") = jpsvbBlankToNull(InvTotal)
objRS("InvNote") = jpsvbBlankToNull(InvNote)

' Set rec tracking var.
Call SetRecTrack(objRS, "Inv")

' Update.
objRS.Update

' Close rs.
objRS.Close
Set objRS = Nothing

' If InvID was changed, then update any InvDetail recs too.
If (InvID <> InvIDOrig) And (InvIDOrig <> 0) Then
' Update child table recs' InvDetailInvID.
' Set sql.
' No quote for numeric fld.
strSQL = "UPDATE tblInvDetail SET InvDetailInvID=" & InvID & ", "
strSQL = strSQL & SetRecTrackSQL("InvDetail")
' No quote for numeric fld.
strSQL = strSQL & "WHERE (InvDetailInvID=" & jpsvbFixSQL(InvIDOrig)
& ")"

' Update.
objConn.Execute strSQL
End If

' End transaction.
objConn.CommitTrans

Best regards,
-Paul
www.Bullschmidt.com - Freelance Web and Database Developer
www.Bullschmidt.com/DevTip.asp - ASP Design Tips

Re: Form to Update Two Database Tables with One-To-Many Relationship

Wow, I think I just realized that I might be in way over my head. I think I
see what your code does but not sure how it would apply to what I'm trying
to do. Yours seems more like a one-to-one relationship reference whereas
mine needs to be one-to-many; one car owner can own and enter multiple cars.

Let me clarify if I can...

I have two tables in an Access database, called Table1 and Table2. Table1 is
the "contact information" for the car owner. Table2 is the car data (make,
model, etc.) One single owner can have more than one car. The tables are
linked via a "seed record" in a one-to-many fashion, and full
(update/delete) referential integrity has been enforced. Table1 has an ID
field which is autonumber, and Table2 has a number field, and these two
fields are tied together for the one-to-many relationship. On the web-based
ASP form, I need to first collect the user's contact information then
provide a method for allowing him/her to submit multiple cars which he/she
owns. I don't care if this is done on one page or two but prefer one.

I have seen a few links online to similar issues, but I cannot figure out
how to bridge the gap and make either work:

http://www.frontpagewebmaster.com/m-299266/tm.htm

http://www.outfront.net/spooky/adv_new_id.htm

http://www.builderau.com.au/architect/dotnet/soa/Coding_a_On e_to_Many_Form_with_ASP_NET/0,39024710,39129443,00.htm

I am guessing that I will need to add the owner's contact info on the first
page, submit that to Table1, grab the ID of the owner's record as well as
the first car's data and apply that to Table2 somehow, then repeat somehow
for additional cars.

The ideal picture would be to have everything on one form, but again, I have
no clue as to where to start.

Maybe I need to hire an ASP programmer?

Thanks,
Tony

wrote in message
news:1137443048.672245.211510@g43g2000cwa.googlegroups.com.. .
> Here's a code sample from my sample Web database that assumes a user
> may have changed the invoice number and thus updates the tblInv
> (invoice) fields in the database as well as any related tblInvDetail
> (invoice detail) records:
>
> ' Begin transaction as updating multiple tables.
> objConn.BeginTrans
>
> ' Open rs to add.
> If InvIDOrig = 0 Then
> strSQL = "SELECT * "
> strSQL = strSQL & "FROM tblInv "
> strSQL = strSQL & "WHERE (1<>1)"
>
> ' Open rs.
> Set objRS = Server.CreateObject("ADODB.Recordset")
> ' (1=CursorType of adOpenKeyset in case ever want to get an
> autonumber of new rec,
> ' 3=LockType of adLockOptimistic because updating.)
> objRS.Open strSQL, objConn, 1, 3
>
> ' Add new.
> objRS.AddNew
> Else ' Open rs to edit.
> strSQL = "SELECT * "
> strSQL = strSQL & "FROM tblInv "
> ' No quote for numeric fld.
> strSQL = strSQL & "WHERE (InvID=" & jpsvbFixSQL(InvIDOrig) & ")"
>
> ' Open rs.
> Set objRS = Server.CreateObject("ADODB.Recordset")
> ' (3=LockType of adLockOptimistic because updating.)
> objRS.Open strSQL, objConn, , 3
>
> ' Move to 1st (and probably only) rec.
> objRS.MoveFirst
> End If
>
> ' Set var.
> objRS("InvUserID") = jpsvbBlankToNull(InvUserID)
> objRS("InvCustID") = jpsvbBlankToNull(InvCustID)
> objRS("InvID") = jpsvbBlankToNull(InvID)
> objRS("InvDt") = jpsvbBlankToNull(InvDt)
> objRS("InvIsComp") = jpsvbBlankToNull(InvIsComp)
> objRS("InvSubtotal") = jpsvbBlankToNull(InvSubtotal)
> objRS("InvSalesTax") = jpsvbBlankToNull(InvSalesTax)
> objRS("InvShipping") = jpsvbBlankToNull(InvShipping)
> objRS("InvTotal") = jpsvbBlankToNull(InvTotal)
> objRS("InvNote") = jpsvbBlankToNull(InvNote)
>
> ' Set rec tracking var.
> Call SetRecTrack(objRS, "Inv")
>
> ' Update.
> objRS.Update
>
> ' Close rs.
> objRS.Close
> Set objRS = Nothing
>
> ' If InvID was changed, then update any InvDetail recs too.
> If (InvID <> InvIDOrig) And (InvIDOrig <> 0) Then
> ' Update child table recs' InvDetailInvID.
> ' Set sql.
> ' No quote for numeric fld.
> strSQL = "UPDATE tblInvDetail SET InvDetailInvID=" & InvID & ", "
> strSQL = strSQL & SetRecTrackSQL("InvDetail")
> ' No quote for numeric fld.
> strSQL = strSQL & "WHERE (InvDetailInvID=" & jpsvbFixSQL(InvIDOrig)
> & ")"
>
> ' Update.
> objConn.Execute strSQL
> End If
>
> ' End transaction.
> objConn.CommitTrans
>
> Best regards,
> -Paul
> www.Bullschmidt.com - Freelance Web and Database Developer
> www.Bullschmidt.com/DevTip.asp - ASP Design Tips
>

Re: Form to Update Two Database Tables with One-To-Many Relationship

"Tony Scarola" wrote in message
news:OHmxzswGGHA.1288@TK2MSFTNGP09.phx.gbl...
> Wow, I think I just realized that I might be in way over my head. I think
I
> see what your code does but not sure how it would apply to what I'm trying
> to do. Yours seems more like a one-to-one relationship reference whereas
> mine needs to be one-to-many; one car owner can own and enter multiple
cars.
>
> Let me clarify if I can...
>
> I have two tables in an Access database, called Table1 and Table2. Table1
is
> the "contact information" for the car owner. Table2 is the car data (make,
> model, etc.) One single owner can have more than one car. The tables are
> linked via a "seed record" in a one-to-many fashion, and full
> (update/delete) referential integrity has been enforced. Table1 has an ID
> field which is autonumber, and Table2 has a number field, and these two
> fields are tied together for the one-to-many relationship. On the
web-based
> ASP form, I need to first collect the user's contact information then
> provide a method for allowing him/her to submit multiple cars which he/she
> owns. I don't care if this is done on one page or two but prefer one.
>
> I have seen a few links online to similar issues, but I cannot figure out
> how to bridge the gap and make either work:
>
> http://www.frontpagewebmaster.com/m-299266/tm.htm
>
> http://www.outfront.net/spooky/adv_new_id.htm
>
>
http://www.builderau.com.au/architect/dotnet/soa/Coding_a_On e_to_Many_Form_w
ith_ASP_NET/0,39024710,39129443,00.htm
>
> I am guessing that I will need to add the owner's contact info on the
first
> page, submit that to Table1, grab the ID of the owner's record as well as
> the first car's data and apply that to Table2 somehow, then repeat somehow
> for additional cars.
>
> The ideal picture would be to have everything on one form, but again, I
have
> no clue as to where to start.
>
> Maybe I need to hire an ASP programmer?

Okay, I'm available -- but before we go there...

Is that what you're looking for? Watch for word-wrap:

<% @Language="VBScript" %>
<% Option Explicit
'***
' This ASP (Active Server Pages) Program does the following:
' 1) Provides for the entry of an Owner and their Cars.
'***
'*
'* Declare Constants
'*
Const cASP = "CarOwner.asp"
Const cMDB = "CarOwner.mdb"
Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
'*
'* Declare Globals
'*
Dim strOWN
strOWN = Request.Form("Owner")
'*
'* Call CarOwner()
'*
If strOWN <> "" Then
Call CarOwner()
Response.Write "Owner " & strOWN & " was added.  "
Response.End
End If

Sub CarOwner()
'*
'* Declare Constants
'*
Const adCmdTable = &H0002
Const adLockOptimistic = 3
Const adOpenKeySet = 1
'*
'* Declare Variables
'*
Dim intCAR
Dim strRF1
Dim strRF2
'*
'* Declare Objects
'*
Dim objADO
Set objADO = Server.CreateObject("ADODB.Connection")
objADO.Open cDSN & Server.MapPath(cMDB)
Dim objRS1
Set objRS1 = Server.CreateObject("ADODB.Recordset")
objRS1.Open "Owner", objADO, adOpenKeyset, adLockOptimistic,
adCmdTable
Dim objRS2
Set objRS2 = Server.CreateObject("ADODB.Recordset")
objRS2.Open "Car", objADO, adOpenKeyset, adLockOptimistic,
adCmdTable
'*
'* AddNew Owner
'*
objRS1.AddNew
objRS1("OwnerName").Value = strOWN
objRS1.Update
'*
'* AddNew Cars
'*
For intCAR = 1 To 4
strRF1 = Request.Form("CarMake" & intCAR)
strRF2 = Request.Form("CarYear" & intCAR)
If strRF1 <> "" Then
objRS2.AddNew
objRS2("CarMake").Value = strRF1
objRS2("CarYear").Value = strRF2
objRS2("OwnerID").Value = objRS1("OwnerID").Value
objRS2.Update
End If
Next
'*
'* Destroy Objects
'*
objRS1.Close
Set objRS1 = Nothing
objRS2.Close
Set objRS2 = Nothing
objADO.Close
Set objADO = Nothing
End Sub
%>

Re: Form to Update Two Database Tables with One-To-Many Relationship

I will let you know how it turns out. Thank you.

If I can't get it working, hopefully we'll be in touch.

"McKirahan" wrote in message
news:95mdnRhVdNb6LVHeRVn-rw@comcast.com...
> "Tony Scarola" wrote in message
> news:OHmxzswGGHA.1288@TK2MSFTNGP09.phx.gbl...
>> Wow, I think I just realized that I might be in way over my head. I think
> I
>> see what your code does but not sure how it would apply to what I'm
>> trying
>> to do. Yours seems more like a one-to-one relationship reference whereas
>> mine needs to be one-to-many; one car owner can own and enter multiple
> cars.
>>
>> Let me clarify if I can...
>>
>> I have two tables in an Access database, called Table1 and Table2. Table1
> is
>> the "contact information" for the car owner. Table2 is the car data
>> (make,
>> model, etc.) One single owner can have more than one car. The tables are
>> linked via a "seed record" in a one-to-many fashion, and full
>> (update/delete) referential integrity has been enforced. Table1 has an ID
>> field which is autonumber, and Table2 has a number field, and these two
>> fields are tied together for the one-to-many relationship. On the
> web-based
>> ASP form, I need to first collect the user's contact information then
>> provide a method for allowing him/her to submit multiple cars which
>> he/she
>> owns. I don't care if this is done on one page or two but prefer one.
>>
>> I have seen a few links online to similar issues, but I cannot figure out
>> how to bridge the gap and make either work:
>>
>> http://www.frontpagewebmaster.com/m-299266/tm.htm
>>
>> http://www.outfront.net/spooky/adv_new_id.htm
>>
>>
> http://www.builderau.com.au/architect/dotnet/soa/Coding_a_On e_to_Many_Form_w
> ith_ASP_NET/0,39024710,39129443,00.htm
>>
>> I am guessing that I will need to add the owner's contact info on the
> first
>> page, submit that to Table1, grab the ID of the owner's record as well as
>> the first car's data and apply that to Table2 somehow, then repeat
>> somehow
>> for additional cars.
>>
>> The ideal picture would be to have everything on one form, but again, I
> have
>> no clue as to where to start.
>>
>> Maybe I need to hire an ASP programmer?
>
> Okay, I'm available -- but before we go there...
>
> Is that what you're looking for? Watch for word-wrap:
>
> <% @Language="VBScript" %>
> <% Option Explicit
> '***
> ' This ASP (Active Server Pages) Program does the following:
> ' 1) Provides for the entry of an Owner and their Cars.
> '***
> '*
> '* Declare Constants
> '*
> Const cASP = "CarOwner.asp"
> Const cMDB = "CarOwner.mdb"
> Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
> '*
> '* Declare Globals
> '*
> Dim strOWN
> strOWN = Request.Form("Owner")
> '*
> '* Call CarOwner()
> '*
> If strOWN <> "" Then
> Call CarOwner()
> Response.Write "Owner " & strOWN & " was added.  "
> Response.End
> End If
>
> Sub CarOwner()
> '*
> '* Declare Constants
> '*
> Const adCmdTable = &H0002
> Const adLockOptimistic = 3
> Const adOpenKeySet = 1
> '*
> '* Declare Variables
> '*
> Dim intCAR
> Dim strRF1
> Dim strRF2
> '*
> '* Declare Objects
> '*
> Dim objADO
> Set objADO = Server.CreateObject("ADODB.Connection")
> objADO.Open cDSN & Server.MapPath(cMDB)
> Dim objRS1
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
> objRS1.Open "Owner", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> Dim objRS2
> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> objRS2.Open "Car", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> '*
> '* AddNew Owner
> '*
> objRS1.AddNew
> objRS1("OwnerName").Value = strOWN
> objRS1.Update
> '*
> '* AddNew Cars
> '*
> For intCAR = 1 To 4
> strRF1 = Request.Form("CarMake" & intCAR)
> strRF2 = Request.Form("CarYear" & intCAR)
> If strRF1 <> "" Then
> objRS2.AddNew
> objRS2("CarMake").Value = strRF1
> objRS2("CarYear").Value = strRF2
> objRS2("OwnerID").Value = objRS1("OwnerID").Value
> objRS2.Update
> End If
> Next
> '*
> '* Destroy Objects
> '*
> objRS1.Close
> Set objRS1 = Nothing
> objRS2.Close
> Set objRS2 = Nothing
> objADO.Close
> Set objADO = Nothing
> End Sub
> %>
>
>
>
>
>
>
>
>

Re: Form to Update Two Database Tables with One-To-Many Relationship

It works like a charm. Thank you very much. I will send the URL once
completed.

"McKirahan" wrote in message
news:95mdnRhVdNb6LVHeRVn-rw@comcast.com...
> "Tony Scarola" wrote in message
> news:OHmxzswGGHA.1288@TK2MSFTNGP09.phx.gbl...
>> Wow, I think I just realized that I might be in way over my head. I think
> I
>> see what your code does but not sure how it would apply to what I'm
>> trying
>> to do. Yours seems more like a one-to-one relationship reference whereas
>> mine needs to be one-to-many; one car owner can own and enter multiple
> cars.
>>
>> Let me clarify if I can...
>>
>> I have two tables in an Access database, called Table1 and Table2. Table1
> is
>> the "contact information" for the car owner. Table2 is the car data
>> (make,
>> model, etc.) One single owner can have more than one car. The tables are
>> linked via a "seed record" in a one-to-many fashion, and full
>> (update/delete) referential integrity has been enforced. Table1 has an ID
>> field which is autonumber, and Table2 has a number field, and these two
>> fields are tied together for the one-to-many relationship. On the
> web-based
>> ASP form, I need to first collect the user's contact information then
>> provide a method for allowing him/her to submit multiple cars which
>> he/she
>> owns. I don't care if this is done on one page or two but prefer one.
>>
>> I have seen a few links online to similar issues, but I cannot figure out
>> how to bridge the gap and make either work:
>>
>> http://www.frontpagewebmaster.com/m-299266/tm.htm
>>
>> http://www.outfront.net/spooky/adv_new_id.htm
>>
>>
> http://www.builderau.com.au/architect/dotnet/soa/Coding_a_On e_to_Many_Form_w
> ith_ASP_NET/0,39024710,39129443,00.htm
>>
>> I am guessing that I will need to add the owner's contact info on the
> first
>> page, submit that to Table1, grab the ID of the owner's record as well as
>> the first car's data and apply that to Table2 somehow, then repeat
>> somehow
>> for additional cars.
>>
>> The ideal picture would be to have everything on one form, but again, I
> have
>> no clue as to where to start.
>>
>> Maybe I need to hire an ASP programmer?
>
> Okay, I'm available -- but before we go there...
>
> Is that what you're looking for? Watch for word-wrap:
>
> <% @Language="VBScript" %>
> <% Option Explicit
> '***
> ' This ASP (Active Server Pages) Program does the following:
> ' 1) Provides for the entry of an Owner and their Cars.
> '***
> '*
> '* Declare Constants
> '*
> Const cASP = "CarOwner.asp"
> Const cMDB = "CarOwner.mdb"
> Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
> '*
> '* Declare Globals
> '*
> Dim strOWN
> strOWN = Request.Form("Owner")
> '*
> '* Call CarOwner()
> '*
> If strOWN <> "" Then
> Call CarOwner()
> Response.Write "Owner " & strOWN & " was added.  "
> Response.End
> End If
>
> Sub CarOwner()
> '*
> '* Declare Constants
> '*
> Const adCmdTable = &H0002
> Const adLockOptimistic = 3
> Const adOpenKeySet = 1
> '*
> '* Declare Variables
> '*
> Dim intCAR
> Dim strRF1
> Dim strRF2
> '*
> '* Declare Objects
> '*
> Dim objADO
> Set objADO = Server.CreateObject("ADODB.Connection")
> objADO.Open cDSN & Server.MapPath(cMDB)
> Dim objRS1
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
> objRS1.Open "Owner", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> Dim objRS2
> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> objRS2.Open "Car", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> '*
> '* AddNew Owner
> '*
> objRS1.AddNew
> objRS1("OwnerName").Value = strOWN
> objRS1.Update
> '*
> '* AddNew Cars
> '*
> For intCAR = 1 To 4
> strRF1 = Request.Form("CarMake" & intCAR)
> strRF2 = Request.Form("CarYear" & intCAR)
> If strRF1 <> "" Then
> objRS2.AddNew
> objRS2("CarMake").Value = strRF1
> objRS2("CarYear").Value = strRF2
> objRS2("OwnerID").Value = objRS1("OwnerID").Value
> objRS2.Update
> End If
> Next
> '*
> '* Destroy Objects
> '*
> objRS1.Close
> Set objRS1 = Nothing
> objRS2.Close
> Set objRS2 = Nothing
> objADO.Close
> Set objADO = Nothing
> End Sub
> %>
>
>
>
>
>
>
>
>

Re: Form to Update Two Database Tables with One-To-Many Relationship

McKirahan,

See completed project here: http://www.pvregister.com

This is for a Volvo PV Register site.

Now I just need to figure out how to generate an email when someone adds an
entry... and perform some field validation. I had to remove the FrontPage
validation code because I couldn't figure how to get both to work. Currently
I have the results being displayed on the main page, however, this display
is pulled from a query and only displayed if the "AddToWeb" field is
checked - a manual option. So, now without the email, I have to check it
once a day to see if there are any new entries. No big deal but could be
better!

The hunt for code is back on!

Thanks again and take care.

Tony


"McKirahan" wrote in message
news:95mdnRhVdNb6LVHeRVn-rw@comcast.com...
> "Tony Scarola" wrote in message
> news:OHmxzswGGHA.1288@TK2MSFTNGP09.phx.gbl...
>> Wow, I think I just realized that I might be in way over my head. I think
> I
>> see what your code does but not sure how it would apply to what I'm
>> trying
>> to do. Yours seems more like a one-to-one relationship reference whereas
>> mine needs to be one-to-many; one car owner can own and enter multiple
> cars.
>>
>> Let me clarify if I can...
>>
>> I have two tables in an Access database, called Table1 and Table2. Table1
> is
>> the "contact information" for the car owner. Table2 is the car data
>> (make,
>> model, etc.) One single owner can have more than one car. The tables are
>> linked via a "seed record" in a one-to-many fashion, and full
>> (update/delete) referential integrity has been enforced. Table1 has an ID
>> field which is autonumber, and Table2 has a number field, and these two
>> fields are tied together for the one-to-many relationship. On the
> web-based
>> ASP form, I need to first collect the user's contact information then
>> provide a method for allowing him/her to submit multiple cars which
>> he/she
>> owns. I don't care if this is done on one page or two but prefer one.
>>
>> I have seen a few links online to similar issues, but I cannot figure out
>> how to bridge the gap and make either work:
>>
>> http://www.frontpagewebmaster.com/m-299266/tm.htm
>>
>> http://www.outfront.net/spooky/adv_new_id.htm
>>
>>
> http://www.builderau.com.au/architect/dotnet/soa/Coding_a_On e_to_Many_Form_w
> ith_ASP_NET/0,39024710,39129443,00.htm
>>
>> I am guessing that I will need to add the owner's contact info on the
> first
>> page, submit that to Table1, grab the ID of the owner's record as well as
>> the first car's data and apply that to Table2 somehow, then repeat
>> somehow
>> for additional cars.
>>
>> The ideal picture would be to have everything on one form, but again, I
> have
>> no clue as to where to start.
>>
>> Maybe I need to hire an ASP programmer?
>
> Okay, I'm available -- but before we go there...
>
> Is that what you're looking for? Watch for word-wrap:
>
> <% @Language="VBScript" %>
> <% Option Explicit
> '***
> ' This ASP (Active Server Pages) Program does the following:
> ' 1) Provides for the entry of an Owner and their Cars.
> '***
> '*
> '* Declare Constants
> '*
> Const cASP = "CarOwner.asp"
> Const cMDB = "CarOwner.mdb"
> Const cDSN = "DRIVER=Microsoft Access Driver (*.mdb);DBQ="
> '*
> '* Declare Globals
> '*
> Dim strOWN
> strOWN = Request.Form("Owner")
> '*
> '* Call CarOwner()
> '*
> If strOWN <> "" Then
> Call CarOwner()
> Response.Write "Owner " & strOWN & " was added.  "
> Response.End
> End If
>
> Sub CarOwner()
> '*
> '* Declare Constants
> '*
> Const adCmdTable = &H0002
> Const adLockOptimistic = 3
> Const adOpenKeySet = 1
> '*
> '* Declare Variables
> '*
> Dim intCAR
> Dim strRF1
> Dim strRF2
> '*
> '* Declare Objects
> '*
> Dim objADO
> Set objADO = Server.CreateObject("ADODB.Connection")
> objADO.Open cDSN & Server.MapPath(cMDB)
> Dim objRS1
> Set objRS1 = Server.CreateObject("ADODB.Recordset")
> objRS1.Open "Owner", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> Dim objRS2
> Set objRS2 = Server.CreateObject("ADODB.Recordset")
> objRS2.Open "Car", objADO, adOpenKeyset, adLockOptimistic,
> adCmdTable
> '*
> '* AddNew Owner
> '*
> objRS1.AddNew
> objRS1("OwnerName").Value = strOWN
> objRS1.Update
> '*
> '* AddNew Cars
> '*
> For intCAR = 1 To 4
> strRF1 = Request.Form("CarMake" & intCAR)
> strRF2 = Request.Form("CarYear" & intCAR)
> If strRF1 <> "" Then
> objRS2.AddNew
> objRS2("CarMake").Value = strRF1
> objRS2("CarYear").Value = strRF2
> objRS2("OwnerID").Value = objRS1("OwnerID").Value
> objRS2.Update
> End If
> Next
> '*
> '* Destroy Objects
> '*
> objRS1.Close
> Set objRS1 = Nothing
> objRS2.Close
> Set objRS2 = Nothing
> objADO.Close
> Set objADO = Nothing
> End Sub
> %>
>
>
>
>
>
>
>
>

Re: Form to Update Two Database Tables with One-To-Many Relationship

"Tony Scarola" wrote in message
news:ePzwQb8HGHA.740@TK2MSFTNGP12.phx.gbl...
> McKirahan,
>
> See completed project here: http://www.pvregister.com
>
> This is for a Volvo PV Register site.
>
> Now I just need to figure out how to generate an email when someone adds
an
> entry... and perform some field validation. I had to remove the FrontPage
> validation code because I couldn't figure how to get both to work.
Currently
> I have the results being displayed on the main page, however, this display
> is pulled from a query and only displayed if the "AddToWeb" field is
> checked - a manual option. So, now without the email, I have to check it
> once a day to see if there are any new entries. No big deal but could be
> better!
>
> The hunt for code is back on!
>
> Thanks again and take care.
>
> Tony

[snip]

I own a Volvo but I don't know what "PV" means.

"how to generate an email ..."

What SMTP Email object does your Web host support?
Here's an example of one. Another is CDO.Message.

Set objPMS = Server.CreateObject("Persits.MailSender")
objPMS.AddAddress "Someone", arrPMS(intPMS,1)
objPMS.Host = "127.0.0.1"
objPMS.From = Tony Scarola"
objPMS.FromName = "tony@scarolas.com"
objPMS.Subject = "Your Subject"
objPMS.Body = "Your Message"
objPMS.Send
Set objPMS = Nothing


Note -- You could suppress this text
Visit Car's Webpage [If available]
if it's not avaialble.

Re: Form to Update Two Database Tables with One-To-Many Relationship

"McKirahan" wrote in message
news:zu6dneSuU6uc_0neRVn-hg@comcast.com...
> "Tony Scarola" wrote in message
> news:ePzwQb8HGHA.740@TK2MSFTNGP12.phx.gbl...
> > McKirahan,
> >
> > See completed project here: http://www.pvregister.com
> >
> > This is for a Volvo PV Register site.
> >
> > Now I just need to figure out how to generate an email when someone adds
> an
> > entry... and perform some field validation. I had to remove the
FrontPage
> > validation code because I couldn't figure how to get both to work.
> Currently
> > I have the results being displayed on the main page, however, this
display
> > is pulled from a query and only displayed if the "AddToWeb" field is
> > checked - a manual option. So, now without the email, I have to check it
> > once a day to see if there are any new entries. No big deal but could be
> > better!
> >
> > The hunt for code is back on!
> >
> > Thanks again and take care.
> >
> > Tony
>
> [snip]
>
> I own a Volvo but I don't know what "PV" means.
>
> "how to generate an email ..."
>
> What SMTP Email object does your Web host support?
> Here's an example of one. Another is CDO.Message.
>
> Set objPMS = Server.CreateObject("Persits.MailSender")
> objPMS.AddAddress "Someone", arrPMS(intPMS,1)
> objPMS.Host = "127.0.0.1"
> objPMS.From = Tony Scarola"
> objPMS.FromName = "tony@scarolas.com"
> objPMS.Subject = "Your Subject"
> objPMS.Body = "Your Message"
> objPMS.Send
> Set objPMS = Nothing
>
>
> Note -- You could suppress this text
> Visit Car's Webpage [If available]
> if it's not avaialble.
>
>

Oops, reverse those (and include a quote); as in:

objPMS.From = "tony@scarolas.com"
objPMS.FromName = "Tony Scarola"

Re: Form to Update Two Database Tables with One-To-Many Relationship

How coincidental! See the following link for the definition of a PV:

http://www.scarolas.com/58VolvoPV444/profile_on_the_volvo_pv 444.htm

and for my PV 444 restoration project site:

http://www.scarolas.com/58VolvoPV444

Thanks for the code. I actually found and used some CDO code, reproduced
below, to send myself as well as the person who entered the data an alert:

Dim objMailAdmin
Set objMailAdmin = Server.CreateObject("CDONTS.NewMail")
Dim objMailSender
Set objMailSender = Server.CreateObject("CDONTS.NewMail")

objMailAdmin.From = strEMA '* That's the variable for the email address
field used in the form
objMailAdmin.Subject = "PV Register Entry Alert"
objMailAdmin.To = "{my email address}"
objMailAdmin.Body = "New Scarola's PV Registry entry has been added by " &
strOWN & "." '* The owner variable from form
objMailAdmin.Send

objMailSender.From = "{my email address}"
objMailSender.Subject = "Scarola's PV Register Entry"
objMailSender.To = strEMA '* Again, the variable for the email address
field used in the form
objMailSender.Body = "Your Scarola's PV Registry entry has been submitted
for entry." & vbcrlf&_
"It will be reviewed and added to the database." & vbcrlf&_
"Please check in a few days to make sure it has been added" & vbcrlf&_
"and email me if not. Thank you. "
objMailSender.Send

Set objMailAdmin = Nothing
Set objMailSender = Nothing

Thanks again for all your help.

Tony

"McKirahan" wrote in message
news:zu6dneSuU6uc_0neRVn-hg@comcast.com...
> "Tony Scarola" wrote in message
> news:ePzwQb8HGHA.740@TK2MSFTNGP12.phx.gbl...
>> McKirahan,
>>
>> See completed project here: http://www.pvregister.com
>>
>> This is for a Volvo PV Register site.
>>
>> Now I just need to figure out how to generate an email when someone adds
> an
>> entry... and perform some field validation. I had to remove the FrontPage
>> validation code because I couldn't figure how to get both to work.
> Currently
>> I have the results being displayed on the main page, however, this
>> display
>> is pulled from a query and only displayed if the "AddToWeb" field is
>> checked - a manual option. So, now without the email, I have to check it
>> once a day to see if there are any new entries. No big deal but could be
>> better!
>>
>> The hunt for code is back on!
>>
>> Thanks again and take care.
>>
>> Tony
>
> [snip]
>
> I own a Volvo but I don't know what "PV" means.
>
> "how to generate an email ..."
>
> What SMTP Email object does your Web host support?
> Here's an example of one. Another is CDO.Message.
>
> Set objPMS = Server.CreateObject("Persits.MailSender")
> objPMS.AddAddress "Someone", arrPMS(intPMS,1)
> objPMS.Host = "127.0.0.1"
> objPMS.From = Tony Scarola"
> objPMS.FromName = "tony@scarolas.com"
> objPMS.Subject = "Your Subject"
> objPMS.Body = "Your Message"
> objPMS.Send
> Set objPMS = Nothing
>
>
> Note -- You could suppress this text
> Visit Car's Webpage [If available]
> if it's not avaialble.
>
>

Re: Form to Update Two Database Tables with One-To-Many Relationship

McKirahan,

By the way, I figured out how to suppress text and images [which are not
available] from being displayed by using if...then...else commands like
this:

<% If FP_FieldVal(fp_rs,"Car_Webpage_URL")<>"None" Then %>

size="2">
">
Visit Car's Webpage

Re: Form to Update Two Database Tables with One-To-Many Relationship

"Tony Scarola" wrote in message
news:uzYKphVIGHA.1132@TK2MSFTNGP10.phx.gbl...
> McKirahan,
>
> By the way, I figured out how to suppress text and images [which are not
> available] from being displayed by using if...then...else commands like
> this:
>
> <% If FP_FieldVal(fp_rs,"Car_Webpage_URL")<>"None" Then %>
>

face="Arial"> > size="2">
> href="<%=FP_FieldVal(fp_rs,"Car_Webpage_URL")%>">
> Visit Car's Webpage

Re:Form to Update Two Database Tables with One-To-Many Relationship

Hi, I have a similar problem. I have three tables in Access with data of
patients. the same patients have different data in three tables but some of
the fields are same. My problem is repeating the same common information in
three different tables each time. How Can I update the common fields in all
tables by filling in one. As you can see I am a docor and has no detailed
knowledge re. data base. Please help.