Primary Key Autonumber

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

Guest

I use a primary key which is an autonumber for a table in
my database. When I open up the form for this table I
would like to get this field automatically populated
without having to enter in any information. Is this
possible? Right now I have to go to a text box or one of
the combo boxes and either select one or type in a value
for the autonumber to kick in.
 
Why? As in "why do you care when Access adds the autonumber?" Are you
"using" that Autonumber for other purposes than its intended design, i.e., a
unique row identifier?

Yes, if you are using an Access/Jet back-end, Access doesn't generate an
Autonumber until the row is "dirty". If you were using SQL Server, you
wouldn't get an "autonumber" until after the row was saved.
 
The reason is that I have subforms on the main form and
the way the system is the number is needed to populate the
subforms. What is happening is, depending on what
information is received first, the users are scrolling
down the page and entering information in the subforms
first and they keep getting an error message saying that
the primary key is a null number. I've already put in a
message box asking them to go back and put the date of the
incident first, but they still are not doing it, and I was
hoping to automate the entire process, so I didn't get any
errors at all. This is the only one they are consistently
coming up with. I knew that Access populated the
autonumber when you entered in anything at all, but I was
hoping I could put in something to remedy this.
 
Why not just add a 'splash screen' at the startup that asks them for the
date first (or whatever criteria you want), then have it start a new record
(DoCmd.GoToRecord, acNew) on your current form. If the user cancels (the
startup form, that is), then the record isn't made.

Jeff's right here: you can't create a record ID without first creating a
record. Instead of changing the user (a frustrating and often fruitless
task) way of doing things, force the users to do it your way.

When they save, have the form close, and reload the startup form.

Another thing you can *try* (I stress this for reasons made clear below) you
can try this code:

Private Sub Command2_Click()
Dim LastID As Integer
'=== get last system ID
LastID = GetLastSystemID

'=== fill text box
Me.Text_LastNum = LastID

'=== increase system ID and fill text box
Me.Text_NewNum = LastID + 1

MsgBox "Done"
End Sub

This takes whatever is the last Autonumber created and adds 1 to it. You can
use that in source on your table as well. *However* it also will add
regardless of whether a record is made, causing lots of problems with
records that have a 'LastID' (variable in code above) and no other
information. We just use it on two-person database to increment a Computer
System's ID so we can sort it in other areas. A table full of blank rows is
*very* disturbing, let me tell you, and it will cause trouble if you ever do
a combo box to lookup records in the table.

HTH-
Gary
 
Thanks, what I decided to do was disable all of the
subforms and put an option button that enables them all.
The splash screen is a good idea, and I may add that
instead, but I already put in the option and let them
start using it again. Thanks alot for the help.
 
Back
Top