Access 2007 Feild Select based on conrol

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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
 
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.
 
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...
 
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...
 
Back
Top