Error 3421

[Triscuit]

Code for a "Search button", lookups the record if not found adds the record to the table.

I don't recognize where the 3421 Data type conversion error comes from.



Private Sub Search_Click()
On Error GoTo Error_Search_Click:
Dim db As DAO.Database
Dim rstCalibrationGroup As DAO.Recordset
Const conFilter As String = " & Me.cboFilter & "

Set db = CurrentDb
Set rstCalibrationGroup = db.OpenRecordset("tblCalibrationGroup")

If IsNull(Me!cboFilter) Then
GoTo Finish
End If
rstCalibrationGroup.OpenRecordset "Select * from tblCalibrationGroup WHERE txtCalibGroup = conFilter"
'Then, if the recordset comes back with no records, you know you have to add:
If rstCalibrationGroup.EOF Then
rstCalibrationGroup.AddNew
rstCalibrationGroup("txtCalibGroup").Value = Me!cboFilter
Else
Me!LstSearch.RowSource = "SELECT txtLabNum FROM tblCalibrationGroup WHERE [txtCalibGroup] = '" & Me!cboFilter & "' "
End If


Finish:
Exit Sub

Exit_Search_Click:
Exit Sub

Error_Search_Click:
MsgBox Err.Number & Err.Description
Resume Exit_Search_Click

End Sub

 

[lcook1974]

Check your "Where" critieria...

is the Me!cboFilter a text or number?

 

[Triscuit]

Me!cboFilter is the combo box who's rowsource is based off the txtCalibGroup in the table tblCalibrationGroup, which is a Text variable.

I would assume that the text variable in a table is a String?

I am certain this line

Me!LstSearch.RowSource = "SELECT txtLabNum FROM tblCalibrationGroup WHERE [txtCalibGroup] = '" & Me!cboFilter & "' "

is correct had it working before I tried to add the add new record capability.

 

[vbaInet]

First of all it would be useful to know which line is highlighted when the error pops up and you click DEBUG.

 

[Triscuit]

This is definitely going to sound stupid.

When I open my form in design mode and then hit Ctrl+G to pull up the debug window, under the Debug pulldown I can't compile the code and when I try to step into it I get the "Windows error sound" and nothing happens.

I read the 3421 error when I open the form regularly type something in the combo box and hit the "Search" command button.

 

[vbaInet]

Click the stop button. It looks like a square. Hover your mouse and you will find the stop button.

The Debug button I was referring to should show up in the error message box.

 

[Triscuit]

Open database project (Microsoft Access file type).
Open form frmCalibGroupOrganizer in design mode.
Hit Ctrl+G to open code view.
Click the "stop" button in the top toolbar.
Close code and design view.
Open form click button same Error window with error and "OK" button, no "Debug..." button appears.

 

[vbaInet]

Comment out the error handler in your code.

 

[Triscuit]

Thank you very much.

Here's the highlighted line.
rstCalibrationGroup.OpenRecordset "Select * from tblCalibrationGroup WHERE [txtCalibGroup] = '" & Me!cboFilter & "' "

 

[vbaInet]

You would need to check in the table to confirm what the datatype of the first column of cboFilter is. Number or Text?

 

[Triscuit]

Text. This is now more than 10 characters.

 

[vbaInet]

What is more than 10 characters?

Try this:

rstCalibrationGroup.OpenRecordset "Select * from tblCalibrationGroup WHERE [txtCalibGroup] = '" & Me.cboFilter.column(1) & "'"

 

[Triscuit]

I guess you can't have a post of less than 10 characters.

Your suggestion yielded the same error, I attempted to use the "FindFirst" method of the recordset object instead and IT WORKED OMGGGGG.

The working line is:
rstCalibrationGroup.OpenRecordset.FindFirst "txtCalibGroup = '" & Me.cboFilter & "'"

vbaInet thanks for all your help.

 

[vbaInet]

I don't see why it would have errored there if it was text. Maybe we needed Nz().

Try this later and see if it works.

rstCalibrationGroup.OpenRecordset "Select * from tblCalibrationGroup WHERE [txtCalibGroup] = '" & Nz(Me.cboFilter, "") & "'"

Happy coding

 

[Triscuit]

Still the same error.

 

[vbaInet]

Hmm... I'm curious. Can you attach your db?

 

[Triscuit]

Here you go.

 

[vbaInet]

What form is the code using?

 

[Triscuit]

The form frmCalibGroupOrganizer.

 

[vbaInet]

The problems were:

1. You did not set the rst object to anything
2. You were opening the table, then trying to open the same table (with that same recordset) but with a different criteria.

See attached.