Access Help

How about this:

1. Set Up a Relationship Between Tables
• Make sure your Component # in Table 1 is set up as a foreign key referencing the Component # field in Table 2.
• This ensures that only valid entries from Table 2 can be saved in Table 1.
2. Use a Combo Box for Entry
• Instead of a text box, consider using a combo box in your data entry form that pulls existing component numbers from Table 2.
• If the user selects an existing component, all good. If they try to enter a new one, you’ll trigger a form.
3. Create a Pop-Up Form for New Components
• In your form’s event properties, use VBA (Visual Basic for Applications) to trigger a pop-up when a non-existent Component # is entered.
• The pop-up form allows users to input the new component number and its associated data into Table 2.
4. Prevent Saving if the Component # Doesn't Exist
• In the table design, use Referential Integrity so Access prevents saving invalid component numbers.
• Use BeforeUpdate VBA code in the entry form to check if the Component # exists in Table 2 before saving.

Below VBA to trigger popup:

Private Sub ComponentNumber_BeforeUpdate(Cancel As Integer)
    Dim compExists As Variant
    compExists = DLookup("Component#", "Table2", "Component#='" & Me.ComponentNumber & "'")

    If IsNull(compExists) Then
        MsgBox "Component # not found. Please add it first.", vbExclamation, "Missing Component"
        DoCmd.OpenForm "NewComponentForm", , , , acFormAdd
        Cancel = True
    End If
End Sub