Command Button Mismatch Error

[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

 

[PHV]

Replace this:
Dim dbs As Database, rst As Recordset

with this:
Dim dbs As DAO.Database, rst As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya HGoodloe . . .

Just triming the code a little:

[blue]   Dim dbs As DAO.Database, rst As DAO.Recordset, Ctl As Control, Cri As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Set Ctl = Me.txtSearch
   DL = vbNewLine & vbNewLine
   Ctl.SetFocus
   
   If Trim(Ctl & " ") = "" Then
      Msg = "Please enter a Contract Number!"
      Style = vbCritical + vbOKOnly
      Title = "No Contract Number Error!"
      MsgBox Msg, Style, Title
   Else
      Set dbs = OpenDatabase("Construction.mdb")
      Cri = "WHERE ([ContractNo] Like '*" & Ctl & "*');"
      Set rst = dbs.OpenRecordset("SELECT ContractNo FROM tblContracts " & Cri, dbOpenDynaset)
      
      If rst.BOF Then
         Msg = "Construction # invalid or not in database!" & DL & _
               "Please try again . . ."
         Style = vbInformation + vbOKOnly
         Title = "Code Error! . . ."
         MsgBox Msg, Style, Title
      Else
         Cri = "[ContractNo] Like '*" & Forms!Construction_Records!txtSearch & "*'"
         DoCmd.OpenForm "tblContracts", acFormDS, , Cri
      End If
      
      Set rst = Nothing
      Set dbs = Nothing
   End If
   
   Set Ctl = Nothing
  
End Sub[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[HGoodloe]

Thank you very much!!!

 

[HGoodloe]

Thank you very much. Changing the variable fixed the problem.