ACCESS FEED: RE: [MS_AccessPros] How am I to tell the report to use the formï¿½s record source?

Minggu, 02 Juni 2013

RE: [MS_AccessPros] How am I to tell the report to use the formï¿½s record source?

Looping through records has its place as in manipulating data and writing to a
text file. I'm sure I've looped through recordsets many times in the past, but
as my skills have grown, I've found less reason to do so. It's a slow and
usually unnecessary process.

One case that comes to mind was when I received records into a table that had
headers, parent records and child records. I had to loop through the table and
parse out the single rows into multiple records in multiple tables. A complex
task, but necessary due to the structure of the in-coming data.

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5
Sent: Saturday, June 01, 2013 5:51 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] How am I to tell the report to use the formï¿½s
record source?

Thank you very much Bill.Now it loads and filters the records correctly.

Another question that I have, is, when should the Looping method (the procedure
which I posted) be used.

Thanks Bill

Phucon

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@...>
wrote:
>
> Phucon
>
> PMJI...You can't load the recordset that way. You are trying to do it one
record at a time. Just assign each control's ControlSource to the appropriate
field. Don't loop through the records.
>
> With Me
> .pkeyCustomerID.ControlSource = "pkeyCustomerID"
> .strCompanyName.ControlSource = "strCompanyName"
> .strContactName.ControlSource = "strContactName"
> .strContactTitle.ControlSource = "strContactTitle"
> .strCity.ControlSource = "strCity"
> .strRegion.ControlSource = "strRegion"
> .strPostalCode.ControlSource = "strPostalCode"
> .strCountry.ControlSource = "strCountry"
> End With
>
> Me.RecordSource = strSQL
> Me.Requery
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "saigonf7q5" <saigonf7q5@>
wrote:
> >
> >
> >
> >
> >
> >
> > Thanks Duane. It works perfectly.
> >
> > I tried another version of displaying records on the (Continuous) form.
However I can't figure out why it keeps displaying the same record while it
should move to the next. I suspected the MoveNext statement wasn't placed
correctly. was it? Phucon.
> >
> > RANCH Rancho grande Sergio Gutiï¿½rrez Sales Representative Buenos Aires
1010 Argentina
> > RANCH Rancho grande Sergio Gutiï¿½rrez Sales Representative Buenos Aires
1010 Argentina
> > RANCH Rancho grande Sergio Gutiï¿½rrez Sales Representative Buenos Aires
1010 Argentina
> >
> >
> > Private Sub cboCountry_AfterUpdate()
> >
> > Dim db As DAO.Database
> > Dim rs As DAO.Recordset
> > Dim strSQL As String
> >
> > strSQL = "SELECT distinct pkeyCustomerID, strCompanyName, strContactName,
strContactTitle, strCity, strRegion, " & _
> > "strPostalCode, strCountry " & _
> > "FROM qryCustomers WHERE strCountry= " & Chr$(34) & Me.cboCountry & Chr$(34)
& ""
> >
> > Set db = CurrentDb
> > Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
> >
> > Me.RecordSource = strSQL
> >
> > If Not rs.EOF Then
> > Do
> > With Me
> > .pkeyCustomerID = rs!pkeyCustomerID
> > .strCompanyName = rs!strCompanyName
> > .strContactName = rs!strContactName
> > .strContactTitle = rs!strContactTitle
> > .strCity = rs!strCity
> > .strRegion = rs!strRegion
> > .strPostalCode = rs!strPostalCode
> > .strCountry = rs!strCountry
> > End With
> > rs.MoveNext
> > Loop Until rs.EOF
> > End If
> >
> > rs.Close
> > Set rs = Nothing
> > Set db = Nothing
> >
> > End Sub
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , Duane Hookom <duanehookom@>
wrote:
> > >
> > > Try,
> > >
> > > Private Sub cmdOpenRpt_Click()
> > > ï¿½
> > > ï¿½ ï¿½Dim strWhere as String
> > > ï¿½ ï¿½strWhere = Me.Filter
> > > ï¿½ ï¿½DoCmd.OpenReport "RptCustomers", acViewPreview, ,strWhere ,
acWindowNormal
> > >
> > > End Sub
> > >
> > > Duane Hookom MVP
> > > MS Access
> > >
> > > ----------------------------------------
> > > > From: saigonf7q5@
> > > >
> > > > Hello Duane
> > > >
> > > > Here's the 2 procedures that in the form.
> > > >
> > > > combo box rowsource:
> > > > SELECT DISTINCT qryCustomers.strCountry FROM qryCustomers;
> > > >
> > > > criteria which user enters:
> > > > Private Sub cboCountry_AfterUpdate()
> > > >
> > > > Me.Filter = "strCountry = " & Chr$(34) & Me.cboCountry & Chr$(34) & ""
> > > > Me.FilterOn = True
> > > >
> > > > 'Debug.Print Me.Filter
> > > >
> > > >
> > > > End Sub
> > > >
> > > >
> > > > Private Sub cmdOpenRpt_Click()
> > > >
> > > > Dim strCntryName As String
> > > > strCntryName = Me.cboCountry
> > > >
> > > > DoCmd.OpenReport "RptCustomers", acViewPreview, , , acWindowNormal,
strCntryName
> > > >
> > > > End Sub
> > > >
> > > >
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , Duane Hookom <duanehookom@>
wrote:
> > > >>
> > > >> Phucon,
> > > >>
> > > >> Tell us how you are using the combo box to filter the form records. I
expect you could use a similar expression as a WHERE CONDITION in the
DoCmd.OpenReport method.
> > > >>
> > > >> Duane Hookom MVP
> > > >> MS Access
> > > >>
> > > >> ----------------------------------------
> > > >>> From: saigonf7q5@
> > > >>>
> > > >>> My form has a combo box for filtering records, and a Cmdbutton to open
a report.
> > > >>>
> > > >>> I have been trying to pass the form's OpenArgs to the Report_Open
procedue, so the report's Recordsource can base on the criteria that the user
selected from the form. How am I to tell the report to use the form's record
source?
> > > >>>
> > > >>> Below's what I have been trying to do.
> > > >>>
> > > >>> Private Sub cmdOpenRpt_Click()
> > > >>>
> > > >>> Dim strCntryName As String
> > > >>>
> > > >>> strCntryName = Me.cboCountry
> > > >>>
> > > >>> DoCmd.OpenReport "RptCustomers", acViewPreview, , , acWindowNormal,
strCntryName
> > > >>>
> > > >>> End Sub
> > > >>>
> > > >>> Private Sub Report_Open(Cancel As Integer)
> > > >>> Me.OpenArgs
> > > >>> End Sub
> > > >>>
> > > >>> Thanks
> > > >>> Phucon
> > > >>
> > > >
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> >
>

[Non-text portions of this message have been removed]