0

I have a 2 tables, call them PO and PO_LI. There is a column in PO called PO# which is a primary key, and is in a 1 to many relationship with the PO# column in PO_LI (the 1 being in PO). In PO_LI, the other columns are Line#, Description and LineAmount.

How can I reset the number back to 1 for every new PO #?
Can this be done while still using autonumber?
Can this be done in the ms-access gui or is VBA code required?

Michael
  • 3,093
  • 7
  • 39
  • 83
  • http://stackoverflow.com/questions/20738596/how-to-reset-an-access-tables-autonumber-field-it-didnt-start-from-1 – marlan Jul 11 '16 at 14:05

1 Answers1

1

You cannot manually edit an AutoNumber field or change its starting value. Your best bet is to maybe use VBA to get the max line number and increment it, and if you're using a new PO then you start at 1. You would put this VBA in the AfterUpdate event of the PO field.

You could do something like this:

Dim db as Database
Dim rec as Recordset
Dim rec2 as Recordset
Dim MyVal as Integer

Set db = CurrentDB
Set rec = db.OpenRecordset("Select LineNum from MyTable where PO = '" & Me.txtPO & "' group by PO")

'If there are no records returned, start at 1.  Otherwise, increment.
If rec.EOF = true then
  MyVal = 1
else
  Set rec = db.OpenRecordset("Select Max(LineNum) from MyTable where PO = '" & Me.txtPO & "'")
  MyVal = rec(0) + 1
endif

MyVal is now the number you will write to LineNum. Of course, you'll need to change the variables and such to be what's actually on your forms and in your tables, but you should get the idea.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • I was thinking along those same lines too, but how do I check if the data entry has a different PO than the old ones? Would I need to use an SQL statement to retrieve all the values from the PO # column and compare each of those values to the new one? It seems like that wouldn't be the most efficient solution. As of right now iit won't matter since there isn't too much data, but in the future it may become a problem – Michael Jul 11 '16 at 14:17
  • Or in one line: `Nz(DMax("LineNum", "MyTable", "PO = '" & Me.txtPO & "'"), 0) + 1`. With Nz(), there is no need for the if-then-else, Max of nothing is NULL. – Andre Jul 11 '16 at 15:43
  • @JohnnyBones I edited the line of code to look like this: Set rec = db.OpenRecordset("Select `Line#` from t_PURCHASES_PO_ByLI where `PO#` = '" & Me.PO_.Value & "' group by `PO#`") but now I get the error: "Run-time error '3122': Your query does not include the specified expression 'Line#' as part of an aggregate function." – Michael Jul 11 '16 at 16:04
  • also what is Me.txtPO? am I replacing the txtPO with PO#.text? – Michael Jul 11 '16 at 16:06
  • Take off the "group by PO#" piece. I forgot to edit that. Sorry. :o( Yes, txtPO was my guess at what your PO textbox was called. – Johnny Bones Jul 11 '16 at 17:15