B
biganthony via AccessMonster.com
Hi,
I have been asked to look at a database at work that has a query by form
where the user can select a table to query. There are three columns of boxes
on the form. The first column are drop-down combo boxes that allow the user
to select the fields depending upon the table selected above. The next column
of five are for the comparison operators (<,>, etc) and the third column of
five boxes are text boxes where the criteria values are typed. There is one
connector box that has two values: OR or AND.
The form works and displays the query in a table view. This is the code
behind the button that creates the output:
***** Begin Code Quote **********
On Error GoTo cmdRun_Click_Err
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = SQLBuild()
If Len(strSQL) > 0 Then
On Error Resume Next
db.QueryDefs.Delete "tempQueryByForm"
On Error GoTo cmdRun_Click_Err
Set qdf = db.CreateQueryDef("tempQueryByForm", strSQL)
DoCmd.Close
DoCmd.OpenQuery "tempQueryByForm", acNormal, acReadOnly
End If
cmdRun_Click_Exit:
Exit Sub
cmdRun_Click_Err:
MsgBox "Your Query could not run. Please check the SQL syntax and
criteria that you used.", vbOKOnly + vbCritical, "Help."
Resume cmdRun_Click_Exit
***** End Code Quote ********************
Currently, when the Run button is clicked, the output is displayed in a table
(just like when a query is run). I have been asked two questions at work and
I don't know the answers:
1. Can the output be displayed on a listbox on the form? (I don't know why
they want that - but I'm interested to learn how it could be done)
2. Can the data be sent straight to a report when the user clicks another
button on the form? Would I need to create a report first or can you create a
report "on the fly"?
I tried to answer question 1 myself and created a list box called lstOutput
and thought maybe code similar to the following could work which is being
used elsewhere in their database: (have used general names below)
***** Begin Code Quote **********
Dim strSQL As String
Dim strItem As String
strSQL = "SELECT Fieldname FROM " & Table_name
Set db = CurrentDb
Set RS = db.OpenRecordset(strSQL)
Do Until RS.EOF
strItem = RS.Fields(fieldname).value
Me.ListBox.AddItem UCase(strItem)
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set db = Nothing
***** EndCode Quote **********
Unfortunately, I cannot get the output displayed in the listbox.
I would appreciate it if someone could give me some help regarding this
situation.
Thanks
Anthony
I have been asked to look at a database at work that has a query by form
where the user can select a table to query. There are three columns of boxes
on the form. The first column are drop-down combo boxes that allow the user
to select the fields depending upon the table selected above. The next column
of five are for the comparison operators (<,>, etc) and the third column of
five boxes are text boxes where the criteria values are typed. There is one
connector box that has two values: OR or AND.
The form works and displays the query in a table view. This is the code
behind the button that creates the output:
***** Begin Code Quote **********
On Error GoTo cmdRun_Click_Err
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb
strSQL = SQLBuild()
If Len(strSQL) > 0 Then
On Error Resume Next
db.QueryDefs.Delete "tempQueryByForm"
On Error GoTo cmdRun_Click_Err
Set qdf = db.CreateQueryDef("tempQueryByForm", strSQL)
DoCmd.Close
DoCmd.OpenQuery "tempQueryByForm", acNormal, acReadOnly
End If
cmdRun_Click_Exit:
Exit Sub
cmdRun_Click_Err:
MsgBox "Your Query could not run. Please check the SQL syntax and
criteria that you used.", vbOKOnly + vbCritical, "Help."
Resume cmdRun_Click_Exit
***** End Code Quote ********************
Currently, when the Run button is clicked, the output is displayed in a table
(just like when a query is run). I have been asked two questions at work and
I don't know the answers:
1. Can the output be displayed on a listbox on the form? (I don't know why
they want that - but I'm interested to learn how it could be done)
2. Can the data be sent straight to a report when the user clicks another
button on the form? Would I need to create a report first or can you create a
report "on the fly"?
I tried to answer question 1 myself and created a list box called lstOutput
and thought maybe code similar to the following could work which is being
used elsewhere in their database: (have used general names below)
***** Begin Code Quote **********
Dim strSQL As String
Dim strItem As String
strSQL = "SELECT Fieldname FROM " & Table_name
Set db = CurrentDb
Set RS = db.OpenRecordset(strSQL)
Do Until RS.EOF
strItem = RS.Fields(fieldname).value
Me.ListBox.AddItem UCase(strItem)
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Set db = Nothing
***** EndCode Quote **********
Unfortunately, I cannot get the output displayed in the listbox.
I would appreciate it if someone could give me some help regarding this
situation.
Thanks
Anthony