Help with adding new record - Access FE, SQL Server BE

[tucker61]

Sorry for another post but recently started moving my back end over to SQL.

When the DB went live this morning, the users let me know they could not add any records to the DB.

I have Primary Key Set,
identity specification set as yes, With increment value also set,
I have relinked the Table -
I can add data manually into the SQL Table

In access when i started entering data this would automatically assign a job number -

Do i have to use code to insert the job number into the linked table ? or is their another way ?

 

[Minty]

With SQL Server the Identity field isn't populated until the record is saved.

If your form relies on the number being present you would have to force a save before the rest of your code runs.

 

[tucker61]

With SQL Server the Identity field isn't populated until the record is saved.

If your form relies on the number being present you would have to force a save before the rest of your code runs.

I thought that was the case.
I have tried the AttemptSave - but this does not help.

    If Nz(Forms!frmintake.tbjobid, "") = "" Then
                DoCmd.RunCommand acCmdSaveRecord
                End If

The main form - only has 3 fields on it - TBInputdate, TBEntered_By and TBJobid.

The Input Date has a default on the form for todays Date, The TBEntered_By, looks up the user when something on the subform is completed, and the TBJobid is the Autonumber.

Also Tried inserting the Name and the Date using the below - But still did not insert into SQL.

Dim strsql As String

    strsql = "INSERT INTO tblQCintakeSQL ( Entered_By, Input_date) " & _
                          "SELECT " & GetLongName & ", " & SQLDate(Now()) & " AS Expr1;"

The print Debug shows the data
INSERT INTO tblQCintakeSql ( Entered_By, Input_date) SELECT Tucker, #07/26/2023 11:11:04# AS Expr1;

 

[Minty]

You can force a save on a form by simply using

Me.Dirty = False

But moving to a subform from the main form will also cause the main record to be saved.
I'm not understanding the process very well here.
What are the steps that are supposed to happen in what order?

 

[tucker61]

Form opens, and Input Date, Entered_By are already completed by Default. JOB ID is blank. (Showing as New on the screenshot)
Nothing else is entered on the main form -
When I move to the sub form - and start to enter the details - The Job ID is the link between the 2 forms -

 

[adhoustonj]

Do your users have the permissions to create/update/delete, etc in the SQL Server database?

 

[Minty]

Hmm - that seems odd behaviour.
I'm guessing because the forms' values are default values it doesn't see anything as being entered to cause the record to be populated.

Does the form always open on a new record?
Have you set the correct values in the Parent/Child sections of the subform?

 

[tucker61]

Do your users have the permissions to create/update/delete, etc in the SQL Server database?

Yes

 

[tucker61]

Hmm - that seems odd behaviour.
I'm guessing because the forms' values are default values it doesn't see anything as being entered to cause the record to be populated.

Does the form always open on a new record?
Have you set the correct values in the Parent/Child sections of the subform?

Yes - always opens on a new record - When looking through old records - no issues due to the job number being populated. Parent and child set up correctly.

 

[Minty]

In that case, rather than setting default values on the form, put something like this in the form load event:

Me.TBInputdate = Now(),
Me.TBEntered_By = GetLongName
Me.Dirty = False

Which should populate the fields and then save the record generating the new record ID.

 

[Pat Hartman]

Warning - I'm going to ask a very insulting question - Did you test this before distributing the new FE? If so, does that FE still work?

Also, NEVER just force a record to save to get around a stupid problem. You are saving empty records which itself should be an error.

The picture you displayed shows the subform as dirty. A form should NEVER open as dirty. What is your code doing to dirty the record? You should not have code in the open/load/current event that dirties a record. PERIOD.

You should ALWAYS have a minimum of two databases. The production database where the users enter production data and a test database where you test new code. And in reality, you almost always need at least THREE versions of a database. The middle version is generally referred to as QA (quality assurance) or user testing and you ALWAYS move from test (where you have thoroughly tested everything) to QA where the user gets to do testing. Once the users are happy with how the app works in QA, THEN and only then would you move to the production environment.

One of my clients, who did work for the armed forces, had EIGHT staging environments prior to the final move to production and the app had to go through testing each time it got promoted to the next level.

 

[tucker61]

Thanks all for the support - in the end i added 1 field from my sub form, to my main form, then added the dirty = false code.

Warning - I'm going to ask a very insulting question - Did you test this before distributing the new FE? If so, does that FE still work?

Also, NEVER just force a record to save to get around a stupid problem. You are saving empty records which itself should be an error.

The picture you displayed shows the subform as dirty. A form should NEVER open as dirty. What is your code doing to dirty the record? You should not have code in the open/load/current event that dirties a record. PERIOD.

You should ALWAYS have a minimum of two databases. The production database where the users enter production data and a test database where you test new code. And in reality, you almost always need at least THREE versions of a database. The middle version is generally referred to as QA (quality assurance) or user testing and you ALWAYS move from test (where you have thoroughly tested everything) to QA where the user gets to do testing. Once the users are happy with how the app works in QA, THEN and only then would you move to the production environment.

One of my clients, who did work for the armed forces, had EIGHT staging environments prior to the final move to production and the app had to go through testing each time it got promoted to the next level.

Hi Pat, good advice as always,

This change was in a test version, i currently have 4 different versions (Main, Test, Admin and Archive - (And backups of each every 2 hours).
The Main version is linking back into a Access back end.
I have now resolved the issue aboe by moving 1 field from my subform to the main form - and then adding the Dirty code.
I will make live tomorrow and then leave alone for a couple of days, to iron out any bugs and then next week move another group of tables into SQL, In total i think i have about 40 different tables to move.



On the Screenshot above - i had selected a contract - which Dirtied the form

 

[Pat Hartman]

I have now resolved the issue aboe by moving 1 field from my subform to the main form - and then adding the Dirty code.

Bad choice. Either the column ALWAYS belonged in the parent record or you have created a data anomaly by duplicating data. Sounds like the parent record has no data entry field. I'm not sure how that is possible but I suppose, if you are using a date as the only field that makes some sense. Forcing an incomplete record to be saved is not a solution.

 

[tucker61]

Bad choice. Either the column ALWAYS belonged in the parent record or you have created a data anomaly by duplicating data. Sounds like the parent record has no data entry field. I'm not sure how that is possible but I suppose, if you are using a date as the only field that makes some sense. Forcing an incomplete record to be saved is not a solution.

Once the team have been using for a few days with no issues, I will remove the duplicate field in the sub form.