filter form by table

J

javablood

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
K

KARL DEWEY

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.
 
J

javablood

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


KARL DEWEY said:
I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


javablood said:
I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
K

KARL DEWEY

it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


javablood said:
Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


KARL DEWEY said:
I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


javablood said:
I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
J

javablood

Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


KARL DEWEY said:
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


javablood said:
Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


KARL DEWEY said:
I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
K

KARL DEWEY

the user could only pick those locations marked with a 'yes' correct?
They could change any to Yes.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


javablood said:
Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


KARL DEWEY said:
it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


javablood said:
Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
J

javablood

Karl,

Okay, I created a separate table with the locations and a Yes/No field. I
put the form in the header of the data entry form that i want to filter. I
Left joined the two tables in the Record Source, added the Use field with a
-1 criteria in the same query. I now get nothing in the filtered form. I
can see the locations and yes/no but I expected to have the check box like
you get in the datasheet view to select the locations but do not get that.

I know I am not very adept at this but maybe there is not a way to do what I
want. Is there something I can post for yuo to see what i am not doing
correctly? Is there some nuance in the master child fields in which I have
it wrong?

thanks,
--
javablood


KARL DEWEY said:
They could change any to Yes.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


javablood said:
Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


KARL DEWEY said:
it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


:

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
K

KARL DEWEY

You did not answer as to what constitutes a 'group'.

I have got lost through times and all the peices. Tell me if I am wrong in
that you want to pick a location from a group (because there are so many
locations) for data entry associated with that location.


--
Build a little, test a little.


javablood said:
Karl,

Okay, I created a separate table with the locations and a Yes/No field. I
put the form in the header of the data entry form that i want to filter. I
Left joined the two tables in the Record Source, added the Use field with a
-1 criteria in the same query. I now get nothing in the filtered form. I
can see the locations and yes/no but I expected to have the check box like
you get in the datasheet view to select the locations but do not get that.

I know I am not very adept at this but maybe there is not a way to do what I
want. Is there something I can post for yuo to see what i am not doing
correctly? Is there some nuance in the master child fields in which I have
it wrong?

thanks,
--
javablood


KARL DEWEY said:
the user could only pick those locations marked with a 'yes' correct?
They could change any to Yes.
why could I not sort on the 'Group' field? But that did not work.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


javablood said:
Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


:

it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


:

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
J

javablood

Karl,

I'm sorry, the obvious to me is not as obvious to you and visa versa. For
example:
Locations 1-10 = Group A
Locations 11- 20 = Group B
and so on.

In the 'Group' table there are 155 locations and 11 Groups. So although
there are 155 distinct locations, the actual 'Group' name is repeated
depending on the number of locations/group.

Anyway, I created a combo box with the 'Group' table as the RowSurce and
bound the 'Group' column for selection. I then synched the 'Group' combo box
with the 'Location' combo box by putting the following in the 'Group" combo
box AfterUpdate Event:

Private Sub cboGrp_AfterUpdate()

' Update the row source of the cboLctn combo box
' when the user makes a selection in the cboGrp
' combo box.
Me.cboLctn.RowSource = "SELECT tblHardageSiteIdentification.STATION_ID "
& _
"FROM tblHardageSiteIdentification " & _
"WHERE (((tblHardageSiteIdentification.Report_Group) =
[Forms]![frmWLntry]![cboGrp])) " & _
"ORDER BY tblHardageSiteIdentification.STATION_ID; "

Me.cboLctn = Me.cboLctn.ItemData(0)

End Sub

This works but when I tried to get either unique records or unique values
for the Groups by having a query in the RowSource and setting either Unique
Records or Unqiue Values to Yes, I just get a blank box. So I took your
initial concept and worked with it to get to where I am now. Now, do you
know how I can get just the unique Group names in the 'Group' combo box?

BTW Thanks for your help to date. I hope I have provided enough information
this time.

---
javablood


KARL DEWEY said:
You did not answer as to what constitutes a 'group'.

I have got lost through times and all the peices. Tell me if I am wrong in
that you want to pick a location from a group (because there are so many
locations) for data entry associated with that location.


--
Build a little, test a little.


javablood said:
Karl,

Okay, I created a separate table with the locations and a Yes/No field. I
put the form in the header of the data entry form that i want to filter. I
Left joined the two tables in the Record Source, added the Use field with a
-1 criteria in the same query. I now get nothing in the filtered form. I
can see the locations and yes/no but I expected to have the check box like
you get in the datasheet view to select the locations but do not get that.

