listboxes and setting selection as criteria

[dbruzzese84]

Does anyone know how to do this?

I want to create 2 listboxes. Each listbox must contain a column from a table that has been imported. Example for the 1st listbox:

288394 CALVIN KLEIN ESCAPE (500mL)
239484 HUGO BOSS (650mL)
874257 JEANPAULG (500mL) ..and so on

The second listbox must contain Cities. (also taken from a table made in excel)

Now, I want to be able to choose a product, choose a specific city, and press a command button so it will direct me to a screen that lets me know how much of that product was ordered or sold in that particular city.

any suggestions ?

[JConsulting]

Begin by using the listbox wizard. It will walk you through creating exactly what you want for your listboxes.

If you plan to use these listboxes as a kind of a filter or criteria for a select query (for your form) then you want to be sure that you only allow a single select, otherwise there's coding involved.

If you already have a query that you want to build your form on, then paste that here and we'll assist you in setting it up to use your new listboxes.
J

[dbruzzese84]

how do i do that?

[dbruzzese84]

my listboxes are set up, but i dont know how to create a button that returns information. example, if you clicked 432489 calvin klein in 1 box, and then calgary in another, then click search.. i want the search to come up with the results of all the orders of 432489 calvin klein in calgary.

[JConsulting]

how do i do that?

Which part.

This is the Listbox part

Using the Access Toolbar, you select the Listbox control. Select it then go down into your form and click on it. The listbox will be added to your form and the wizard will open.

It will ask you what you want to use for the rowsource. Select either your table, or if you have a query, use that.

It will prompt you to add the fields from your previous selection.

Follow the prompts.

J

[JConsulting]

my listboxes are set up, but i dont know how to create a button that returns information. example, if you clicked 432489 calvin klein in 1 box, and then calgary in another, then click search.. i want the search to come up with the results of all the orders of 432489 calvin klein in calgary.

do you have a query built already that has the fields you want returned in it? If not, you need to create one.

There is a row inside the query builder grid called Criteria. It's there you need to reference your listbox by name.

Can you tell me what the "bound" columns are for your listboxes?

The bound column defaults to 1....so that would be the first field listed in your rowsource.

it's those fields that we'll put criteria into in your query.
J

[dbruzzese84]

ok my listboxes are set up. and a query is built. what do i type in the criteria line?

[dbruzzese84]

they are both "1" in the bound

[JConsulting]

they are both "1" in the bound

in the criteria fields in your query...reference your form

forms!yourformname!yourlistboxname

you'll use that for both fields that you're filtering.

create a button to run your query

Docmd.OpenQuery "yourqueryname"

Now when the query opens, it is filtered using your entries.

PS I got your PM..if you can't get this to work..then we'll go that route.
J

[dbruzzese84]

in the criteria fields in your query...reference your form

forms!yourformname!yourlistboxname

you'll use that for both fields that you're filtering.

create a button to run your query

Docmd.OpenQuery "yourqueryname"

Now when the query opens, it is filtered using your entries.

PS I got your PM..if you can't get this to work..then we'll go that route.
J

its asking me to enter data. i dont want to type in data. i want to be able to click an entry in the listbox and press the command button. the command [forms]![frmForm1]![lstlist0] doesnt work.

[JConsulting]

its asking me to enter data. i dont want to type in data. i want to be able to click an entry in the listbox and press the command button. the command [forms]![frmForm1]![lstlist0] doesnt work.

can you paste in the SQL from your query please

[JConsulting]

can you paste in the SQL from your query please

can you also paste in the ROWSOURCE of your listbox

[dbruzzese84]

can you also paste in the ROWSOURCE of your listbox

i am kind of an access "newb", how would i go about copying and pasting my query to SQL?

[dbruzzese84]

like this?

SELECT SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
ORDER BY SalesLic952.CSPC, LSList.CITY;



SELECT [CSPC_LIST].[CSPC] FROM CSPC_LIST; <--rowsource for the 1st listbox containing the products

