Re: recordset.clone

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Yes, that is different and would require code.

I assume that your Combo58 has a Row Source something like:

SELECT location_code, location_name FROM locations ORDER BY location_name

If you change that to a UNION query you can add an option for 'All':

SELECT location_code, location_name FROM locations
UNION
SELECT ' ', ' All Locations' FROM locations
ORDER BY location_name

Note the space in from of the description to put it at the top of the list.

Then in the After Update event of the Combo you can put:

Dim SQL as String
SQL = "SELECT some_columns FROM some_tables"
If Me.Combo58 <> " " THEN
SQL = SQL & " WHERE Location = '" & Me![Combo58] & "'"
End IF
Me!SubFormControlName.Form.RecordSource = SQL

Substituting your own column, table and control names. Note that the initial sql assigned to SQL will be the same sql that the subform is currently based on. If this includes a WHERE clause then change the WHERE in the code to AND.

HTH
John
##################################
Don't Print - Save trees

Denver wrote:
I have try this suggestions but no luck. it doesnt sort at all after i select from my combo. the code that i have i used it to sort all records for a certain Location..it works will...
what i want is a code that displays all the records i have in my subform even i have not select a location in my combo? or a code that clears my selection and displays my all my records in the sub-form..

sorry for not elaborating..but thanks this was helpful to me.

"John Smith" wrote:

To do that you need no code at all. Just set the Link Master field property of the sub-form control to Form!Combo58 and the Link Child field to Location.

Note that Location must be part of the Record Source of the sub-form and that if you are using controls rather than columns you cannot use the wizard, you have to type the values in yourself.

HTH
John
##################################
Don't Print - Save trees

Denver wrote:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Location] = '" & Me![Combo58] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
I have this code.
what code do i need to add so that i can see all the records in my subform after i select from my combo box?
.

Relevant Pages

  • Re: Combo Box Help
    ... This is the SQL View from the Row Source of cboAgencyName: ... I set the criteria under the StateName field in this query. ... post the SQL. ... that I can only select the first record in the second combo box. ...
    (microsoft.public.access.formscoding)
  • RE: passing data to new form using open args
    ... If the SQL you are trying to send to the form is the data you want for your ... As to the SQL you have for your combo boxes. ... Row Source type to Table/Query and put the SQL in the Row Source property. ...
    (microsoft.public.access.formscoding)
  • RE: Will this stump even the best of you?
    ... I have a simple form with 2 combos and a listbox. ... the 1st in its Row Source and the listbox references the 2nd query in its Row ... saved query objects. ... When I copied the combo boxes, they contained SQL statements as their ...
    (microsoft.public.access.forms)
  • Re: Prob with mainform cbo & subform
    ... Yes, sorry I did meant Row Source, not Record Source! ... I tried creating the query from scratch, and this is the SQL I got: ... I'm using the same code to populate the Row Source of cbo_REF in the ON ...
    (microsoft.public.access.formscoding)
  • Re: Combo Box Help
    ... Is the row source for cboAgancyName literally tblOutOfStateAgencies, or is it a query based on the table? ... The syntax with the multiple quote marks in a row assumes you are using the StateName text field in the SQL rather than a numeric key field. ... You can use query design view to put together the query, then click View>> SQL to see the SQL, if you would rather not type it all out. ... > that I can only select the first record in the second combo box. ...
    (microsoft.public.access.formscoding)