Query First and Last Record of Most Current Batch

[roxannep]

I need to have a message box come up showing the contents of one of the fields in the table based on the most current items entered. The user sometimes must enter 25 identical records except that each one must have a different file number (example: inventory but each item is given it's own identifier). I have it set up so the user can enter all the data in unbound boxes, select how many copies of this record to create, then the database copies those to the inventory table and adds the unique identifier to each record.

The user now needs to see the first identifier and the last identifier of the records that were just added to the inventory table.

So the message box would read something like:

"You have just added 25 new records beginning with file no. B1150 and ending with B1174."

I know how to bring up a message box. I don't know how to grab those beginning and ending file nos. based on the number of records they just created. I do have a timestamp field in the table if that helps.

 

[WildHare]

1) Go to the LAST record. Grab it's number.
2) use the GoToRecord method to move backwards n-1 records, where N is the number of entries you just made.
3) Grab that number.

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[roxannep]

Well, that sounds reasonably easy! However, not really being a programmer but just a little more advanced user, I need to know where and how to do this? In a query? On a form ... please give me a a bit more about how to write out the GoToRecord request you are showing me.

Thanks!

 

[cmmrfrds]

"I have it set up so the user can enter all the data in unbound boxes, select how many copies of this record to create, then the database copies those to the inventory table and adds the unique identifier to each record."

When you do the above process can you save the first and last file numbers. Once you update the database it is going to be hard to find the batch of records unless there is some unique identifier for the batch. Is this a single user system or could multiple users be entering batches of file numbers?

If you have or add a unique identifier for the batch then you could use the timestamp field with
"select min(timestampfield), max(timestampfield) group by batchNumber from table" after the records are added to the database.

 

[roxannep]

That's just it, the file no. is not added until the records are created through the copying process. It is those files numbers I need to have display. I need a way for Access to automatically pull the most recently created record file no. (which would be the last file of this batch) AND the first file no. of this batch and display these simultaneously (see sample of message as it should come up for user).

There is only one user that handles this part of the inventory and accesses this particular table in the database though I have 10 users logged in and entering data to other tables daily.

 

[roxannep]

Okay, here is the code so you can better see what I need:

