Syntax problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have just created a record in tblA
I need to create a linking record in tblB with the primary key from tblA, say "aID", in tblB's foreign key fiel
I've used DMAX to get the newly created "aID" and store it in lngaID, an integer variable
I've created an Append query and set the foreign key "aID" to "lngaID
In the query, the variable name is in quotes
I get "Data type mismatch in criteria expression
1) What's the problem and the fix? I thought long integer was equivalent to an auto-number ID field's data type
2) Is there a better way, can I set the foreign key in tblB to the latest-n-greatest primary key in tblA "on the fly" with an expression in the query and not have to mess with visual basic variables
 
It would help if you would post your code.

Sounds like you have not built your SQL string properly.

If you are doing something like this:

sSQL = "Insert Into tblb (<ForeignKeyFieldName>) Values (""lngaID"")"

You should be doing:

sSQL = "Insert Into tblb (<ForeignKeyFieldName>) Values (" & lngaID & ")"

Hint...

Set your code to break after you create your SQL string. Take a look at
that string and see if it is valid. You can even take that string to the
query designer and see if it works.

If this doesn't help, post your code.

--
Kipp Woodard


Desert Bear said:
I have just created a record in tblA.
I need to create a linking record in tblB with the primary key from tblA,
Click to expand...
say "aID", in tblB's foreign key field
I've used DMAX to get the newly created "aID" and store it in lngaID, an integer variable
I've created an Append query and set the foreign key "aID" to "lngaID"
In the query, the variable name is in quotes.
I get "Data type mismatch in criteria expression"
1) What's the problem and the fix? I thought long integer was equivalent
Click to expand...
to an auto-number ID field's data type.
2) Is there a better way, can I set the foreign key in tblB to the
Click to expand...
latest-n-greatest primary key in tblA "on the fly" with an expression in the
query and not have to mess with visual basic variables?
 
Thanks for your assistance
I can use your code, if I can figure out creating sSQL and execution of same

Not knowing how to create a sql string in VBA, I have an actual append query
INSERT INTO tblVersion ( pverPlanVersionNum, peoPeopleID
SELECT tblVersion.pverPlanVersionNum, tblVersion.peoPeopleI
FROM tblVersio
WHERE (((tblVersion.pverPlanVersionNum)=1) AND ((tblVersion.peoPeopleID)="lngPeoID"))
( the flaw in this is obvious, it's searching for the record I want it to create.)
 
Back
Top