Can't add record with ID from another table

Question

I have a database which functions similarly to a "help desk" system - an outside user creates a request ticket, then a consultant will use the database for various tracking and reporting.

There is a form that the consultant uses to display and work with the end user request. They select an existing record (request ticket) from a combo box. The form then populates with the information of the request, which the consultant can edit if needed.

I then have a series of buttons on this form that the consultant can use to open different forms (worksheets) and enter data in each one. These are all optional and will vary based on the type of request.

These worksheets all populate the "Consulting" table, while the initial requests are in a "Request" table. There is only one record in the "Consulting" table per record in "Request" (1:1 relationship).

Here is the code I have to open the forms (worksheets):

Private Sub ButtonGap_Click()
DoCmd.OpenForm "Gap Analysis", acNormal, , "[ID] = " & Me!ID, acFormEdit, acDialog
End Sub

This only partially works. If there is existing information in the "Consulting" table with an ID that corresponds to the ID from "Request" (what they selected in the combo box at the start), the worksheet will open pre-populated and editable. Great!

But... if there is no existing information in the "Consulting" table with an ID that corresponds to the ID from "Request", the worksheet opens blank, to a new record. This record autonumbers from where the "Consulting" table left off. This creates a problem if several requests have come in - we are not always working on them in order, so the new record ID in "Consulting" does not match the existing record from the "Request".

I'm sure it's something incredibly simple I'm overlooking. Can anyone help?

Answer 1

I was able to find a way to do this, it appears. If I missed something, please let me know! I don't want to break anything else. :)

I created a query to find the unmatched records, giving me a list of all IDs from Request without a matching ID in Consulting.

From there, I based an append query on these results, to append just the missing IDs to Consulting.

That query runs when the Request form is submitted:

Private Sub Form_AfterUpdate()
DoCmd.OpenQuery "Add Blank Consulting"
End Sub

Now my worksheets on the Consulting form are populating with the correct ID!