Private Sub cmdAdd_Click()
' Roxanne Pratt 14-Nov-2000
' create multiple identical records for recurring inventory
' add records to Inventory database and clear form for next
' entry of recurring inventory items
Dim rst As Recordset
Dim IntCounter As Integer
Set rst = CurrentDb.OpenRecordset("CSInventory&quot

For IntCounter = 1 To Me!txtItemCount
rst.AddNew
rst![Description] = Me!txtItemDesc
rst![CostBP] = Me!txtItemCost
rst![DenominationID] = Me!DenominationIDCombo
rst![Year] = Me!txtYear
rst![Title] = Me!txtItemTitle
rst![CompanyID] = Me!CompanyIDCombo
rst![EmployeeID] = Me!EmployeeIDCombo
rst![LotID] = Me!LotIDCombo
rst![CoinLetterID] = Me!CoinLetterIDCombo
rst![PCID] = Me!PCIDCombo
rst![Enddate] = Me!Enddate
rst![Image1] = Me!Image1
rst![Image2] = Me!Image2
rst![Image3] = Me!Image3
rst![AuctionID] = Me!AuctionNameCombo
rst.Update
Next
DoCmd.Close acForm, "CSIRecurringDataEntry"
DoCmd.RunMacro "mcrBarcode UpdateCSI"
DoCmd.OpenForm "CSIRecurringDataEntry"
MsgBox "The barcode of the first and last items you just entered are: ***HERE IS WHERE I NEED TO PUT IN THOSE TWO FILE NUMBERS*** "

End Sub

 

[WildHare]

Roxanne - after your batch entry process, do something like this. You'll need to substitute your field and table names where appropriate:

--------------------------------------
Dim rst as recordset
Dim LastGuy as string ( ??? )
Dim FirstGuy as string (???)
set rst = db.OpenDynaset("table-name here&quot

with rst
.MoveLast
LastGuy = !{record-identifier-field}
DoCmd.GoToRecord acDataTable, "table-name here", acPrevious, 24
FirstGuy = !{record-identifier-field}
.close
End with

Set rst = Nothing

--------------------------------------

This assumes that your "record identifier" field is a string, and that you do 25 entries at a time.

If the number you do at a time is variable, you'll need to save that and use it in the GotoRecord code instead....



There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[roxannep]

WildHare -

I think I posted my current code at the same time you posted yours. How do I fit what you provided in with what I have going now?

Thanks!

 

[WildHare]

For IntCounter = 1 To Me!txtItemCount
rst.AddNew
rst![Description] = Me!txtItemDesc
rst![CostBP] = Me!txtItemCost
rst![DenominationID] = Me!DenominationIDCombo
rst![Year] = Me!txtYear
rst![Title] = Me!txtItemTitle
rst![CompanyID] = Me!CompanyIDCombo
rst![EmployeeID] = Me!EmployeeIDCombo
rst![LotID] = Me!LotIDCombo
rst![CoinLetterID] = Me!CoinLetterIDCombo
rst![PCID] = Me!PCIDCombo
rst![Enddate] = Me!Enddate
rst![Image1] = Me!Image1
rst![Image2] = Me!Image2
rst![Image3] = Me!Image3
rst![AuctionID] = Me!AuctionNameCombo
rst.Update
Next

with rst
.MoveLast
LastGuy = !{record-identifier-field}
DoCmd.GoToRecord acDataTable, "table-name here", acPrevious, txtItemCount
FirstGuy = !{record-identifier-field}
.close
End with

set rst = Nothing

DoCmd.Close acForm, "CSIRecurringDataEntry"
DoCmd.RunMacro "mcrBarcode UpdateCSI"
DoCmd.OpenForm "CSIRecurringDataEntry"
MsgBox "The barcode of the first and last items you just entered are" & FirstGuy & " and " LastGuy ".", vbOkOnly

The only thing we're missing here is the reference to your key field - what is the key field in this table???

There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[roxannep]

The key field is actually FileNo, but the field data I need displayed is BarcodeNo which uses the FileNo and a couple of other pieces for a composite identifer (B1140-C0420 ... the fileNo is the 1140).

The Barcode data is updated at the RunMacro line, then I need to pull the first/last, so again, I guess I need to move the code around to work with that?

 

[roxannep]

okay, been trying the code you sent, but on the line:

DoCmd.GoToRecord acDataTable, "CSInventory", acPrevious, txtItemCount

I keep getting an error telling me it can't go to that record. Is it because we are starting with the last record and telling it to move xxx forward from there (which would be no record). How can we tell it to go to the record xxx less from there?

 

[WildHare]

No, the acPREVIOUS option should be telling it to move "backwards"...

You might need to make sure the txtPrevious returns a valid integer instead of a string number.. try VAL(txtItemCount)

Jim There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[roxannep]

Okay, tried VAL[txtItemcount].... no go, same message. Did I put it where I was supposed to?

Message I get:

Runtime error: 2105 You can't go to the specified record.

Also I had to add the rst! to even get the program to recognize the line (see green text below). It kept erring on me on the Compile, and finally stopped when I added the rst! .... maybe that isn't the right way to direct it?

So here is what this looks right now.

Dim rst As Recordset
Dim IntCounter As Integer
Dim LastGuy As String
Dim FirstGuy As String
Set rst = CurrentDb.OpenRecordset("CSInventory&quot

For IntCounter = 1 To Me!txtItemCount
rst.AddNew
rst![Description] = Me!txtItemDesc
rst![CostBP] = Me!txtItemCost
rst![DenominationID] = Me!DenominationIDCombo
rst![Year] = Me!txtYear
rst![Title] = Me!txtItemTitle
rst![CompanyID] = Me!CompanyIDCombo
rst![EmployeeID] = Me!EmployeeIDCombo
rst![LotID] = Me!LotIDCombo
rst![CoinLetterID] = Me!CoinLetterIDCombo
rst![PCID] = Me!PCIDCombo
rst![Enddate] = Me!Enddate
rst![Image1] = Me!Image1
rst![Image2] = Me!Image2
rst![Image3] = Me!Image3
rst![AuctionID] = Me!AuctionNameCombo
rst.Update
Next
DoCmd.OpenTable "CSInventory"

With rst
.MoveLast
LastGuy = rst![FileNo]
LINE THAT ERRS: DoCmd.GoToRecord acDataTable, "CSInventory", acPrevious, Val(txtItemCount)
FirstGuy = rst![FileNo]
.Close
End With

Set rst = Nothing

DoCmd.RunMacro "mcrBarcode UpdateCSI"
DoCmd.Close acForm, "CSIRecurringDataEntry"
MsgBox "The barcode of the first and last items you just entered are: " & FirstGuy & " and " & LastGuy & ".", vbOKOnly
DoCmd.OpenForm "CSIRecurringDataEntry"

End Sub

Thanks so much for going through this with me! Now I am determined to get this to work!

Roxanne