I know I am not very adept at this but maybe there is not a way to do what I
want. Is there something I can post for yuo to see what i am not doing
correctly? Is there some nuance in the master child fields in which I have
it wrong?

thanks,
--
javablood


KARL DEWEY said:
the user could only pick those locations marked with a 'yes' correct?
They could change any to Yes.

why could I not sort on the 'Group' field? But that did not work.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


:

Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


:

it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


:

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
K

KARL DEWEY

Now, do you know how I can get just the unique Group names in the 'Group'
combo box?
Use this for the combo source but substitute your table and field names --
SELECT Group_Name
FROM Location_Table
GROUP BY Group_Name;

Group is a reserved word and may give you problems.


--
Build a little, test a little.


javablood said:
Karl,

I'm sorry, the obvious to me is not as obvious to you and visa versa. For
example:
Locations 1-10 = Group A
Locations 11- 20 = Group B
and so on.

In the 'Group' table there are 155 locations and 11 Groups. So although
there are 155 distinct locations, the actual 'Group' name is repeated
depending on the number of locations/group.

Anyway, I created a combo box with the 'Group' table as the RowSurce and
bound the 'Group' column for selection. I then synched the 'Group' combo box
with the 'Location' combo box by putting the following in the 'Group" combo
box AfterUpdate Event:

Private Sub cboGrp_AfterUpdate()

' Update the row source of the cboLctn combo box
' when the user makes a selection in the cboGrp
' combo box.
Me.cboLctn.RowSource = "SELECT tblHardageSiteIdentification.STATION_ID "
& _
"FROM tblHardageSiteIdentification " & _
"WHERE (((tblHardageSiteIdentification.Report_Group) =
[Forms]![frmWLntry]![cboGrp])) " & _
"ORDER BY tblHardageSiteIdentification.STATION_ID; "

Me.cboLctn = Me.cboLctn.ItemData(0)

End Sub

This works but when I tried to get either unique records or unique values
for the Groups by having a query in the RowSource and setting either Unique
Records or Unqiue Values to Yes, I just get a blank box. So I took your
initial concept and worked with it to get to where I am now. Now, do you
know how I can get just the unique Group names in the 'Group' combo box?

BTW Thanks for your help to date. I hope I have provided enough information
this time.

---
javablood


KARL DEWEY said:
You did not answer as to what constitutes a 'group'.

I have got lost through times and all the peices. Tell me if I am wrong in
that you want to pick a location from a group (because there are so many
locations) for data entry associated with that location.


--
Build a little, test a little.


javablood said:
Karl,

Okay, I created a separate table with the locations and a Yes/No field. I
put the form in the header of the data entry form that i want to filter. I
Left joined the two tables in the Record Source, added the Use field with a
-1 criteria in the same query. I now get nothing in the filtered form. I
can see the locations and yes/no but I expected to have the check box like
you get in the datasheet view to select the locations but do not get that.

I know I am not very adept at this but maybe there is not a way to do what I
want. Is there something I can post for yuo to see what i am not doing
correctly? Is there some nuance in the master child fields in which I have
it wrong?

thanks,
--
javablood


:

the user could only pick those locations marked with a 'yes' correct?
They could change any to Yes.

why could I not sort on the 'Group' field? But that did not work.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


:

Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


:

it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


:

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 
J

javablood

wonderful! thank you Karl!
--
javablood


KARL DEWEY said:
combo box?
Use this for the combo source but substitute your table and field names --
SELECT Group_Name
FROM Location_Table
GROUP BY Group_Name;

Group is a reserved word and may give you problems.


--
Build a little, test a little.


javablood said:
Karl,

I'm sorry, the obvious to me is not as obvious to you and visa versa. For
example:
Locations 1-10 = Group A
Locations 11- 20 = Group B
and so on.

In the 'Group' table there are 155 locations and 11 Groups. So although
there are 155 distinct locations, the actual 'Group' name is repeated
depending on the number of locations/group.

Anyway, I created a combo box with the 'Group' table as the RowSurce and
bound the 'Group' column for selection. I then synched the 'Group' combo box
with the 'Location' combo box by putting the following in the 'Group" combo
box AfterUpdate Event:

