append query: dlookup or dmax problem

[jeffr27]

Hello all! I'm having a problem with an append query that appends to 2 different tables with one click of a button. My tables are:

tblReview(ReviewID-autonum, CC, etc..., DateAdded)
tblRecoup(RecoupID-autonum, ReviewID, CC, etc...)

The first part of the code enters the info from a form into tblReviews - obviously it creates a random number for ReviewID and DateAdded = now().

The second part of the code takes different info from the same form and saves it into tblRecoup. However, tblRecoup.ReviewID should be the same as tblReview.ReviewID, but I cannot reproduce the result.

The code I'm using is:

strSQL = "INSERT INTO tblRecoupments ([ReviewID], [CC], etc...) " & _
"VALUES (" & DLookup("ReviewID", "tblReviews", DMax("DateAdded", "tblReviews", "tblReviews.CC = Forms!frmCCReview!cboCC")) & ", etc...

What I hoped the code would do is retrieve the ReviewID(an autonumber) in tblReviews with the most recent date where CC=user entered form. All of the fields save their information accurately, with the exception of ReviewID in tblRecoup, which always saves the ReviewID as 1 instead of whatever the first part of the code assigned the autonumber as, and I don't know why.

Any help is appreciated! Thanks, -J

[NTC]

The way I approach this is to make a plain ole Select query of tblReview that results in the record just appended. It serves a very useful sanity check that I have found/isolated the correct record. Save that query with a name.

Then one must be able to join this query into the 2nd Append Query - and will of course provide that ID value that you want.

Hope this helps.