bring up form with most recently added record

[ArielZusya]

I'm using DoCmd.TransferSpreadsheet to import data from an excel
spreadsheetinto my tblMain. The data in the spreadsheet contains something
like:

FirstName LastName RefNum

The RefNum associated with all the FirstName and LastName entries will be
the same for a given imported excel spreadsheet. In other words the data
might look something like:

Jane Doe 32CGD221
John Smith 32CGD221

etc.

I then use:

INSERT INTO tblRef ( RefNum )
SELECT DISTINCT RefNum
FROM tblMain;

There is a one : Many relationship between each record in tblRef and each of
the many records imported into tblMain. tblRef also contains a feild for a
description of each RefNum. I'd like to open a form, immediately after
importing the data and instering the unique RefNum into tblRef, which brings
up that most recently imported RefNum and allows me to enter a description.
The way this all happens so far is I've got an "Import" button on a menu
form. When clicked, the code looks something like this:

Dim stDocName As String
Dim stTableName As String
Dim stFormName As String
Dim stLinkCriteria As String

stDocName = "qryInsertRefNum"
stTableName = "tblMain"
stFormName = "frmDescriber"

DoCmd.Close
DoCmd.SetWarnings = False
DoCmd.TransferSpreadsheet acImport, , stTableName, OpenExcelFile, True
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings = True
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've got that stLinkCriteria (which the system adds to any wizzarded button
to open a form) so I thought I might take advantage of it but I'm not sure
what the criteria would be or even if that is the rigth way to go about this.
Your help is greatly appreciated! Thanks!

 

[Allen Browne]

Add another field to the RefNum table, to indicate which batch the record
was part of.

Say the new field is named BatchNum, of type Number (size Long Integer.) You
can now open the form filtered to the new batch like this:

Dim lngBatch As Long
Dim strSql As String
Dim stLinkCriteria As String
Const stFormName = "frmDescriber"

lngBatch = Nz(DMax("BatchNum", "tblRef"), 0&) + 1&
strSql = "INSERT INTO tblRef ( RefNum ) " & vbCrLf & _
"SELECT DISTINCT RefNum, " & lngBatch & " AS BatchNum " & vbCrLf & _
"FROM tblMain;
dbEngine(0)(0).Execute strSql, dbFailOnError

stLinkCriteria = "BatchNum = " & lngBatch
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[ArielZusya]

OK... so... I think I follow the code. If I understand correctly the code is
looking at BatchNum in tblRef and determining if the largest BatchNum number
is zero (ie no numbers yet in BatchNum). If the number is zero it returns
0+1, otherwise it returns BatchNum + 1 and stores the returned value in
lngBatch (so if this is the first record to be put into tblRef, lngBatch
would be 1). It then sets the SQL statement as (assuming this is the first
record to be put into tblRef):

INSERT INTO tblRef ( RefNum )
SELECT DISTINCT RefNum, 1 AS BatchNum
FROM tblMain;

and then runs the query using that sql statement. Finally, it sets the
stLinkCriteria as "BatchNum = 1" and opens the form frmDescriber with the
record that has that link criteria. I'm getting an error though... it says,
"Number of query values and destination fields are not the same." Where am I
going wrong? I think I need to insert lngBatch into BatchNum but I'm not
sure what part of this I need to change. Thanks for your help!

 

[ArielZusya]

Wait! I figured it out... I just made a couple of changes and everything
worked. Namely (in case anyone else looks at this at tries the same code):

strSql = "INSERT INTO tblRef ( RefNum ) " & vbCrLf & _
"SELECT DISTINCT RefNum, " & lngBatch & " AS BatchNum " & vbCrLf & _
"FROM tblMain;

became:

strSql = "INSERT INTO tblRef ( RefNum, BatchNum ) " & vbCrLf & _
"SELECT DISTINCT RefNum, " & lngBatch & vbCrLf & _
"FROM tblMain;

I also changed the DoCmd to open the next form from:

DoCmd.OpenForm stDocName, , , stLinkCriteria

to:

DoCmd.OpenForm stFormName, , , stLinkCriteria


Very cool. Thanks for your help!