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

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
>

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

<< 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

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
|

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.

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

<< 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

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

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.
>
>
>

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