Select where form's combo column holds the info?

[Guest]

I am trying to get data in a combo box from another combo box using the
Select Statement below:

SELECT Subdepartments.ID, Subdepartments.[Subdepartment Name],
Subdepartments.[Department ID] FROM Subdepartments WHERE
Subdepartments.[Department ID]=Forms![Audit Database
Entry]![Department]??????????????

Forms![Audit Database Entry]![Department] is a combo box with 4 columns and
one of those colunns has the subdepartment number associated with the
department. I keep trying to do some variation on the Column(2), but it
doesn't like the syntax. Is this impossible or do I need Acess Grammar
lessons?

 

[Ron2005]

something in the order of

Forms![Audit Database Entry]![comboboxname]!column(n) remember
that the column is 0 based first field is 0 second is 1, etc.

 

[Guest]

Try using a DLookup first to get the value from the column you want and then
place that variable into your select statement.

 

[Marshall Barton]

I am trying to get data in a combo box from another combo box using the
Select Statement below:

SELECT Subdepartments.ID, Subdepartments.[Subdepartment Name],
Subdepartments.[Department ID] FROM Subdepartments WHERE
Subdepartments.[Department ID]=Forms![Audit Database
Entry]![Department]??????????????

Forms![Audit Database Entry]![Department] is a combo box with 4 columns and
one of those colunns has the subdepartment number associated with the
department. I keep trying to do some variation on the Column(2), but it
doesn't like the syntax. Is this impossible or do I need Acess Grammar
lessons?

Query expressions do not understand the indexed property
syntax. You will need to construct the row source query in
VBA code (probably in the department combo box's AfterUpdate
event and in the form's Current event. The code will look
something like:

Dim strSQL As String
strSQL = "SELECT ID, [Subdepartment Name], " & _
"[Department ID] " & _
"FROM Subdepartments " & _
"WHERE [Department ID]=" & Me.Department.Column(2)
Me.subdepartmentcombo.RowSource = strSQL

 

[Guest]

It IS a syntax problem. I was hoping it wasn't.

Thank you all for your help!!

I am trying to get data in a combo box from another combo box using the
Select Statement below:

SELECT Subdepartments.ID, Subdepartments.[Subdepartment Name],
Subdepartments.[Department ID] FROM Subdepartments WHERE
Subdepartments.[Department ID]=Forms![Audit Database
Entry]![Department]??????????????

Forms![Audit Database Entry]![Department] is a combo box with 4 columns and
one of those colunns has the subdepartment number associated with the
department. I keep trying to do some variation on the Column(2), but it
doesn't like the syntax. Is this impossible or do I need Acess Grammar
lessons?

Query expressions do not understand the indexed property
syntax. You will need to construct the row source query in
VBA code (probably in the department combo box's AfterUpdate
event and in the form's Current event. The code will look
something like:

Dim strSQL As String
strSQL = "SELECT ID, [Subdepartment Name], " & _
"[Department ID] " & _
"FROM Subdepartments " & _
"WHERE [Department ID]=" & Me.Department.Column(2)
Me.subdepartmentcombo.RowSource = strSQL