Struggling with Error 3022 in SQL Server Table

[tucker61]

Run time error 3022 - when the me.dirty = false code is trying to run.

Table in background is set to allow Nulls apart from the Primary Key Job_Id is the identity specification, set to increment by 1.
The BIT Field is set to Default 0.

The code runs after TbReference has been updated.

Private Sub tbReference_AfterUpdate()
Me.dirty = False
tbReference.Value = Trim(UCase(Nz(tbReference, "")))
 
If Nz(Input_by, "") = "" Then
    Input_by = Nz(DLookup("Long_Name", "tblqcusers", "User_Name='" & GetUserName & "'"), "")
End If

If Nz(Input_Date, "") = "" Then
    tbInputDate = date
End If

If Nz(tbDeliveryDate, "") = "" Then
    Me.tbDeliveryDate = date
End If

If Nz(tbCarrier, "") = "" Then
    tbCarrier = Nz(DLookup("Container", "Temp_Deliveries_Last_30", "Delivery_reference='" & tbReference & "'"), "")
End If
Exit Sub
Handler:
        Call LogError(Err.Number, Err.Description, "NC Current", Forms!FrmNonConformanceSQL.Job_ID)
        Forms!FrmNonConformanceSQL.Visible = True
        Exit Sub
End Sub

 

[theDBguy]

Which line is causing the error?

 

[Minty]

Can you edit the table directly, or add a new record?

 

[tucker61]

Yes - When editing table directly I only need to input 1 field and it saves.

Error is on the Me.dirty = false
Record is not saved

 

[Minty]

Out of interest, why do you need to save the record at that point?
What happens if you comment out the forced save?

 

[tucker61]

I have the record save at that point to assign the job number to the record. The job number is a link between form and sub form.

3 images below -
Image 1 - from when it was connected to Access Back end - and it worked,
Image 2 - is when i start a new record.
Image 3 - from now i have connected to SQL and job_ID is not populating due to SQL only assigning identity specification when the job saves - and i cant save it due to getting the error. I have removed the me.dirty code now and still get the error.


1

2

3

 

[tucker61]

Ok- working though the issue - created a new form - and it works without the me.dirty code - automatically assigning the job id - so it is definately a issue with the form, but not sure where to start.

Might just be easier starting a new form from scratch.

 

[Minty]

It sounds like something wasn't tied up correctly, maybe a subform link or similar?
If a new form works then it must be something else causing the premature save or some weird corruption.

 

[Pat Hartman]

Ok- working though the issue - created a new form - and it works without the me.dirty code - automatically assigning the job id - so it is definately a issue with the form, but not sure where to start.

Do you have the master/child links set correctly? You should NEVER, EVER, EVER, EVER (did I say NEVER) dirty the form in the form's AfterUpdate event. You are essentially putting the form into an infinite loop.

If you have a popup form, you don't have the help of Access to populate the foreign key. Therefore, in the OpenArgs, pass in the FK. Then in the popup form's BeforeInsert event,copy the OpenArgs to the FK

Me.SomeFK = Me.OpenArgs

That will link every new record without sending the form into an infinite loop.