Problematic SQL emmbedding

[SirLlyod]

I just started using VB for my small Access project that seems to be expanding (by choice). I am just stuck with this bit of code. It is a search fuction. An Access Pop-up form is opens and there are three search fields... ID, Company and State... Well I've finally got it to work OR so I thought... it can search by the ID it's the other two that are giving me problems... I usually get a "Too Few Parameters. Expected <number>" error message. Help somebody out there... Here's my code....

Option Compare Database
Option Explicit

Private Sub cmdOK_Click()
On Error GoTo Err_cmdOK_Click

Dim stDocName As String, MySelect As String, MyFrom As String
Dim MyWhere As String, MyOrder As String, MyQry As String
Dim stLinkCriteria As String
Dim Mydb As Database
Dim MyRST As Recordset

MySelect = "SELECT DISTINCTROW Tours.ToursID, Tours.[Company Name], Tours.State, Tours.WorkPhone"
MyFrom = " FROM Tours"
MyOrder = " ORDER by Tours.[Company Name];"
MyWhere = " WHERE ((Not (Tours.ToursID) Is Null))"

If Not (IsNull(Me![ID])) Then
MyWhere = MyWhere & " AND (Tours.ToursID = " & Me![ID] & " )"
End If

If Not (IsNull(Me![Company])) Then
MyWhere = MyWhere & " AND (Tours.[Company Name] = " & Me![Company] & " )"
End If

If Not (IsNull(Me![State])) Then
MyWhere = MyWhere & " AND (Tours.State = " & Me![State] & " )"
End If

MyWhere = MyWhere & " AND (Tours.WorkPhone)"
MyQry = MySelect & MyFrom & MyWhere & MyOrder

Set Mydb = CurrentDb
Set MyRST = Mydb.OpenRecordset(MyQry)
DoCmd.Close acForm, "Find_Company_Sub"
If MyRST.RecordCount = 0 Then
MsgBox "No Records Found", vbOKOnly, "No Records"
Else
Forms![Find_Company].RecordSource = MySelect & MyFrom & MyWhere & MyOrder
End If

'stDocName = "Find_Company"

'stLinkCriteria = "[Company Name]=" & "'" & Me![Company] & "'"
'DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOK_Click:
Exit Sub

Err_cmdOK_Click:
MsgBox Err.Description
Resume Exit_cmdOK_Click

End Sub
Private Sub cmdCancel_Click()
On Error GoTo Err_cmdCancel_Click


DoCmd.Close

Exit_cmdCancel_Click:
Exit Sub

Err_cmdCancel_Click:
MsgBox Err.Description
Resume Exit_cmdCancel_Click

End Sub


Thanks!

[Mandelbrot]

Strewth, Lloyd!!


You like to pick the hard ways, don't you?!

Firstly, the ID, is this numeric or Text?

If it's numeric, then I'll have to recommend a different way.

One thing to note, when using Access, is that SQL scripts run faster than code.

You could do the following:

SELECT 
    Tours.ToursID, 
    Tours.[Company Name], 
    Tours.State, 
    Tours.WorkPhone
FROM 
    Tours
WHERE
    Tours.[Company Name] Like Forms![mySearchDialog]![txtSEARCHCONAME] & '*' AND
    Tours.State Like Forms![mySearchDialog]![txtSEARCHSTATE] & '*'
ORDER BY 
    Tours.[Company Name]
;

That will allow you to search by the company and state. Simply copy this into a new query, and save it.

Make a list-box on the form called lstGOTOBOX and use the query as the row-source. Create two text boxes called txtSEARCHCONAME and txtSEARCHSTATE. In the AfterUpdate event of each text box type:

=RequeryMe([lstGOTOBOX])

In the OnDoubleClick event for the list box type:

=FindRec([lstGOTOBOX], Forms![frmMyMainForm]![TourID], [Company Name])


(Please note I don't know the name of your main form & controls, so you'll need to sort that...)
Create a new module and copy this to it...

Public Function RequeryMe(ctrl as Control) as Boolean

    ctrl.requery

RequeryMe = True

End Function


Public Function FindRec(findwhat as Variant, findin as Control, switchfocus as control) As Boolean

Dim V As Boolean
Dim E As Boolean
Dim L as Boolean

    With findin
        V = .Visible
        E = .Enabled
        L = .Locked

        .Visible = True
        .Enabled = True
        .Locked = False

        DoCmd.FindRecord findwhat

        switchfocus.SetFocus

        .Visible = V
        .Enabled = E
        .Locked = L
    End With

FindRec = True

End Function

That only does the two text fields, as I said.

You can, however, write a piece of code to modify the query above to include the ID number. In the After update events of the three search fields you will need to put a piece of code in that modifies the query to include the extra WHERE criteria.

Would you like this all putting in an MDB?