Increment an auto number field from a form

[WinDancer]

I have a form that is getting it's record number from a closed table, then adding 1 to it.
Now I am trying to increment that table by 1 so I get a new number the next time.
I have been fighting with this for a couple of hours.

I have gotten several errors with various different tries-
The pauses are so I can watch it happen (or, more correctly, not happen.
It will open the table, add a new record and then puke..i can't get the auto number to happen- it dies right there with the record number still at 'New' and will not move to the second field, causing the number to happen.

Here is what WONT work;

Private Sub Command19_Click()
DoCmd.OpenTable "CustomPID"
Pause (1)
DoCmd.GoToRecord , , acNewRec
Pause (1)
Tables!CustomPID!ID.SetFocus
Pause (1)
Tables!CustomPID![udder].SetFocus
Pause (1)

DoCmd.RunCommand acCmdSaveRecord
'DoCmd.NewRecord
'DoCmd.Save
DoCmd.Close
End Sub

Thanks again,
Dave

 

[dkinley]

I am not sure what you mean by a "closed table."

-dk

 

[dkinley]

Oh wait ... I think I see what you are attempting to do.

What I do know is that the autonumber field will not 'trigger' until some data is added to some field in that record. This does not include any default values you have, either. You can test this manually.

I am not sure why you are trying to do this. If it is to add a record or what-have-you, why not an Append query?

Apologies, I've never tried anything like this.

-dK

 

[missinglinq]

Your title hints at the ambiguity here. You're really talking about an auto-incrementing number, not an "autonumber" as defined by Access. And this is probably what you really should be doing. Autonumbers shouldn't be seen or heard from, merely used for internal housekeeping. You don't really need a specific table to hold these numbers. Here's a typical Auto-incrementing Number hack. The first code here would be for an IDNumber that is defined in the table as Text datatype. "Number" fields that aren't used for math really should be defined as Text.

For Text:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = "1"
  Else
   Me.IDNumber = DMax("val([IDNumber])", "YourTableName") + 1
  End If
End If
End Sub

Here's the same code for an IDNumber defined as Numerical:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
  If RecordsetClone.RecordCount = 0 Then
   Me.IDNumber = 1
  Else
   Me.IDNumber = DMax("[IDNum]", "YourTableName") + 1
  End If
End If
End Sub

 

[WinDancer]

I am not going to bore you all to death, but this dbase was delivered three weeks ago and there were only happy campers

The the question is asked- what about people who are not IN the system?

So that is where this came up.

The app as built has an autonumber field for PID.

My answer was to make a composit key, record source AND PID.

Records in the licensing system are indicated now as PID and the source is RB.

To avoid possible dupes the unlicensed records are those woth a PID and a rec source of 'Other".

The app is finished and being used. I am not going to invest several more weeks to accomodate this new data in the forms and reports.

Hence my question as posted.

What I am now doing is running a simple quey against the main table that holds the data, and asking for all the record PIDs that have a source of 'Other.

Then I take the largest number there and add one to it for the new Other sourced PID.

I just run the query everytime a new other PID is needed. The query gets me a new largest number that is then used to write this record out to the main data table.

I am always flustered with changes that are this major after the system is done. I fought with this the entire day but am now happily finishing the code to make it work.

The folks here on this board are very tolerant of my and my questions, and most always try to help.

I also appreciate it when folks take extra time to show me how they would accomplish this task or that task.

I am far from an expert. I have been building application in Access for about 15 years now, with Paradox for three years before that. And I still run into stumpers once or twice with each one.

Anyway, thanks for everyones help and ideas!

Dave