I'm with Duane here. I would say that more than half the forms I create
have code that changes the RowSource of one or more combo boxes at run-time,
and I never Requery after changing the RowSource.
--
Graham
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Wednesday, 31 August 2011 09:55
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Re: Setting rowsource to a query in code
I just created a form and did a little test in Access 2007. I added a combo
box with a row source of:
SELECT [EmployeeID], [LastName] FROM [Employees] ORDER BY [LastName];
I added a command button with the following code:
Private Sub cmdChangeRowSource_Click()
Dim strSQL As String
strSQL = "SELECT [EmployeeID], [LastName] " & _
"FROM [Employees] WHERE lastName >'g' ORDER BY [LastName];"
Me.cboEmployeeID.RowSource = strSQL
End Sub
The combo box orginally displayed 10 records in the drop down. After I
clicked the command button, the combo box was limited to 5. I didn't have to
perform any requery of the combo box.
There are a number of properties of combo boxes that depend on the order and
number of columns. I don't like leaving this to chance with SELECT *.
The solution of changing the SQL of a saved query assumes the saved query is
used only for the one combo box and each user has their own copy of the
front end. I typically use this more often when my Row Sources are
pass-through queries.
Duane Hookom
MS Access MVP
To: MS_Access_Professionals@yahoogroups.com
From: no_reply@yahoogroups.com
Date: Tue, 30 Aug 2011 21:10:07 +0000
Subject: [MS_AccessPros] Re: Setting rowsource to a query in code
Duane,
It didn't work for me until I requeried.
I'm curious about why would you not use SELECT * in a Row Source. In
thinking about it originally I decided that it'd be best in case I end up
adding something to the table/query underlying the form. That way I'd not
have to go change much code.
I want to use one query as the rowsource for different versions of the
Listings form. And I don't want it to appear filtered. Otherwise the users
might switch it and inadvertently change something that shouldn't be
changed. Does your method of using a saved query work for this kind of
situation?
Thanks so much :-)
Connie
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom
<duanehookom@...> wrote:
>
>
> Bill & Connie,
>
> I thought setting/changing the Row Source property would not require a
requery.
>
> I would generally not use SELECT * in a Row Source (or most everywhere
else). I would specify the field names if possible.
>
> I have used another method that uses a saved query as the Row Source. You
can use a little DAO code to set the SQL property of the saved query and
then open the listing form containing the search combo box.
>
> Duane Hookom
> MS Access MVP
>
>
>
>
> To: MS_Access_Professionals@yahoogroups.com
> From: wrmosca@...
> Date: Tue, 30 Aug 2011 15:52:28 +0000
> Subject: [MS_AccessPros] Re: Setting rowsource to a query in code
>
>
>
>
>
>
> Connie
>
> That's the right way to do it. But be sure to requery the combo. Changing
the Rowsource is not enough to load the data.
>
> 'Set RowSource for cboSearch box
> Forms!Listings.cboSearch.RowSource = strSql2
> Forms!Listings.cboSearch.Requery
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@>
wrote:
> >
> > When a button is clicked on another form I am opening the Listings form
and setting the Rowsource of cbo.search (an unbound box). The code I am
using is:
> >
> > 'Make string for Rowsource for cboSearch box
> > strSql2 = "SELECT DISTINCT qListingContacts_SoldSellers.* " & _
> > "FROM qListingContacts_SoldSellers;"
> > 'Set RowSource for cboSearch box
> > Forms!Listings.cboSearch.RowSource = strSql2
> >
> > Is there a better way to do this? I am just using
qListingContacts_SoldSellers as the base, but the only way I knew to put it
into a proper string was the above.
> >
> > Thanks!
> > Connie
> >
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
[Non-text portions of this message have been removed]
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar