Custom Dialog Box Code

[Deverry]

Hi,
I have created a custom dialog box which is to replace query parameter popups. Query results in 3 pop up boxes and I'd like the CDB to replace same with one pop up box where all criteria may be entered.

I believe this would have to be coded but I am way over my head in this area having never written any before.

The query as it stands now works perfectly and the SQL for the optional criteria is as follows:

WHERE ((InStr([Which Vendor/s?, Blank = All],[Vendor]))>0 Or (InStr([Which Vendor/s?, Blank = All],[Vendor])) Is Null) AND ((InStr([Which MC/s?, Blank = All],[Movement Category]))>0 Or (InStr([Which MC/s?, Blank = All],[Movement Category])) Is Null) AND ((InStr([Which FP/s?, Blank = All],[FP]))>0 Or (InStr([Which FP/s?, Blank = All],[FP])) Is Null))

How do I translate this into code for the custom dialog box? Also do I need to create a hidden control to assign said code to?

Thanks for any help

Dev

 

[GJT]

This can be done!

What you will need to do is specify the source controls in the Query (if this query is only ever going to be called by your CDB).

To do this you need to specify the forms collection followed by the name of the form on which the control appears and finally the control name.

i.e I presume the fields in your SQL are marked with the question mark - Which Vendor/s? this should be replaced by :

[Forms]![Form1].[txtVendors]

I am assuming this will work for text fields - I have only ever used such code when working with combo boxes......

Good luck!

 

[bwyrwitzke]

Something similar

Dev,
I created a dialog box where all the query options were Yes/No. CheckX, CheckKa, etc are the check boxes on my form, qryModemSearch is the query that's modified in the process...

Hope this helps.

Bruce
______________________

Function ChangeQueryDef(strQuery As String, strSQL As String) As Boolean

If strQuery = "" Or strSQL = "" Then Exit Function

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs(strQuery)
qdf.SQL = strSQL
qfd.Close
RefreshDatabaseWindow

ChangeQueryDef = True

End Function

'------------------------------------------------
Function BuildSQLstring(strSQL As String) As Boolean

Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strORDER As String

strSELECT = "s.* "
strFROM = "tblModemSpecs s "
strORDER = "s.[Modem Type]"

If CheckX Then
strWHERE = strWHERE & " AND s.x =" & True
End If

If CheckKa Then
strWHERE = strWHERE & " AND s.Ka =" & True
End If

If CheckBPSK Then
strWHERE = strWHERE & " AND s.BPSK =" & True
End If

If CheckQPSK Then
strWHERE = strWHERE & " AND s.QPSK =" & True
End If

If CheckOQPSK Then
strWHERE = strWHERE & " AND s.OQPSK =" & True
End If

If Check8PSK Then
strWHERE = strWHERE & " AND s.[8PSK] =" & True
End If

If Check16QAM Then
strWHERE = strWHERE & " AND s.[16QAM] =" & True
End If

strSQL = "SELECT " & strSELECT
strSQL = strSQL & "FROM " & strFROM
If strWHERE <> "" Then strSQL = strSQL & "WHERE " & Mid$(strWHERE, 6)

strSQL = strSQL & " ORDER BY " & strORDER

BuildSQLstring = True

End Function

'------------------------------------------------
Private Sub cmdFind_Click()

Dim strSQL As String

If Not BuildSQLstring(strSQL) Then
MsgBox "There was a problem building the SQL string"
Exit Sub
End If

CurrentDb.QueryDefs("qryModemSearch").SQL = strSQL

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGoToModemFiltered"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

 

[Deverry]

GJT You are a legend Thank you. I almost have it working now, just one tiny gremlin which hopefully I'll figure out soon.