List box value based on combo box??? 2

[KrebsATM02AG]

I've got a form with 2 combo boxes in the header that controls the tabs in a form. I can use text fields and sub forms and link to the combo boxes, but how do i link a list box???

For example the 2 combo boxes do the samething, just looking up the person by last name or ss#. So in a list box i have SS_No, Account_Id, Fund_No, and Allocation_Pct. The problem is it pulls up all that data in that query and I only want the data that corresponds to the data in the control boxes in the header. Here is my sql in the Row Source:

SELECT tblAllocations.SS_No, tblAllocations.Account_Id, tblAllocations.Fund_No, tblAllocations.Allocation_Pct
FROM tblAllocations
WHERE (((tblAllocations.SS_No)=[SS_No]) AND (([tblAllocations.Account_Id])="TTSA&quot);

and i have SS_No in the Control Source

What am i doing wrong?????

 

[spiralmind]

you have to edit the query feeding the rowsource of the listbox to include the comboboxes as parameters:

under, for example, the SS_No field, type:

= [forms]![your form name]![your combobox name]

this will limit the query results based on the value in the combo box

 

[KrebsATM02AG]

It is all on the same form, just under different tabs. So do you need to distinguish which form still???

 

[spiralmind]

you have to distinguish which form in the query builder so it knows which form to be looking for in the database. it doesn't matter much to it if all your fields are on one form or spread across a dozen that are all open at one time, you just have to tell it which form each field belongs to.

 

[KrebsATM02AG]

Ok, so now i have this code:

SELECT qryAllocations.Fund_No, qryAllocations.Allocation_Pct, qryAllocations.SS_No
FROM qryAllocations
WHERE (((qryAllocations.SS_No)=[forms]![frmName]![cboSelectSS]));

and i get nothing in the list box now

Thanks for your help so far

 

[Bowers74]

Thanks SpiralMind,

You get a Star!!!

I was having the same problem as KrebsATM02AG.

To allow the ListBox to Update automatically whenever you change the value in the ComboBox, you have to create a simple event procedure in the "After Click" or "After Update" Events that "Re-States" that the RowSource for the ListBox is the SQL Statement.

Something like this:

Private Sub cboSelectSS_Change()
Me.lstCustomers.RowSource = "SELECT
qryAllocations.Fund_No, qryAllocations.Allocation_Pct,
qryAllocations.SS_No FROM qryAllocations WHERE
(((qryAllocations.SS_No)=[forms]![frmName]!
[cboSelectSS]));
End Sub

After doing this you can then remove the SQL Statement in the RowSource Property Field for the ListBox.

One more thing: KrebsATM02AG, Is your Form's Name really frmName? If not, then you need to change the [frmName] to whatever your Form's Name really is!



If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[KrebsATM02AG]

Private Sub cboSelectSS_Change()
Me.lstTSA.RowSource = "SELECT "qryAllocations.Fund_No, qryAllocations.Allocation_Pct,
qryAllocations.SS_No FROM qryAllocations WHERE
(((qryAllocations.SS_No)=[forms]![frmName]!
[cboSelectSS]));
End Sub


gives me a compile error on the first qryAllocations, says, "Expected: end of statement"

 

[Bowers74]

Try removing the first quotation mark after SELECT. That might do the trick.

Are you sure that your Form is called frmName?

If you can't be "The Best", be the best at what you can!!!

Never say Never!!!
Nothing is impossible!!!

 

[KrebsATM02AG]

yeah the form is named frmName.....old programmer I was working with named it....

I'm still getting all the records in the list box instead of the just the ones that match the SS#

 

[spiralmind]

why not just requery the listbox instead of restating it's rowsource?

if you want to state the rowsource from code, though, that gives you an opportunity to do this:

some event here()

If Me.cmbSelectSS.Value = "" Then

Me.lstTSA.RowSource = "[insert a sql statement with no criteria here]"

Else

Me.lstTSA.RowSource = "[insert a sql statement with criteria in the where line something like this:
"WHERE ((qryAllocations.SS_No = " & Chr(34) & me.cmdSelectSS & Chr(34) & &quot)"

End If

- by doing this you can explicitly state the conditions in which you want to filter the list by the combobox and those where you want all values to be shown

 

[KrebsATM02AG]

I'm new to this as you can see...So how do you requery??

I'm trying to limit the list box to the social security number that is chosen, so would a requery work?

Thanks for helping - Doug

 

[spiralmind]

me.lstTSA.Requery

the requery just makes sure that the rowsource is based on the ss number just chosen - as opposed to, for example, the one selected when the form was opened.

the limitation to the ss number chosen is actually implemented in the "where" statement of the query (visible in SQL view). here you should either have a (if it is a "static" query, outside of your code) "WHERE [TableFeild] = [Forms]![Your Form]![Control on Form]", or (if it is a "dyanamic" query, and built into your code) an expression on the lines of what i put in my last post. Either way should work, it's all just a matter of preference (the performance difference should be very small in most cases, and is difficult to predict in any, so i wouldn't worry about that).

 

[KrebsATM02AG]

Ok...the requery isn't doing anything...Where should I be putting it, in the After Update????

 

[spiralmind]

it should be in the afterupdate of the combobox being used as a parameter.

you should try running the query used as the rowsource of your list through the query builder, typing in a ss number when it prompts you (it should if the statement is written properly) for that field. you should be getting a set of results specific to that ss number. try it while leaving the prompt blank. you should then get a set of all records. if this doesn't happen the problem lies in your query and not in your form.

 

[mk2lee1]

Hi,
I have same problem with this...

this is my code

Private Sub comboRegion_AfterUpdate()
Me.Combo35.RowSource = "select school.schoolname from school where (((school.region)= '" & [Forms]![School]![comboRegion] & "'))"

Me.Combo35.Requery
End Sub

which combo region is the first combo box. and combo35 is second combo box. What i would like to get is if I select the region in the first combo box, the combo35, which list the name of the school, to only list those in regions according to region combo box.

right now.. i only get blank when I select the region combo box.

Can anyone help me with this?
thanks

 

[spiralmind]

set the rowsource of combo35 staticly (written exactly as it is in your code - just in the property sheet instead). keep the requery statement. this should work if you've typed all of you object names correctly. if it doesn't you might want to try editing the rowsource in the query builder by clicking on the little [...] button at the end of the field on the property sheet.

 

[koresnordic]

Spiralmind you have helped me a lot. I have four list boxes dependant on each of the previous results, e.g. the second depends on the first, the third on the first & second etc. This has solved my problem. Have another star from me.



Graham