Forms that add records to a table but also pull records from a different table?

[maax555]

Hi, been struggling with this for a while and would really appreciate some help.

I have a table called CableParts. I use the form frmAddNewParts to add initial basic information to create the record in CableParts.
I will later add another form that add more details to the existing records in CableParts.

I have another big table called tblQuality. This has many fields in it so again I will be using an initial form to create the record and then probably another form or more to update the records in tblQuality.
I think there is a better way to do the large tblQuality table but I would like to understand how to achieve the form frmOpenNCR before I look into optimising.

So I need to have quite a few fields in the frmOpenNCR form but once I can get the following working I hope that will allow me to complete the form.

So the frmOpenNCR will have its own record ID and the following initial fields,



Customer. This will be a combo listing all the customers already added in the Customers table.
Partnumber. This will be a combo filtered according to the Customer field chosen. If the partnumber is not already created then the combobox will allow me to add a new partnumber.
This partnumber would be created in the tblQuality table against QCRecordID.
Next in the frmOpenNCR form would be description field. If the partnumber already existed then the description would be pulled in from the CableParts table.
Next would be daterecorded field, this would be stored in the tblQuality table.

As you will see in the tblQuality there are many fields but given help and advice on what I have above I hope to be able to complete the rest of the form myself.

I would be very grateful as I have literally spent 30+ hours trying to get this working.

thanks Russ

[jojowhite]

ask copilot, chatgpt, agnes ai for a starter on how to setup your ncr database.
tblQuantity is too "wide" table and need to be split into smaller related tables.

[xps35]

There are several redundant fields in tblQuality. Customer and Description are part of CableParts and should not be stored in tblQuality.

I made a example for frmOpenNCR. It has not an option to add a new part (yet).
As tblQuality has so many fields you could build a form with multiple tabs to enter the details.

[maax555]

Hi Peter, many thanks. However this is not working for me.
Customer combo works as expected.
Part is pulling in the description of the part number and not the actual part number.
Also if the part number required does not already exist, part number combo should allow a new part number to be entered and saved as part of the tblQuality.
NCRref is working as expected.

thanks for looking

[maax555]

ask copilot, chatgpt, agnes ai for a starter on how to setup your ncr database.
tblQuantity is too "wide" table and need to be split into smaller related tables.

Hi Jojo, yes I do often try Chatgpt first and for a lot of queries its really quite good. However on the more complex queries I often have errors. When I report back the errors, CHagpt does attempt to correct but mostly fails.

[Ken Sheridan]

Part is pulling in the description of the part number and not the actual part number.
Also if the part number required does not already exist, part number combo should allow a new part number to be entered and saved as part of the tblQuality.

The part combo box in Peter's amended form is bound the the PartNumberID column, but is set up to show the description. If you want it to show the part number then the AfterUpdate event procedure of the customer combo box would be amended as follows:

Private Sub cboCustomer_AfterUpdate()

    Me.PartNumberID = Null
    Me.PartNumberID.RowSource = "SELECT PartnumberID, PartNumber FROM CableParts WHERE CustomerID = " & Me.cboCustomer & " ORDER BY PartNumber"

End Sub

To insert a new row into the CableParts when a new part number value is entered into the combo box, firstly you'd need a form bound to CableParts (frmCableParts in the example below), and the code for the combo box's NotInList event procedure would be as follows:

    Dim ctrl As Control
    Dim strMessage As String
    
    Set ctrl = Me.ActiveControl
    strMessage = "Add " & NewData & " to list?"
    
    If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
        DoCmd.OpenForm "frmCableParts", _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:=NewData
        ' ensure frmCableParts closed
        DoCmd.Close acForm, "frmCableParts"
        ' ensure part has been added
        If Not IsNull(Dlookup("PartNumberID", "CableParts", "PartNumber = """ & _
            NewData & """")) Then
        Response = acDataErrAdded
        Else
            strMessage = NewData & " was not added to CableParts table."
            MsgBox strMessage, vbInformation, "Warning"
            Response = acDataErrContinue
            ctrl.Undo
        End If
    Else
        Response = acDataErrContinue
        ctrl.Undo
    End If

In the Open event procedure of frmCableParts you'd put the following code to assign the new part number you entered into the combo box to the PartNumber control's DefaultValue property:

    If Not IsNull(Me.OpenArgs) Then
        Me.PartNumber.DefaultValue = """" & Me.OpenArgs & """"
    End If

You could of course use your frmAddNewParts form in place of frmCableParts if you only want to insert the limited data.

Note that before you can insert a row for the new part number into tblQuality you must insert a row into the CableParts table. Otherwise referential integrity would be violated. You could extend my code above to execute an INSERT INTO statement to do this, or the code could open another bound form, passing the new part number into it as its OpenArgs property.

[mike60smart]

Hi
Why are you not using a Main Form based on "CableParts"
with a Subform based on "tblQuality"

[xps35]

Part is pulling in the description of the part number and not the actual part number.

Also if the part number required does not already exist, part number combo should allow a new part number to be entered and saved as part of the tblQuality.

As others explained, tables CableParts and tblQuality are joined by PartNumberID. The combobox stores a PartNumberID (that is the foreign key) and shows the description. Partnumber has no role here because it not a key nor a foreign key. So my database works as required.

I told you that my example has "not an option to add a new part (yet)". Solutions to this have been provided to you also in the mean time. I added the option in a new version of my example.