How to retrieve autonumber field data from SQL linked table?

[iladtp]

I am trying to retrieve the autonumber from a linked SQL table into an Access DB form. What I need is to have the autonumber field data to be displayed as soon as I tab out of another field on the form. I tried the following using AfterUpdate and the Requery as follows:

Private Sub fieldname_AfterUpdate()
Me!fieldname.Value = UCase(Me!fieldname.Value)
Forms!Formname.Requery

This displays the autonumber in the Identity Field but then immediately displays the first record in the linked table. I need to redisplay the new record and continue to update the other fields of the record.

I also tried updating all fields and then Save the record but the Autonumber field only momentarily displays the new record ID. Again, I need the new ID (Identity/AutoNumber) to remain on the screen until I can record it for future processing in another form at an unspecified date and time.


Thank You for your help.

 

[cmmrfrds]

I am not sure what you are trying to do, but you may be able to get the old value of the auto number field on the after update event for the Form - the old value at this point should be the record you just updated. Try using a reference to the OldValue property of the field

AfterUpdate()
Dim oldid As Long
oldid = Me.adjustmentID.OldValue
Debug.Print "old id = "; oldid
sub

 

[iladtp]

Thanks for your response but I must be doing something wrong because I still cannot get what I need.

I need to get the auto number just entered into the SQL database that using an access form to update the database.
I need to display the newly entered record for edit/update.

Can you help?

 

[cmmrfrds]

Are you using Sql server as the database? Which version? Can you paste in your code including the event where you are trying to capture the id field. Which version of Access?
Do you need the actual ID or a requery of the record?

 

[iladtp]

I am using SQL server 7.0 as the database adn Access 97. I need the actual ID to be displayed. What I am doing now is just a requery of one of the controls on the form after the User clicks the Save button. Then the user can record the ID and then click the Exit button to get out. I know this seems be a hoaky way to do this but right now it is the only way I can get the record to remain on the screen.

Here is the section of the code I have put in the Requery.

Private Sub cmd_save_Click()
On Error GoTo Err_cmd_save_Click

Dim strMsg As String
Dim oldid As Long
If (IsNull(Me![txt_ml].Value)) Then
strMsg = "Please enter a value for M/L."
MsgBox strMsg, vbOKOnly, "Required Field Missing"
Me![txt_ml].SetFocus
Exit Sub
End If

If (IsNull(Me![txt_ssdl].Value)) Then
strMsg = "Please enter a value for SSDL."
MsgBox strMsg, vbOKOnly, "Required Field Missing"
Me![txt_ssdl].SetFocus
Exit Sub
End If

If (IsNull(Me![txt_part_number].Value)) Then
strMsg = "Please enter a value for Part Number."
MsgBox strMsg, vbOKOnly, "Required Field Missing"
Me![txt_part_number].SetFocus
Exit Sub
End If

If (IsNull(Me![txt_supplier_code].Value)) Then
strMsg = "Please enter a value for Supplier Code."
MsgBox strMsg, vbOKOnly, "Required Field Missing"
Me![txt_supplier_code].SetFocus
Exit Sub
End If

If (IsNull(Me![cmb_reason_code].Value)) Then
strMsg = "Please select a value for Reason Code."
MsgBox strMsg, vbOKOnly, "Required Field Missing"
Me![cmb_reason_code].SetFocus
Exit Sub
End If


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'Replace original DoCmd to a Requery to display new ID
'DoCmd.GoToRecord , , acNewRec
DoCmd.Requery "cmb_reason_code"

Exit_cmd_save_Click:
Exit Sub

Err_cmd_save_Click:
MsgBox Err.Description
Resume Exit_cmd_save_Click
End Sub


Thanks for your help.