OBJECT REQUIRED ERROR! -confused at which line

[star18]

Hello everyone or anyone,

below is my coding where i need to retrieved/list out items that have specific conditions(combo box). however i got an error that make me really confused.
"Run-time error 424, Object Required!" .
what caused it happen??. <excel newbie>

Option Explicit
Public cnn As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String

Public Sub OpenDB()
If cnn.State = adStateOpen Then cnn.Close
cnn.ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
cnn.Open
End Sub

Public Sub closeRS()
If rs.State = adStateOpen Then rs.Close
rs.CursorLocation = adUseClient
End Sub







Private Sub cmdShowData_Click()
'populate data
strSQL = "SELECT * FROM [INVENTORY&RET$] WHERE "
If cmbStatus.Text <> "" Then
strSQL = strSQL & " [STATUS]='" & cmbStatus.Text & "'"
End If

If cmbItem.Text <> "" Then
If cmbStatus.Text <> "" Then
strSQL = strSQL & " AND [ICT ITEM]='" & cmbItem.Text & "'"
Else
strSQL = strSQL & " [ICT ITEM]='" & cmbItem.Text & "'"
End If
End If

If cmbStatus.Text <> "" Or cmbItem.Text <> "" Then
'now extract data
closeRS

OpenDB
Set rs = New ADODB.Recordset
'rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
With rs
.Source = strSQL
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open

If rs.RecordCount > 0 Then
Sheets("VIEW").Visible = True
Sheets("VIEW").Select
Range("dataSet").Select
Range(Selection, Selection.End(xlDown)).ClearContents

'Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs
Else
MsgBox "I was not able to find any matching records.", vbExclamation + vbOKOnly
Exit Sub
End If
End With

End If
End Sub

[Norie]

Which line of code is highlighted when you get the error?

PS Can you add code tags?

[star18]

this line get highlighted

Please Login or Register  to view this content.

but if that line i change to

Please Login or Register  to view this content.

Please Login or Register  to view this content.

get highlighted.

[Norie]

I think it should be cnn rather than ActiveProject.Connection, what error do you get when you change to cnn?

[star18]

this line get highlighted

Please Login or Register  to view this content.

with error message "[Microsoft][ODBC Excel Driver.] too few parameters. expected 1"

[Norie]

One cause of that error could be a problem with the SQL query, have you checked what it is?

Another problem, which is connected to the above, could be with the sheet.

For example, the field names have changed.

Hard to tell without seeing a sample workbook.

[star18]

example.xlsm

here is the sample of my work. kindly hope it helpful.

[star18]

the button "show data" at sheet VIEW is the problem.

[Izandol]

You may not use the SQL string you do because the first row of your Inventory&Ret sheet is not the header row. You must therefore specify a range. You may use:

Please Login or Register  to view this content.

[Norie]

If Izandol is right then here are some other options.

Remove the first row on the worksheet so the fields/headers are in the first row.

Replace the field names in the query with Field1, Field4 or whatever, where FieldX is the field in column X.

Create a named range for the data and use that in the query in place of the sheet name.

[star18]

thank you Izandol. now the error is gone. however I still cant extract the data out to the VIEW sheet based on drop down list condition.
maybe either of you or anyone can solved this problem?

p/s:Norie, I cant get it which field name that i should replace and how to create the name range of the data.?

Thank you.

[Norie]

Are you sure no data is returned?

Try removing the formatting from rows 10 down on the VIEW sheet.

[star18]

yes i am very sure. I also try remove the formatting but still no data returned. Is it something wrong with my coding?

[Norie]

When I make the change Izandol suggests the code works and results are returned.

I thought nothing was being returned but then I noticed that when I selected all cells on sheet 'VIEW' I could see data.

So I removed all the formatting on that sheet and the data from the last run of the code was there.

[star18]

ok. now I know why I cant see the data. Thank you for your help Norie.
However just one more last question regarding my reset button, do you know why i have "automation error"
when i run this coding

Please Login or Register  to view this content.

[Norie]

That code isn't in the workbook you uploaded so I added it.

The problem is that you've populated the 2 comboboxes on the VIEW worksheet using the ListFillRange.

Excel doesn't like it when you try and clear a combobox that's connected to a range using Clear

If you want to clear the combobox then break the link by settting ListFillRange to ""

Please Login or Register  to view this content.

Mind you are you sure you want to clear it?

Don't you just want to clear the selections?

Please Login or Register  to view this content.

[star18]

Ok I mean to clear the selection. Thank you Norie for helping me doing this project.
Really appreciate tor contributions. =)
have a nice day!

[star18]

error.png

Norie, do you know why i have this error?
"General error unable to open registry key..." like in picture above?

i have this error when i put my coding like in example file in excel file located in server.

this line got highlighted when i click debug

Please Login or Register  to view this content.

do you know what happen?

[Norie]

Sorry I've never seen that error.

Does it only happen when you run the code with the file located on the server?

[star18]

yup. only happen when i run that file on server.

ok thank you, Norie