REPOST: Subform Combo Requery - Hair Pulling Stage!!

[Guest]

PLEASE, PLEASE, PLEASE - DESPERATE HELP REQUIRED


** Posted previously as "Combo not updating from change on form". It never
was resolved, although efforts were made. **

****** Still Stuck and Extremely Frustrated******

Hi (A2K3):

My continuous subform shows a list of products that are in stock. The first
column is a combo tied to a query and then some bound fields tied to a
different query:

Product Lookup Model Serial Received In Stock Comments

The Lookup column retrieves the data for the current record; meaning that
the model, serial etc are filled in after finding the product in the lookup.

What needs to happen (and what I am stuck with) is that when a product is
selected with the lookup column and In Stock is set to "No" for this
product (it's a check box) then I do not want the product to show up in the
lookup of the next record. Setting InStock = No means that the product has
been ordered and is no longer available for any other orders.

****NOW BEFORE YOU SAY, "OH VERY EASY"****

I can get this to work if only the subform is open......BUT......if you
access the subform via the parent it does not work!!

I have tried making the combo requery in the following events: Form Dirty,
Form Current, ysnInStock After Update and I don't know where else.

Please only respond to this post if you are POSITIVE you know how to solve
this problem.

Many, many thanks in advance.
CJ

 

[Guest]

I'm not positive I can solve your problem, but I will post anyway. It would
be helpful if you posted the code that works when only the subform is open
and also the code you are trying to use from the main form.

 

[Roger Carlson]

To requery a combo box on a subform, you have to reference the control
something like this:

Forms!Form1!subControl1.Form!cboYourComboBox.Requery

Substitute in the above:
- your actual main form for Form1
- the subform CONTROL name (if it is different than the subform) for
subControl1
- your Combobox name for cboYourComboBox

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Thanks for stepping in and seeing what you can do.......

Hey, let's give it a try....

OK, my code for the subform is as follows:

Private Sub cmbHeaterLookup_AfterUpdate()

Me.strHeaterModel.Value = Me.cmbHeaterLookup.Column(2)
Me.strHeaterSerial.Value = Me.cmbHeaterLookup.Column(3)
Me.dtmDateReceived.Value = Me.cmbHeaterLookup.Column(4)
Me.ysnInStock.Value = Me.cmbHeaterLookup.Column(5)
Me.strSurgeTank.Value = Me.cmbHeaterLookup.Column(6)
Me.memHeaterComments.Value = Me.cmbHeaterLookup.Column(7)

End Sub

Private Sub Form_Current()
Me.cmbHeaterLookup.Requery
End Sub

Private Sub Form_Dirty(Cancel As Integer)
Me.cmbHeaterLookup.Requery
End Sub

Private Sub ysnInStock_AfterUpdate()

DoCmd.RunCommand acCmdSelectRecord
Me.cmbHeaterLookup.Requery

End Sub

My main form does not have any code at the moment and I can't remember what
I tried....sorry.

Please let me know if you need more data.

Thanks
CJ

 

[Guest]

I'm slightly at a lose because I never use the runcommand method.
What happens when you try to run it from the main form? do you get an error,
or does it just do nothing? How are you running it from the main form since
this code is for the sub form?

 

[Guest]

Hi Roger.

Thanks for your input! To which event on my main form do I put your code? I
am moving between records on the subform but the subform is on the main.

CJ

 

[Guest]

No error, but the product is still showing up as in stock when I move down to
the next record in the subform.

 

[Guest]

Try adding Me.Requery in the current event.

No error, but the product is still showing up as in stock when I move down to
the next record in the subform.

 

[Guest]

I tried that for both the main and the sub. It did not solve the problem :-(

 

[Guest]

This one is tough! What about the sub form's recordset? Is it a table, or a
query? Do you have the parameters in the query correct?

 

[Ken Snell [MVP]]

PMFJI ....

What is the Row Source of the combo box? Is it a query that omits records
where the stock value is zero? If not, how is the combo box supposed to know
that an item is not to be included in the query any more?

Also, the Requery in the Dirty event will not accomplish much if the query
is supposed to be using data from the subform, as the data have not yet been
saved to the table when the Dirty event occurs.

May I suggest that you tell us more about what is happening on the subform,
what is meant by "doesn't work when accessed from the parent form", and
other info about the setup.

 

[CJ]

Hi Ken

1. The Row Source for the combo is a query set to only find records that are
in stock.

2. I was desperate when I tried the Requery in the Dirty event......I tried
it everywhere.

3. When I open up the subform by itself, what I want to accomplish works.
When I select an item in the combo, the data fills in for that item, I
uncheck in stock. When I move down to the next record in the subform the
previous item is not showing up as in stock. That is what is supposed to
happen.

3a. When I open the subform as part of the parent form, everything works
except for when I move to the next record in the subform. The item that is
unchecked in the previous record is still showing up as in
stock.......that's the problem.

.....sigh.....

 

[Ken Snell [MVP]]

Chances are that the criterion expression you're using in the query is not
properly referencing the subform when it's part of the main form. A subform
is not "open" on its own when part of a main form, so the standard way to
reference that form doesn't work:
Forms!NameOfSubform!ControlOnSubform

Instead, use a reference that goes through the main form:
Forms!NameOfMainForm!SubformControl!ControlName

where SubformControl is the name of the subform control (the control that
holds the subform object) on the main form.

 

[CJ]

Except.....I am not referencing the subform from the main form. The main
form shows the customer information and the subform show the new order
information. The combo box and the other fields (model, serial number, in
stock etc) are all on the subform.

Any other thoughts??

 

[Ken Snell [MVP]]

It would help if you post the SQL Statement of the combo box's Row Source
query....

 

[John Vinson]

Except.....I am not referencing the subform from the main form

But you are referencing it in the query, and the correct way to do so
uses the main form name as Ken suggests. Please post the SQL of the
rowsource query, as requested (and as would have helped solve your
problem weeks ago if you had done so).

John W. Vinson[MVP]

 

[Guest]

Hi Ken:

Here's the SQL from the combo on the subform:

SELECT qryHeaters.strHeater, qryHeaters.lngHeaterID,
qryHeaters.strHeaterModel, qryHeaters.strHeaterSerial,
qryHeaters.dtmDateReceived, qryHeaters.ysnInStock, qryHeaters.strSurgeTank,
qryHeaters.memHeaterComments, qryHeaters.lngOrderID
FROM qryHeaters
WHERE (((qryHeaters.ysnInStock)=Yes));

Thanks for checking this out for me.

 

[Ken Snell [MVP]]

How does the field ysnInStock get assigned its value? My initial assessment
is that that field is not getting assigned a False value when you use a part
on your subform.
--

Ken Snell
<MS ACCESS MVP>

 

[CJ]

Well, I just manipulate the check mark in the y/n box for in stock....like I
said it works just fine if you open the subform all by itself.......

................................

 

[Ken Snell [MVP]]

Only thing that comes to my mind is that the data in the subform's record
are not being saved to the table before you requery the combo box's Row
Source query.

I find it unusual that the use of Yes as a criterion value will work for
you. Assuming that ysnInStock is a "Yes/No" field, change Yes to True.
--

Ken Snell
<MS ACCESS MVP>