Change Parameter Value into drop down list

I

Ian

I have a report that is based on a query. The query is
based on multiple tables but one of the sorted fields is
a drop down list (based on a LU_table). When the report
is run the user is promted to enter the value they want -
it has to be one of the values in the LU_table. How do I
turn the enter parameter box into a drop box based on the
same LU_table?
 
T

Tom Wickerath

Create a form that includes a combo box with the data from the lookup table. Use the reports
Open event procedure to open this form in dialog mode. The form can include a cancel command
button.


Code in the Report includes the following:

Option Compare Database 'Use database order for string comparisons
Option Explicit

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

Dim SQLStatement As String

DoCmd.OpenForm "frmReportsSelection", WindowMode:=acDialog

'Cancel the report if "cancel" was selected on the dialog form.
If Forms.frmReportsSelection.txtContinue = "no" Then
Cancel = True
End If

' remove semi-colon if necessary
If Right$(Me.RecordSource, 1) = ";" Then
Me.RecordSource = Mid$(Me.RecordSource, 1, Len(Me.RecordSource) - 1)
End If
Me.RecordSource = Me.RecordSource & " " & Forms![frmReportsSelection]![txtWhereClause]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

______________________

You should also include a Report_NoData event procedure to handle no records found:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "No records match this selection criteria.", 0, "Your title here"
DoCmd.CancelEvent

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
________________________

along with code to maximize the report on activate and restore on deactivate:

Private Sub Report_Activate()
On Error GoTo ProcError

DoCmd.Maximize

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Private Sub Report_Deactivate()
On Error GoTo ProcError

DoCmd.Restore

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

_______________________

Extra cool feature: Include a checkbox on your form that allows the user to print either a
summary report or a report with the detail included. In this case, the name of the detail
section in the report is "Detail1", and the name of the checkbox on the form is chkShowDetail:

Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ProcError

Cancel = Not ([Forms]![frmReportsSelection]![chkShowDetail].Value)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

____________________

Code in the form includes the following. The form includes a hidden textbox named txtContinue.
The form remains open, but in hidden mode. This way, the report can "see" the values in the form:
______________________

Option Compare Database 'Use database order for string comparisons
Option Explicit

Private Sub cmdCancel_Click()
On Error GoTo ProcError

Me.txtContinue = "no"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Private Sub cmdOK_Click()
On Error GoTo ProcError

Me.txtContinue = "yes"
Me.Visible = False

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

______________________

The combo box includes an After_Update event procedure

Private Sub cboComboBoxName_AfterUpdate()
On Error GoTo ProcError

RebuildWhereClause


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub


I have to run right now. I'll post the last remaining part of my answer later tonight. In the
meantime, try creating this much.

Good Luck!
Tom
_______________________________________________

I have a report that is based on a query. The query is
based on multiple tables but one of the sorted fields is
a drop down list (based on a LU_table). When the report
is run the user is promted to enter the value they want -
it has to be one of the values in the LU_table. How do I
turn the enter parameter box into a drop box based on the
same LU_table?
 
I

Ian

Sorry for the delay - I had to remove two lookup wizards
and adjust a bunch of forms and reports (forgot how
deadly they can be). Thanks for the amazing code!
however.....I know very little about VBA so I tried to
input stuff were I thought it belonged. It opened the
new form (called LU_servicesII) based on the table of the
same name. It was a blank form with one combo box to
select the name of the field [service name]. Each
[service name] does have a uniqueID called [serviceID]
but I haven't used the lookup wizard on this one just
coded the combo boxes from the forms.

Here is the actual on Open Event Code I put in.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

Dim SQLStatement As String

DoCmd.OpenForm "LU_ServicesII", WindowMode:=acDialog

'Cancel the report if "cancel" was selected on the dialog
form.
If Forms.frmReportsSelection.txtContinue = "no" Then
Cancel = True
End If

' remove semi-colon if necessary
If Right$(Me.RecordSource, 1) = ";" Then
Me.RecordSource = Mid$(Me.RecordSource, 1, Len
(Me.RecordSource) - 1)
End If
Me.RecordSource = Me.RecordSource & " " & Forms!
[LU_ServicesII]![Service name]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

And here is the SQL statment for the
 
I

Ian

I forgot to mention that when I open the report
LU_servicesII form opens, I choose the drop down name and
then click the X (close) button and get the error message
Error 438 Object doesn't support this property or method.

Here is the sql view for the query that creates the
report;

SELECT servicebyTypeStepII.ID, servicebyTypeStepII.[Last
Name], servicebyTypeStepII.[First Name],
servicebyTypeStepII.[Phone Number],
servicebyTypeStepII.Address, servicebyTypeStepII.City,
servicebyTypeStepII.Province, servicebyTypeStepII.[Postal
Code], servicebyTypeStepII.Size,
SevicebyTypeStepI.Service, SevicebyTypeStepI.ServiceID
FROM servicebyTypeStepII INNER JOIN SevicebyTypeStepI ON
servicebyTypeStepII.PropertyID =
SevicebyTypeStepI.PropertyID;
 
T

Tom Wickerath

Hi Ian,

Don't worry about any delay....I just returned home after being gone all day.
It opened the new form (called LU_servicesII) based
on the table of the same name.
Click to expand...

You'll want an unbound form (ie. no recordset specified). You will need an unbound combo box on
the form (ie. no control source). The Row Source should be a SQL statement that selects the
values that you want to use to populate your combo box. For example, if I was creating a combo
box in the Northwind sample database, to display all products, the row source would look like
this:

SELECT Products.ProductID, Products.ProductName
FROM Products
ORDER BY Products.ProductName;

