combobox values based on subform values (1 Viewer)

[ChrisC]

Hi everyone,

I'm gradually getting to grips with my database but now I need to display the results of the second column of a subform in a combobox on the main form.


Basically, users enter a PartID in a text box on the main form. The subform then displays the corresponding PartID, WHLocatID (warehouse location) and LocationQty (stock quantity in the location) from the Inventory table.

This works well (simple but a big "victory" for me ).

But... now im stuck!

Back on the main form, I have a combobox called cboWarehouseLocat which I want to only display the WHLocatID details as shown in the subform for the PartID that was given originally.


Is this all possible at all?

Many thanks as always!
Chris

 

[arnelgp]

do you use Query as recordsource of your subform?

select * from yourWareHouseTable Where [PartID] Like Nz([Forms!yourForm!yourComboPartID, "*") And [WHLocatID] Like Nz([Forms]!yourForm!yourComboWHLoc, "*")

on AfterUpdate event of your 2 combos, Requery the subform:

Me.yourSubform.Requery

 

[ChrisC]

Hi Arnelgp,

yes I do use a query to get the information - qryInventoryQuery.

Thank you for the code. forgive me - what does "*" and "Nz" denote in this case?

i.e. - what do they do?

 

[arnelgp]

a function. Nz(), i think Not Zero.
Nz(variable, 0) means if the variable is Null, then use 0 as return value. if the variable is not null use the value on that variable.

 

[ChrisC]

Thank you

I keep getting an error message appear to say that the syntax is incorrect;


= select * from [Inventory] Where [PartID] Like Nz([Forms]!frmIssue!cboPartID, "*") And [WHLocatID] Like Nz([Forms]!frmIssue![cboWarehouseLocat], "*")

I thought I had substituted the correct terms into your code:

yourWarehouseTable = Inventory
Forms!yourForm!yourComboPartID = Forms!frmIssue!cboPartID
[Forms]!yourForm!yourComboWHLoc = ([Forms]!frmIssue![cboWarehouseLocat]

the error message tells me to "check the subquery's syntax and enclose the subquery in parentheses"

Where I have "Inventory" as my Warehousetable, I have also tried setting that as the query -"InventoryQuery" but no change to the error.

thanks again!

 

[ChrisC]

I have had a little bit of a result - in so much as with the following code, I can get the first record showing in my cboWarehouseLocat, but no other records.

=[InventoryQuerysubformISSUE].[Form]![WHLocatID]

 

[arnelgp]

see the sample I made.
I change the criteria of query.
subform uses the query as recordsource.
see the code in each combos after update event.

 

[ChrisC]

great - thank you, I will have a look

 

[ChrisC]

Hi Arnelgp

Thank you again for the sample, it works, but not in the way I am looking for im afraid.

It appears in yours that I select the part, then select a warehouse to look at what stock is available.

What I need is that I select the part, all the locations that contain that part then appear in the subform (both of which I have working) and then the combobox values match what appears in the subform.

So your flow is: Choose Part > Choose Warehouse > view stock, whereas I need Part > view stock > view warehouse (in the combobox).

I can show the Selected line of the subform in the combobox, using the =[InventoryQuerysubformISSUE].[Form]![WHLocatID] syntax, but not the whole list at once.

Thanks again for any more help you are help to offer.

Chris

 

[theDBguy]

Hi Chris. Are you able to post a sample copy of your db?

 

[ChrisC]

Hi DB guy, thanks for getting in touch - I will but not until I am back in work tomorrow im afraid... dont have a copy here at home.


Thanks
Chris

 

[ChrisC]

Good morning all,

Please find attached a version of my DB. frmIssue is where my focus is and on the Issued From combo box called cboWarehouseLocat. Fields after that can be ignored as they are later things for me to work on.

As you will see, when a part number is selected from the first combo box on the form, the description appears and the subform displays the location details and stock.

At the moment cboWarehouseLocat on displays the location that is clicked on in the subform. This can be worked with at a push, but it's not what I am after. I want all warehouse locations for the chosen part to display in cboWarehouseLocat; whether the subform line is selected or not.

Many thanks once again for your help and thoughts,
Chris

 

[arnelgp]

your form should be bound to an issuance table.

 

[ChrisC]

Thank you so much for this - thank you!

Thanks also for putting the explanation in the code of what it all does - that is a great help in my learning!


Kind regards
chris

 

[arnelgp]

you're welcome.
see my motto...