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

Try this:

    objRS("Bus")=strBus

    SavedKey = objRS("ID") ' <<<< new line


    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")= SavedKey ' <<<< changed


If this does not work and you are using JET4 drivers or SQL server lookup how to use @@IDENITY in Microsoft's KB.

See this post for more info


Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com