Type mismatch error when basing one combox on an another

[John V]

I know this has been discussed here a million times before and I have searched but I cannot find an answer that fits my situation. I looked for both Type Mismatch and One Combobox based on another.

I have two ComboBoxes on my form and I am trying to have the list in one based on the results in another. The two comboboxes are Event and Position, the idea being when I select an Event in the Event combobox the Positions available to choose from in the Positions combobox will be based on the Employer for that Event.

The form is a Calls (as in work calls) Form (frmCallss) that has an Events Combobox (cboEvents) that has five columns associated with it, the last being EmployerFK. I also have a Positions Combobox (cboPositions) that has 4 columns associated with it once again the last being EmployerFK.



The Events Combobox uses an Events Table (tblEvents) as the basis for its RowSource (I am using a select query) and the Positions Combobox uses a Positions Table as the basis for its Rowsource (once again I am using a select query).

I have not forgotten that when referring to the columns in the Combobox query the columns are numbered with a base 0. (The last column of 5 is number 4)

This is the SQL statement I am using in the Positions Combobox Rowsource:

SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK
FROM tblPositions
WHERE (((tblPositions.EmployerFK)="&[Forms]![frmCalls]![cboEvents.Column(4)&"));

This select query is in the Criteria field of the EmployerFK column (the last column - Column 5, referred to as 4) of the Query builder.

I have set the Data Type of EmployerFK in both tables to Number (Long Integer).

When I click on the Positions Combobox Access comes up with the Type Mismatch error. Why?

TIA
John V

[June7]

The query is in the RowSource property, not being constructed in VBA?

Missing ][ and the concatenation is not needed.Try:

WHERE tblPositions.EmployerFK=[cboEvents].[Column](4);

[John V]

Yes that is correct the query is in the Rowsource property. I couldn't figure out how to use a Select Query in VBA. I know it has something to with defining a string variable, but this seemed like a simpler solution (or so I thought).

I should point out that my Combobox is cboEvent not cboEvents as I originally posted. I have corrected this error and entered the WHERE clause you suggested. Now it tells me that [cboEvent].[Column] is an undefined function.

I tried changing the WHERE clause to:

WHERE tblPositions.EmployerFK=[cboEvent].[Column(4)];

Then it asks for a parameter value. I am, of course, trying to get it to derive its own parameter value.

Access is hard, but it is the "hard" that makes it great (and fun)!

Thanks June 7
John V

[John_G]

Hi -

In this code :

WHERE tblPositions.EmployerFK=[cboEvent].[Column(4)];

Column(4) is a property, not a control name, so it cannot have square brackets. Try:

WHERE tblPositions.EmployerFK=[cboEvent].Column(4);

HTH

John

[John V]

John,

I tried your suggestion and it still comes up with an Undefined Function error.

"Undefined Function '[cboEvent].Column' in expression"

I have checked and double checked and the name of the Event Combobox is in fact cboEvent.

I should mention that Access adds extra brackets to the WHERE statement so it looks like this:

WHERE (((tblPositions.EmployerFK)=[cboEvent].Column(4)));

I am baffled!

John V

[John_G]

Hi -

It might be easier to reset the rowsource of Position in the After Update event of Event.

Try it like this:

Dim SQL as String
SQL = "SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK
FROM tblPositions
WHERE tblPositions.EmployerFK = " & Me![cboEvents].Column(4)
me!cboPosition.rowsource = SQL
me!cboPosition.Requery


John

[June7]

Sorry, I had forgotten that Column property does not work in SQL. Usually, dependent combobox is looking for the unique ID that is in first column of primary combobox.

Referring to Column property in textbox ControlSource does work.

=[cboEvents].[Column](4)

[John V]

So I have decided to give up on using a query in the RowSource property of the Position ComboBox and move on to John's suggestion of writing code for the AfterUpdate event of the Event ComboBox. I entered John's code with some modification to correct some syntax errors. The code now looks like this:

Dim SQL As String
SQL = "SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK" & _
"FROM tblPositions" & _
"WHERE tblPositions.EmployerFK = " & Me![cboEvent].Column(4) & ""
Me!cboPosition.RowSource = SQL
Me!cboPosition.Requery

This does work (in that it produces no errors) but it also produces no results. I suspect that there is in fact something happening because if I comment out the Requery statement it does in fact know what the value of Column 4 of the Position Combobox is as evidenced by the Missing Operator error message that pops up.

I am wondering if I actually need to have an OpenRecordset statement before the Requery statement. I have no idea how recordsets work though, and anything I can find online just confuses me.

I have attached a copy of my database in the hopes of solving this.

John V

Attachment 17192

[June7]

No need for opening a recordset. The code is setting combobox RowSource property with an SQL string. You are missing a space before the FROM and WHERE keywords which means the text is running together. Would have thought that would generate some kind of error.

I tried downloading the file but get invalid attachment error.

[John V]

Thanks June 7. I added the spaces, but still no results. Aargh!

I have tried attaching my file again. Hopefully it will work this time.

John V

Income.zip

[June7]

The code did not have the spaces (I have added them to the end of the lines):

Private Sub cboEvent_AfterUpdate()
Dim SQL As String
SQL = "SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK " & _
"FROM tblPositions " & _
"WHERE tblPositions.EmployerFK = " & Me![cboEvent].Column(4)
Me!cboPosition.RowSource = SQL
Me!cboPosition.Requery
End Sub

It works. However, there are no records in tblPositions that match the EmployerFK 3 so nothing lists in the combobox for the event Wicked.

[John V]

Thank you June 7 and John G. This code works great!

June 7 you are correct the DB I posted in my last reply did not have the suggested spaces inserted in the code. I did try putting the spaces in and it didn't work. I put them in the wrong place though. Like this:

Dim SQL As String
SQL = "SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK" & _
" FROM tblPositions" & _
" WHERE tblPositions.EmployerFK = " & Me![cboEvent].Column(4) & ""
Me!cboPosition.RowSource = SQL
Me!cboPosition.Requery

After your last reply I put the spaces in as you suggested. Like this:

Dim SQL As String
SQL = "SELECT tblPositions.PositionID, tblPositions.Department, tblPositions.Position, tblPositions.EmployerFK " & _
"FROM tblPositions " & _
"WHERE tblPositions.EmployerFK = " & Me![cboEvent].Column(4) & ""
Me!cboPosition.RowSource = SQL
Me!cboPosition.Requery

TaDa!! It works like a charm. Thank you so much.

Now on to the next thing I want to do with the same form. I am sure I will be back with more questions, but I want to try to make it work on my own first.

John