Bound forms with sql backend (1 Viewer)

Derek

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
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

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
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

Wino Moderator
Staff member
Local time
Today, 12:57
Joined
Aug 30, 2003
Messages
36,118
Also, make sure the tables in SQL Server have primary keys defined. If they don't, they'll be read only from Access.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:57
Joined
Oct 29, 2018
Messages
21,358
How exactly did you migrate the data from Access to SQL Server? It might give us some clues why this is happening.
 

Derek

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
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
 

Attachments

  • Doc3.docx
    37.6 KB · Views: 72

CJ_London

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
I can't see identity increment and identity seed in primary key field . I scrolled down the properties. Please see attached the screenshot.
 

Attachments

  • Doc4.docx
    37 KB · Views: 73

Derek

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
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.

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

Minty

AWF VIP
Local time
Today, 19:57
Joined
Jul 26, 2013
Messages
10,355
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

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
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

AWF VIP
Local time
Today, 19:57
Joined
Jul 26, 2013
Messages
10,355
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

Super Moderator
Staff member
Local time
Today, 19:57
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 12:57
Joined
May 4, 2010
Messages
234
You are a star Minty !!!! It worked a treat. Thanks a million .

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
 

Users who are viewing this thread

Top Bottom