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!
