Linking Two Many-to-Many Subforms 1

[nyamrembo]

I have the following situation:
Main Form = tblCustomer with pkey as customerID

Subform1 = tbleReservationType has a lookup field with Lunch or Dinner value list and has ReservationTypeID as pkey

Subform2 = tblNumberofReservations holds details of Customer reservation and has NumberofReservationID as pkey ReservationTypeID as a fkey customerID as fkey.

I have been able to link subform2 to subform1 using the ReservationTypeID but I am not able to link Subform1 to Main Form because they don't have a common field. I also want to be able to synchronize the data so that if I select Joe Blow as customer and select Dinner as his reservationtype, I should only see the details for Joe Blow's dinner reservations and if I select lunch, then I should only see his lunch reservations. Please help me do this without a whole lot of code because I a really novice at VBA.
Your help is greatly appreciated!

 

[MajP]

Do you really need a subform1? It sounds as this could simply be a combo box where you pick the reservation type.

Depending on what you want to do, I think reservation type is simply an unbound combobox "cmboReservationType" where you pick either lunch or dinner.

You could then do this. Link subform 2 to the main form by customerID, and link to the combobox.

linkMasterFields:customerID;[cmboReservationType]
linkChildFields:customerID;ReservationTypeID

This design will only allow you to pick either lunch or dinner. If you want to show all customer records when nothing is picked in the combo. Then build the subform2 recordsource based on a query. And link it only by customerID. The query then would need a criteria something like
where reservationTypeID = forms!mainForm.cmboReservationType or forms!mainForm.cmboReservationType is null

None of these solutions require any code.

 

[nyamrembo]

Thanks, let me try this out and will keep you posted if it gives me what I am looking for.

 

[nyamrembo]

Sorry but I am having a little bit of difficulty. Please can you clarify how I should link combobox to subform2. What I have done is linked subform2 to customer mainform as you suggested with LinkMasterField as CustomerID;ComboReservationType and LinkChildField as CustomerID;ReservationType. Then I create an unbound combobox on the customer Form that is populated by a query from ReservationType table. When I run it, I get an popup error asking me to enter customerID.
What am I doing wrong? When I just link Subform2 by customerID, it looks fine. But I want to be able to filter or select diner/lunch when I am entering a new reservation for a new or existing customer.

tblReservationType has one-to-many relationship with tblNumberofReservations and tblCustomer also has a one-to-many relationship with tblNumberofReservations. What I want to show is dinner/lunch reservation for a specific customer. I am using a continuous form for the frmNumberofReservations which is subform2.

thanks,

 

[nyamrembo]

Hi, I got it to work where it's displaying all the records associated with dinner or lunch. That is great but now I will need to figure out how to limit the number of reservations based on a specific customer. Can you help? I have the combo box on Customer form which is a single form

Thanks

 

[MajP]

how do you determine how many reservations a specific customer is allowed? Is there a field that says customer X is allowed N reservations.

 

[nyamrembo]

There is no limit as to how many reservations a customer can have but a customer can only make two reservations at a time. For example, he/she can make a business lunch reservation and also a business dinner reservation with different number of guests. Basically, we are trying to just see how many of our customers make more than 20 reservations in a year and then we send them gift certificates. I could do this using a report but I also wanted to set the database where it's easy to see the pattern and numbers when they call in for reservations. There is more to it than that, it's my boss who asked for the feature.

So now my form displays All dinner reservation and All Lunch reservations depending on which one i select. But what I want to do is also be able to filter Joe Blow's reservations only based on the Dinner/Lunch selection from my combo box.
Hope this is not giving you a headache. I am willing to use code if you help with where to put it and how to set it up.

 

[MajP]

I am a little lost. Do you want to limit to two "reservations at a time"? I assume that means 2 lunch and 2 dinner on the same date. Or do you want to display the users count of reservations.

