Combo Box that pulls info based on project

[JuniorWoodchuck24]

I'm trying to code a combo box that based on a previous project selection the combo box will list items from that project.

Form:
The form allows the user to select a project number. Once this project number is selected I want the next combo box to pull info from that project's table.

Tables:
ProjectInfo
101OverallProjectGroup
*OverallProjectGroup (Other Projects)

Code that I have thus far:
Dim xProjNo, xFullName, xProjectName

xProjNo = Me.cboProjectNo.Column(1) 'This pulls in the number not the ID
xProjectName = xProjNo & "OverallProjectGroup"
xFullName = "xProjectName.FirstName & ' ' & xProjectName.LastName"
cboEmployee.RowSource = "SELECT xProjectName.ID, xFullName" & _
"FROM xProjectName" & _
"ORDER BY FirstName;"

I know that if I type the following in the rowsource box for the cboEmployee I'll get a specific project but I'll have to manually change it each time:
SELECT 101OverallProjectGroup.ID, [FirstName] & " " & [LastName] FROM 101OverallProjectGroup ORDER BY FirstName;

That gets me what I want but I'm stuck with project 101

 

[vbaInet]

This might be what you're after:

http://baldyweb.com/CascadingCombo.htm

 

[JuniorWoodchuck24]

I'm trying to use the first box to give me the number to place in front of the string.

If user selects 101 then: 101OverallProjectGroup
If user selects 702 then: 702OverallProjectGroup

I think my syntax is in error with attaching all the strings.

If I dump in a msgbox calling for the *OverallProjectGroup it gives me the right string. But when I go to hit the pull down menu it says record source doesn't exist.

I understood the link and have used that before. Tried multiple other options to solve this issue and only one I can get is when I type in the actuall project number into the table name.

 

[vbaInet]

Probably this:

xProjNo = Me.cboProjectNo.Column(1) 'This pulls in the number not the ID
xProjectName = xProjNo & "OverallProjectGroup"
xFullName = xProjectName.FirstName & "' '" & xProjectName.LastName AS NameOfAlias
cboEmployee.RowSource = "SELECT ID, " & xFullName & _
            " FROM " & xProjectName & _
            " ORDER BY FirstName;"
cboEmployee.requery

 

[JuniorWoodchuck24]

xProjNo = Me.cboProjectNo.Column(1) 'This pulls in the number not the ID
xProjectName = xProjNo & "OverallProjectGroup"
xFullName = xProjectName.FirstName & "' '" & xProjectName.LastName AS NameOfAlias
cboEmployee.RowSource = "SELECT ID, " & xFullName & _
            " FROM " & xProjectName & _
            " ORDER BY FirstName;"
cboEmployee.requery

It works. The mistake I got after I pulled what RowSource was, is that the FROM and ORDER needed a space after opening quotes. Simple space >,<

Anyways thanks much for the help

 

[vbaInet]

Actually there were a few more mistakes apart from the spaces. You enclosed the variables in quotes so the parser wasn't seeing your variables as variables but as text, and you forgot to add an alias.

Glad it's working.