recordset.clone

[Denver]

Hi,

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?

thanks

denver

 

[John Smith]

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]

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.

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

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?

 

[John Smith]

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

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.

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

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?

 

[Denver]

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

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

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.

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?

 

[John Smith]

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

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

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.

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.

:

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?

 

[Denver]

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].[Sheet 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

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

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

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.

:

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?

 

[John Smith]

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

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].[Sheet 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

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.

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
:
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.

:

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?

 

[Denver]

John,

I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement

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

thanks again John..

denver

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

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].[Sheet 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

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.

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
:
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.

:

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?

 

[Denver]

John,

I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement
how can i work from this error or modify my codes?hope you are patience and kind in helping me with this..

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

the second thing is that i try to use for the moment the vbCrlf so that i
can continue in writng my codes..
so i write this codes...but i have this
Run Time Error '3142':Characters found after end of SQL statement.
what is my mistakes here?

If Me!Combo58 <> " All Locations" Then

SQL = SQL & " WHERE location = '" & Me![Combo58] & "'"
End If

Me.RecordSource = SQL

thanks again John..thank you very much John....

denver
***********************************************************

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

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].[Sheet 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


:

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.

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
:
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.

:

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?

 

[John Smith]

You took out the line continuation characters as well as the vbcrlf. Your
code should look like this:

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

The cause your second error was the colon at the end of the SQL.

Incidentally, if you keep your names to strict alpha-numeric, no other
characters or spaces you will not need to bracket them every time that you
refer to them. You should also refrain from using reserved words as names for
your objects as it can get Access very confused. Potential problems that I
notice in this table are Index, Size and Date.

John,

I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement
how can i work from this error or modify my codes?hope you are patience and kind in helping me with this..

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

the second thing is that i try to use for the moment the vbCrlf so that i
can continue in writng my codes..
so i write this codes...but i have this
Run Time Error '3142':Characters found after end of SQL statement.
what is my mistakes here?

If Me!Combo58 <> " All Locations" Then

SQL = SQL & " WHERE location = '" & Me![Combo58] & "'"
End If

Me.RecordSource = SQL

thanks again John..thank you very much John....

denver
***********************************************************

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

Denver wrote:
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].[Sheet 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


:

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.

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
:
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.

:

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?

 

[Denver]

John,

now i have this code after my SQL = "SELECT......

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

and set the Link child Field:Location
Link master Field:Forms!Combo58

the problem is when i click All Locations from my Combo58 it resulted to blank
SubForm. Please can you guide how i can let this works?
the rest of the location after being selected from Combo58 it does work and
filters.
but when i choose "All Locations", it doesn't work at all, it gives me a
blank subform.

i have read your last reply and you said "Potential problems that I notice
int his table are Index, Size and Date...What is this problem about? can you
help me to fix this?

thanks again John Smith...i really appreciate...

denver

You took out the line continuation characters as well as the vbcrlf. Your
code should look like this:

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

The cause your second error was the colon at the end of the SQL.

Incidentally, if you keep your names to strict alpha-numeric, no other
characters or spaces you will not need to bracket them every time that you
refer to them. You should also refrain from using reserved words as names for
your objects as it can get Access very confused. Potential problems that I
notice in this table are Index, Size and Date.

John,

I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement
how can i work from this error or modify my codes?hope you are patience and kind in helping me with this..

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

the second thing is that i try to use for the moment the vbCrlf so that i
can continue in writng my codes..
so i write this codes...but i have this
Run Time Error '3142':Characters found after end of SQL statement.
what is my mistakes here?

If Me!Combo58 <> " All Locations" Then

SQL = SQL & " WHERE location = '" & Me![Combo58] & "'"
End If

Me.RecordSource = SQL

thanks again John..thank you very much John....

denver
***********************************************************

:

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

Denver wrote:
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].[Sheet 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


:

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.

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
:
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.

:

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?

 

[John Smith]

Sorry for the delay in replying, I've been so busy that I've not had chance to
get to the list.

You only want to have master child links if you are using them to control the
content of the sub form. Since you want an option of all you cannot do it
that way so blank out the link fields.

I must admit that I had forgotten that it was the sub form that you needed to
filter so the code should have read:

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

replacing SubFormControlName with the actual name of your sub-form control
(possibly but not necessarily the same as the name of the form in it). Sorry
about that.

If you use Access reserved words to name your objects (tables, columns,
controls etc) you may well find access getting confused when you refer to
them. For example understanding Date to be the built in function that returns
today's date rather than the column in your table. This can cost you hours
trying to discover why something is not doing what you expect it to. You are
much better off avoiding using them.

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

John,

now i have this code after my SQL = "SELECT......

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

and set the Link child Field:Location
Link master Field:Forms!Combo58

the problem is when i click All Locations from my Combo58 it resulted to blank
SubForm. Please can you guide how i can let this works?
the rest of the location after being selected from Combo58 it does work and
filters.
but when i choose "All Locations", it doesn't work at all, it gives me a
blank subform.

i have read your last reply and you said "Potential problems that I notice
int his table are Index, Size and Date...What is this problem about? can you
help me to fix this?

thanks again John Smith...i really appreciate...

denver

You took out the line continuation characters as well as the vbcrlf. Your
code should look like this:

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

The cause your second error was the colon at the end of the SQL.

Incidentally, if you keep your names to strict alpha-numeric, no other
characters or spaces you will not need to bracket them every time that you
refer to them. You should also refrain from using reserved words as names for
your objects as it can get Access very confused. Potential problems that I
notice in this table are Index, Size and Date.

John,
I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement
how can i work from this error or modify my codes?hope you are patience and kind in helping me with this..
SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name], "
[Data Entry2].[Drawing Ref] , [Data Entry2].[Sheet No], [Data Entry2].Rev,
[Data Entry2].Description, ""
[Data Entry2].[Drwg Typ] , [Data Entry2].[Drawing Title], [Data Entry2].[Rd
Line], [Data Entry2].[TR Rd Line], " "
[Data Entry2].[TR Rd Line Date] , [Data Entry2].AsBuilt, [Data
Entry2].[Forecast (SubCon)], " "
[Data Entry2].[Reviewed Red Line] , [Data Entry2].[Reviewed (%)], " "
[Data Entry2].[Forecast (SLSA)] , [Data Entry2].[Reviewed Red Line(PMT)],
[Data Entry2].[TR Rd Line(PMT)], " "
[Data Entry2].[TR Rd Line(PMT) Date] , [Data Entry2].[Reviewed (%) PMT],
[Data Entry2].[Forecast PMT], " "
[Data Entry2].SubContractor , [Data Entry2].CWP, [Data Entry2].[CWP Title],
[Data Entry2].Index, " "
[Data Entry2].DISCIPLINE , [Data Entry2].Prefix, [Data Entry2].Size, " "
[Data Entry2].size_value , [Data Entry2].[Qty of Sheets], [Data
Entry2].[Eqv_A_Size], " "
[Data Entry2].[Earned ASize] , [Data Entry2].[Physical Prog], [Data
Entry2].TR, [Data Entry2].Date, " "
[Data Entry2].SubCon, [Data Entry2].Location, [Data Entry2].[Drawing Control
No] FROM [Data Entry2]:
the second thing is that i try to use for the moment the vbCrlf so that i
can continue in writng my codes..
so i write this codes...but i have this
Run Time Error '3142':Characters found after end of SQL statement.
what is my mistakes here?

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & "'"
End If

