Insert mutliple comboxes

[Mike70]

Trying to get data from Access to Excel. This is working fine but...

With Recordset
Source = "SELECT Field1, Field2, Field3, Field4, Field5 FROM table1" & _
"WHERE criteria = " & Worksheets("SomeName").cboBox1 & ";"
.Open Source:=Source, ActiveConnection:=Connection
End With

Range("A5").Offset(1, 0).CopyFromRecordset Recordset

...When i´m trying to insert 1 combobox more it´s not working. I tried this:



With Recordset
Source = "SELECT Field1, Field2, Field3, Field4, Field5 FROM table1" & _
"WHERE criteria = " & Worksheets("SomeName").cboBox1 & " AND " & Worksheets("SomeName").cboBox2 & ";"
.Open Source:=Source, ActiveConnection:=Connection
End With
' Write Recordset
Range("A5").Offset(1, 0).CopyFromRecordset Recordset

It's only taking criteria from the first combobox

[CJ_London]

what is Worksheets("SomeName").cboBox2 equal to? You've said criteria=cboBox1

[Mike70]

Forgot that :-). The criteria is in maybe field 6 and 7.

With Recordset
Source = "SELECT Field1, Field2, Field3, Field4, Field5 FROM table1" & _
"WHERE field6 = " & Worksheets("SomeName").cboBox1 & " AND field7 = " & Worksheets("SomeName").cboBox2 & ";"
.Open Source:=Source, ActiveConnection:=Connection
End With
' Write Recordset
Range("A5").Offset(1, 0).CopyFromRecordset Recordset

[CJ_London]

so is this now working?

On the basis your first bit of code works the following won't apply

if not, you are missing a space between table1 and WHERE

Since you are using dummy field names, I presume 'recordset' is also a dummy. If not, be aware it is a reserved word and using it can cause unexpected issues

Finally I presume field6 and field7 are numeric, if not you need to use single quotes