One to Many, Form Fill

[one3nine0]

Frustrating because I did this once before a while back but I cannot seem to do it again...

I have a table [CustomerInfo] with customer info: Customer Number, Customer Name, Address, etc. There is no key as this is a linked table, but the Customer Numbers are unique

I have a second table [Notes] for notes about the customer: Note ID (Key), Note Date, Customer Number, Notes

The goal is to have a form where the user can input a customer number which would then populate text boxes with the corresponding customer info, as well as any notes for that customer in a subform. The form would also allow user to add new notes for the customer in the notes subform. It would not change anything in the [CustomerInfo] table.

I know I need to establish a one to many relationship, and I attempted to do this using the lookup wizard and form wizard, but I couldn't get the customer info fields to auto populate. I know I need to create an After Event code, but not sure how to establish which text boxes correspond to the correct column.

Am I on the right track? What else do I need to do?

 

[welshgasman]

Create a mainform (single form) whose source is the Customer table.
In the header of this form either put an unbound textbox to enter the customer data and use the AfterUpdate event of that control to retrieve the record, or you could use a combo with data from the customer table.
Create another form whose record source is the Notes table.
On the main form add a subform control and select the second form.

Access will either ask for the link, or know it from your relationships.

That is about it really.

 

[one3nine0]

Thanks. So this worked, but the format is a little different from what I had before.

In the subform for the notes, I remember it used to look like a table. So if I entered in a customer number, customer info would show up then in the subform it would look like:

Note ID	Note Date	Notes
1	11/18/19	etc etc etc
(New)

<tbody>
</tbody>

Right now it looks like a traditional form, like if you used the form wizard. Is there a way to change how the subform looks?

 

[welshgasman]

You make it a data sheet, or a continuous form. I tend to have used datasheets in the past and have only just started using continuous forms.

 

[one3nine0]

I am not sure what just happened.

I made sure Customer Number in the Customer Info table was indexed with no dups, so that the relationship would be a one to many relationship.

I added in the subform for the notes, and changed the form into a table via changing the source object of the subform to Table.Notes

Everything looks right, but now the combo box will no longer allow me to manually enter in a customer number. I can only select via drop down list, but I cannot select anything that doesn't have a note already established.

The Customer number object is still a combo box, and "limit to list" is No. What did i do wrong?

 

[one3nine0]

Quick note: It looks like my original form based off the Customer info table is limited to the number of notes already entered. IE i only see 4 records, where there are over 40,000 rows of customers...

 

[one3nine0]

New Info: 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?

 

[welshgasman]

No, you have already stated that some customers might not have any notes.
Your best bet is to look at the Northwind database and see how they do it.

FWIW I have just used that to create another customer form when the Customer table is selected.
If you relationships are set up correctly, even 2007 will build a form for the Customer, but also a control for the datasheet of the orders.
I then put a combo in the header, used the wizard to set it up, and then added this code to the After_Update event of the combo

    On Error Resume Next
    Dim rst As Object
    Set rst = Me.RecordsetClone
    rst.FindFirst "ID = " & Me.cboCustomer
    Me.Bookmark = rst.Bookmark
    Set rst = Nothing

Then when I select an entry in the combo, the customer details are showed in the form and the orders for the customers in the order form
If you have no notes for a customer then the the notes datasheet should be empty.
You should have an arrow point to the customer table and an infinity symbol on the notes table. Again if you look at the Northwind database you will see how the relationships are setup.

HTH

 

[Micron]

but now the combo box will no longer allow me to manually enter in a customer number.

Then you must have the limit to list property set to yes. However, unless you want to expose the customer combo data to editing or deletion, it is best to make this control unbound where its row source is set to a query that gets only the customer already stored in the db, and limit it to list (trying to turn this into a form that allows new customer entries is probably not a good idea). Also, being unbound, it will have no record source. In this control's AfterUpdate event, you requery the main form. Since you would/might 'lose' your unique value upon which you link the subform, you would add a textbox to hold the unique value (you don't need to show it since the combo displays that data) and link the subform to the main form via the hidden textbox.

As for the combo row source, there are at least 3 ways to populate it: table, query or just paste a valid sql statement into the row source. The easiest for you to manage would be a query.

 

[one3nine0]

Figured it out. Finally. Thanks everyone!