Using two tables in ASP
Hello P2P - I'm a new user here, so please bear with me.
I have an html form pulling from ASP from one table. I found the table structure to be faulty and am fixing it, however, need to know how to accomplish this task since the restructure.
With the restructure I have two tables:
1st with fields ID and common contact information
2nd table with three fields ID, Date and TakingBus (boolean)
Once the online user fills out the form, I'd need the form to update both tables:
The first with their contact info (ID is an autonumber within Access)
The second with the dates they choose to participate (could be multiple dates) and if they will be riding the bus to the event.
What I am trying to do, is once I update the first table, I'd like to use the autonumber generated to populate the second tables ID field in order to "tie" the two tables together. Can I do that?
Here's the code I was playing with (form fields not included here):
strID=Request.form("ID")
strDID=Request.form("DID")
strFirstName=Request.form("FirstName")
strLastName=Request.form("LastName")
strPhone=Request.form("Phone")
strAddress=Request.form("Address")
strCity=Request.form("City")
strState=Request.form("State")
strZip=Request.form("Zip")
strEmail=Request.form("Email")
strShirtSize=Request.form("ShirtSize")
strCommunityService=Request.form("CommunityService ")
strOver18=Request.form("Over18")
strDate=Request.form("Date")
strBus=Request.form("Bus")
Set objConn = Server.CreateObject("ADODB.Connection")
ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("rose_parade.mdb")
objConn.Open ConnectionString
sqlQuery = "SELECT * FROM tblVolunteers"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open sqlQuery, objConn, 1, 2
objRS.AddNew
objRS("ID")=strID
objRS("FirstName")=strFirstName
objRS("LastName")=strLastName
objRS("Phone")=strPhone
objRS("Address")=strAddress
objRS("City")=strCity
objRS("State")=strState
objRS("Zip")=strZip
objRS("Email")=strEmail
objRS("ShirtSize")=strShirtSize
objRS("CommunityService")=strCommunityService
objRS("Over18")=strOver18
objRS("Date")=strDate
objRS("Bus")=strBus
objRS.Update
objRS.Close
Set objRS = Nothing
sqlQuery = "SELECT * FROM tblDates"
Set objRSD = Server.CreateObject("ADODB.Recordset")
objRSD.Open sqlQuery, objConn, 1, 2
objRSD.AddNew
objRSD("DID")=objRS("ID")
objRSD("Date")=strDate
objRSD("Bus")=strBus
objRSD.Update
objRSD.Close
Set objRSD = Nothing
Thank you for any and all help you can provide!
Tanisha
|
