Subform Combo Requery - Hair pulling stage!!

[CJ]

** 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

 

[Gary B via AccessMonster.com]

I'm not an expert at this so I'm sure there is a better way of doing it but I
have an invoice form which uses this method and it works. On my subform I
have a combo which selects a Job No from a list of completed jobs. Once a
job has been selected it doesn't want to be selected again so when the next
record (in the continuous subform) combo selects a jobno, the previous jobno
doesn't appear in the list. Like I say, I'm sure there is a better way of
doing it, but this works for me:

Run this code in the AfterUpdate event of the ProductLookup combo:


Dim sqlstmt As String
Dim rst As New ADODB.Recordset

RunCommand acCmdSaveRecord
sqlstmt = "select InStock from tblProduct where [productid] = " & Me.
productid_
& " and [instock] = 0"
rst.Open sqlstmt, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

Do Until rst.EOF

rst!InStock = -1
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

me.Model.setfocus 'I do this so that the combo isn't the active field
on Refresh

Me.Refresh
'end code

In the RowSource of the ProductLookup Combo put something like:

SELECT tblProduct.productid, tblProduct.model, tblProduct.Serial, tblProduct.
Received, tblProduct.InStock, FROM tblProductWHERE [OrderNo]=forms!
frmOrders!OrderNo And tblProduct.InStock=0;

Assume that frmOrders is your Parent form.
Obviously I don't know the names of your objects so I've made them up.

I'm self taught at this game, so I completely empathize with the hair-pulling
Still-Stuck-and-Extremely-Frustrated state. I assure you that the above
method (notwithstanding any typo's) does work for me, but I've used other
people's suggestions so many times now, where it doesn't work (adding the the
frustrated state) that I will not promise anything.

Good luck.

Gary Beale

 

[CJ]

Cool Gary that is pretty much the same thing that I need to happen. Thanks
very much for posting all of your code. I'm pretty sure that I should be
able to make it work for my situation.

Thanks for stepping in.
CJ

I'm not an expert at this so I'm sure there is a better way of doing it
but I
have an invoice form which uses this method and it works. On my subform I
have a combo which selects a Job No from a list of completed jobs. Once a
job has been selected it doesn't want to be selected again so when the
next
record (in the continuous subform) combo selects a jobno, the previous
jobno
doesn't appear in the list. Like I say, I'm sure there is a better way of
doing it, but this works for me:

Run this code in the AfterUpdate event of the ProductLookup combo:


Dim sqlstmt As String
Dim rst As New ADODB.Recordset

RunCommand acCmdSaveRecord
sqlstmt = "select InStock from tblProduct where [productid] = " & Me.
productid_
& " and [instock] = 0"
rst.Open sqlstmt, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

Do Until rst.EOF

rst!InStock = -1
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

me.Model.setfocus 'I do this so that the combo isn't the active
field
on Refresh

Me.Refresh
'end code

In the RowSource of the ProductLookup Combo put something like:

SELECT tblProduct.productid, tblProduct.model, tblProduct.Serial,
tblProduct.
Received, tblProduct.InStock, FROM tblProductWHERE [OrderNo]=forms!
frmOrders!OrderNo And tblProduct.InStock=0;

Assume that frmOrders is your Parent form.
Obviously I don't know the names of your objects so I've made them up.

I'm self taught at this game, so I completely empathize with the
hair-pulling
Still-Stuck-and-Extremely-Frustrated state. I assure you that the above
method (notwithstanding any typo's) does work for me, but I've used other
people's suggestions so many times now, where it doesn't work (adding the
the
frustrated state) that I will not promise anything.

Good luck.

Gary Beale

** 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