Microsoft Access Forms With Combo List

Posted on 2009-05-28
Medium Priority
Last Modified: 2013-11-28
How can I create a display only form in MS Access to display all the rows in a table, with one of the fields being a combo box so the result would be:

PartNo - Description - Products(Combo)

For the Products(Combo), I wanted to specify the source as an SQL Query that displays all the Products associated with the PartNo (in that row)
I would need to refer to the field in that particular row somehow in the SQL statement but I cant work out how to specify it.

Anyone know the best way to achieve this ?
Question by:ls21gce
  • 2
  • 2
LVL 10

Expert Comment

ID: 24494208
Can you give us a couple of sample rows from your table?


Author Comment

ID: 24495131
Hi Calpurnia,

Please See the two tables attached.

I want to see only 1 line of output for each record which includes the comb list of Product_Code/Qty
LVL 10

Accepted Solution

calpurnia earned 2000 total points
ID: 24495706
I'm assuming your form will be displayed in 'Continuous forms' view.

The first thought that comes to mind is to set the Row Source for the Combo box in the 'On current' event of the form.

Then you'd be looking at something like:

Private Sub Form_Current()
    Dim strSQL As String
    strSQL = "SELECT * FROM Products WHERE [Number]=" & Me!Number
    Me.Combo1.RowSource = strSQL
End Sub

You'll need to change 'Combo1' to the name of your combo box.

Could I also suggest that you don't use 'Name', 'Number' and 'Date' as field names - they can cause problems. Field names like 'ClientName' or 'ProductNumber' are fine. It's also good practice to avoid spaces in field names.

Author Closing Comment

ID: 31586284
Hi Calpurnia,
Great thanks, the solution using Me! works perfectly just what I needed.

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question