Combo box options limited by form control (1 Viewer)

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
I have a combo box which I want to provide categories based on the name of the vendor. If I literally put the name of one of my vendors in the Row Source query it works. When I put the name of the control the vendor name should come form I just get no nothing in the combo box (nothing passes the filter) Here is the Query with the literal vendor name:

Code:
SELECT tblCategory.ID, tblCategory.VendorName, tblCategory.Category FROM tblCategory WHERE (((tblCategory.VendorName)="HYCO CANADA, LTD"));

this 'works' but obviously it doesn't. It gives me the categories it should when HYCO Canada is the Vendor but this needs to work for all vendors as I move from record to record. Here is the query I tried which didn't work.

Code:
SELECT tblCategory.ID, tblCategory.VendorName, tblCategory.Category FROM tblCategory WHERE (((tblCategory.VendorName)=Forms!frmMRRLog!SupplierName));

I have used Msgbox to verify that Forms!frmMRRLog!SupplierName give the right Vendor.

I have vba code on another Cbo box but I don't think it would affect this.

It seems like the query I tried should work but for some reason its not. Help is appreciated.

Thanks in Advance
 
Last edited by a moderator:

bob fitz

AWF VIP
Local time
Today, 11:58
Joined
May 23, 2011
Messages
4,717
Can you post a copy of the db for us to play with
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:58
Joined
Sep 21, 2011
Messages
14,051
Try
Code:
SELECT tblCategory.ID, tblCategory.VendorName, tblCategory.Category FROM tblCategory WHERE tblCategory.VendorName=[SupplierName]

You will need to requery the combo on AfterUpdate of Suppliername ?

HTH
 

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
Here is the database. Some data is mildly sensitive, please don't share.
 

Attachments

  • MRRA 5-3 Gary lite.accdb
    860 KB · Views: 35

theDBguy

I’m here to help
Staff member
Local time
Today, 04:58
Joined
Oct 29, 2018
Messages
21,358
Here is the database. Some data is mildly sensitive, please don't share.
Hi. In the On Got Focus event of your combobox, try the following code:
Code:
Me.Combo53.Requery
Hope it helps...
 

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
Hi. In the On Got Focus event of your combobox, try the following code:
Code:
Me.Combo53.Requery
Hope it helps...

I tried a couple combo boxes and that seems to be working. Thank You. I'm thinking the same solution should work for the Sub Category. I'll try that and let you know how that goes.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:58
Joined
Oct 29, 2018
Messages
21,358
I tried a couple combo boxes and that seems to be working. Thank You. I'm thinking the same solution should work for the Sub Category. I'll try that and let you know how that goes.
Hi. You're welcome. This was suggested by Gasman earlier, I just decided to use the Focus event instead. Glad we could assist. Let us know if you need more help. Good luck with your project.
 

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
I may need a little more help. I thought I could that the same concept you all helped me with and extend that to the next combo box in line which is subcategory. I guess I was wrong. Somehow I wasn't able to do it. Please take a look and let me know where I went wrong. Basically SubCategory should be based on the entry in Category just like Category was based on Vendor. Other than changing the control names in the RowSource and the Control Name in the GotFocus event, I added the SubCategory field to the Row Source Query. Any help is appreciated.

One more little issue. the entries for the Category don't show up when the form is first opened but as soon as you click on the cbo box (It gets focus) the values appeaer. So I am thinking I'll have to do a requery form open. I think I do that myself but would like know if you feel that is the best approach

Thank You
 

Attachments

  • MRRA 5-3 Gary lite.accdb
    832 KB · Views: 41

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
I could go to single form view instead of tabular format. There is a little value in seeing multiple records at once but not a lot. So there is an alternative if the tabular format won't work.
 

June7

AWF VIP
Local time
Today, 03:58
Joined
Mar 9, 2014
Messages
5,425
It is Datasheet or Continuous view that complicates. Doesn't matter if it is a subform or not. It can work, as described in referenced link.
 

gakiss2

Registered User.
Local time
Today, 04:58
Joined
Nov 21, 2018
Messages
168
It is Datasheet or Continuous view that complicates. Doesn't matter if it is a subform or not. It can work, as described in referenced link.

Thank You for the clarification. I ended up deciding I would try to get by with just a category for now and see if there is really a demand for a subcategory. If there is I will pull out this advice and apply it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:58
Joined
Oct 29, 2018
Messages
21,358
Thank You for the clarification. I ended up deciding I would try to get by with just a category for now and see if there is really a demand for a subcategory. If there is I will pull out this advice and apply it.
Hi. Good luck!
 

Users who are viewing this thread

Top Bottom