A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Better way to utilize my form



 
 
Thread Tools Display Modes
  #1  
Old June 26th, 2009, 09:24 PM posted to microsoft.public.access
mlieberstein
external usenet poster
 
Posts: 11
Default Better way to utilize my form

I have a user go into a Form which allows the user to select a BusinessGroup
with a combo box. Then the user has a command button on the form which
launches an event procedure.

What happens 1st is:

An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes
via a msgbox)
Then the following code is executed:

Private Sub cmd_OpenReport_Click()

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business
Group I] = '" & Me.cbxBusinessGroup & "'"
DoCmd.RunCommand acCmdPrint

End Sub

This works fine, but is there a better way?

---I’d like for the user to 1st enter the YearMonthValue in a text box on my
form, then select a BusinessGroup in the ComboBox. Then use a command Button
to use both the text box and the combo box data as filters to OpenReport.


  #2  
Old June 27th, 2009, 01:34 AM posted to microsoft.public.access
Arvin Meyer MVP
external usenet poster
 
Posts: 640
Default Better way to utilize my form

The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be
changed to something like:

[Forms]![YourFormName]![YourTextboxName]

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"mlieberstein" wrote in message
...
I have a user go into a Form which allows the user to select a
BusinessGroup
with a combo box. Then the user has a command button on the form which
launches an event procedure.

What happens 1st is:

An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes
via a msgbox)
Then the following code is executed:

Private Sub cmd_OpenReport_Click()

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business
Group I] = '" & Me.cbxBusinessGroup & "'"
DoCmd.RunCommand acCmdPrint

End Sub

This works fine, but is there a better way?

---I'd like for the user to 1st enter the YearMonthValue in a text box on
my
form, then select a BusinessGroup in the ComboBox. Then use a command
Button
to use both the text box and the combo box data as filters to OpenReport.




  #3  
Old June 30th, 2009, 02:07 PM posted to microsoft.public.access
mlieberstein
external usenet poster
 
Posts: 11
Default Better way to utilize my form

Thank You very Much. I appreciate your advice.

After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to
put the textbox data (YearMonthValue) into my report. I created a text box
and tried the following:

=[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data)
=Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data)

Any ideas to get this to work?




"Arvin Meyer MVP" wrote:

The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be
changed to something like:

[Forms]![YourFormName]![YourTextboxName]

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"mlieberstein" wrote in message
...
I have a user go into a Form which allows the user to select a
BusinessGroup
with a combo box. Then the user has a command button on the form which
launches an event procedure.

What happens 1st is:

An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes
via a msgbox)
Then the following code is executed:

Private Sub cmd_OpenReport_Click()

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business
Group I] = '" & Me.cbxBusinessGroup & "'"
DoCmd.RunCommand acCmdPrint

End Sub

This works fine, but is there a better way?

---I'd like for the user to 1st enter the YearMonthValue in a text box on
my
form, then select a BusinessGroup in the ComboBox. Then use a command
Button
to use both the text box and the combo box data as filters to OpenReport.





  #4  
Old June 30th, 2009, 05:12 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Better way to utilize my form

If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not
formula, that's a spreadsheet term) in the text box on the report it sounds
rather like its been entered in the ControlSource property as a string
expression rather than a reference to the control, but that seems unlikely
and you do not include any delimiting quotes characters around the expression
as posted to suggest this is the case. However, you can achieve the same end
result without any references to the control as parameters in the query or as
the ControlSource property of a text box in the report. You can filter the
report by means of the WhereCondition argument of the OpenReport method
(which you are doing already, so its just a case of extending the expression),
and you can assign a value to the text box on the report by passing it to the
report as the OpenArgs argument of the OpenReport method:

Const conMESSAGE = _
"Both a business group and YMV value must be provided"

Dim strCriteria As String, strArgs as String

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.txtYMV) Then

strArgs = Me.txtYMV

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = """ & strArgs & """"

DoCmd.OpenReport "BusinessGroupReport_rpt", _
View:=acViewPreview, _
WhereCondition:=strCriteria, _
OpenArgs:=strArgs

DoCmd.RunCommand acCmdPrint
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

This assumes that the field in the report's underlying recordset is named
YearMonth and is of text data type. If it’s a number data type omit the
delimiting quotes:

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = " & strArgs

In the report's Open event procedure assign the value passed to the report as
its OpenArgs property to an unbound text box in the report:

Me.txtYMV = Me.OpenArgs

As with this approach you are passing values to the report rather than
referencing controls on the form you can if you wish automatically close the
form after the button is clicked by adding the following to its code:

DoCmd.Close acForm, Me.Name

