Requery subform combobox based on other subform selections

G

Guest

I am working on a database for my wife who is an independent hair dresser. I
am currently having some difficulties creating a way for her to track the
dyes and the amounts of dyes that she uses. I basically have one form for
appointments there is one subform on top which has the customer, date, time,
etc., and another subform below that on a tabbed control for the services.
On the other tabbed control I have a third subform which contains the dyes
for the services. On the dyes subform there is a combo box where she selects
what services she used the dyes on, I have changed the dye boxes criteria so
that it only shows the services that use dyes performed during that
appointment and it works great. The problem is that when she selects another
appointment in the top subform that combo box still only returns the dye
services from the first appointment selected. If you go to Records=>Refresh
it works fine, however I want it to happen automatically when another
appointment is clicked. I’ve tried seemingly endless variations of requery
in code, in all kinds of different places, any advice would be greatly
appreciated.

Thank you very much!

Adam
 
M

Marshall Barton

Adam said:
I am working on a database for my wife who is an independent hair dresser. I
am currently having some difficulties creating a way for her to track the
dyes and the amounts of dyes that she uses. I basically have one form for
appointments there is one subform on top which has the customer, date, time,
etc., and another subform below that on a tabbed control for the services.
On the other tabbed control I have a third subform which contains the dyes
for the services. On the dyes subform there is a combo box where she selects
what services she used the dyes on, I have changed the dye boxes criteria so
that it only shows the services that use dyes performed during that
appointment and it works great. The problem is that when she selects another
appointment in the top subform that combo box still only returns the dye
services from the first appointment selected. If you go to Records=>Refresh
it works fine, however I want it to happen automatically when another
appointment is clicked. I’ve tried seemingly endless variations of requery
in code, in all kinds of different places, any advice would be greatly
appreciated.


Depends on how you are filtering the combo box's row source.
Most like likely you want the requery in the main form's
Current event.
 
G

Guest

Depends on how you are filtering the combo box's row source.
Most like likely you want the requery in the main form's
Current event.
I am filtering the combo box with this query:
SELECT T_Items.ColorService, T_Items.ItemName, T_ItemDetail.ItemID
FROM T_Items INNER JOIN T_ItemDetail ON T_Items.ItemID = T_ItemDetail.ItemID
WHERE (((T_Items.ColorService)=Yes) AND
((T_ItemDetail.AppointmentID)=[Forms]![F_Appointments]![IncomeAppointmentID]));

What would you recommend for the code to requery, just docmd.requery?

Thanks,

Adam
 
M

Marshall Barton

Adam said:
Depends on how you are filtering the combo box's row source.
Most like likely you want the requery in the main form's
Current event.
I am filtering the combo box with this query:
SELECT T_Items.ColorService, T_Items.ItemName, T_ItemDetail.ItemID
FROM T_Items INNER JOIN T_ItemDetail ON T_Items.ItemID = T_ItemDetail.ItemID
WHERE (((T_Items.ColorService)=Yes) AND
((T_ItemDetail.AppointmentID)=[Forms]![F_Appointments]![IncomeAppointmentID]));

What would you recommend for the code to requery, just docmd.requery?


In that case, use the text box's AfterUpdate event:

Me.combobox.Requery
 
G

Guest

In that case, use the text box's AfterUpdate event:
Me.combobox.Requery

Thanks for the reply! Which text box are you referring to? I tried putting
Me.combobox.Requery in the combo box's AfterUpdate event, but after doing
that it wouldn't even query the first time. Also should I be replacing
combobox with the actual name of the combo box or just leaving it as combo
box?

Thanks again for your help!

Adam
 
M

Marshall Barton

Adam said:
Thanks for the reply! Which text box are you referring to? I tried putting
Me.combobox.Requery in the combo box's AfterUpdate event, but after doing
that it wouldn't even query the first time. Also should I be replacing
combobox with the actual name of the combo box or just leaving it as combo
box?


Use the IncomeAppointmentID (the one referenced in the row
source query) text box's AfterUpdate event.

You need to replace the combobox in the line of code with a
reference to the combo box. Unless the combo box is on the
same (sub)form as the text box the name is not sufficient.
Since you have not provide the names of the subforms and
pertinate controls, I can't tell exactly what it should be.
I think(?) the text box is on the main form and the combo
box is on the dyes subform, in which case the statement
would be something like:
Me.subformcontrolname.Form.comboboxname.Requery

You may also need the same statement in the main form's
Current event.
 
G

Guest

Thanks again for your help, I've kind of gotten this to work. I can't get
the Me.F_ColorCardDetail.Form.ItemID.Requery to work in the afterupdate text
box, however if I put it on the afterclick event of a command button it works
perfect. This doesn't make any sense to me, and if you have any ideas that
would be great.

Thanks again,

Adam
 
G

Guest

I also forgot to add that that text box is actually based on the top subform
F_AppointmentsAppointmentSubform. The text box on the main form is set to
=F_AppointmentsAppointmentSubform.Form!AppointmentID. I don't know if that
makes a difference I just can't think of any other reason this wouldn't work.

Adam
 
G

Guest

I finally got it to work!! Thanks so much for your help, I used
Me.Form.Parent!F_ColorCardDetail!ItemID.Requery in the OnCurrent of the top
subform. The only issue is that I get error 2455, expression with an invalid
reference propert, this is probably because as I understand it access loads
the subforms before the main form. I'll mess around for a while and make
another post for that issue if I can't get it.

Thanks again!!

Adam
 
M

Marshall Barton

You have diagnosed the reason for the error correctly.

However, I think there are some misunderstandings going on
here. You said the mainform text box is not a data entry
text box, but it has a reference to another control on
another subform. Somehow that strikes me as wrong (or at
least the long way around) to filter the combo box. I think
the combo box's row source query should refer to the
original control that has the real value, not refer to
another control that references the original control.

Boy, this is getting confusing. If I have correctly kept
track of which form is which, I now suspect that the row
source query's criteria should be:

Forms!F_Appointments!F_AppointmentsAppointmentSubform.Form!AppointmentID

And the requery should be in the
F_AppointmentsAppointmentSubform's Current event:

Me.Parent!F_ColorCardDetail.Form!ItemID.Requery

If this still give you the same error message, try this
trick. Select the F_AppointmentsAppointmentSubform control,
then use Ctrl+X to cut the control and Ctrl+V to paste it
back and drag it back to the correct position. This should
place it lower in the Z-Order so the F_ColorCardDetail
subform loads before the requery is executed, hopefully
without introducing a different error.
 

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

Top