Insert record into linked tables from Data Access Page

[Guest]

I have a data access page with a bunch of unbound textboxes and comboboxes
with a submit button on the bottom. I am able to program the onclick of the
button to insert a record into my first main table, which is part of what I
need. What I need to have happen is that once I insert a record into this
first main table, it generates an auto number for the primary key, which I
then need to grab in order to insert a record into another joined table that
has it as the foreign key. Like I said, I have the INSERT working for the
first table, but I just need to know how to grab the primary key of one
record so I can use it in the INSERT of another joined table.

Thanks,

Matt

 

[Bill Mosca, MS Access MVP]

When you use Access databases, the only way I know of is to run another
query to get the MAX autoNumber, but this will only work if traffic is low
and you can be sure that you are getting the one YOU just inserted and not
the one someone else inserted.

If you are using SQL Server as the back end, you can get the AutoNumber
using "SELECT @@IDENTITY FROM MyTable" right after the insert. The best way
to do that would be with a stored procedure that does the whole job using
parameters from your DAP.

 

[Guest]

Thanks,

I'm running Access and this MAX autoNumber sounds like it will work because
there's barely any traffic, and the same person generating the autoNumber is
also the one needing to grab it (in the sense that it will be used as the
foreign key in a linked table). Could you maybe help me with the script to
get the MAX autoNumber by giving me an example? I've been working with Data
Access Pages for the past 6 months and learning vbscript along the way, but I
just honestly don't know the syntax for this. Just to re-iterate, I am
inserting a record into one table while also needing to insert a record into
a linked table, and the only thing missing is the autoNum to act as the
foreign key in my linked table.

Thanks again,
Matt

 

[Bill Mosca, MS Access MVP]

Matt

Do your insert then open a recordset using an SQL statement with your same
connection. It's been a while since I did anything with VBScript so forgive
me if my syntax doesn't work. Maybe someone else can give better help than
I.

Dim rs
Dim strSQL
Dim lngReturn
Set rs = New ADODB.Recordset
strSQL = "SELECT Max(YourAutoNumber) FROM YourTable"
rs.Open sstrSQL, YourConnectionObject
lngReturn = rs.Fields(0)

 

[Guest]

Thanks Bill,

I keep gettings this error "Invalid Character" on the line with rs.Open.

Here is what I have for the code from what you gave me:

Dim rs
Dim strSQL
Dim lngReturn
Set rs = New ADODB.Recordset
strSQL = "SELECT Max(Activity) FROM [Activity Information]"
rs.Open strSQL, YourConnectionObject
lngReturn = rs.Fields(0)

Is there a way to do this with the MSODSC model? Otherwise, what is my
connection object and why do I keep getting that error? Here is an example
of an INSERT statement that works for me earlier on in this page:

dim mySql
mySql = "INSERT INTO [Activity Information] ([Activity Title], [Region], [HR
Objective], [Quarter], [Year]) VALUES ("&Title&", "&Region&",
"&HrObjective&", "&Quarter&", "&theYear&")"
MSODSC.Connection.Execute(mySql)

Could I maybe do something looking like this but with that SELECT Max
statement?

Thanks again,
Matt

 

[Guest]

Actually I just got it!! Thanks alot. All I really needed was that
..Fields(0) things cause I was using the MSODSC model and couldn't figure out
the small step.

Thanks,
Matt

 

[Bill Mosca, MS Access MVP]

Glad you got it, Matt. I was afraid I was pounding sand when you couldn't
get it.