Another possible enhancement would be to include two combo boxes cbxYear and
cbxMonth, on the form for the YMV value rather than a single text box, one
for the year and one for the month (rather like when entering the expiry date
of a credit card in an online form), the former with values over a suitable
range of years, the latter with values 01 to 12. You'd then amend the code:

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.cbxYear ) And _
Not IsNull(Me.cbxMonth ) Then

strArgs = Me.cbxYear & Me.cbxMonth

You could if you wish by default set the combo boxes to the current
year/month in the form's Open event procedure with:

Me.cbxYear = Format(VBA.Date,"yyyy")
Me.cbxMonth = Format(VBA.Date,"mm")

Ken Sheridan
Stafford, England

mlieberstein wrote:
Thank You very Much. I appreciate your advice.

After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to
put the textbox data (YearMonthValue) into my report. I created a text box
and tried the following:

=[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data)
=Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data)

Any ideas to get this to work?

The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be

[quoted text clipped - 28 lines]
Button
to use both the text box and the combo box data as filters to OpenReport.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1

  #5  
Old July 8th, 2009, 12:56 PM posted to microsoft.public.access
mlieberstein
external usenet poster
 
Posts: 11
Default Better way to utilize my form

great thanks for your assistance, Ken.

"KenSheridan via AccessMonster.com" wrote:

If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not
formula, that's a spreadsheet term) in the text box on the report it sounds
rather like its been entered in the ControlSource property as a string
expression rather than a reference to the control, but that seems unlikely
and you do not include any delimiting quotes characters around the expression
as posted to suggest this is the case. However, you can achieve the same end
result without any references to the control as parameters in the query or as
the ControlSource property of a text box in the report. You can filter the
report by means of the WhereCondition argument of the OpenReport method
(which you are doing already, so its just a case of extending the expression),
and you can assign a value to the text box on the report by passing it to the
report as the OpenArgs argument of the OpenReport method:

Const conMESSAGE = _
"Both a business group and YMV value must be provided"

Dim strCriteria As String, strArgs as String

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.txtYMV) Then

strArgs = Me.txtYMV

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = """ & strArgs & """"

DoCmd.OpenReport "BusinessGroupReport_rpt", _
View:=acViewPreview, _
WhereCondition:=strCriteria, _
OpenArgs:=strArgs

DoCmd.RunCommand acCmdPrint
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

This assumes that the field in the report's underlying recordset is named
YearMonth and is of text data type. If it’s a number data type omit the
delimiting quotes:

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = " & strArgs

In the report's Open event procedure assign the value passed to the report as
its OpenArgs property to an unbound text box in the report:

Me.txtYMV = Me.OpenArgs

As with this approach you are passing values to the report rather than
referencing controls on the form you can if you wish automatically close the
form after the button is clicked by adding the following to its code:

DoCmd.Close acForm, Me.Name

Another possible enhancement would be to include two combo boxes cbxYear and
cbxMonth, on the form for the YMV value rather than a single text box, one
for the year and one for the month (rather like when entering the expiry date
of a credit card in an online form), the former with values over a suitable
range of years, the latter with values 01 to 12. You'd then amend the code:

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.cbxYear ) And _
Not IsNull(Me.cbxMonth ) Then

strArgs = Me.cbxYear & Me.cbxMonth

You could if you wish by default set the combo boxes to the current
year/month in the form's Open event procedure with:

Me.cbxYear = Format(VBA.Date,"yyyy")
Me.cbxMonth = Format(VBA.Date,"mm")

Ken Sheridan
Stafford, England

mlieberstein wrote:
Thank You very Much. I appreciate your advice.

After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to
put the textbox data (YearMonthValue) into my report. I created a text box
and tried the following:

=[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data)
=Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data)

Any ideas to get this to work?

The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be

[quoted text clipped - 28 lines]
Button
to use both the text box and the combo box data as filters to OpenReport.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1


  #6  
Old July 8th, 2009, 01:56 PM posted to microsoft.public.access
mlieberstein
external usenet poster
 
Posts: 11
Default Better way to utilize my form

Ken, the code worked great. But there was one problem.

After I entered the 200907 in the text box txtYMV, and chose a Business
Group in the cbxBusinessGroup, I hit the OpenReport Button. But then I was
prompted immediately with a box YearMonth. I don't want the user to enter
the YMV twice.

"KenSheridan via AccessMonster.com" wrote:

If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not
formula, that's a spreadsheet term) in the text box on the report it sounds
rather like its been entered in the ControlSource property as a string
expression rather than a reference to the control, but that seems unlikely
and you do not include any delimiting quotes characters around the expression
as posted to suggest this is the case. However, you can achieve the same end
result without any references to the control as parameters in the query or as
the ControlSource property of a text box in the report. You can filter the
report by means of the WhereCondition argument of the OpenReport method
(which you are doing already, so its just a case of extending the expression),
and you can assign a value to the text box on the report by passing it to the
report as the OpenArgs argument of the OpenReport method:

Const conMESSAGE = _
"Both a business group and YMV value must be provided"

Dim strCriteria As String, strArgs as String

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.txtYMV) Then

strArgs = Me.txtYMV

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = """ & strArgs & """"

DoCmd.OpenReport "BusinessGroupReport_rpt", _
View:=acViewPreview, _
WhereCondition:=strCriteria, _
OpenArgs:=strArgs

DoCmd.RunCommand acCmdPrint
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

This assumes that the field in the report's underlying recordset is named
YearMonth and is of text data type. If it’s a number data type omit the
delimiting quotes:

strCriteria = _
"[Business Group I] = """ & Me.cbxBusinessGroup & _
""" And [YearMonth] = " & strArgs

In the report's Open event procedure assign the value passed to the report as
its OpenArgs property to an unbound text box in the report:

Me.txtYMV = Me.OpenArgs

As with this approach you are passing values to the report rather than
referencing controls on the form you can if you wish automatically close the
form after the button is clicked by adding the following to its code:

DoCmd.Close acForm, Me.Name

Another possible enhancement would be to include two combo boxes cbxYear and
cbxMonth, on the form for the YMV value rather than a single text box, one
for the year and one for the month (rather like when entering the expiry date
of a credit card in an online form), the former with values over a suitable
range of years, the latter with values 01 to 12. You'd then amend the code:

If Not IsNull(Me.cbxBusinessGroup) And _
Not IsNull(Me.cbxYear ) And _
Not IsNull(Me.cbxMonth ) Then

strArgs = Me.cbxYear & Me.cbxMonth

You could if you wish by default set the combo boxes to the current
year/month in the form's Open event procedure with:

Me.cbxYear = Format(VBA.Date,"yyyy")
Me.cbxMonth = Format(VBA.Date,"mm")

Ken Sheridan
Stafford, England

mlieberstein wrote:
Thank You very Much. I appreciate your advice.

After I updated the query, [Forms]![frm_BusinessGroup]![txtYMV] I wanted to
put the textbox data (YearMonthValue) into my report. I created a text box
and tried the following:

=[Forms]![frm_BusinessGroup]![txtYMV] (The formula came through, no data)
=Forms!frm_BusinessGroup!txtYMV (Again, The formula came through, no data)

Any ideas to get this to work?

The only improvement I can think of is to avoid the message box and put the
criteria in a textbox on the form. The query criteria will need to be

[quoted text clipped - 28 lines]
Button
to use both the text box and the combo box data as filters to OpenReport.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200906/1


  #7  
Old July 8th, 2009, 06:34 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Better way to utilize my form

It sounds like you either still have the parameter in the report's underlying
query, in which case remove it as its no longer required; or the column name
is not YearMonth as I'd assumed for my example , but something else. If the
latter change the reference to YearMonth in the code to the real name of the
column.

Ken Sheridan
Stafford, England

mlieberstein wrote:
Ken, the code worked great. But there was one problem.

After I entered the 200907 in the text box txtYMV, and chose a Business
Group in the cbxBusinessGroup, I hit the OpenReport Button. But then I was
prompted immediately with a box YearMonth. I don't want the user to enter
the YMV twice.

If you are getting the expression [Forms]![frm_BusinessGroup]![txtYMV] (not
formula, that's a spreadsheet term) in the text box on the report it sounds

[quoted text clipped - 89 lines]
Button
to use both the text box and the combo box data as filters to OpenReport.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200907/1

  #8  
Old July 8th, 2009, 07:06 PM posted to microsoft.public.access
mlieberstein
external usenet poster
 
Posts: 11
Default Better way to utilize my form

Perfect. Yes, I changed YearMonth to my field: yearmonthvalue, and I removed
the parameter from my query.

I'm good now. thanks for the advice

"mlieberstein" wrote:

I have a user go into a Form which allows the user to select a BusinessGroup
with a combo box. Then the user has a command button on the form which
launches an event procedure.

What happens 1st is:

An expression in a Query that says [Enter a YearMonthValue: yyyymm] (comes
via a msgbox)
Then the following code is executed:

Private Sub cmd_OpenReport_Click()

DoCmd.OpenReport "BusinessGroupReport_rpt", acViewPreview, , "[Business
Group I] = '" & Me.cbxBusinessGroup & "'"
DoCmd.RunCommand acCmdPrint

End Sub

This works fine, but is there a better way?

---I’d like for the user to 1st enter the YearMonthValue in a text box on my
form, then select a BusinessGroup in the ComboBox. Then use a command Button
to use both the text box and the combo box data as filters to OpenReport.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 06:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.