populate form dynamically

[bill]

I would like to allow users to create a select statement dynamically and
display the results in a form or report.

What is the best way to do this?

The fields to be displayed will not be known until runtime.

Can I build a form or report dynamically?

Thanks!
Bill

 

[Graham R Seach]

Bill,

At face value, your requirement is not a good idea. Allowing users to
specify their own query leads the way to "SQL injection", where a malicious
(or stupid) user can enter something like:
DELETE * FROM tblSomeTable

But I think maybe what you really want is to be able to filter the records.
Is that right?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[bill]

I have created an interface where the user can select the parameters and
conditions and output fields which they want.

The ADE actually builds the sql from the user selections, so SQL injection
is prevented.

They then run the query, but they want to be able to view the results before
exporting to Excel.

So I guess I have to dynamically build a form or report containing the
fields they selected for output, with the data matching the criteria the
user entered.

Thanks
Bill

 

[bill]

I want to filter the records, yes, but only display selected fields!

The interface prevents SQL Injection

Do you know a way to do this?

-Bill

 

[Graham R Seach]

Bill,

The essence of this is a two-part sequence. Firstly, you need to build the
SQL statement in VBA.

Assuming you have a CheckBox for each field you want to display, and a
TextBoxe for each field (into which the user can enter criteria values) you
want included in the criteria.

Place the name of the underlying field in each control's Tag property. For
example, if chkItemID's underlying fieldname is ItemID, then enter "ItemID"
(without the quotes) into the control's Tag property. Do the same for each
CheckBox and TextBox involved in the query.

The following example takes 3 CheckBoxes (named chkItemID, chkCategory and
chkDescription) and 3 TextBoxes (named txtItemID, txtCategory and
txtDescription). I know that ItemID is numeric and the other two are
strings, so I've taken that into account when I build the criteria string.
The code is pretty self-explanatory.

Private sSQL As String

Private Sub cmdBuildQuery_Click()
Dim sShowFields As String
Dim sCriteria As String

sSQL = "SELECT * FROM lkpItem"

'Get all the fields to display
If (Me.chkItemID = True) Then
sShowFields = "[" & Me.chkItemID.Tag & "]"
End If

If (Me.chkCategory = True) Then
If Len(sShowFields) > 0 Then sShowFields = sShowFields & ","
sShowFields = sShowFields & "[" & Me.chkCategory.Tag & "]"
End If

If (Me.chkDescription = True) Then
If Len(sShowFields) > 0 Then sShowFields = sShowFields & ","
sShowFields = sShowFields & "[" & Me.chkDescription.Tag & "]"
End If

'Get all the criteria fields and their values
If Len(Trim(Me.txtItemID)) > 0 Then
sCriteria = "[" & Me.txtItemID.Tag & "] = " & Me.txtItemID
End If

If Len(Trim(Me.txtCategory)) > 0 Then
If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
sCriteria = sCriteria & "[" & Me.txtCategory.Tag & "] = """ &
Me.txtCategory & """"
End If

If Len(Trim(Me.txtDescription)) > 0 Then
If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
sCriteria = sCriteria & "[" & Me.txtDescription.Tag & "] = """ &
Me.txtDescription & """"
End If

sSQL = Replace(sSQL, "*", sShowFields)
If Len(sCriteria) > 0 Then sSQL = sSQL & " WHERE " & sCriteria
'Debug.Print sSQL
End Sub

Secondly, you need to display the results of that query. To do that, create
a temporary query, which you delete immediately after displaying it. Of
course, you can keep it if you wish, by removing the line in the following
procedure which deletes it.

Private Sub cmdDisplayResults_Click()
Dim db As Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("qdfTemp", sSQL)

DoCmd.OpenQuery "qdfTemp", acViewPreview, acReadOnly

Set qdf = Nothing
db.QueryDefs.Delete "qdfTemp"
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Graham R Seach]

Bill,

A slight bug...
Change the following line at the end of cmdBuildQuery_Click()...
sSQL = Replace(sSQL, "*", sShowFields)
....to...
If Len(sShowFields) > 0 Then sSQL = Replace(sSQL, "*", sShowFields)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Bill,

The essence of this is a two-part sequence. Firstly, you need to build the
SQL statement in VBA.

Assuming you have a CheckBox for each field you want to display, and a
TextBoxe for each field (into which the user can enter criteria values)
you want included in the criteria.

Place the name of the underlying field in each control's Tag property. For
example, if chkItemID's underlying fieldname is ItemID, then enter
"ItemID" (without the quotes) into the control's Tag property. Do the same
for each CheckBox and TextBox involved in the query.

The following example takes 3 CheckBoxes (named chkItemID, chkCategory and
chkDescription) and 3 TextBoxes (named txtItemID, txtCategory and
txtDescription). I know that ItemID is numeric and the other two are
strings, so I've taken that into account when I build the criteria string.
The code is pretty self-explanatory.

Private sSQL As String

Private Sub cmdBuildQuery_Click()
Dim sShowFields As String
Dim sCriteria As String

sSQL = "SELECT * FROM lkpItem"

'Get all the fields to display
If (Me.chkItemID = True) Then
sShowFields = "[" & Me.chkItemID.Tag & "]"
End If

If (Me.chkCategory = True) Then
If Len(sShowFields) > 0 Then sShowFields = sShowFields & ","
sShowFields = sShowFields & "[" & Me.chkCategory.Tag & "]"
End If

If (Me.chkDescription = True) Then
If Len(sShowFields) > 0 Then sShowFields = sShowFields & ","
sShowFields = sShowFields & "[" & Me.chkDescription.Tag & "]"
End If

'Get all the criteria fields and their values
If Len(Trim(Me.txtItemID)) > 0 Then
sCriteria = "[" & Me.txtItemID.Tag & "] = " & Me.txtItemID
End If

If Len(Trim(Me.txtCategory)) > 0 Then
If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
sCriteria = sCriteria & "[" & Me.txtCategory.Tag & "] = """ &
Me.txtCategory & """"
End If

If Len(Trim(Me.txtDescription)) > 0 Then
If Len(sCriteria) > 0 Then sCriteria = sCriteria & " AND "
sCriteria = sCriteria & "[" & Me.txtDescription.Tag & "] = """
& Me.txtDescription & """"
End If

sSQL = Replace(sSQL, "*", sShowFields)
If Len(sCriteria) > 0 Then sSQL = sSQL & " WHERE " & sCriteria
'Debug.Print sSQL
End Sub

Secondly, you need to display the results of that query. To do that,
create a temporary query, which you delete immediately after displaying
it. Of course, you can keep it if you wish, by removing the line in the
following procedure which deletes it.

Private Sub cmdDisplayResults_Click()
Dim db As Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef("qdfTemp", sSQL)

DoCmd.OpenQuery "qdfTemp", acViewPreview, acReadOnly

Set qdf = Nothing
db.QueryDefs.Delete "qdfTemp"
Set db = Nothing
End Sub

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I want to filter the records, yes, but only display selected fields!

The interface prevents SQL Injection

Do you know a way to do this?

-Bill