ACCESS: parameterized query & dropdown list

Question

Hello everyone!  I'm brand new to the wonderful world of Access users... and I'm already stuck! So if any of you (or even several, the more the merrier!) could spare a little bit of your time to help me, I would be eternally grateful!  So, here is my situation: I'm working in Access 2003, and I need to create a parameterized query on the client name from my Client table. So far, so good, but I would prefer to have a dropdown list to select my client's name, rather than having to type it all in the little "Enter a parameter value" window. Well, yes, my clients have complicated names, and I mess up either in spelling or typing one time out of two, so it doesn't work as well... Especially since in the end, I'm not the one who will be using it, so if I can't even get it to work, even though I designed it, it might not be a pretty sight for the others!  So, is there a kind soul in this forum who could help me? Thanks in advance! Aurore

zenon · Accepted Answer

Good evening,  With the dropdown list assistant, it's very easy.  Let's assume you have a form based on the clients table  You can create a dropdown list that includes the primary key of your table and the names of your clients. Access will ask you what you want to do, choose to search for a record in a table or query. Agree to hide the key column, and you're all set...  When you look at what happens, in the properties sheet of the list, you see under the format tab that Access has created two columns, that the first one has a width of 0 cm, and that it is this last one that is linked (in other words, it contains the primary key that allows filtering one and only one record from the table) Under the data tab, you see the query in SQL mode, but you can display it in graphical mode by clicking on the three dots... on the right. It's in this window that you can choose to index the fields in alphabetical order, for example. If you add columns, don't forget to modify the number and width under the format tab.  You can refine and display multiple columns (e.g., first name and date of birth in your list, if you have namesakes).  Another way to do this is to create an independent text box that "pre-filters" the list and allows you to enter only the first letters of the name... If you want, I can explain it to you.  PS your post would be better in the Database section.

debutante66 · Answer

Hello, when you create your clients table and more specifically the field "NOMCLIENT", instead of leaving the alphabetical type, choose with the little arrow on the right, LIST OF CHOICES, then check "I will type the desired values" NEXT and there you type your values in your first box, then you move from one line to another with the TAB key, definitely not with the ENTER key! only when you have finished your list of clients will you press ENTER there you will find yourself in the design of your table, and you continue to create the other fields.  then you will create your parameterized query on this clients table and you will see that you will have a dropdown list with a little arrow.  did I answer you? good luck

JEROME · Answer

Hello,  I am in the same situation as you regarding Access and the drop-down list.  Have you received a response regarding how to create a drop-down list during the query execution?  Thank you for getting back to me by email.  Wishing you good reception.  Best regards  JEROME  jleig@free.fr

zenon · Answer

Hi,   It's not very difficult to create a drop-down list in a form.  There's an assistant that does almost all the work.  We can also do it manually: -add a drop-down control, -right-click on the control, properties, -under the "data" tab; row source = table/query; in the content row, clicking on the 3 dots opens the query builder. You should try to include the primary key in the first column in order to use the data, but then you need to hide the column (properties, format, number of columns: for example: 0cm;4cm;...)

jerome · Answer

Thank you for the response,  But my problem is more complicated: I would like to have a dropdown list when executing a query to choose the correct spelling of a client, so that I can display the results of the query without having to manually enter the client's name.  There you go  thank you for the response  Sincerely

zenon · Answer

Well, it's possible...  If you know how to create a dropdown list that lists your clients in alphabetical order, you can create an independent text field where you would enter the first letters of the client name you're searching for, and then, in the properties of this field, after pressing SHIFT, you add the following code: [DropdownListName].Requery  Then, you need to modify the underlying query of the dropdown list by adding; WHERE [ClientName] = [IndependentTextFieldName]&"*"; (you can also do this by adding the criterion in the query builder, and of course, you should use the actual field names)  To improve functionality, it's best to "clear" the text field and set focus back to it to allow for a new attempt without further manipulation:  AnotherFieldOnTheForm.SetFocus TextFieldName = "" TextFieldName.SetFocus  You can also, if you find it takes up too much space, replace the dropdown list with a modifiable list.

LOULY57 · Answer

Hello everyone,  I have the same problem as Jérôme but I'm still unable to solve my issue.  So I have two tables:  COMPANY and PRODUCT  I would like to perform a query that, when I select a product using a dropdown list, displays all the companies that sell this product. I do not want to make a query based on the input criteria of the product name because the table has more than 300 entries, so it's not easy to know the exact spelling.  Thank you in advance because this is for a BTS project and I would like it to be top-notch and easy to use.

NOSTRADA · Answer

Hello to all Access experts.  I am seeking, through a query, to extract the even numbers contained in a series of any integers between 1 and 1000.  I can't find it!!  Thank you for your help. I am using ACCESS 2002.

Charlingals1 · Answer

Hello  I have the same problem. Except that for me it’s about managing clients' bank accounts. I created a form that displays all the operations in the database, and I also created a second form with a dropdown list containing all the client accounts.  In the source of my form, I retrieved the value of my dropdown list, but it doesn't work.  In the form where I choose my account, I tried to put a text box instead of a dropdown list and to enter an account. It worked wonderfully (but the downside is that I don't have my dropdown list).  So I think the problem comes from retrieving the value from the dropdown list.  If you have a solution to propose, that would be nice. If I find it, I'll post the solution.  Best regards charlingals1  Perseverance always leads to success!!!

thelegendofben · Answer

Hello

thelegendofben · Answer

Hello I have a little problem I have a database on Access I have a department table, a municipality table, and a theme table (distribution of libraries and other establishments by department) I would like to make a query with a drop-down list which means we could select the department through this list and my theme list would need to be updated when a selection is made that means if I choose a certain department, only the establishments from that department remain and then another drop-down list to choose the theme I don't know if I was very clear I'm not very good with Access thank you so much if someone could help me because this is super important!!!!

DarkAurora · Answer

My problem is different; I want to be able to select a name or all the names in my dropdown list. All my names are entered in my list, and I added "all," but now my selection query related to the form does not work anymore.  How do I ensure that when I select "all," it sends all the names to the query? Also, I've put a filter in my query; does that cause a problem?

helliott · Answer

Hello,  I have the same issue but I don't understand the proposed solutions (I must be clueless)  I want to make a selection query with a criterion, but instead of setting the criterion with a question like [which client?], I would rather have a choice list that proposes the list of clients, so that I can choose the client and the query displays the selection with the filter "client"  I am looking forward to your replies with great impatience  Marie

le raleur · Answer

Lol, people with problems, I think the best thing is not to overload this topic. Now it's a bit too late, no one wants to respond anymore because it has become unreadable.

ACCESS: parameterized query & dropdown list

14 réponses

Texture issues in enshrouded

Bank account charged but aliexpress order not found.

Can there be fake players in scrabble go?

My samsung galaxy tab 3 won't turn on and won't charge.

Philips sbc headphones batteries

Dhgate, reliable site or not?

Universal remote control temium remo3/1 code

Free plug sync problem

Very very slow internet...

Taskbar does not hide anymore