Bound forms with sql backend

Derek · Jul 8, 2019

Hi guys,

I have done data migration from Access to SQL server and the forms which are unbound work fine but the bound forms don't work ( no data gets saved down and pulled through )

As with Access backend , within bound forms automatically 'unique ref' gets stored within a field but now with my sql server backend , that field is blank (no unique ref gets stored)

Any quick workaround for that?

Thanks,

CJ_London · Jul 8, 2019

not enough information to suggest a workaround but clearly

that field is blank (no unique ref gets stored)

is where the problem is. You need to investigate why it is blank

pbaldy · Jul 8, 2019

Also, make sure the tables in SQL Server have primary keys defined. If they don't, they'll be read only from Access.

theDBguy · Jul 8, 2019

How exactly did you migrate the data from Access to SQL Server? It might give us some clues why this is happening.

Derek · Jul 9, 2019

Yes there is primary key defined in the table. Please see attached the screenshot.

I have written the following function to generate primary key for the record in the form. SO whenever a user types in first control then it calls this function . And also when Save button or Exit button is pressed then it calls this function. Not sure if I am going in the right direction or there is a quick workaround for this ?

In the code , gRef is a global variable.

Code:

Private Sub CreateUniqueID()
If IsNull(Me.txtGenMeetingID) Then
gRef = DMax("GenMeetingID", "tblGeneralMeeting") + 1
Else
gRef = Me.txtGenMeetingID
End If
End Sub

CJ_London · Jul 9, 2019

just to confirm - scroll down to the identity specification section in column propertes

what values do you have for identity increment and identity seed?

Derek · Jul 9, 2019

I can't see identity increment and identity seed in primary key field . I scrolled down the properties. Please see attached the screenshot.

Derek · Jul 9, 2019

I used upsizing wizard for data migration and followed the steps.

All seems to be working fine for unbound forms. It's just bound forms which are not generating unique ref by default when first control value is input. With MS Access backend, whenever we type something in the first control on the form then it gives unique ref for bound forms but with sql server backend no unique ref gets stored automatically on the form although when we save down the form then automatically unique id gets generated in the table.

theDBguy said:
How exactly did you migrate the data from Access to SQL Server? It might give us some clues why this is happening.

Minty · Jul 9, 2019

Derek said:
I can't see identity increment and identity seed in primary key field . I scrolled down the properties. Please see attached the screenshot.

You need to open the plus section next to the Identity Spec to get to the seed and increment properties.

From memory SQL populates the new record ID at the point of saving the record not on the initial population of a field, so maybe this behaviour is confusing the issue?

CJ_London · Jul 9, 2019

although when we save down the form then automatically unique id gets generated in the table.

sounds like seed and increment are set

Minty is correct, the ID is not created until the record is saved in sql server.

If you are using autonumbers, not sure what the benefit is of your createuniqueID function - as a predictor it is not safe - particularly if calculated when you first start a new record (another user starting another new record before the first user has saved would get the same value)

And why does it matter anyway? ID's should be of no interest to the user and should not be assigned a meaning by the developer beyond being a unique identifier to the record. The fact there may be gaps due to records being deleted (and in the case of access also not completing a new record) should not matter. Nor should the increment be taken as gospel as being the order in which records were inserted.

Derek · Jul 10, 2019

CJ_London, in sql server , the ID has int datatype and seed and increment are set for tat field. ID is important in this case because sometimes the users partially complete the form and close it down so in this it doesn't give any record id to the users so they can go back in to complete the form .

When using MS Access , it does give record id on the initial population of a field so when users close down the form in the middle then it tells them the record id .

Hope it makes sense.

Minty · Jul 10, 2019

You can't alter this behaviour with SQL server so, in your case simply force a save after the first field that isn't the ID is completed.

Put this in any field that the user might complete. The ID would then populate.

Code:

If Me.Dirty Then Me.Dirty = False

CJ_London · Jul 10, 2019

ID is important in this case because sometimes the users partially complete the form and close it down so in this it doesn't give any record id to the users so they can go back in to complete the form .

I understand - but what if the user forgets to write down the ID or writes it down incorrectly?

It's your app but would think it better if you have a query that returns incomplete records and the user selects from that list - could be as simple as an unbound combo which perhaps goes red if the rowsource recordcount<>0

Derek · Jul 10, 2019

You are a star Minty !!!! It worked a treat. Thanks a million .

Minty said:
You can't alter this behaviour with SQL server so, in your case simply force a save after the first field that isn't the ID is completed.

Put this in any field that the user might complete. The ID would then populate.

Code:

If Me.Dirty Then Me.Dirty = False

Bound forms with sql backend

Derek

Registered User.

CJ_London

Super Moderator

pbaldy

Wino Moderator

theDBguy

I’m here to help

Derek

Registered User.

Attachments

CJ_London

Super Moderator

Derek

Registered User.

Attachments

Derek

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

Derek

Registered User.

Minty

AWF VIP

CJ_London

Super Moderator

Derek

Registered User.

Similar threads

Users who are viewing this thread