VBA Query Generator based on Form Controls (1 Viewer)

[Trevor G]

I am looking to generate a query based on selected controls in a form. The idea is select items from a list box (Multi Selection) then check some check boxes and it then generates a new query showing only the items selected from the listbox and what I have checked from the check boxes.

I am getting an error message which first states it cannot find the database.

The data comes from a single table called TempImport.
The form name is frmAdhocReportCreatorApril2012
The listbox name is lstSALCLV
Then each checkbox name begins with chk (sample would be chkSALNCL or chkSALORIG or chkSALSTC and so on)


The code I am developing is shown below. When I first select items from the list box and check some of the check boxes then click the Generate button it gives me this error "Could not find File" and Error Number 3024. The path it indicates is completely wrong as it indicates "M:\TempImport.mdb" as the database is located on my desktop and has a different name.

If I reset it and try again it then tells me the query already exist and gives me a run time error of 3012. Then highlights the line indicated in Red

Private Sub cmdGenerate_Click()
Dim frm As Form, ctl As Control
Dim I As Long
Dim LngCnt As Long
Dim dummy As Variant
Dim strsql As String
Dim db As Database
Dim qry As QueryDef
Dim varItm As Variant
Dim strparam As String
Dim strfrom As String

strsql = "SELECT [tempImport].SALCLV"
strfrom = " FROM [tempImport].SALCLV"
strparam = " WHERE "

Set frm = Forms!frmAdhocReportCreatorApril2012
Set ctl = frm!lstSALCLV
For Each varItm In ctl.ItemsSelected
strparam = strparam & "[tempImport].SALCLV=" & "'" & ctl.ItemData(varItm) & "'" & " OR "
Next varItm

strparam = Left(strparam, Len(strparam) - 4)
On Error GoTo start

Do While I = 0
LngCnt = LngCnt + 1
dummy = Me.Controls.Item(LngCnt).Name
Loop
start:
For I = 1 To LngCnt - 1
If Me.Controls.Item(I).Tag = "CHK" Then
If Me.Controls.Item(I).Value = True Then
strsql = strsql & ", "
End If
End If

Next I
strsql = strsql & strfrom & strparam
Set db = CurrentDb()
On Error Resume Next
Set qry = db.CreateQueryDef("qryAReportGenerator", strsql)
If Err > 0 Then
MsgBox ("You must select at least one product")
Exit Sub
End If
DoCmd.OpenQuery "qryAReportGenerator"
db.QueryDefs.Delete ("qryAReportGenerator")
End Sub

 

[Trevor G]

I have attached an extract of the database showing just the table with a little data and form if you can assist that would be very helpful.

 

[apeters]

I'm a little confused by the SQL:

strfrom = " FROM [tempImport].SALCLV"

Should it be:

strfrom = " FROM [tempImport]"

 

[Trevor G]

Thanks for the reply to my thread, I have altered that and it now generates the query but only shows the listbox content that I select it doesn't show any fields when I select the checkboxes.

 

[apeters]

In your code:

For I = 1 To LngCnt - 1
    If Me.Controls.Item(I).Tag = "CHK" Then
        If Me.Controls.Item(I).Value = True Then
        strsql = strsql & ", "
    End If
End If

your aim is to add a comma delimited list of table column names to strSql. There are two problems with the above code:

• If you're going to check each control's TAG property for 'CHK' to see if it's a check-box, then you need to ensure that this tag property is populated on the form (currently they're all blank);
• If a check-box is discovered and it has been checked (.value = true) then you need to add the corresponding table column name to strSql (currently you're just adding a load of commas).

In the code below, instead of using tags, I using the fact that you've prefixed each check-box name with 'chk'. I'm also taking advantage of the fact that your check-box names correspond to your table column names (once the prefix has been removed) :

For I = 1 To LngCnt - 1
    If UCase(Left(Me.Controls.Item(I).Name, 3)) = "CHK" Then
        If Me.Controls.Item(I).Value = True Then
            strsql = strsql & ", " & Right(Me.Controls.Item(I).Name, Len(Me.Controls.Item(I).Name) - 3)
        End If
    End If
Next I

You also need to double-check your check-box names to make sure they all follow the convention: chk<table column name> as I think there may be one or two exceptions.

 

[Trevor G]

apeters, thank you for assisting me. This has been a working challenge which I have been stuck on for a while now.

I am very grateful as this has resolved the issue. I have tag each check box and will clairfy the names.

I have a very big smile on my face now thanks to you.

 

[apeters]

Glad to help