ACCESS FEED: RE: [MS_AccessPros] Re: Setting rowsource to a query in code

Selasa, 30 Agustus 2011

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