Avoiding Duplicates in a One-to-Many Tab Conrol Form with Multiple Tabs

[Curious]

Hi guys,

I've been experimenting with Tab Control forms in Access 2003.

I've setup 3 tabs on my form with a Tab Control.

The first 2 tabs, Customer Info 1, Customer Info 2, reference the same table.

The last tab, Payments, has a subform that displays all the payments the customer has made.

The underlying Record Source for the entire form is:

SELECT tblCustomers.*, tblPayments.* FROM tblCustomers LEFT JOIN tblPayments ON tblCustomers.ID=tblPayments.Customer;

The problem is, when I scroll through my Customers on the first two tabs (i.e. only referencing the Table: Customers), I get duplicates of the same customer for every payment the customer has made.

I setup a LEFT JOIN between the Customers and Payments table purposefully for this Form, to ensure that I could veiw all Customers, regardless of whether they had made a Payment or not.

Perhaps I am using the Tab Control inappropriately? Maybe it's better suited for a single table with many fields?

Not sure if this is a programming problem, or poor design. Not very familiar with Tab Control forms yet. But if I can get it working, it will fit everything together beautifully!!

Anyone got around this before?

 

[Curious]

* Bump To New Posts*

 

[Tim L]

Hi Curious,

I think that the form is giving you what you asked for. You said that you have set up the Record Source query so that you can see all customers whether they have a payment history or not. So if you look at the query in table view those customers who have multiple payments would be listed the same number of multiple times, consequently this is why you are seeing multiple instances of the same customer in the first two tabs on your form.

I think what you need to do is set the Record Source for the form back to one that only lists the customer, all of them. Then, to place the payments records on the third tab, use a subform - you can place a subform into a tab as well as a form, I guess this is your learning point. The Record Source for the subform *could* be your current query, although it wouldn't need to be, just so long as the PK-FK attribute/row/field (in your case tblCustomers.ID, tblPayments.Customer) is included and can be referenced back to the parent form.

You should then find that you can scroll through the records whilst looking at tabs one and two and you have just one entry per customer. Then when you switch to the third tab all of the payments for the currently selected customer are displayed.

Incidentally, if this is what you were after it would be good interface design practice to keep some of the customer information on the main form, and not on the tabs. Then when the user switches to between tabs, and particulary to the third tab they can that information. The sort of information that I mean would be, perhaps, their name and customer number, but this would vary depending on your what you application is for. Tabs are good for segretating information types about a record but the user should not have to constantly switch between them to get commonly/regularly required data.

HTH

Tim

 

[Curious]

Thankyou kindly Tim!

It was a simple matter of the record source property.

I did not realise I already had my subform setup to work correctly, without needed to

setup a LEFT JOIN between the Customers and Payments table purposefully for this Form, to ensure that I could veiw all Customers, regardless of whether they had made a Payment or not.

So restoring the main form's record source property to just the Customer's table was all that was needed.

Thanks for the tip on interface design. I already had a combo box looking up the customer's name at the top of each tab, I've now added the Customer ID as well.

 

[Tim L]

Thankyou kindly Tim!

You're welcome.

Thanks for the tip on interface design. I already had a combo box looking up the customer's name at the top of each tab, I've now added the Customer ID as well.

One of the things that I don't like about the combo box 'go to a record' wizard is that once you have gone to that record the value in the combo box stays there, even if you navigate to a different record, say with 'next record', 'previous record', 'first record' or pretty much any other way that you could mention that doesn't use the combo box again. I feel that because the combo is then displaying information that doesn't related to the new current record that the user is being lead astray/given false information, which is poor interface design (fails the 'feedback' check). Consequently I like to clear the contents of the combo box.

Because I am a bit lazy I just put the code to clear the combo into the AfterUpdate event which is fired when the user chooses an entry from the drop down . Although this covers all eventualities it does mean that once a record is selected you can't see what you actually chose in the combo (so I'm sort of breaking the rules myself). The alternative would be to cover every eventuality by clearing the combo on all of the events I mention above, and the rest... (see, lazy. )

Tim

 

[boblarson]

The alternative would be to cover every eventuality by clearing the combo on all of the events I mention above, and the rest...

Nah, just clear it in the On Current event.

 

[Tim L]

Bob,

Spotty Dog!!

Tim

 

[Curious]

One of the things that I don't like about the combo box 'go to a record' wizard is that once you have gone to that record the value in the combo box stays there, even if you navigate to a different record, say with 'next record', 'previous record', 'first record' or pretty much any other way that you could mention that doesn't use the combo box again. I feel that because the combo is then displaying information that doesn't related to the new current record that the user is being lead astray/given false information, which is poor interface design (fails the 'feedback' check). Consequently I like to clear the contents of the combo box.

Tim, I thought about what you said here, and using the suggestion by boblarson to work with the OnCurrent event, I used the combo box wizard to lookup my customers and GoTo customer on click from the list from the combo box.

But I wanted more than just to clear the value in the combo field after another button was pressed, e.g. through Next or Previous record selectors, I wanted to display which customer had been selected as well.

I put the following code behind the OnCurrent Event of my form:

Me.cboSelectCustomer.Requery
Me.cboSelectCustomer.Value = Me.txtCustomerID


This first line requeries my combo box incase I've added any new customers.

The next line constantly looks up the current customerID I've selected.

I put this combo box outside my Tab control, so it's directly on the Main form. This avoids me having to put separate combo boxes on each tab to lookup customer details.

Wow, this really works a treat!

Now I can simply scroll through, select from the combo box, select Next or Previous, and the combo box on the top of my form, always displays the correct customer details.

Thanks for your input!

 

[Tim L]

An excellent adaptation (and one that I think I may use in future). Thanks for posting your final solution and I'm pleased that it all worked out well.

Tim

 

[Curious]

I put the following code behind the OnCurrent Event of my form:

Me.cboSelectCustomer.Requery
Me.cboSelectCustomer.Value = Me.txtCustomerID


This first line requeries my combo box incase I've added any new customers.

The next line constantly looks up the current customerID I've selected.

In reference to my comment above, I realise now, a better way to do it is to requery the lookup combo box on the AfterUpdate Event, rather than OnCurrent.

Althought I don't notice slow performance with my application now, if my number of customers was to increase into 10's of 1000's, it would not be smart to requery the lookup combo box everytime I scroll through my list of customers. It is only necessary when a New Customer is added.

So I changed the above code to:

MyCustomersForm_OnCurrent Event
Me.cboSelectCustomer.Value = Me.txtCustomerID

MyCustomersForm_AfterUpdate Event
Me.cboSelectCustomer.Requery
Me.cboSelectCustomer.Value = Me.txtCustomerID