Re: recordset.clone

Hello John,

i have this code..for my Combobox (Combo58)
SELECT DISTINCT location FROM [Data Entry2] UNION SELECT ' All Locations'
FROM [Data Entry2] ORDER BY location;

i have this Afterupdate
Dim SQL As String

SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name]," & _
vbCrLf & "[Data Entry2].[Drawing Ref] , [Data Entry2].[*** No], [Data
Entry2].Rev, [Data Entry2].Description," & _
vbCrLf & "[Data Entry2].[Drwg Typ], [Data Entry2].[Drawing Title], [Data
Entry2].[Rd Line], [Data Entry2].[TR Rd Line]," & _
vbCrLf & "[Data Entry2].[TR Rd Line Date], [Data Entry2].AsBuilt, [Data
Entry2].[Forecast (SubCon)]," & _
vbCrLf & "[Data Entry2].[Reviewed Red Line], [Data Entry2].[Reviewed (%)],"
& _
vbCrLf & "[Data Entry2].[Forecast (SLSA)], [Data Entry2].[Reviewed Red
Line(PMT)], [Data Entry2].[TR Rd Line(PMT)]," & _
vbCrLf & "[Data Entry2].[TR Rd Line(PMT) Date], [Data Entry2].[Reviewed (%)
PMT], [Data Entry2].[Forecast PMT]," & _
vbCrLf & "[Data Entry2].SubContractor, [Data Entry2].CWP, [Data Entry2].[CWP
Title], [Data Entry2].Index," & _
vbCrLf & "[Data Entry2].DISCIPLINE, [Data Entry2].Prefix, [Data
Entry2].Size," & _
vbCrLf & "[Data Entry2].size_value, [Data Entry2].[Qty of Sheets], [Data
Entry2].[Eqv_A_Size]," & _
vbCrLf & "[Data Entry2].[Earned ASize], [Data Entry2].[Physical Prog],
[Data Entry2].TR, [Data Entry2].Date," & _
vbCrLf & "[Data Entry2].SubCon, [Data Entry2].Location, [Data
Entry2].[Drawing Control No] FROM [Data Entry2];"

If Me!Combo58 = " All Locations" Then
Me.FilterOn = False
Else
Me.Filter = "location = '" & Me![Combo58] & " '"
Me.FilterOn = True
End If

my problem is when i select 'All Locations' it doesnt show me all the
records that i have in my subform....what codes do i miss here?

thanks for any help


"John Smith" wrote:

If you do not have a code in your table the union query would be:

SELECT DISTINCT location FROM [Data Entry]
UNION
SELECT ' All Locations' FROM [Data Entry]
ORDER BY location

some_columns and some_tables were intended as place holders for the names in
your database, which I do not know. Look at the query in the record source of
your form and insert the column and table names from there.

Alternatively if the query is very complex you could do:

If Me!Combo58 = " All Locations" THEN
Me.FilterOn = False
Else
Me.Filter = "location = '" & Me![Combo58] & "'"
Me.FilterOn = True
End If

If you do not want the records filtered when you open the form then remove all
of the FormOpen code.

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

Denver wrote:
right now i have this RowSource for my Combobox
SELECT [Data Entry].Location FROM [Data Entry] GROUP BY [Location];

so i change like this but it dosen't work....
SELECT ' ', ' All Locations' FROM [Data Entry].locations ORDER BY
[location];
is there correct? please guide with this i am not a programmer.

for my afterupdate Event i have this code.........this work will but i need
to see all my records before a i made selection from my combo box....the
moment i open my form with the subform it applieas already the filters...
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Location] = '" & Me![Combo58] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

i don't how to change the code from the example you give me
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

because i don't know what is some_columns FROM some_tables is all about?

please can you guide thru my codes i pasted here

thanks anyway, John Smith

denver
"John Smith" wrote:
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.

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.

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?

.