Requery a ComboBox on a SubForm from AfterUpdate of a different Combo Box on same SubForm

I have a SUBFORM with a COMBO BOX called "ItemNo" that looks up an ITEM.  
In the AFTER UPDATE event, a CONTROL called "ItemType" is updated to reflect a value from the "tblInventory" Table.

Also in the AFTER UPDATE event, I've been trying to ReQuery another COMBO BOX called "ItemSpaceDesc"on the same SUBFORM.   "ItemSpaceDesc" has a RowSource that uses the "ItemType" CONTROL as criteria.

It seems like I've done similar things dozens of times in the past, but I'm guessing that it has something to do with these controls being on a SUBFORM.  ???
Any help would be such a wonderful thing.

The SUBFORM is named "frmNewTicketEntryItems"  (this form is in Continuous Forms view
Private Sub ItemNo_AfterUpdate()
Dim stPrice As String
stPrice = Nz(DLookup("[Price]", "tblInventory", "[ItemNo] = [ItemNumber]"), 0)
Me.ItemPrice.Value = stPrice
Me.ItemDisc.Value = 0

Dim stType As String
stType = Nz(DLookup("[ItemType]", "tblInventory", "[ItemNo] = [ItemNumber]"), "unknown")
Me.ItemType.Value = stType

'This next line was the last thing I've tried, would have thought "me.itemspacedesc.requery" would have worked.

Me.ItemDisc.Value = 0

Call LineItemCalc

End Sub

Open in new window

The Parent FORM (if applicable) is named "frmNewTicketEntry"
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The DLookup() is incorrectly formatted (and probably unnecessary but that's neither here nor there).

stPrice = Nz(DLookup("[Price]", "tblInventory", "[ItemNo] = [ItemNumber]"), 0)

should be

stPrice = Nz(DLookup("[Price]", "tblInventory", "[ItemNo] = " & Me.[ItemNumber]), 0)

Assuming that ItemNumber is numeric.  If it is not numeric, then it must be enclosed in quotes.

stPrice = Nz(DLookup("[Price]", "tblInventory", "[ItemNo] = '" & Me.[ItemNumber] & "'"), 0)

The .Value property is the default property of a text box and so it can be omitted.  It is not wrong to use it, just unnecessary.
TechGuiseAuthor Commented:
Hi Pat, thanks for your input. The DLookup statement you mention (both in fact) are working, although I’ll always take suggestions for improvements. I’ve kind of scraped by in my vba skills and am fully aware I am not always following “ best practices”.

Refreshing the combo box is what is not functioning correctly.
Line 12 in the code above is what I’m having trouble with.
Unless [ItemNumber] is in tblInventory, it is not being evaluated correctly and so it is unlikely that you are getting correct results.  

Your naming convention gives me no clue how to fix the requery. Is the combo you are requerying on the form where the code is running?  If so the statement would be:


If the combo is on the parent form, the statement would be:


If the combo is on a "sibling" subform, the statement would be:

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

TechGuiseAuthor Commented:
Yes, thought I mentioned in first post, all controls are on same sub form.

Me.ItemSpaceDesc.Requery Does not work.

And just to be 110% clear the DLookup statements are working and updating controls based on their results.
John TsioumprisSoftware & Systems EngineerCommented:
At first

Open in new window

is the correct way yo refer to it...
also something must be wrong with your combo RowSource...
in order to check this just put this line before the requery
debug.print Me.ItemSpaceDesc.RowSource

Open in new window

maybe what you expect is not what it has...
TechGuiseAuthor Commented:
OK Pat & John, thanks for the input.   If you don't want to read this whole thing, bottom line is it's fixed.

The Combo Boxes (all of them) were functioning fine, I just couldn't make VBA requery them so that they looked for results that matched a recent change.
So when you're typing...   "me.itemspacedesc."     After you hit that second period, vba normally offers up a list of functions (I guess that's what they're called) alphabetically (AddColon, AddItem, AfterUpdate, etc...)  There are probably 5 or 6 dozen of these "suggestions" on the list.
Well.... when I was typing "me.itemspacedesc.", when I hit the second Period, the only thing it offered up was "Value"....   (kind of the way it acts when you're modifying code on a FORM that isn't currently open.
Hope that makes sense.
To fix the issue, I just deleted the Combo Box (which as I say, was functioning properly) and created a new one from scratch, and gave it the same name, used the exact same Row Source, it started letting me see all of the functions....
and when I entered "Me.itemspacedesc.requery" it worked without giving me an error as it had been.
So I guess "that Control" was corrupt?   Don't know?
Thanks again for trying.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
When you have a control with a Name property that is different from the name of the bound column (recommended), you will see different properties after the second dot.

Me.cboFieldName.   --- will show properties of the control
Me.FieldName.   --- will show properties of the column and you will usually just see .Value

Please humor me and change the syntax of your DLookup() to what I suggested.
TechGuiseAuthor Commented:
So I'd like to award points to show appreciation for comments, but for those who find this question in the future, I need to be clear that the only thing I did to fix the problem is to delete and recreate the control.   All NAMES, ROW SOURCES, CODE... everything, was exactly the same.

What happened to the ability to spread points across multiple contributors?

I will get in the habit of using the "Me.[Control]" in my DLookup statements from now on.   Don't know if I'll change this one because it is working, and I've lost WAY to much time messing with it.

I've never been in the habit of naming a CONTROL differently than the name of the FIELD.   But I can see how the naming convention you showed above could be easy to keep track of.   Is there a short answer as to why that is recommended?  
Thanks again for all the help.
In earlier versions of Access, it made a difference.  Current versions will work fine if the control name is the same as the bound field name.

I rename the controls because I prefer the clarity in code to know when I am referring to a control or to the recordsource.

For an example of a subtle difference.

Me.CustomerName = "ABC"  -- will modify the CustomerName in the recordsource but will NOT show on the form if the control name is Me.txtCustomerName.

Me.txtCustomerName = "ABC"  -- will modify the visible value but the recordsource will not change until the record is saved.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.