dropdown box

[Joe]

my access application let user to print monthly report.
and i had use module to extract data.

in previous version, when user click the report button, it prompt a input
box
to let them input value between 1 to 12 and year.

.......

Dim monthValue, Message, Title As String
Message = "Please enter a value between 1 and 12" ' Set prompt.
Title = "Month To Print" ' Set title.
monthValue = InputBox(Message, Title)

If monthValue = "" Then
Exit Sub
End If

If monthValue > 0 And monthValue < 13 Then

Dim MonthDes As String
If monthValue = 1 Then
MonthDes = "January"
ElseIf monthValue = 2 Then
..........

Now, they want me to use dropdown box instead of input box.

but i can't find the vbscript for dropdown box in "Help"

thx !
Joe

 

[Nikos Yannacopoulos]

Joe,

You don't "create" a dropdown list (combo box) in code, like you make an
input box appear, the approach is different: you already have the combo box
on a form, and make it show through the code for as long as you need it. You
have two options, i.e. (a) put the combo box on the same form, or (b) put
it on its own, separate form.

In case (a), you can have the combo visibla at all times, and ask the user
to select a month before they click on the button; the code would be
modified to something like:

Dim Message As String, Title As String
Dim MonthDes As String
If IsNull(Me.cboMonth) Then
Message = "Please select a month" ' Set prompt.
Title = "Month To Print" ' Set title.
MsgBox Message, Title
Exit Sub
MonthDes = Me.cboMonth
..........

where I have assumed the combo to be called cboMonth. The code checks to see
if a selection is made, and warns and exits if not.

Alternatively, you can have the combo hidden in the form's design, make it
appear on the click of the button, and hide it and run the rest of the code
(to produce the report) once a selection is made in the combo. In that case,
the code behind the command button would be simply:

Me.cboMonth.Visible = True

and the code behind the Click event of the combo would be something like:
MonthDes = Me.cboMonth
Me.cboMonth.Visible = False
.........

The problem with this approach is that if you accidentally select the wrong
month, you can't stop the code (unless you take extra measures such as
confirmation).

If you go for option (b), suppose you put cboMonth on its own form,
frmMonthSelection; the code behind the command button on the original form
would be simply:

DoCmd.OpenForm "frmMonthSelection"

Then you can use the combo's Click event or a command button on
frmMonthSelection (I prefer the latter in that it allows the user to change
an accidental wrong selection) to run the rest of the code:


MonthDes = Me.cboMonth
DoCmd.Close
...... the rest of your code

In any case, I have assumed the combo to display/return the month names
rather thean numbers, so there is no need for the If's.

HTH,
Nikos

 

[Joe]

thank you.

Joe,

You don't "create" a dropdown list (combo box) in code, like you make an
input box appear, the approach is different: you already have the combo
box
on a form, and make it show through the code for as long as you need it.
You
have two options, i.e. (a) put the combo box on the same form, or (b) put
it on its own, separate form.

In case (a), you can have the combo visibla at all times, and ask the user
to select a month before they click on the button; the code would be
modified to something like:

Dim Message As String, Title As String
Dim MonthDes As String
If IsNull(Me.cboMonth) Then
Message = "Please select a month" ' Set prompt.
Title = "Month To Print" ' Set title.
MsgBox Message, Title
Exit Sub
MonthDes = Me.cboMonth
..........

where I have assumed the combo to be called cboMonth. The code checks to
see
if a selection is made, and warns and exits if not.

Alternatively, you can have the combo hidden in the form's design, make it
appear on the click of the button, and hide it and run the rest of the
code
(to produce the report) once a selection is made in the combo. In that
case,
the code behind the command button would be simply:

Me.cboMonth.Visible = True

and the code behind the Click event of the combo would be something like:
MonthDes = Me.cboMonth
Me.cboMonth.Visible = False
........

The problem with this approach is that if you accidentally select the
wrong
month, you can't stop the code (unless you take extra measures such as
confirmation).

If you go for option (b), suppose you put cboMonth on its own form,
frmMonthSelection; the code behind the command button on the original form
would be simply:

DoCmd.OpenForm "frmMonthSelection"

Then you can use the combo's Click event or a command button on
frmMonthSelection (I prefer the latter in that it allows the user to
change
an accidental wrong selection) to run the rest of the code:


MonthDes = Me.cboMonth
DoCmd.Close
..... the rest of your code

In any case, I have assumed the combo to display/return the month names
rather thean numbers, so there is no need for the If's.

HTH,
Nikos