SELECT [LSList].[ID], [LSList].[CITY] FROM LSList; <-rowsource from 2nd listbox containing the cities.

[dbruzzese84]

forms!yourformname!yourlistboxname

forms!frmForm1!lstList1 <-- this command line shows up in a textbox that asks me to enter data once the command button is clicked. if i type in the product number and city, it will work, but i want it to recognize that each entry in the listbox means something.

[JConsulting]

forms!yourformname!yourlistboxname

forms!frmForm1!lstList1 <-- this command line shows up in a textbox that asks me to enter data once the command button is clicked. if i type in the product number and city, it will work, but i want it to recognize that each entry in the listbox means something.

Replace the SQL with this. I had to add the ID field to it...and the where clause.

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
ORDER BY SalesLic952.CSPC, LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID]);
 
 

[dbruzzese84]

Replace the SQL with this. I had to add the ID field to it...and the where clause.

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
ORDER BY SalesLic952.CSPC, LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID]);
 
 

its not working. missing operators. is there anyway u can create a 2 line excel sheet, import it into access, create what im talking about and send it? it would be much easier for my to look at something.

[JConsulting]

its not working. missing operators. is there anyway u can create a 2 line excel sheet, import it into access, create what im talking about and send it? it would be much easier for my to look at something.

try taking the SQL you sent prior to my reply...and where you have your table name...surround it in brackets.

My Test should be [My test]

Same for any field you have in the SQL that has a space in it.

Also, take a look at the Left, Instr and Mid functions in your Access Help.

I could do this for you (and have actually) but you wont learn it that way.

I also don't have a way to download a "spreadsheet", because I'm at work and that's not allowed. So I apologise, as I can't assist you that way.

J

[dbruzzese84]

My tables are:

CITY
(field names:ID, CITY)

CSPC_LIST
(field names: ID, CSPC)

LSList
(field names: ID, LIC_NO, NAME, PHONE, FAX, ADDRESS, MAILING ADDRESS, CITY, POSTAL CODE)

SalesLic952
(field names: ID, CSPC, DESCRIPTION, SIZE, UNITS, QTY, UOM, CUSTOMER, NAME)

Form name is Form1
Listbox w/CSPC is List0
Listbox w/City is List1

[JConsulting]

My tables are:

CITY
(field names:ID, CITY)

CSPC_LIST
(field names: ID, CSPC)

LSList
(field names: ID, LIC_NO, NAME, PHONE, FAX, ADDRESS, MAILING ADDRESS, CITY, POSTAL CODE)

SalesLic952
(field names: ID, CSPC, DESCRIPTION, SIZE, UNITS, QTY, UOM, CUSTOMER, NAME)

Form name is Form1
Listbox w/CSPC is List0
Listbox w/City is List1

give this a go

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER where SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID])
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

[dbruzzese84]

give this a go

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER where SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID])
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

ok this one has no errors, but it is still prompting me to enter something in an input box.

[dbruzzese84]

ok this one has no errors, but it is still prompting me to enter something in an input box.

maybe my relationships are wrong?

[dbruzzese84]

this is what it needs to look like. In available columns, City should be added

[dbruzzese84]

the input box says "Enter Parameter Value"

[JConsulting]

the input box says "Enter Parameter Value"

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER where SalesLic952.CSPC = nz(forms!frmForm1!lstList1,"SalesLic952.CSPC") and [LSList].[ID] = nz(forms!frmForm1!lstList2, "[LSList].[ID]")
ORDER BY SalesLic952.CSPC, LSList.CITY;

[dbruzzese84]

That will probably work, I hate wasting your time like this. I am going to start over. See the problem I am having as well is that most of the fields are repeating themselves. i need them to just show up once. Calgary is listed 60times. Is there an easier way to do this? step by step from the beginning kinda thing?

[JConsulting]

