Prefill Parent Form Existing Record In Order To Add New Child Records

[chemguy120]

I have a form set up as follows:


Main Form: Customers: AccountNumber, FirstName, LastName
Subform: Reference: Date, Reference
Subform within Reference: Model, Serial

The flow goes from entering customer data, then related reference data, then related item data. One customer can have many references and one reference can have many items. The account number is indexed with no duplicates allowed.

I set up the account number control as a combo box that lists all previously imputed parent records and prefill the first and last name controls with the following:

Private Sub AccountNumber_AfterUpdate()


Dim db As DAO.Database, rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
    
    If Not rst.BOF Then
        rst.FindFirst "[AccountNumber]='" & Me!AccountNumber & "'"
        
        If Not rst.NoMatch Then
            With rst
                Me!FirstName = !FirstName
                Me!LastName = !LastName
            End With
        End If
    End If
    
    Set rst = Nothing
    Set db = Nothing
End Sub

This works well at pulling up the customer information, but when I enter the subform I'm informed the record cannot be created as it will cause a duplicate account number. My intention is to pull up an existing parent record to add additional child records. If the account number does not exist in any records then a new parent record will be created. I am a novice and most of what I've learned has been from searching through forums so please keep that in mind when drafting a reply. Any help is appreciated!

[John_G]

It sounds like you have the wrong field set as the primary key in the Reference table, or you have put a unique index where there should not be one. The Account Number does have to be in the Reference table, but if indexed, it cannot be unique. Do you have a Primary Key defined in the Reference table?

rst.FindFirst "[AccountNumber]='" & Me!AccountNumber & "'"

If the account number is numeric, then it must not be in quotation marks. It should look like this:
rst.FindFirst "[AccountNumber]=" & Me!AccountNumber

Is the AccountNumber control on your form bound to a table field, and does your main form have Customers as its recordsource?

[chemguy120]

The customer table has an autonumber field set as the primary key with a short text account number field indexed to not allow duplicates. There will only be one account number per customer name, I just find it easier to have a non-visible (to the user) autonumber as the primary key. The account number control is bound to the account number field in the customer table. The primary key in the customer table relates to a corresponding foreign key in the reference table and the reference primary key to it's foreign key in the item table.

The account number combobox on the main form is displaying all the existing account numbers and the parent form auto populates as expected, the problem is it tries to create a new parent record every time and a new parent record should only be created when there is no matching account number. I am able to create new parent records along with child records just fine for new accounts, I'm just not able add new child records for existing customers when it populates with an existing parent record without it trying to create a new parent record. A duplicate customer record is not the goal for existing customers, just adding new child records.

[orange]

chemguy120,
Can you show us a jpg of your tables and relationships?

[chemguy120]

The involved tables are Customer, Reference, and Return. Everything is working well except for this one issue. To note, the "AccountNumber" in the reference table has been changed to "Customer" to avoid confusion, I just forgot to take another screen shot. Everything else remains unchanged.

[aytee111]

First, in the combobox for the account number, have all the fields displayed. The in the AfterUpdate of the combobox, assuming you have the 4 fields as in your table,
Me!FirstName=Me!comboboxname.Column(2)
Me!LastName=Me!comboboxname.Column(3)
Set the column widths to zero if you don't want to show them.

The first subform, Reference, should have its Link Master property to the name of the combobox (assuming the CustomerID is in the first column). The LinkChild property should be set to AccountNumber, which is the name of the corresponding field in the Reference table. This field should not be shown to the user.

[chemguy120]

I'm not exactly following your response. Maybe this will help. Here is a blank copy of the full database so all customer info has been removed. The image above is from a tinkering copy so I don't mess up what's working in the real file. The involved tables are ATT_Customer, ATT_Reference, and ATT_Return. Data entry is done through the ATT_Customer_Entry form.
Equipment Returns Copy.zip

[aytee111]

1 - don't include attachments in the database, there is a 2gb file size limit and you will reach it very quickly. Rather store the files in a folder and put the path name in the database as a hyperlink
2 - remove lookups from your tables, they are cumbersome and cause confusion
3 - keep field names the same across tables - "accountnumber" is one thing on customer table but a completely different thing on reference
The table structures are being created as a "user" but they need to be done as a "developer". Keep user and techy things separate, everything in the navigation pane should be done in the best technical fashion, when you display data to the user it is done for the user - keep these two functions separate.

I have shown you a way to do your customer form, you need to do the same for reference - there needs to be a way to select a reference record.

[chemguy120]

Thanks, I'll delve into this a bit later when I have the time. Question, does the attachment actually load a copy of the document into the database? I was under the assumption that it just referenced the file to open it when needed. If not then yes, I'll need to change that. Is there anything to explain code-wise so I can learn what and why to apply later down the line? Feel free to reply at your convenience, I'm just doing my best to learn as I go!

[aytee111]

does the attachment actually load a copy of the document into the database?

Yes, hence the huge file size.

Is there anything to explain code-wise so I can learn what and why to apply later down the line?

Does this pertain to the attachment question, or is it general?

[chemguy120]

About the changes you made to help with my initial issue

[aytee111]

When a table is a record source for a form, you need to provide two methods: a way to enter new records and a way to select existing records. To enter new records (usually a command button, btw) you clear the screen, you already had that working. To select an existing record you need an unbound combobox (or listbox) which shows which records there are and allows the user to select one from the list. Any or all of the fields could be used as select criteria. When they have made their selection you filter the record source (in this case table) to show just that one record.

[chemguy120]

Putting it that way makes sense, thanks! I'll take a look through it all later this evening.

[John_G]

You have way too many tables in there. You dont need separate tables for each group of customers (ATT, CRK, etc). Put them all into one Customers table, with a field to indicate which group they belong to.

Oops - I posted by mistake - I have a lot to add to this. Sorry.

[John_G]

• You have way too many tables in there. You dont need separate tables for each group of customers (ATT, CRK, etc). Put them all into one Customers table, with a field to indicate which group they belong to.
• You can combine the Reference, Return and Shipment tables in the same way. You will have to "standardize" the structure, but there won't be many more fields than there are now.
• You don't need separate forms and reports for each group, either. It is quite easy to filter forms/reports to include only the data of interest.
• If the customer number is always numeric, then you should change it to a numeric type, make it the PK of Customers, and delete the autonumber field. You have the Account Number as numeric in ATT_Reference anyway, so it should be numeric everywhere
• You will need another table to contain the customer group ID's - ATT, CRK, etc - and their descriptions. Then your forms can have a combo box to select the group for a new customer, or filter customer lists for searching.

Question - can one customer be in more than one group of customers, i.e. can one customer be in ATT and CRK etc.? To me it makes sense that they could. If so, it complicates things a little bit, but it's easily dealt with.

I realize there is a lot to deal with here; these are just some things I would do to simplify it.

Just as an aside, in the database you originally posted, there is an error (at least one) in the relationships: ATT_Customer and ATT_Reference should be joined on AccountNumber (in each), not on Customer_ID in ATT Customer and AccountNumber in ATT_Reference.