We select the primary key, ProductID, which will be available in column 1 with 0" width (ie. it
is not visible). The ORDER BY statement orders the products ascending (default order by) in
alphabetical order. In this case, I'd probably name the combo box cboSelectProduct, and this
combo box would include an After_Update event procedure, originally shown as Private Sub
cboComboBoxName_AfterUpdate():

Private Sub cboSelectProduct_AfterUpdate()
On Error GoTo ProcError

RebuildWhereClause


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

So, here is where I had to leave you hanging this morning. If you only have one control (ie.
combo box, text box, etc.) that the user will select a value from as a parameter, then you really
don't need to call a separate RebuildWhereClause function. However, if you have more than one
control where the user can enter parameters, then call a RebuildWhereClause from the After_Update
event procedures of these controls. That way, your code will be more compact. For example, let's
say you wanted to set a default value in a text box when the form opens. You could do so as
follows:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Me!txtCountry = "US"
RebuildWhereClause
Me.Visible = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

The same RebuildWhereClause function is run during the Form_Open event procedure in this case.
Here is a cut-down version of this function (my source function is designed to handle several
controls on the form that the user can enter parameters into). Add two textboxes to the form, and
name them txtWhereClause and txtSelectionTitle. Set the visible property for these textboxes to
no. You can leave these textboxes visible during development, but you should make them invisible
before releasing the application to your users. By the way, my standard when ever I set a
control's visible property to no is to make it's Fore Color (font) red. That way, it's very
noticeable when I open the form in design view.

Private Sub RebuildWhereClause()
On Error GoTo ProcError

' This subroutine builds an SQL WHERE clause based on the choices
' made by the user on the form. It can be used as the WHERE parameter
' in the OpenReport command. The invisible text box Me![txtWhereClause]
' displays the completed WHERE clause.
'
' SelectionTitle string that contains a title to place at the top of the report,
' which specifies the selection made. Stored on form in invisible text box
' Me![txtSelectionTitle].

Dim WhereSQL As String
Dim ANDvariable As String
Dim SelectionTitle As String
Dim CommaVariable As Variant

WhereSQL = "WHERE "
ANDvariable = ""
SelectionTitle = ""
CommaVariable = ""

If Not IsNull(Me!cboSelectProduct) And Not IsEmpty(Me!cboSelectProduct) Then

' Comment out one of the following WhereSQL clauses (but not both):
' Numeric primary key:
WhereSQL = (WhereSQL + ANDvariable) & " (Products.ProductID=" & Me!cboSelectProduct & ") "
' Text type primary key:
WhereSQL = (WhereSQL + ANDvariable) & " (Products.ProductID=""" & Me!cboSelectProduct & """)
"

ANDvariable = " AND "
SelectionTitle = "Product = " & Me!cboSelectProduct
CommaVariable = ", "

End If

' Use the following if you have a text box that the user can enter a parameter into:
' If Not IsNull(Me!txtName And Not IsEmpty(Me!txtName) Then
' WhereSQL = (WhereSQL + ANDvariable) & " (TableName.FieldName=""" & Me!txtName & """) "
' SelectionTitle = SelectionTitle & CommaVariable & "Nation = " & Me!txtNat
' End If

If ANDvariable = "" Then
WhereSQL = ""
Else
WhereSQL = WhereSQL & ";"
End If

Me![txtWhereClause] = WhereSQL
Me![txtSelectionTitle] = SelectionTitle


ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

Here is the actual on Open Event Code I put in.
Private Sub Report_Open(Cancel As Integer)
Click to expand...

You put this code in the module associated with the report, right? If you happened to put this
code in the module associated with the form, this could possibly explain the Error 438 Object
doesn't support this property or method error that you reported in your follow-on message (I
haven't tested this, but it seems like it could throw this error if you put the code behind a
form).

The report should have a SQL statement shown as it's recordsource (ie. not a saved query). You
can open a query that the report might be based upon in design view, click on View > SQL View,
and copy the SQL statement. Paste it into the record source property for the report.

Oh, you'll also want a textbox at the top of your report with the following control source:

=[Forms]![LU_servicesII]![txtSelectionTitle]

This will print the selected criteria on the report.

I think I've pretty much covered it now. If you send me a private e-mail address, I can send you
a working sample. My e-mail address is pretty easy to figure out. It should be 18 characters
total, with no spaces.

Tom

AOS168 AT comcast dot net
_______________________________


Sorry for the delay - I had to remove two lookup wizards
and adjust a bunch of forms and reports (forgot how
deadly they can be). Thanks for the amazing code!
however.....I know very little about VBA so I tried to
input stuff were I thought it belonged. It opened the
new form (called LU_servicesII) based on the table of the
same name. It was a blank form with one combo box to
select the name of the field [service name]. Each
[service name] does have a uniqueID called [serviceID]
but I haven't used the lookup wizard on this one just
coded the combo boxes from the forms.

Here is the actual on Open Event Code I put in.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

Dim SQLStatement As String

DoCmd.OpenForm "LU_ServicesII", WindowMode:=acDialog

'Cancel the report if "cancel" was selected on the dialog
form.
If Forms.frmReportsSelection.txtContinue = "no" Then
Cancel = True
End If

' remove semi-colon if necessary
If Right$(Me.RecordSource, 1) = ";" Then
Me.RecordSource = Mid$(Me.RecordSource, 1, Len
(Me.RecordSource) - 1)
End If
Me.RecordSource = Me.RecordSource & " " & Forms!
[LU_ServicesII]![Service name]

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub

And here is the SQL statment for the
 
Top