Me.RecordSource = SQL

thanks again John..thank you very much John....

denver
***********************************************************
:

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

Denver wrote:
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].[Sheet 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


:

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.

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
:
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.

:

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?

 

[Denver]

John,

Nothing to worried about that, I understand.
But anyway Thank you very very much.....

it really works now... Thank you i really appreciate.

Sorry for the delay in replying, I've been so busy that I've not had chance to
get to the list.

You only want to have master child links if you are using them to control the
content of the sub form. Since you want an option of all you cannot do it
that way so blank out the link fields.

I must admit that I had forgotten that it was the sub form that you needed to
filter so the code should have read:

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

replacing SubFormControlName with the actual name of your sub-form control
(possibly but not necessarily the same as the name of the form in it). Sorry
about that.

If you use Access reserved words to name your objects (tables, columns,
controls etc) you may well find access getting confused when you refer to
them. For example understanding Date to be the built in function that returns
today's date rather than the column in your table. This can cost you hours
trying to discover why something is not doing what you expect it to. You are
much better off avoiding using them.

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

John,

now i have this code after my SQL = "SELECT......

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

and set the Link child Field:Location
Link master Field:Forms!Combo58

the problem is when i click All Locations from my Combo58 it resulted to blank
SubForm. Please can you guide how i can let this works?
the rest of the location after being selected from Combo58 it does work and
filters.
but when i choose "All Locations", it doesn't work at all, it gives me a
blank subform.

i have read your last reply and you said "Potential problems that I notice
int his table are Index, Size and Date...What is this problem about? can you
help me to fix this?

thanks again John Smith...i really appreciate...

denver

You took out the line continuation characters as well as the vbcrlf. Your
code should look like this:

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

The cause your second error was the colon at the end of the SQL.

Incidentally, if you keep your names to strict alpha-numeric, no other
characters or spaces you will not need to bracket them every time that you
refer to them. You should also refrain from using reserved words as names for
your objects as it can get Access very confused. Potential problems that I
notice in this table are Index, Size and Date.

Denver wrote:
John,
I try doing this but if i am going to remove the vbcrlf's i have this
error....
:Expected End of Statement
how can i work from this error or modify my codes?hope you are patience and kind in helping me with this..
SQL = "SELECT [Data Entry2].[Site ID],[Data Entry2].[Site Name], "
[Data Entry2].[Drawing Ref] , [Data Entry2].[Sheet No], [Data Entry2].Rev,
[Data Entry2].Description, ""
[Data Entry2].[Drwg Typ] , [Data Entry2].[Drawing Title], [Data Entry2].[Rd
Line], [Data Entry2].[TR Rd Line], " "
[Data Entry2].[TR Rd Line Date] , [Data Entry2].AsBuilt, [Data
Entry2].[Forecast (SubCon)], " "
[Data Entry2].[Reviewed Red Line] , [Data Entry2].[Reviewed (%)], " "
[Data Entry2].[Forecast (SLSA)] , [Data Entry2].[Reviewed Red Line(PMT)],
[Data Entry2].[TR Rd Line(PMT)], " "
[Data Entry2].[TR Rd Line(PMT) Date] , [Data Entry2].[Reviewed (%) PMT],
[Data Entry2].[Forecast PMT], " "
[Data Entry2].SubContractor , [Data Entry2].CWP, [Data Entry2].[CWP Title],
[Data Entry2].Index, " "
[Data Entry2].DISCIPLINE , [Data Entry2].Prefix, [Data Entry2].Size, " "
[Data Entry2].size_value , [Data Entry2].[Qty of Sheets], [Data
Entry2].[Eqv_A_Size], " "
[Data Entry2].[Earned ASize] , [Data Entry2].[Physical Prog], [Data
Entry2].TR, [Data Entry2].Date, " "
[Data Entry2].SubCon, [Data Entry2].Location, [Data Entry2].[Drawing Control
No] FROM [Data Entry2]:
the second thing is that i try to use for the moment the vbCrlf so that i
can continue in writng my codes..
so i write this codes...but i have this
Run Time Error '3142':Characters found after end of SQL statement.
what is my mistakes here?

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & "'"
End If

Me.RecordSource = SQL

thanks again John..thank you very much John....

denver
***********************************************************
:

You seem to have a mixture of both methods here. If you are going to use a
filter then you don't need to have the SQL, if you want to use the SQL then
you need to append the criteria and set the recordsource.

i.e.:

SQL = "SELECT ...

If Me!Combo58 <> " All Locations" Then
SQL = SQL & " WHERE location = '" & Me![Combo58] & " '"
End If

Me.RecordSource = SQL

OR just:

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

Incidentally, you do not need all the vbcrlf's in the declaration of the SQL,
just a space between each item.

Apart from that it should work provided that " All Locations" is identical in
both the RowSource and the event code, especially check that the spaces match
as people tend not to notice them but computers do!

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

Denver wrote:
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].[Sheet 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


:

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.

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
:
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...