Solved
database query problem
Posted on 2001-07-10
Hi all,
I have a simple problem here and was wandering if anyone cold point me in the right direction. I am trying to carry out a simple query to a Access 2000 database.
I am using an SQL string to search for details by giving either a surname or a forename.
Here's the code... this first checks to see if a query already exists for that search... if so it will delete it and do a new one. It does work, a surename or forename will create a new query and display the relevent feilds.
The problem occurs on the line
Set RecSet = db.OpenRecordset(SQLString, dbOpenSnapshot)
giving a type mismatch error, maybe i've over looked something but I cannot understand why! can anyone help?
I am using Visual Basic 6 with Service Pack 5. i can send the complete database via email should that help.
Private Sub cmdSearch_Click()
Dim SQLString As String
Dim db As Database
Dim qdfTemp As QueryDef
Dim Reply As Integer
Dim RecCount As Integer
Dim RecSet As Recordset
Set db = OpenDatabase("c:\ticket\ticket.mdb")
Load stafftesterresult
'Build the SQL String according to the criteria entered
SQLString = "select * from Staff "
If txtSname <> "" Then
SQLString = SQLString & "where Sname like '" & txtSname & "*'"
Else
If txtFname <> "" Then
SQLString = SQLString & "where Fname like '" & txtFname & "*'"
End If
End If
db.QueryDefs.Refresh
For Each qdfTemp In db.QueryDefs
If qdfTemp.Name = "qryStaffList" Then db.QueryDefs.Delete "qryStaffList"
Next qdfTemp
Set qdfTemp = db.CreateQueryDef("qryStaffList", SQLString)
Set RecSet = db.OpenRecordset(SQLString, dbOpenSnapshot)
RecCount = RecSet.RecordCount
stafftesterresult.Show vbModeless, frmSplash
End Sub
thank you,
Byte_me.