A combo box problem, but not a show stopper!

[tstowe]

Same dbase, I have to issue equipment where the stock numbers (NSN) are required on the report. The sizeable items have different NSN's, i.e.:
Helmet
Small 7526
Medium 7527
Large 7528

I currently have the form set up with the bound field for helmet with a quantity requirement; next to it is a combo box that is linked to a query showing all items with sizes -this combo box lists each item requiring a size, i.e. Helmet, Small or Helmet, Large; next to it is a label that autofills with the correct NSN once a selection is made. Also in this code is a afterupdate for cbohelmet to show the size (large, Medium, etc) once a selection is made. Below is the code.

===================================
Private Sub cbohelmet_AfterUpdate()
Me.txthelmet.Value=dlookup "[lastfour]", "qrysize",
"[equipment]='" & Me.cbohelmet.Value & "'&quot
Me.cbohelmet.Value=dlookup("[size]", "qrysize", "[equipment]='" & Me.cbohelmet.Value & "'&quot
End Sub
===================================

What I want to do is have the combo box ONLY pull the information from the query that it needs, i.e. when I select the combo box for Helmet I don't want to see the other items such as chemsuits, sweaters, wet weather items, etc. In my thinking I need to somehow related the Afterupdate code to the label for the item that I am working with, i.e. the code looks at the label for Helmet and realizes that it should only show the sizes for the item. I hope this makes sense. I would love to send the file to anyone not understanding my direction here.

Thanks.

Tony

 

[Savil]

You sound like a Squipper to me?

What I would do is make CBOhelmet have two columns and bring in the NSN as well the Item Description so you would have to change the query slightly. Make the boundcolumn for CBOHelmet column 2 and set the column widths to 2,0 this then makes the NSN invisible and all you see is the Item descriptions. Then all you have to do is change the query sql value to filter for the NSNs

Add to the CBO helmet afterupdate event

Currentdb.QueryDefs("WhateverItsNameIs&quot.Sql =
"SELECT WhateverFields FROM WhateverTableOrQuery WHERE NSN = " & Me.CBOHelmets & ""

You may have to tinker with this but I hope I have pointed you in the right direction.

 

[tstowe]

Although not sure what a squipper is, I will try your suggestion. Thanks for your time.

Tony

 

[tstowe]

Savil,

Thanks for your suggestion. Being on the front edge of this learning curve I am having difficulty making your recommendation to work. First off, I am not quite sure what data to put in your code to make this work.

** you wrote **
Currentdb.QueryDefs("WhateverItsNameIs&quot.Sql =
"SELECT WhateverFields FROM WhateverTableOrQuery WHERE NSN = " & Me.CBOHelmets & ""
***************
What I don't understand is:
Currentdb.QueryDefs : Am I to replace this with some other data? I don't fully understand the whole SQL route to do this.

To make this short and sweet. . .is it possible to put this in a more "idiot proof" wording, or the lowest level at any rate, so that I can work with this?

Thanks ahead of time.

Tony

 

[tstowe]

Savil,

Ok. I went in and adjust my cbo to reflect two columns and adjusted the query (correctly I believe). I also changed the boundcolumn and the column widths. I went in and attempted to use the code you provided but to no avail. Would it be possible for a small example dbase to be emailed to me? It would be a huge help in me learning this.

Thanks,

Tony