Access Help

open DataEntryForTable1 in design view and see the code on Not In List Event of the combobox, ComponentID. Normally, you will need to save is the ComponentID (not the Component #), and just

create a Query (qryTable1) that will join Table1 with Component table to display related fields from Component table.

Component.accdb

We need to step back and establish a better understanding of how a relational database application, like those created with Access, need to be designed and built. The way this question is framed suggests that you've not yet mastered that practice. 

All relational databases are based on data stored related tables. You have two to start with; there will be more, no doubt. 

The relationship between those tables is that the Things recorded in some (unnamed) table include Components listed in a second table. 

I.e. "Each TableOneThing includes one Component". 

Or "One Component is included in one or more TableOneThings."

The relationship is "includes". The relationship type is "One to Many".

To understand this better, I recommend you invest time in learning about Database Normalization. That's the method used to create properly structured relational tables. Search for Database Normalization--there are other ways normalization is used.

As Kidd_Ip stated, you need to designate the relationship formally. You do that in the relationship window.

Here's a screenshot showing the relationship between two tables. Note that Referential Integrity is enforced.

That means only values already in the CompanyTypes table are allowed in the CompanyTypeID field in Companies. Access will raise an error and refuse to save any other value in that field.

CompanyTypeID is the Primary Key of the table called CompanyTypes. It's an AutoNumber. I think your Component# is not an AutoNumber, based on the description. I think it probably means some number assigned by your organization for each standard component you use. It'll work as long as every record in the Component table has a unique value for Component#. But it must be designated as the Primary Key, or PK.

CompanyTypes is the "one-side" table I mentioned above, corresponding to your Component table.

One or more Companies can have the same value for CompanyTypeID. That's a Foreign Key, or FK, in the Companies table. Companies is the "many-side" table in this relationship.

In your case, the TableOnethings is the "many-side" table in the relationship with Components. Your FK is again Component#. 

I strongly recommend you change the name from Component# to something less likely to cause problems, such as ComponentNbr. The reason for that is Access doesn't like most non-Alpha characters in object names and having something like the octothorpe, #, as part of a name can result in subtle errors. Avoid that.

The rest of Kidd_lp's comments are appropriate for this task as long as our assumptions about the tables are valid. That is to say, if our assumption that you have a one-to-many relationship is correct, then the combobox on a form will be appropriate. 

Finally, all data entry needs to be done through forms, never directly in tables. 

Forms allow you to put structure around the data entry so that you can do things like offer a combobox of valid ComponentNbrs, use code to manage the process and control the flow of steps and so on.

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