docmd.runsql 3073 runtime error

[JeffBarker]

Hello all,

I'm hoping someone can give me a bit of a push in the right direction here, as I seem to be going round and round in circles!

I'm currently creating a continuous subform which acts as a data entry area, with a query containing two tables as the recordsource.

The first table contains an autonumber as the ID field, as well as some other data fields. The second table in the query is linked into a seperate back end, as we're using it to store attachments (keeping the size of the main db down) and links to the first table's autonumber ID field.

The attachment field from table two sits on the subform and allows the user to add an image or document etc as part of the data entry process.

I'm trying to create a routine using docmd.runsql on the attachment's after update event (as this is the last thing to be added) that will populate table one's autonumber ID field into the corresponding field on table two - thus linking the two.

I've tried:

Private Sub attMyAttachment_AfterUpdate()

'myID_ID is the field in table two, and me.txtID is table one's autonumber field that sits on the subform

me.MyID_ID = me.txtID
    
End Sub

But that doesn't work, so I'm currently fiddling around with a docmd.runsql approach instead:

Private Sub attMyAttachment_AfterUpdate()
    
    If Nz(MyID_ID.Value, vbNullString) = vbNullString Then
        DoCmd.RunSQL "UPDATE tblMytbl SET MyID_ID = " & Me.txtID
    End If
    
End Sub

But I keep getting a 3073 runtime error.

The reason I'm checking to see if MyID_ID is null first is from time to time the users may go back to a certain record and add or amend an attachment.

I have googled this, and also searched through this very website but to no avail - so I'm hoping you guys could amend the above code into something that actually works, or suggest a different approach!

Thanks in advance,

Jeff.

 

[JeffBarker]

Another user kindly PM'd me with some suggestion, and helped me come up with the following:

MyID_ID = DMax("ID", "tblMyTable") + 1
Me.ID = MyID_ID

Grabs the highest ID number from table one and then inserts it into the MyID_ID field of table two!