|
 |
access thread: Use of A combo box to select records displyed in a report
Message #1 by "G Worley" <worleygs@h...> on Thu, 13 Jun 2002 23:31:00
|
|
Hi,
Could anyone help me out here?
Is it possible to have a form with a combo box in it, to show specific
records in a report based the selection in that combo box.
The combo box is getting data from a Table of Suppliers [SupplierName]
After selecting this I have a button to run code but it doesn't work
correctly, it opens the report ok but not with the one supplier.
Regards
GW
Message #2 by "Wesley Kendrick" <wez.k@n...> on Fri, 14 Jun 2002 02:06:51 +0100
|
|
Hi, make sure that your form is bound to the table from which you get your
data. if you create the form using the wizard you will be asked to specify
this.
Then use the control wizard to create your combo box. You will be asked to
specify something like 'select a record on my form based on the value I
select in my combo box'. Check this box, and when you have finished, your
form will jump to the appropriate record when you select a value in the
combo.
regards, Wesley Kendrick
----- Original Message -----
From: "G Worley" <worleygs@h...>
To: "Access" <access@p...>
Sent: Thursday, June 13, 2002 11:31 PM
Subject: [access] Use of A combo box to select records displyed in a report
> Hi,
>
> Could anyone help me out here?
>
> Is it possible to have a form with a combo box in it, to show specific
> records in a report based the selection in that combo box.
>
> The combo box is getting data from a Table of Suppliers [SupplierName]
>
> After selecting this I have a button to run code but it doesn't work
> correctly, it opens the report ok but not with the one supplier.
>
> Regards
>
> GW
>
Message #3 by "Amy Wyatt" <amyw@c...> on Fri, 14 Jun 2002 13:19:06
|
|
Not knowing what your code is makes it difficult to determine where it is
breaking down. . If you include the code for the cmdButton we would be
able to tell you where it is breaking down.
Aside from that, the way I would probably do what you are describing is to
use the form's filter property and apply a filter based on the combo box's
selection.
Hope this helps,
Amy
> Hi,
> Could anyone help me out here?
> Is it possible to have a form with a combo box in it, to show specific
r> ecords in a report based the selection in that combo box.
> The combo box is getting data from a Table of Suppliers [SupplierName]
> After selecting this I have a button to run code but it doesn't work
c> orrectly, it opens the report ok but not with the one supplier.
> Regards
> GW
Message #4 by "Gregory Serrano" <SerranoG@m...> on Fri, 14 Jun 2002 16:00:55
|
|
<< the way I would probably do what you are describing is to use the
form's filter property and apply a filter based on the combo box's
selection. >>
Yes, that's basically what I do, too. I have a form with
Me.FilterOn = True
Me.Filter = ""
on its "On Open" event. Then I pick a value on the combobox. In the
combobox's "After Update" event, I change the form's filter to match the
choice on the combobox. Then on the report's "On Open" event, I have
Me.FilterOn = True
Me.Filter = Forms.MyForm.Form.Filter
This makes sure the report matches the form. Make sure to have a clear
filter command button on your form to make Me.Filter = "" again. If you
use the right-click menu to clear the filter on the form, it doesn't
change the form's Me.Filter property for some reason. As a result, the
report, in turn, will keep filtering!
Greg
Message #5 by "Leo Scott" <leoscott@c...> on Fri, 14 Jun 2002 09:49:11 -0700
|
|
It sounds like he just needs to set the forms Filter property to the
combobox value and set the filter on in the combobox afterupdate event.
|-----Original Message-----
|From: Amy Wyatt [mailto:amyw@c...]
|Sent: Friday, June 14, 2002 1:19 PM
|To: Access
|Subject: [access] Re: Use of A combo box to select records displyed in a
|report
|
|
|Not knowing what your code is makes it difficult to determine where it is
|breaking down. . If you include the code for the cmdButton we would be
|able to tell you where it is breaking down.
|
|Aside from that, the way I would probably do what you are describing is to
|use the form's filter property and apply a filter based on the combo box's
|selection.
|
|Hope this helps,
|
|Amy
|
|
|> Hi,
|
|> Could anyone help me out here?
|
|> Is it possible to have a form with a combo box in it, to show specific
|r> ecords in a report based the selection in that combo box.
|
|> The combo box is getting data from a Table of Suppliers [SupplierName]
|
|> After selecting this I have a button to run code but it doesn't work
|c> orrectly, it opens the report ok but not with the one supplier.
|
|> Regards
|
|> GW
|
Message #6 by "Graham Worley" <worleygs@h...> on Mon, 17 Jun 2002 07:13:25 +0000
|
|
Hi,
Thanks for the guidance. What I want to do is use a form to select an
account to be sent to a report. To select this one account I am trying to
use a combo box. I haven't got it to work yet to isolate the account ref,
only to open report in print preview. Here's the code I have so far, any
help would be appreciated.
Graham.
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
'Dim strDocName As String
Dim strWhere As String
strWhere = Me![cbo_Supp_Acc] 'Combo Box Selection
DoCmd.OpenReport "F_Report_Supplier_Bal_Each", acViewPreview, strWhere
'Open Report with Combo Box Result?
Exit_cmdReport_Click:
Exit Sub
Err_cmdReport_Click:
MsgBox Err.Description
Resume Exit_cmdReport_Click
End Sub
>From: "Wesley Kendrick" <wez.k@n...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Use of A combo box to select records displyed in a
>report
>Date: Fri, 14 Jun 2002 02:06:51 +0100
>
>Hi, make sure that your form is bound to the table from which you get your
>data. if you create the form using the wizard you will be asked to specify
>this.
>Then use the control wizard to create your combo box. You will be asked to
>specify something like 'select a record on my form based on the value I
>select in my combo box'. Check this box, and when you have finished, your
>form will jump to the appropriate record when you select a value in the
>combo.
>regards, Wesley Kendrick
>
>----- Original Message -----
>From: "G Worley" <worleygs@h...>
>To: "Access" <access@p...>
>Sent: Thursday, June 13, 2002 11:31 PM
>Subject: [access] Use of A combo box to select records displyed in a report
>
>
> > Hi,
> >
> > Could anyone help me out here?
> >
> > Is it possible to have a form with a combo box in it, to show specific
> > records in a report based the selection in that combo box.
> >
> > The combo box is getting data from a Table of Suppliers [SupplierName]
> >
> > After selecting this I have a button to run code but it doesn't work
> > correctly, it opens the report ok but not with the one supplier.
> >
> > Regards
> >
> > GW
> >
>
>
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.
Message #7 by "G Worley" <worleygs@h...> on Mon, 17 Jun 2002 15:19:48
|
|
Update - Thanks everyone for the help so far.
Code so far
Private Sub Form_Open(Cancel As Integer) ' Sets Filter On Open
Me.FilterOn = True
Me.Filter = ""
End Sub
Private Sub cbo_Supp_Acc_AfterUpdate() 'Sets Filter after Update
Me.Filter = [cbo_Supp_Acc] 'cbo_Supp_Acc
MsgBox ("You have Chosen " & Me.Filter)
End Sub
Private Sub CmdRefresh_Click() 'Should remove filter
Me.Filter = ""
MsgBox ("Refreshed Filter" & Me.Filter)
End Sub
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click
Dim stDocName As String
Me.Filter =
[cbo_Supp_Acc] 'Combo Box
Filter
MsgBox ("You are about to Filter the Records Supplier " & Me.Filter)
stDocName
= "F_Report_Supplier_Bal_Each" 'Sets Form
Required to Open
DoCmd.OpenReport stDocName,
acPreview 'Opens Report in Preview Mode
Exit_Command44_Click:
Exit Sub
Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click
End Sub
However it is still failing to send the filter correctly to my report so
get a debug screen.
Here's the code for the Report Open :-
Private Sub Report_Open(Cancel As Integer)
Me.FilterOn = True
Me.Filter = Forms.F_Retail_Acc_Ball.Form.Filter
Message #8 by "Gregory Serrano" <SerranoG@m...> on Mon, 17 Jun 2002 17:55:06
|
|
<< Private Sub cbo_Supp_Acc_AfterUpdate() 'Sets Filter after Update
Me.Filter = [cbo_Supp_Acc] 'cbo_Supp_Acc
MsgBox ("You have Chosen " & Me.Filter)
End Sub >>
The problem is the way you're definining the filter in code. The statement
Me.Filter = Me.cbo_Supp_Acc
will not work because the filter statement has to be of the proper
syntax. Suppose the record source of your form is tblRecords and the
control source of cbo_Supp_Acc is a field called str_Supp_Acc. The filter
statement should look something like this:
Me.Filter = "[tblRecords].[str_Supp_Acc] = "' & Me.cbo_Supp_Acc & "'"
Greg
Message #9 by "Amy Wyatt" <amyw@c...> on Mon, 17 Jun 2002 18:04:32
|
|
In both your old code and your new code the problem is you are not
idenifying the field to filter by. The Me.Filter property takes a string
that is a WHERE statement without the WHERE so to filter by Account number
(field name AcctNum) equal to 123456 you would have to use
Me.Filter="AcctNum=123456"
if 123456 is stored in a variable lngAcctNum then it would look like this
Me.Filter="AcctNum=" & lngAcctNum
if 123456 is a string stored in a variable strAcctNum it would look like
Me.Filter="AcctNum='" & strAcctNum & "'"
Hope this helps,
Amy
> Update - Thanks everyone for the help so far.
> Code so far
> Private Sub Form_Open(Cancel As Integer) ' Sets Filter On Open
> Me.FilterOn = True
> Me.Filter = ""
> End Sub
> Private Sub cbo_Supp_Acc_AfterUpdate() 'Sets Filter after Update
>
> Me.Filter = [cbo_Supp_Acc] 'cbo_Supp_Acc
> MsgBox ("You have Chosen " & Me.Filter)
>
E> nd Sub
> Private Sub CmdRefresh_Click() 'Should remove filter
>
> Me.Filter = ""
>
> MsgBox ("Refreshed Filter" & Me.Filter)
>
E> nd Sub
> Private Sub Command44_Click()
O> n Error GoTo Err_Command44_Click
> Dim stDocName As String
>
> Me.Filter =
[> cbo_Supp_Acc] 'Combo Box
F> ilter
> MsgBox ("You are about to Filter the Records Supplier " & Me.Filter)
> stDocName
=> "F_Report_Supplier_Bal_Each" 'Sets
Form
R> equired to Open
> DoCmd.OpenReport stDocName,
a> cPreview 'Opens Report in Preview Mode
> Exit_Command44_Click:
> Exit Sub
> Err_Command44_Click:
> MsgBox Err.Description
> Resume Exit_Command44_Click
>
E> nd Sub
> However it is still failing to send the filter correctly to my report so
g> et a debug screen.
> Here's the code for the Report Open :-
> Private Sub Report_Open(Cancel As Integer)
> Me.FilterOn = True
> Me.Filter = Forms.F_Retail_Acc_Ball.Form.Filter
Message #10 by "Wesley Kendrick" <wez.k@n...> on Mon, 17 Jun 2002 22:38:12 +0100
|
|
Hi Graham, sorry, I misunderstood your original message. What you should do
is to create a query containing the fields you need in your report. Insert
the full path to the combo box ([Forms]![YourFormName]![YourComboName]) as
the criteria for the account ref. field , then base your report on this
query. When you open the report it will contain the record identified by
what you selected in the combo.
regards, Wesley Kendrick
----- Original Message -----
From: "Graham Worley" <worleygs@h...>
To: "Access" <access@p...>
Sent: Monday, June 17, 2002 8:13 AM
Subject: [access] Re: Use of A combo box to select records displyed in a
report
> Hi,
>
> Thanks for the guidance. What I want to do is use a form to select an
> account to be sent to a report. To select this one account I am trying to
> use a combo box. I haven't got it to work yet to isolate the account ref,
> only to open report in print preview. Here's the code I have so far, any
> help would be appreciated.
>
> Graham.
>
> Private Sub cmdReport_Click()
> On Error GoTo Err_cmdReport_Click
>
> 'Dim strDocName As String
> Dim strWhere As String
>
> strWhere = Me![cbo_Supp_Acc] 'Combo Box Selection
>
> DoCmd.OpenReport "F_Report_Supplier_Bal_Each", acViewPreview, strWhere
> 'Open Report with Combo Box Result?
>
> Exit_cmdReport_Click:
> Exit Sub
>
> Err_cmdReport_Click:
> MsgBox Err.Description
> Resume Exit_cmdReport_Click
>
> End Sub
>
>
> >From: "Wesley Kendrick" <wez.k@n...>
> >Reply-To: "Access" <access@p...>
> >To: "Access" <access@p...>
> >Subject: [access] Re: Use of A combo box to select records displyed in a
> >report
> >Date: Fri, 14 Jun 2002 02:06:51 +0100
> >
> >Hi, make sure that your form is bound to the table from which you get
your
> >data. if you create the form using the wizard you will be asked to
specify
> >this.
> >Then use the control wizard to create your combo box. You will be asked
to
> >specify something like 'select a record on my form based on the value I
> >select in my combo box'. Check this box, and when you have finished, your
> >form will jump to the appropriate record when you select a value in the
> >combo.
> >regards, Wesley Kendrick
> >
> >----- Original Message -----
> >From: "G Worley" <worleygs@h...>
> >To: "Access" <access@p...>
> >Sent: Thursday, June 13, 2002 11:31 PM
> >Subject: [access] Use of A combo box to select records displyed in a
report
> >
> >
> > > Hi,
> > >
> > > Could anyone help me out here?
> > >
> > > Is it possible to have a form with a combo box in it, to show specific
> > > records in a report based the selection in that combo box.
> > >
> > > The combo box is getting data from a Table of Suppliers [SupplierName]
> > >
> > > After selecting this I have a button to run code but it doesn't work
> > > correctly, it opens the report ok but not with the one supplier.
> > >
> > > Regards
> > >
> > > GW
> > >
> >
> >
>
>
>
>
> _________________________________________________________________
> Get your FREE download of MSN Explorer at
http://explorer.msn.com/intl.asp.
>
>
>
Message #11 by "John Ruff" <papparuff@c...> on Mon, 17 Jun 2002 15:33:13 -0700
|
|
Here is code on how to generate a report based on what is selected in a
combobox.
Private Sub cmdReport_Click()
On Error GoTo Err_cmdReport_Click
' Report Name
Dim stDocName As String
stDocName = "rpt_Products"
' Create a report in preview mode for
' a specific product
DoCmd.OpenReport stDocName, acPreview, , "ProductID=" & cboSearch
End Sub
This sample is based on the Product table in the Northwind database.
The stDocName is the name of the report and it includes a field from the
Products table called ProductID
The combobox is called cboSearch and the rowsource of cboSearch has the
ProductID and ProductDescription from the Products table of the
Northwind database.
John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity
xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498
-----Original Message-----
From: G Worley [mailto:worleygs@h...]
Sent: Thursday, June 13, 2002 11:31 PM
To: Access
Subject: [access] Use of A combo box to select records displyed in a
report
Hi,
Could anyone help me out here?
Is it possible to have a form with a combo box in it, to show specific
records in a report based the selection in that combo box.
The combo box is getting data from a Table of Suppliers [SupplierName]
After selecting this I have a button to run code but it doesn't work
correctly, it opens the report ok but not with the one supplier.
Regards
GW
|
 |