Form/Subform and One to Many Relationship

[one3nine0]

I have two tables: Customer Info and Notes
Customer Info: Customer number (Unique Index), Customer Name, Etc
Notes: NoteID (Key), Note Date, Notes

I established a one to many relationship, and then created a form based off of the Customer info table, and use it to auto populate fields from the table based off a combo box using customer number. This had over 40,000 records.

Then I added a subform based on Notes, but changed the Source object to: Table.Notes so that I would be able to see any notes and add to the notes table as a datasheet for a customer selected from the combo box.

My problem is that I can no longer enter or select a customer number in the combo box. The number of records of the original form have shrunk to the number of notes. I have to create a new record in the main form order to select a different customer, which isn't what I want to do since that would create a new line in the customer info table which is not what I want to do.

Where did I go wrong?

Edit: I found that if I try to select a customer from the drop down menu the following shows on the bottom of the screen: Records in the Table notes would have no records on the one side. Does that mean that my one to many relationship was made in the wrong direction?

 

[CJ_London]

don't know without seeing your relationships, but the data you provided on the notes table does not include a customer foreign key, so perhaps that is the problem

 

[arnelgp]

Did you add Cutomer Number to notes table, this is the linking field.

 

[Pat Hartman]

and use it to auto populate fields from the table based off a combo box using customer number.

Are you saying that you are using the unbound combo to filter the main form records? or are you saying that you are actually copying data from the combo to unbound fields on the main form?

1. the main form should be bound to a query of the customer table. Select whatever columns from customer that you wish to view or update. If the autonumber is shown, it will be locked so set its tab order to No. No need to allow anyone to tab into a control they cannot change.
2. create a subform that is bound to the notes table. You can base it directly on the table.
3. add the subform to the main form. Make sure that the master/child links are set correctly. If not (because you did not define a relationship), then set the link manually. This is what Access uses to sync the main form with the subform.
4. add an unbound combo to the main form header. This will be used to pick a specific customer record.
5. modify the mainform's ReordSource query to reference the unbound combo as criteria:
Where CustomerID = Forms!frmCustomer!cboCustomerID
6. In the Click event of the search combo, requery the form.
Me.Requery.

 

[one3nine0]

I am using an unbound combo box to filter for the main form records.

I think an issue is that the customer number is not an autonumber, it is a number a different division set up, and I was hoping that by indexing it (with no duplicates) that it would be treated the same and let establish a one to many relationship.

I did not base the main form off a query, I based it on the actual Customer Info table.

I will try again using Pat's suggestions and post results.

Thanks everyone

 

[Mark_]

Just to be clear, you do not have Customer Number in your notes table, correct?

In any one to many relationship you need to have a way of telling which child is for which parent. You would normally keep a copy of the Customer Number (your unique ID) in the child.

More specifically I would really add an autonumbered Primary Key to your Parent table (Customer Info) with something like CustomerInfo_ID as its name. I would keep a copy of it in your Notes file and I would use the exact same field name. In the Notes file it would be a number, not autonumber.

When you set up the relationship, match the two together. When you put on the subform it should work fine.

When creating the subform, have it run off of your Notes file. If your relationship is set up proper ACCESS will take care of making sure the matching child records are shown. Just make sure that the entry on the subform uses the field from the file (is bound) rather than being unbound.

 

[Pat Hartman]

@one,
Using a field from another application as a primary key in your application is dangerous. It makes you dependent on changes made by them to the format of the key. It is better to take their code field and store it as text. Make a unique index on it but also add an autonumber and make the autonumber the primary key. Use the autonumberPK in ALL relationships. In your app, the key from the other app is simply a data field that the business rule says must be unique.

 

[one3nine0]

So I get an excel download from SAP of the customer info every day, that automatically replaces the old file. This process is something someone else set up and I am using. The customer numbers are part of this download.

I linked that file into Access, but realized I needed to make some changes so I created a new table [Customer Info] off the linked one and ran some queries to make the necessary changes, but I did not add an autonumber field, 1 because I didn't know how to add one via query and 2 because of below.

The concern with making a new autonumber field and then using that to link to my notes table, is that the order in which the customers are listed in the original download are not consistent due to changes people might make in SAP, which would change the autonumber and then give me the wrong notes. So I didn't think I needed it.

Was my thinking wrong?

 

[arnelgp]

Then add a cutomer number to the note so you can identify which cust the notes belong.

 

[Mark_]

@one3nine0

Does the customer number in your import change? i.e. if I go into customer 1 and make a change, does their customer number become 2? Can the customer number be edited by users in the system you are importing from?

If NO, simply use the customer number as a foreign key in your notes file.
If YES, how would determine which customer is which?

The order in the excel file should not have a bearing on your process. The only problem I can see is making sure you only have one record per customer and that you don't get rid of customers if they are no longer in the import file. Depending on HOW you do the import this can be relevant.

If you delete customer information then import you may have orphan notes. You would not be able to use an autonumber as a reliable primary key either.

If you check each record to see if it exists BEFORE adding/updating, having an autonumber will work fine as you won't be deleting and re-adding the same customer.

 

[one3nine0]

So yes, the customer number will always be unique and not change.

To make the customer number the key I would follow the example here?
stackoverflow.com/questions/24238068/access-sql-alter-column-to-autonumber

 

[Mark_]

OK, I made a very quick, very simple version of what you are looking for. I am attaching it.

For a real system I would make a real sub form that I would include, but this is as simple an example as I can make.

Take a look at the relationships.

Like your case you can enter a Customer ID. I am relating based on Customer ID. Please let me know if this answers most of your questions.

 

[one3nine0]

Ok, I think I may have figured out why my relationships aren't working. I did not realize that my customer ID had to be a long integer data type in order to establish a one to many relationship. My customer ID field from Excel is "Double".

I'm going to change my customer info table so that I import the table into access instead of linking it. That should help

 

[Mark_]

It is not REQUIRED to be a long. You would just need to make sure it is matching in both tables. Also look at the relationship that I established between the two, it makes things much easier.

 

[one3nine0]

@Mark @Pat

Thank you both so much. Your information helped me figure this out!

 

[Pat Hartman]

Excel is not as smart as it thinks it is. The CustomerID is most certainly not a double. However, unless you import the downloaded data into an empty table where you control the data types, you are stuck with what Excel thinks it is. At least you got it to work. Log this in your problems waiting to happen list.