Access 2007 Feild Select based on conrol

G

Guest

I am creating a reservation form for a customer and I am at a stand still
trying to figure out how to only show the pets that are linked to the
customer. Let me explain:

Table 1 - Reservations:
*ReservationID - Auto Number
EmployeeID - linked to EmployeeTable (no problems with this)
CustomerID - linked to CustomerTable (no problems with this)
(there are also other fields but I'm not having issues with them).

Table 2 - CustomerTable:
*CustomerID - Auto Number
CustomerFirstName
CustomerLastName
(other info fields not having issues or linked with anything).

Table 3 - PetTable:
*PetId - AutoNumber
CustomerID (linked from CustomerTable on column two showing CustomerID,
Customerlastname, CustomerFirstName)
PetName
(other info fields not having issues or linked)

Table 4 - Reservation_Pet
*ReservationID
*PetID
I needed this table because not every pet from the customers would be
brought in for every reservation. (this is the only way I know to link
Multi-to-Multi fields)

I am attempting to put a form together for the Reservations.

It looks kind of like:
- Reservations -
ReservationID
Start
End
Total
CustomerID
(Customer details of last and first name are linked from CustomerID selected
in reservation form)
Subform: Reservation_Pet Subform
ReservationID
PetID

The PetID from the sub form is what I have a problem with. I want to ONLY
show the pets that are linked to the CustomerID from "Reservations" form and
"PetTable". I dont know the SQL to make this happen.

Any help would be appreciated.
Thanks,
John
 
M

Marshall Barton

ahern79 said:
I am creating a reservation form for a customer and I am at a stand still
trying to figure out how to only show the pets that are linked to the
customer. Let me explain:

Table 1 - Reservations:
*ReservationID - Auto Number
EmployeeID - linked to EmployeeTable (no problems with this)
CustomerID - linked to CustomerTable (no problems with this)
(there are also other fields but I'm not having issues with them).

Table 2 - CustomerTable:
*CustomerID - Auto Number
CustomerFirstName
CustomerLastName
(other info fields not having issues or linked with anything).

Table 3 - PetTable:
*PetId - AutoNumber
CustomerID (linked from CustomerTable on column two showing CustomerID,
Customerlastname, CustomerFirstName)
PetName
(other info fields not having issues or linked)

Table 4 - Reservation_Pet
*ReservationID
*PetID
I needed this table because not every pet from the customers would be
brought in for every reservation. (this is the only way I know to link
Multi-to-Multi fields)

I am attempting to put a form together for the Reservations.

It looks kind of like:
- Reservations -
ReservationID
Start
End
Total
CustomerID
(Customer details of last and first name are linked from CustomerID selected
in reservation form)
Subform: Reservation_Pet Subform
ReservationID
PetID

The PetID from the sub form is what I have a problem with. I want to ONLY
show the pets that are linked to the CustomerID from "Reservations" form and
"PetTable". I dont know the SQL to make this happen.
Click to expand...


I suggest the reservation pet subform be in continuous view
with a combo box for the petID field (the reservation id can
be in a hidden text box). The combo box's RowSource query
would simply be:

SELECT petID, PetName
FROM PetTable
WHERE CustomerID = Forms!mainform.CustomerID

Use the main form's Current event to to requery the combo
box:

Me.petrecervationsubformcontrol.Form.thecombobox.Requery
 
G

Guest

Marshall, thank you for your input, I have tried the SQL statment to this
point, but I do not understand where to place the:
Me.petrecervationsubformcontrol.Form.thecombobox.Requery statement.
Can you please elaborate on this?

Thank you
John
 
M

Marshall Barton

Put it in the main form's Current event procedure. If you
have never used an event procedure before, you can get to it
by locating the OnCurrent property in the form's property
sheet (under the Events tab). Select [Event Procedure] from
the property's drop list and the clicking on the builder
button [...]

That should put the cursor in the event procedure where you
can type the statement. Modify it to use your real names
for the form, subform control and combo box.
 
G

Guest

NO, this is not working...

I have the main form "Reservations" subform "Reservation_Petsubform" control
"PetID" linking cotrol is "ReservationID".

Me.Reservations.reservation_petsubform.PetID.Requery

This codeing isnt working for somereason. Any ideas?
Thanks,
John

Marshall Barton said:
Put it in the main form's Current event procedure. If you
have never used an event procedure before, you can get to it
by locating the OnCurrent property in the form's property
sheet (under the Events tab). Select [Event Procedure] from
the property's drop list and the clicking on the builder
button [...]

That should put the cursor in the event procedure where you
can type the statement. Modify it to use your real names
for the form, subform control and combo box.
--
Marsh
MVP [MS Access]

Marshall, thank you for your input, I have tried the SQL statment to this
point, but I do not understand where to place the:
Me.petrecervationsubformcontrol.Form.thecombobox.Requery statement.
Can you please elaborate on this?
Click to expand...
Click to expand...
 
M

Marshall Barton

You left out the .Form property

Me.Reservations.reservation_petsubform.FORM.PetID.Requery

Are you sure that PetID is the name of the combo box?
--
Marsh
MVP [MS Access]
NO, this is not working...

I have the main form "Reservations" subform "Reservation_Petsubform" control
"PetID" linking cotrol is "ReservationID".

Me.Reservations.reservation_petsubform.PetID.Requery

This codeing isnt working for somereason. Any ideas?

Marshall Barton said:
Put it in the main form's Current event procedure. If you
have never used an event procedure before, you can get to it
by locating the OnCurrent property in the form's property
sheet (under the Events tab). Select [Event Procedure] from
the property's drop list and the clicking on the builder
button [...]

That should put the cursor in the event procedure where you
can type the statement. Modify it to use your real names
for the form, subform control and combo box.

Marshall, thank you for your input, I have tried the SQL statment to this
point, but I do not understand where to place the:
Me.petrecervationsubformcontrol.Form.thecombobox.Requery statement.
Can you please elaborate on this?

:

ahern79 wrote:

I am creating a reservation form for a customer and I am at a stand still
trying to figure out how to only show the pets that are linked to the
customer. Let me explain:

Table 1 - Reservations:
*ReservationID - Auto Number
EmployeeID - linked to EmployeeTable (no problems with this)
CustomerID - linked to CustomerTable (no problems with this)
(there are also other fields but I'm not having issues with them).

Table 2 - CustomerTable:
*CustomerID - Auto Number
CustomerFirstName
CustomerLastName
(other info fields not having issues or linked with anything).

Table 3 - PetTable:
*PetId - AutoNumber
CustomerID (linked from CustomerTable on column two showing CustomerID,
Customerlastname, CustomerFirstName)
PetName
(other info fields not having issues or linked)

Table 4 - Reservation_Pet
*ReservationID
*PetID
I needed this table because not every pet from the customers would be
brought in for every reservation. (this is the only way I know to link
Multi-to-Multi fields)

I am attempting to put a form together for the Reservations.

It looks kind of like:
- Reservations -
ReservationID
Start
End
Total
CustomerID
(Customer details of last and first name are linked from CustomerID selected
in reservation form)
Subform: Reservation_Pet Subform
ReservationID
PetID

The PetID from the sub form is what I have a problem with. I want to ONLY
show the pets that are linked to the CustomerID from "Reservations" form and
"PetTable". I dont know the SQL to make this happen.


I suggest the reservation pet subform be in continuous view
with a combo box for the petID field (the reservation id can
be in a hidden text box). The combo box's RowSource query
would simply be:

SELECT petID, PetName
FROM PetTable
WHERE CustomerID = Forms!mainform.CustomerID

Use the main form's Current event to to requery the combo
box:

Me.petrecervationsubformcontrol.Form.thecombobox.Requery
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform date/time must be greater than date/time of last record 1
Append to two different tables 23
Simple query with criteria dependent on current form value 20
Show Record on subform based on current subform Line ID 1
Returning data in a second subform based on first subform current 1
Procedure Declaration Error 1
Use macro to add new linked record ACC07 8
New to Access, Im sure this is simple, but how??! 9

Top