Problems after SQL Migration

[npsavidge]

Hi,

I have created a database with the goal to move to a SQL Server back end when complete. Today i have moved the back end to SQL server.

I have a form to add a new patient to my database. This form has the Patient_ID(my UID) bound and in order to force a Patient_ID, i set the focus to a data field, get the autonumber generated and pass to a global for later on. It is a convaluted process, but this is how it needs to be done. However, the UID is not populated until the form is closed.

This is the code i have, that works in access, but not anymore

Private Sub Form_Open(Cancel As Integer)
' Force an autonumber out of the DB
DoCmd.GoToRecord , , acNewRec
Me.txt_NHS_Number.SetFocus
Me.txt_NHS_Number = 0
Me.txt_NHS_Number = vbNullString
Me.txtSafeField.SetFocus
' Assign it to the global variable.
glb_New_Patient_ID = Me.Patient_ID
' Set the save flag to false
bln_RecordSaved = False

End Sub

When i pass the Auto ID to the global, it is NULL.

Can anyone help, as the DB is ready to be released.

Thanks

Nathan

 

[pbaldy]

You can try forcing the record to save first:

DoCmd.RunCommand acCmdSaveRecord

 

[boblarson]

Plus you really shouldn't do record operations in the form's OPEN event, but instead should use the LOAD event.

 

[npsavidge]

Hi,

I have tried the following in the load section


DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdSaveRecord
Me.txt_NHS_Number.SetFocus
Me.txt_NHS_Number = 0
Me.txt_NHS_Number = vbNullString

it still gives me a null in the UID field.

Thanks

Nathan.

 

[npsavidge]

Apologies all, it was the positioning of the save line.

It works now, thanks all

Nathan.

 

[pbaldy]

No problemo, Nathan. Glad you got it sorted out.