That will probably work, I hate wasting your time like this. I am going to start over. See the problem I am having as well is that most of the fields are repeating themselves. i need them to just show up once. Calgary is listed 60times. Is there an easier way to do this? step by step from the beginning kinda thing?

starting over? lol

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER 
ORDER BY [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
HAVING SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID])
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

[dbruzzese84]

starting over? lol

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER 
ORDER BY [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
HAVING SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID])
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

check PM please

[JConsulting]

check PM please

Not sure what that means...do you wish a Moderator to view the question?

[JConsulting]

That will probably work, I hate wasting your time like this. I am going to start over. See the problem I am having as well is that most of the fields are repeating themselves. i need them to just show up once. Calgary is listed 60times. Is there an easier way to do this? step by step from the beginning kinda thing?

Adding a group by statement to the query tells it to not display duplicate values for the fields included in the list.

If this doesn't help...please explain how you would like to approach the issue.
J

[JConsulting]

check PM please

aaah, I see that instead of Group By...I had order by. Please..try this again.

SELECT [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER 
Group BY [LSList].[ID], SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
HAVING SalesLic952.CSPC = nz(forms!frmForm1!lstList1,SalesLic952.CSPC) and [LSList].[ID] = nz(forms!frmForm1!lstList2, [LSList].[ID])
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

[dbruzzese84]

This is what happens when the comman button is clicked:

Also, do you see all the duplicate CSPC numbers and cities? I only want one of each.

[JConsulting]

This is what happens when the comman button is clicked:

Also, do you see all the duplicate CSPC numbers and cities? I only want one of each.

>>This is what happens when the comman button is clicked:

what happens?

Also...is there a reason you included the ID in with your listbox 2?
what are you trying to filter...just by city name right?

[dbruzzese84]

>>This is what happens when the comman button is clicked:

what happens?

Also...is there a reason you included the ID in with your listbox 2?
what are you trying to filter...just by city name right?

can you not see the image i posted? i took a snapshot of my screen. i dont want an input box titled "enter parameter" to show up. cant it just generate the results? im filtering by CSPC and CITY

[dbruzzese84]

>>This is what happens when the comman button is clicked:

what happens?

Also...is there a reason you included the ID in with your listbox 2?
what are you trying to filter...just by city name right?

can you not see the image i posted? i took a snapshot of my screen. i dont want an input box titled "enter parameter" to show up. cant it just generate the results? im filtering by CSPC and CITY

[MMcCarthy]

can you not see the image i posted? i took a snapshot of my screen. i dont want an input box titled "enter parameter" to show up. cant it just generate the results? im filtering by CSPC and CITY

When you are running your query the form has to be open with the listboxes selected.

If you want a distinct list of cities then

SELECT DISTINCT CityField FROM TableName

Mary

[JConsulting]

can you not see the image i posted? i took a snapshot of my screen. i dont want an input box titled "enter parameter" to show up. cant it just generate the results? im filtering by CSPC and CITY

Thank you Mary,

dbruzzese84,
No, I can't see it. Sorry.

I did ask if there was a reason you included the ID field in with your city list. The reason you're having issues is it's looking at the ID instead of the city name.


Hopefully Mary's post helped you out.
J

[dbruzzese84]

Thank you Mary,

dbruzzese84,
No, I can't see it. Sorry.

I did ask if there was a reason you included the ID field in with your city list. The reason you're having issues is it's looking at the ID instead of the city name.


Hopefully Mary's post helped you out.
J

is there anyway i can start over and begin a fresh db? everything seems mixed up in this db now. can you give me step by step instructions over the phone? email? whatever is easier.

[JConsulting]

is there anyway i can start over and begin a fresh db? everything seems mixed up in this db now. can you give me step by step instructions over the phone? email? whatever is easier.

dbruzzese84,
This forum is to assist you with issues you encounter during your development efforts. We all volunteer here assisting with questions like yours. In our private lives, most, if not all of us do this kind of work for a living, meaning, we get paid to design databases.

I understand your frustration when it comes time to communicate your needs, and have us respond as you would like. Who wouldn't want a Free consultant to call up when we have an issue that can walk us through, step by step until we get it right...then we would either get an A in our class, or a paycheck from our employer and pass off someone elses work as our own. I want that!!

If you go back to your first entry. You asked if you could have help making two listboxes.

you created these

SELECT [CSPC_LIST].[CSPC] FROM CSPC_LIST; <--rowsource for the 1st listbox containing the products
 
SELECT [LSList].[ID], [LSList].[CITY] FROM LSList; <-rowsource from 2nd listbox containing the cities.
 

What you encountered was duplicate entries in your CSPC listbox.

You need to edit it like this

SELECT [CSPC_LIST].[CSPC] FROM CSPC_LIST Group By CSPC;
 

That will get rid of the duplicate entries.

your second box...you have an ID field in there...it needs to go away. So you need to edit the second box like this.

SELECT [LSList].[CITY] FROM LSList group by [City];
 

Then also on the second box...you need to change the column count to 1 and you need to change the column widths to account for only 1 column.

Now, in your query you need to make these changes

SELECT SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
ORDER BY SalesLic952.CSPC, LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1, "SalesLic952.CSPC") and [LSList].[City] = nz(forms!frmForm1!lstList2, "[LSList].[City]");
 