Look under the help for "aggregate query", "sum data" or "group by". You can make a query that counts the reservations by customer id. Then look at the dlookup function. You can use this function in an unbound control on your form to show the count for that customer.

But what I want to do is also be able to filter Joe Blow's reservations only based on the Dinner/Lunch selection from my combo box.

I thought that was working. Since you have two foriegn keys in your subform 2 then you can link subform 2 to two fields/controls. If you link it to the customerID in and the reservation type combo then this should do what you want. But that is what I thought you did.

Can you post a pic of your form? Take a screen shot and go to tinypic.com and post the link

 

[nyamrembo]

okay, I new i would give you a head ache. This is what my form looks like right now:
MainForm = has customer name and such.
MainForm = has Subform2 (frmNumberofReservations) linked to it. This is a continuous form type.
FormMain = has a combo box that pulls down all customer names.
FormMain = has a combo box that pulls down type of reservation (dinner or Lunch)
What I see would be ideal is be able to synchronize cmboCustomer with cmboReservationType. That is, i select dinner as my reservation type, and it populates all the customers that have diner reservation. So, I select one of those customers and I see their total number of reservation on subform2. How it is working now, I select a dinner or lunch option from the cmboReservationType combo box and all the customers associated with the option selected and displayed in subform2 but there is no list of those customers showing in cmboCustomer combo box. It's actually showing as blank . I know I know there is some code involved to filter this and that is where i need some help.
Sorry about the headache

 

[MajP]

No headache, no problem. There are several ways to do this, it all depends exactly how you want the interface to work. Subforms are very powerful and do a lot of this without any code, but may not be as flexible as you need.

If you want one combo to be dependent on another combo the term is often "cascading combo boxes". There are several faqs on this Faq702-4289 is one.

so assume the main form has a combo 'cmboReservationType' that has reservationType.
The second combos query would be something like
Select DISTINCT customerID, customerName, from tblCustomers INNER JOIN tblNumberOfReservations where reservationType = Forms!frmMain.cmboReservationType

Then requery the combo on the on enter or got focus event. See the faq.

If you pick Dinner in comboReservationType then comboCustomer would only show customers with dinner reservations.

now you can link the subform to these two combos like

linkmasterfields:cmboReservationType;comboCustomer
linkchildfields:reservationTypeID,customerID

 

[nyamrembo]

I think we are almost there. Will try your last suggestion and let you know. Glad you didn't catch a headache

 

[nyamrembo]

I am getting the following Error when I try to select the customer name (combo44). It's actually empty.
Error: "The SELECT Statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect" This is the statement on combo44 Recordsource : SELECT DISTINCT tblCustomer.CustomerID, [FirstName] & " " & [LastName] AS Name, FROM tblCustomer INNER JOIN tblNumberofReservations where tblReservationType.ReservationType = Forms!FormMain.comob26;

This is my setup as it stands now:
linkmasterfields:combo26;combo44
linkchildfields:ReservationTypeID,CustomerID

If this works better with code, I can try it. I am just begining to learn VBA. Problem is knowing where the code goes and how to call functions.
Thanks

 

[MajP]

That sql was incomplete. I did not proofread. Part of the inner join is missing.
In the query designer link your tblCustomer to your tblNumberOfReservations. In the criteria for reservationType put forms!formMain.combo26 (looks like a possible spelling error in your post. "comob26"). Then right click, select properites and you should be able to set "unique values = yes"

Your query should be something like
SELECT DISTINCT tblCustomer.CustomerID, [FirstName] & " " & [LastName] AS Name, FROM tblCustomer INNER JOIN tblNumberofReservations ON tblCustomer.CustomerID = tblNumberOfReservations.customerID where tblReservationType.ReservationType = Forms!FormMain.comob26;

Select a reservationType and then test the query and make sure it returns records.

 

[nyamrembo]

working at it...will keep you posted. Thanks

 

[nyamrembo]

Thank you, I got it to work...my headache from yesterday has vanished