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.