Again, I understand your frustration. But please do not consider my help as any kind of obligation. I am helping you because I want to. I do not want to have a phone conversation, nor do I wish to exchange e-mails...As those to me are considered billable in my profession.

Sincerely,
J

[dbruzzese84]

dbruzzese84,
This forum is to assist you with issues you encounter during your development efforts. We all volunteer here assisting with questions like yours. In our private lives, most, if not all of us do this kind of work for a living, meaning, we get paid to design databases.

I understand your frustration when it comes time to communicate your needs, and have us respond as you would like. Who wouldn't want a Free consultant to call up when we have an issue that can walk us through, step by step until we get it right...then we would either get an A in our class, or a paycheck from our employer and pass off someone elses work as our own. I want that!!

If you go back to your first entry. You asked if you could have help making two listboxes.

you created these

SELECT [CSPC_LIST].[CSPC] FROM CSPC_LIST; <--rowsource for the 1st listbox containing the products
 
SELECT [LSList].[ID], [LSList].[CITY] FROM LSList; <-rowsource from 2nd listbox containing the cities.
 

What you encountered was duplicate entries in your CSPC listbox.

You need to edit it like this

SELECT [CSPC_LIST].[CSPC] FROM CSPC_LIST Group By CSPC;
 

That will get rid of the duplicate entries.

your second box...you have an ID field in there...it needs to go away. So you need to edit the second box like this.

SELECT [LSList].[CITY] FROM LSList group by [City];
 

Then also on the second box...you need to change the column count to 1 and you need to change the column widths to account for only 1 column.

Now, in your query you need to make these changes

SELECT SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
ORDER BY SalesLic952.CSPC, LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1, "SalesLic952.CSPC") and [LSList].[City] = nz(forms!frmForm1!lstList2, "[LSList].[City]");
 

Again, I understand your frustration. But please do not consider my help as any kind of obligation. I am helping you because I want to. I do not want to have a phone conversation, nor do I wish to exchange e-mails...As those to me are considered billable in my profession.

Sincerely,
J

woo hoo!! some progress! GREAT, i just got both textboxes the way I wanted them without them being duplicated. ok now, the code you gave me for the query is giving me a syntax error its missing an operator in here:

LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1, "SalesLic952.CSPC") and [LSList].[City] = nz(forms!frmForm1!lstList2, "[LSList].[City]");

it highlights the word, "where"

[JConsulting]

woo hoo!! some progress! GREAT, i just got both textboxes the way I wanted them without them being duplicated. ok now, the code you gave me for the query is giving me a syntax error its missing an operator in here:

LSList.CITY where SalesLic952.CSPC = nz(forms!frmForm1!lstList1, "SalesLic952.CSPC") and [LSList].[City] = nz(forms!frmForm1!lstList2, "[LSList].[City]");

it highlights the word, "where"

Great! Revised..

SELECT SalesLic952.CSPC, LSList.NAME, LSList.CITY, LSList.ADDRESS, LSList.[POSTAL CODE], SalesLic952.QTY
FROM LSList INNER JOIN SalesLic952 ON LSList.LIC_NO = SalesLic952.CUSTOMER
WHERE SalesLic952.CSPC = nz(forms!frmForm1!lstList1, "SalesLic952.CSPC") and [LSList].[City] = nz(forms!frmForm1!lstList2, "[LSList].[City]")
ORDER BY SalesLic952.CSPC, LSList.CITY;
 

[dbruzzese84]

ok this one has no errors but an "Enter Parameters Value" box appears 3 times and asks me

LSList.Name (ok, cancel)

forms!frmform1!lstList1 (ok, cancel)

Forms!frmform1!lstList2 (ok, cancel)

[JConsulting]

ok this one has no errors but an "Enter Parameters Value" box appears 3 times and asks me

LSList.Name (ok, cancel)

forms!frmform1!lstList1 (ok, cancel)

Forms!frmform1!lstList2 (ok, cancel)

ok...so why do you think that is?

These aren't named correctly. chack your LSList table and see what the Name field is supposed to be.

Check your form and see what the names of those listboxes are.

Oh, and by the way, you need to actually select something from the listboxes. then hit the button.

J

[dbruzzese84]

ok...so why do you think that is?

These aren't named correctly. chack your LSList table and see what the Name field is supposed to be.

Check your form and see what the names of those listboxes are.

Oh, and by the way, you need to actually select something from the listboxes. then hit the button.

J

I added NAME to the query, it was giving the error because it could not find it. As for the textboxes, they are named correctly (List1 and List2)

[JConsulting]

I added NAME to the query, it was giving the error because it could not find it. As for the textboxes, they are named correctly (List1 and List2)

in a previous post you said this

Listbox w/CSPC is List0
Listbox w/City is List1

Also, you selected an item from your listboxes when you try to run the query correct?

[dbruzzese84]

correct, but i chaned the names to list1 and list2. i made sure that in the code you gave me, list1 and list2 are there. i am selecting an entry for list1(the product) and also an entry for list2. i press the command button, and thats what happens. could it be my form?

[JConsulting]

correct, but i chaned the names to list1 and list2. i made sure that in the code you gave me, list1 and list2 are there. i am selecting an entry for list1(the product) and also an entry for list2. i press the command button, and thats what happens. could it be my form?

you have some kind of click event on a button you're trying to run this from right?

paste this into that code. then push the button. You should have two message boxes pop up...one after the other. Tell me what they say

msgbox Forms!frmform1!lstList1
msgbox Forms!frmform1!lstList2
 

[dbruzzese84]

you have some kind of click event on a button you're trying to run this from right?

paste this into that code. then push the button. You should have two message boxes pop up...one after the other. Tell me what they say

msgbox Forms!frmform1!lstList1
msgbox Forms!frmform1!lstList2
 

it says that it cant find the form 'frmForm1' referred to in a macro or expression

[JConsulting]

it says that it cant find the form 'frmForm1' referred to in a macro or expression

what I just showed you was how to find out what's wrong. troubleshooting 101.

So what do you need to do?

If you open your form in design mode...look at the properties. What does the NAME property say?

[dbruzzese84]

it says that it cant find the form 'frmForm1' referred to in a macro or expression

i figured out the problem! instead of using frm in front of Form1 or lst in front of List1, i just use their names. even in the code for the query! it works!! wow, all that for one little thing :) thanks dude ;p u have no idea how happy i am. now that thats figured out, I can tell it to find other things as well.