subform connected to main form and another subform

[Scott]

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

Anyone have any thoughts on this? I appreciate any help you can provide.

Best regards,
Scott B

 

[JBowler]

This may or may not be of help but here goes.

I had a similar problem and wanted may subforms of subforms. In the end I
made a button that opned a form with a subform. So as an example that may
make sense to you, the main form has the People data. The subform on the
People form will show thier stays. So call it Bookings for example. Now on
the bookings subform create a button that opens a new form called Purchases.
On the Purchases form make the data source be the bookings form and just put
the Date for the bookings and the BookingID(invisible) at the top. (reason:
it will link the bookings ID on the booking subform of Poeple form and the
new form that opens. Then add a subform of all the items purchased. This
will give you a form to enter People/Guests and log thier stays in the
Booking subform. When they make a purchase you click the button on the
bookings sub form and that opens the window to log purchases. This should
make the presentation side easier and reports can be easily made. Now you
will also need to have the PeopleID linked to the bookings and then both
brought to the purchases. It will be too hard if not impossible to bring the
PoeplelD and Bookings ID to the Purcahses table. This is where the query
will do it for you then reports based off of that.

Is that clear? Hope that helps.

JBowler

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

Anyone have any thoughts on this? I appreciate any help you can provide.

Best regards,
Scott B

 

[Marshall Barton]

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

The way I do this kind of thing is to use the Current event
of the stays subform to set the stayID value into a hidden,
unbound text box on the main form. Then the purchases
subform can use this text box as the Link Master and the
stayID field as the Link Child.

 

[Scott]

Dareen, Thanks for the quick reply. I have been applying your
instructions, but I have hit a snag. I cannot get the data tab of any of
the 3 forms to show the child and master links properties. It occurs to me
that I did not describe completely the list of tables, just an outline and
did not include queries and did not inlcude the relationship diagram. Do
you need these? Any thoughts on where Access has stolen my child/master
properties to?

Best regards,
Scott B

Double subforms require a little ingenuity, but it will work.

Let's say you have a field GuestID on your main form (GuestMain).

Subform1 (Stay) contains fields StayID and GuestID.

Subform2 (Goods) contains fields StayID and GuestID.

On the DATA tab of your Properties box, Subform1 should have:

Link Child Fields: GuestID
Link Master Fields: GuestID

You need to add another TextBox to your main form called: txtStayID.
It's Control Source should be: =Forms!GuestMain.SubForm1!StayID

SubForm2 should have:

Link Child Fields: GuestID, StayID
Link Master Fields: GuestID, txtStayID

SubForm2 will display items purchased when you select the particular Stay in
SubForm1.

Hope this answers your questions.

Darren

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

Anyone have any thoughts on this? I appreciate any help you can provide.

Best regards,
Scott B

 

[Scott]

Darren, et al, OK I found the way to enable the child/masater link
properties, now I get an "Enter Parameter Value" box with "txtStayID" and a
white space below to fill in. I do not know what I have done wrong.

Best regards,
Scott

Dareen, Thanks for the quick reply. I have been applying your
instructions, but I have hit a snag. I cannot get the data tab of any of
the 3 forms to show the child and master links properties. It occurs to me
that I did not describe completely the list of tables, just an outline and
did not include queries and did not inlcude the relationship diagram. Do
you need these? Any thoughts on where Access has stolen my child/master
properties to?

Best regards,
Scott B

Double subforms require a little ingenuity, but it will work.

Let's say you have a field GuestID on your main form (GuestMain).

Subform1 (Stay) contains fields StayID and GuestID.

Subform2 (Goods) contains fields StayID and GuestID.

On the DATA tab of your Properties box, Subform1 should have:

Link Child Fields: GuestID
Link Master Fields: GuestID

You need to add another TextBox to your main form called: txtStayID.
It's Control Source should be: =Forms!GuestMain.SubForm1!StayID

SubForm2 should have:

Link Child Fields: GuestID, StayID
Link Master Fields: GuestID, txtStayID

SubForm2 will display items purchased when you select the particular Stay in
SubForm1.

Hope this answers your questions.

Darren

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

Anyone have any thoughts on this? I appreciate any help you can provide.

Best regards,
Scott B

 

[Marshall Barton]

Darren, et al, OK I found the way to enable the child/masater link
properties, now I get an "Enter Parameter Value" box with "txtStayID" and a
white space below to fill in. I do not know what I have done wrong.

The Link Master property can be either a text box on the
main form or a field in the main form's record source
table/query.

The link Child property **must** be a field in the subform's
record source table/query. It sounds as if you used a text
box here.
--
Marsh
MVP [MS Access]

Dareen, Thanks for the quick reply. I have been applying your
instructions, but I have hit a snag. I cannot get the data tab of any of
the 3 forms to show the child and master links properties. It occurs to me
that I did not describe completely the list of tables, just an outline and
did not include queries and did not inlcude the relationship diagram. Do
you need these? Any thoughts on where Access has stolen my child/master
properties to?

Best regards,
Scott B

Double subforms require a little ingenuity, but it will work.

Let's say you have a field GuestID on your main form (GuestMain).

Subform1 (Stay) contains fields StayID and GuestID.

Subform2 (Goods) contains fields StayID and GuestID.

On the DATA tab of your Properties box, Subform1 should have:

Link Child Fields: GuestID
Link Master Fields: GuestID

You need to add another TextBox to your main form called: txtStayID.
It's Control Source should be: =Forms!GuestMain.SubForm1!StayID

SubForm2 should have:

Link Child Fields: GuestID, StayID
Link Master Fields: GuestID, txtStayID

SubForm2 will display items purchased when you select the particular Stay in
SubForm1.

Hope this answers your questions.

Darren

:

Greetings. I have a dilemma that I have been scratching my head on for a
while now. I have a db keeping track of guests, stays and purchases at my
B&B. There are 3 main tables of data, no. 1 of guest data (name, address,
etc.) no. 2 of stay info (room, rate, date of stay, length of stay, etc.)
and no. 3 of purchase info (item purchased [granola, coffee, etc.], cost,
quantity). There are also several tables that hold data like a list of
rooms, list of items to be purchased, type of guest, type of referral.
They are used to populate combo boxes.

I have 1 main form that I use to enter data to the 1st main table. I have
a subform used to enter data about a guest's stay. The subform also has a
series of calculated fields to display the cost of the room and tax and
total on the main form. Right now this all works fine. I have reports
that I can get all this info out of with no problem.

The problem comes in when I try to create a second subform to enter data
about purchases that guests make such as bags of coffee or bags of granola
or other value added items beside rooms. I keep track of this seperately
so I can know what guests have purchased in the past. I need to have the
subform link to the 1st main table, but also to the 2nd main table of
stays. I need to know what giuest purchased what during which particular
stay. I have some guests who stayed many times. I want to know if they
bought coffee the last time they were here or granola last year. I also
need to be able to calculate the purchases for each stay like I do for
stays so I can charge for them.

The form is a tabbed form with the guest info form on the first tab and the
stay info form on the second tab. I want to put the guest purchase form on
the second form so I know what stay I am entering purchase data for.

Anyone have any thoughts on this? I appreciate any help you can provide.

Best regards,
Scott B

 

[Scott]

The Link Master property can be either a text box on the
main form or a field in the main form's record source
table/query.

The link Child property **must** be a field in the subform's
record source table/query. It sounds as if you used a text
box here.

SUCCESS!!!
Thanks to all of you for the help.

Best regards,
Scott

 

[Scott]

SUCCESS!!!
Thanks to all of you for the help.

Best regards,
Scott

Marshall, Darren , et.al., If you are still following this thread I have
yet more need for info. The original problem has been solved, but now
presents a new set of problems. First, the Item field (combo box) in the
item sales subform is jittery and does not act like a regular combo box. I
have to hold down the mouse button while I select the row I want or it just
flashes by and goes blank. Second, I cannot add a record of a sale of an
item with out a room stay record. I somtimes sell things without a room
stay and I would like to be able to keep track of both types of sales.
This is expecially important if I sell a gift certificate. Most GCs are
sold over the phone to someone who has not stayed wth us or is not
currently staying with us so the stay record would not be created.

I hope this is clear.

Best regards,
Scott

 

[Marshall Barton]

First, the Item field (combo box) in the
item sales subform is jittery and does not act like a regular combo box. I
have to hold down the mouse button while I select the row I want or it just
flashes by and goes blank. Second, I cannot add a record of a sale of an
item with out a room stay record. I somtimes sell things without a room
stay and I would like to be able to keep track of both types of sales.
This is expecially important if I sell a gift certificate. Most GCs are
sold over the phone to someone who has not stayed wth us or is not
currently staying with us so the stay record would not be created.

I'm afraid this is not at all clear.

It would be better if you would rephrase your question and
post it to a new thread with an appropriat subject.

About all I can guess at here is that one of the forms has
some code that is doing something to cause the (sub?)form or
combo box to be requeried.

Do you have any Timer events that could be active while the
combo is misbehaving?