Form command button mismatch error 1

[HGoodloe]

I Imported tables, queries, forms and reports from another database into a new database. In the new database I created a form that uses a text box that will search for records when entering a contract number. The problem I’m having is that once the command button has been clicked after entering the contract number, I get a type mismatch error.

I have used this same procedure in other projects and it worked just fine, but for some reason I can’t get it to work with this particular project. Could the problem have something to do with the fact that I imported from another database?

The following is the source code used in the command button:

Private Sub cmdSearch_Click()

Dim dbs As Database, rst As Recordset

Me.txtSearch.SetFocus

If Me.txtSearch.Text = "" Then
MsgBox "Please enter a Contract Number"
Me.txtSearch.SetFocus
Exit Sub
End If

Set dbs = OpenDatabase("Construction.mdb")

txtSearch.SetFocus

Set rst = dbs.OpenRecordset("Select ContractNo from tblContracts " & " Where ContractNo Like " & "'*" & _
Me.txtSearch & "*'" & ";")

If rst.RecordCount < 1 Then
MsgBox "Construction # invalid or not in database, Please try again", vbOKOnly, "Code Error"
Else

DoCmd.OpenForm "tblContracts", acFormDS, , "ContractNo Like '*" & Forms!Construction_Records.txtSearch & "*'"

Exit Sub
End If

End Sub

 

[RoyVidar]

Not relevant to the question, but the following construct

[tt] Me.txtSearch.SetFocus

If Me.txtSearch.Text = "" Then[/tt]

can rather be written as

[tt] If Me.txtSearch.Value = "" Then
' or
If Me.txtSearch = "" Then[/tt]

Since .Value is default property, but I have a distaste for default properties, and prefer to specify.

Now, what probably causes your type mismatch, is that the declarations are ambiguous. There are probably more than one object library having a .Recordset datatype, and it picks the wrong one. Much better is to disambiguate the declarations, like this:

[tt]dim db as DAO.Database
dim rs as DAO.Recordset[/tt]

Roy-Vidar