Retrieving autonumber

[SweetDot]

Hi all,

I have a form that allows user to fill out 10 fields where one of them is autonumber. Is there anyway to retrieve that autonumber in the code? I need to do other database operations based on that number, but the number does not appear in the textbox (dataform) until I exit or scroll to another record. Any idea?

Thanks very much!

 

[Melagan]

The problem is that the autonumber probably doesn't get generated until the very end of the record's creation, which I believe happens on the Form_AfterUpdate event...perhaps you could try your code on that event.

Private Sub Form_AfterUpdate()
dim dblYourAutoNum as Double

strYourControl = Me.txtYourAutoNum.Value
...
End Sub

I would set a break point somewhere after the line where you're retrieving the autonumber, then hold your mouse cursor over your variable (strYourControl in my example) to make sure the value is being passed through.

~Melagan
______
"It's never too late to become what you might have been.

 

[jadams0173]

You may try to make your own autonumber or read the current autonumber on DB_Load or form open. Then just increment it by one each time your user adds a record.

 

[Zion7]

Try the AfterInsert event.

 

[TheAceMan1]

How are ya SweetDot . . .

Am I understanding you correctly!

[blue]I have a form that allows user to fill out 10 fields [purple]where one of them is autonumber.[/purple][/blue]

[blue]AutoNumber is system generated[/blue] and requires no user intervention. If users can type in this ID somethings wrong!

Be more specific about the [blue]AutoNumber![/blue]

See Ya! . . . . . .

 

[SweetDot]

TheAceMan1,
Sorry I meant there are 10 fields for the user to fill but autonumber is not entered by the user.

I don't think I can retrieve the number before the record is saved. So I tried saving the record using :

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


but it gives an error:

The command or action "SaveRecord" isn't available now.

 

[Zion7]

it all depends on the event you use, when you can
perform a save action.

AfterUpdate for sure. After Insert possibly?

 

[TheAceMan1]

Roger That SweetDot . . .

Be aware: with [blue]autonumber[/blue] set in the table, in the form, [purple]as soon as you edit a new record in any other field (just one character), you should see the autonumber automatically entered![/purple]

[purple]The problem becomes when is autonumber entered and when can you pick it up?[/purple]

Thru testing I've found the best bets are the forms [blue]BeforeUpdate[/blue] or [blue]AfterUpdate[/blue] events. If necessary you could update a global variable at these times . . .

[blue]Your thoughts? . . .[/blue]

See Ya! . . . . . .