Private Sub cboGrp_AfterUpdate()

' Update the row source of the cboLctn combo box
' when the user makes a selection in the cboGrp
' combo box.
Me.cboLctn.RowSource = "SELECT tblHardageSiteIdentification.STATION_ID "
& _
"FROM tblHardageSiteIdentification " & _
"WHERE (((tblHardageSiteIdentification.Report_Group) =
[Forms]![frmWLntry]![cboGrp])) " & _
"ORDER BY tblHardageSiteIdentification.STATION_ID; "

Me.cboLctn = Me.cboLctn.ItemData(0)

End Sub

This works but when I tried to get either unique records or unique values
for the Groups by having a query in the RowSource and setting either Unique
Records or Unqiue Values to Yes, I just get a blank box. So I took your
initial concept and worked with it to get to where I am now. Now, do you
know how I can get just the unique Group names in the 'Group' combo box?

BTW Thanks for your help to date. I hope I have provided enough information
this time.

---
javablood


KARL DEWEY said:
You did not answer as to what constitutes a 'group'.

I have got lost through times and all the peices. Tell me if I am wrong in
that you want to pick a location from a group (because there are so many
locations) for data entry associated with that location.


--
Build a little, test a little.


:

Karl,

Okay, I created a separate table with the locations and a Yes/No field. I
put the form in the header of the data entry form that i want to filter. I
Left joined the two tables in the Record Source, added the Use field with a
-1 criteria in the same query. I now get nothing in the filtered form. I
can see the locations and yes/no but I expected to have the check box like
you get in the datasheet view to select the locations but do not get that.

I know I am not very adept at this but maybe there is not a way to do what I
want. Is there something I can post for yuo to see what i am not doing
correctly? Is there some nuance in the master child fields in which I have
it wrong?

thanks,
--
javablood


:

the user could only pick those locations marked with a 'yes' correct?
They could change any to Yes.

why could I not sort on the 'Group' field? But that did not work.
You probably could. I do not know your table structure so as to know what
constitutes a 'group'.

--
Build a little, test a little.


:

Karl,

Yes, I think I understand that but my point that I did not articulate very
well is that the user could only pick those locations marked with a 'yes'
correct? I have 155 locations and 11 groups. I am trying to simplify the
data entry by having only those records show per group selected.

What I was trying to do before my last post was to use your concept but
apply it to the group because if I could sort on the 'Use' field' why could I
not sort on the 'Group' field? But that did not work.
--
javablood


:

it does not allow the user the flexibility to choose the locations on the
fly.
Re-read my post. The user would select location in the form.
--
Build a little, test a little.


:

Karl,

Although this workaround might work, it does not allow the user the
flexibility to choose the locations on the fly. I tried a subform in which I
put the table with the locations by group and then tried the left join and
added the group to Field but this did not work.

From my searches of the discussion groups, etc. I am fairly certain that
what I want to do can be done but I have not found out how yet.

Thank you,
--
javablood


:

I do not know VBA but here is how I would do it.
Create a form other than the data entery form to show the locations table
that has at least 2 fields -- Location- text and Use-Yes/No. You could put
this form in the header, footer, or separate. Use it to select the locations
to be used to 'filter' (Criteria) the other form.
In the query for the filtered form left join the two tables on location.
Add Use field to query design grid Field row.
Use -1 (minus one) as criteria for it.

--
Build a little, test a little.


:

I have a data entry form in which I want to filter the locations by a field
in another table (each table has the locations but only one table has the
group by which I want to apply the filter). I am not good at this and have
tried a combo box and subform but I am probably getting confused on the
control source, record source, master, child, etc. Nothing worked. I tried
this in an unbound combo box:

Private Sub cbofltr_AfterUpdate()
If IsNull(Me.cbofltr) Then 'Remove the filter
Me.Filter = vbNullString
Me.FilterOn = False
Else
Me.Filter = "[Description] = '" & Me.cbofltr & "'"
Me.FilterOn = True
End If

End Sub

But I could not even select anything in the combo box. I am definitely
missing a link from the main table in which I want to enter data and table in
which I want to use as a filter.

If someone could point me in a direction that provides elemetary
instructions I would appreciate it. I tried Help and Access 2007 Inside Out
but that has not helped. I found tons of information in these discussions
but I think I confused myself with the overload. :-(

Thanks for any help!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top