Updating 2 tables from a form, problem with requery

[RCurtin]

Hi,
I've been trying to get this working for days now but am not having any luck.

I've got 3 tables:
tblDrawingRegister
DrawingNum (PK)
DrawingName

tblDrawingRevisions
DrawingID (PK) auto-num
DrawingNum (FK to tblDrawingRegister)
Rev(PK)
LatestIssueDate
ElectronicCopy
OrderNum
DrawingStatus

tblReceivedDrawings
DrawingID (PK) auto-num
DateReceived
ActionCode
TrackingNum

I can import the drawing and revision information into my database. Then when hard copies are received by the company I need to add the information in tblReceivedDrawings. The form is based on a query that joins the 3 tables (see screenshot attached). Adding info for drawings already in the DB works fine.

The only problem is when I need to add a completely new drawing. The control source for txtDrawingNum is tblDrawingRevisions.DrawingNum so I was getting an error that there was no matching number in tblDrawingRegister. So I wrote a procedure in the change event of txtDrawingName to add this:

sqlDrawingsRegister = "SELECT * FROM tblDrawingsRegister"
rsAddDrawing.Open sqlDrawingsRegister, connection, adOpenForwardOnly, adLockOptimistic, adCmdText
    
        'Update the record based on input from the user.
        With rsAddDrawing
            .AddNew
            !DrawingNum = Me.txtDrawingNum.Value
            !DrawingName = Me.txtDrawingName.Value
            .Update
        End With
rsAddDrawing.Close
Me.Requery

The record is being added to tblDrawingsRegister. Its not working correctly though because I get the following runtime error at the last line of the code above.

Run-time error '3314'
The field 'tblDrawingsRegister.DrawingNum' cannot contain a Null value because the Required property for this field set to True. Enter a value in this field.

I would really appreciate any suggestions.

(I'd prefer not to use subforms if possible because I would need so many of them inside each other as I will be adding shipping information and there will be a table for the incoming transmittal numbers associated with each drawing)

 

[RCurtin]

Hi Pat,
Am so sorry about that - that was the wrong screenshot of the query - I've attached the right one now. (Those 2 fields were the primary key before but I've changed that.)

The relationship between tblDrawingRevisions and tblReceivedDrawings is definitely 1 to 1. When a particular revision of a drawing is received it has a tracking number, date received etc.
I've just realised that DrawingID should not be an autonumber. I've just changed it to a Long Integer.

This is the correct set up as it stands:
tblDrawingRegister
DrawingNum (PK)
DrawingName

tblDrawingRevisions
DrawingID (PK) auto-num
DrawingNum (FK to tblDrawingRegister)
Rev
LatestIssueDate
ElectronicCopy
OrderNum
DrawingStatus

tblReceivedDrawings
DrawingID (PK)(FK to tblDrawingRevisions)
DateReceived
ActionCode
TrackingNum