Newbie needd help with SELECT DISTINCT coding

[KaJoMFL]

Hello,


I am very new to MS Access. I am trying to teach myself Access in order to set up a record keeping system for my daughter's small taxidermy business. I would greatly appreciate any help that anyone can give me in this endeavor. I apologize upfront for seeming to be clueless about these procedures. At times I am not even sure what question to ask to find a solution for a problem. Anyway...

I have a table titled Customers with fields titles CustomerID, MI_LIcense,OtherLicense1, and OtherLicense2, which I wish to use in this application. The CustomerID is my primary key. I have a form entitled Work Orders with a combo box on it. When I click on it, I want to have this button display the MI_License, OtherLicense1, and OtherLicense2 values for a specific CustomerID, for whom this form is for, and then to be able to click on one of them to fill the form in with. I hope this makes sense to someone.

I have written this line of code in the Data tab, Row source, but I am not sure it is written correctly since it doesn't work.

[SELECTDISTINCT Customers.MI_License OR Customers.OtherLIcense1 ORCustomers.OtherLIcense2 FROM Customers.CustomerID WHERE "CustomerID ='" & Me!CustomerID & "'"]

When I click on the button, I get a message asking me to enter the parameter value for ME!CustomerID. When I enter the CustomerID, it shows a line with blank fields in it. I cannot figure out what I need to do to change the statement to make it work.

Thank you for any ideas that might help.
KaJoMFL

[Minty]

You can't Select Field1 OR Field2. That's not how a select works.

If you can have a variable number of licences per customer you probably should put those in a separate table (tblLicences) ? and then you can easily query if they have one or more .
With your current design if someone rocks up with licence 3 or 5 or 99 your are into a redesign. The table solution handles this in its stride.

[Micron]

This [SELECTDISTINCT is 2 separate words - SELECT DISTINCT. Does Access put square brackets around your code?

Anyway, suggest you develop your sql in query design view and test, as a rule, then adapt to your control rowsource. However,
- you cannot use ME reference in query design. It would have to be Forms!myFormName.MyControlName or a valid value.
- AFAIK, you cannot use ME reference when creating a rowsource for a control; not even like =' & Me.myControlName & '; These types of references are used in code.
- or you can create and save such a query, using the Forms! reference, then set the rowsource to that query name. The caveat is that the form must be open in form view, and the referenced control would have to contain a value for the query to work as expected.

[orange]

KaJoMFL,
Here is a link to info that should help with Database Planning and Design...and more.

[KaJoMFL]

Thank you for your suggestions. I had considered a license table but thought this approach might work instead. I will create this table and see if I can figure out how to make it work for me. Thanks again!

[KaJoMFL]

Thank you for your feedback as well. I did have a space in what I had written, however that seemed to disappear in what I wrote. I need to ponder what your are telling me and see how I can use this information. Not real sure how to create a query but will do some more reading. Appreciate your time.

[KaJoMFL]

Thank you! There appears to be a lot to read here. I will check this all out. It looks like valuable material. Thank you so much for your input.