The usual "Enter Parameter Value" prompt with combo boxes

[chickenfriedsteak]

I've been on Google most of the morning and I can't find a real, solid
answer to this issue, though it looks like lots of people have it.
I'm running Access 2007and have a form I've created that (when
finished) will use a series of three combo boxes, each one based on
the one previous, to drill down to a specific value to populate a text
box. The initial combo box is populated through a SQL query. The
whole form itself is based off a SQL query that connects three tables.

I'm only on the 2nd combo box, and I'm getting the dreaded "Enter
Parameter Value" prompt when the 2nd combo box is updated after
selecting a value from the first.

The data I'm tasked with representing has a number of unavoidable
duplicates, so splitting it out three ways and drilling down was the
easiest way to do it (the only way to create a unique key would have
been to basically make all the columns in each row a combined key).
It breaks down like this:

Combo Box 1: Server Name
Data is populated with the following query statement -
SELECT DISTINCT qrySharePermissions.tblShares_Server_Name
FROM qrySharePermissions;
This combo box populates fine and without error.

As soon as I select a value from this first combo box, I get the
"Enter Parameter Value" prompt; the text box input on the prompt is
blank, but above it is a label with the server name I selected in
combo box 1. If I type the exact same server name, as it appears in
the label in the prompt, combo box 2 populates properly with the list
of all shares on that server.

Combo Box 2: Share Name
Data is populated with the following After Update event on Combo11:
Me.Combo15.RowSource = "SELECT DISTINCT Share_Name FROM" & _
" tblShares WHERE Server_Name = " & _
Me.Combo11 & _
" ORDER BY Share_Name"
Me.Combo15 = Me.Combo15.ItemData(0)
This combo box populates after I fill in the server name on the "Enter
Parameter Value" prompt.

Anyone have any ideas? I've already tried checking my queries and
they seem ok (but obviously something's messed up, so maybe I'm just
not looking in the right area). I also thought it may have something
to do with the DISTINCT so I tried removing it, but that didn't work.
None of my queries are set to order anything; that's only done on the
form with the After Update event above.

 

[bcap]

Maybe this:

Me.Combo15.RowSource = "SELECT DISTINCT Share_Name FROM" & _
" tblShares WHERE Server_Name = """ & _
Me.Combo11 & _
""" ORDER BY Share_Name"

n.b. I don't think much to your naming conventions. Combo11? Combo15?
Anyone who has to maintain this after you (including you in a years' time)
is not gonna thank you for not bothering to use descriptive names.

 

[chickenfriedsteak]

Maybe this:

 Me.Combo15.RowSource = "SELECT DISTINCT Share_Name FROM" & _
                               " tblShares WHERE Server_Name = """ & _
                               Me.Combo11& _
                               """ ORDER BY Share_Name"

n.b. I don't think much to your naming conventions.  Combo11?  Combo15?
Anyone who has to maintain this after you (including you in a years' time)
is not gonna thank you for not bothering to use descriptive names.












- Show quoted text -

Yeah, I meant to put in there that this is just in the playing around,
prototyping, "I just want to see if this works" phase before I worry
about implementing into my real 8-month long project database I've
been building, planning, nurturing, etc. I generally don't worry
about proper naming conventions until I have the actual concepts down
and I fully understand them.

 

[bcap]

But did my suggested amendment to your code work?

 

[chickenfriedsteak]

Maybe this:

 Me.Combo15.RowSource = "SELECT DISTINCT Share_Name FROM" & _
                               " tblShares WHERE Server_Name = """ & _
                               Me.Combo11& _
                               """ ORDER BY Share_Name"

n.b. I don't think much to your naming conventions.  Combo11?  Combo15?
Anyone who has to maintain this after you (including you in a years' time)
is not gonna thank you for not bothering to use descriptive names.












- Show quoted text -

Oh forgot to mention, I think that worked perfectly (initial tests are
positive). Thanks!