Requery Problem

[fish]

I have one combo box that requeries another, the problem
is that the other box shows duplicates in it. How do I fix
this?

 

[Ken Snell]

Sounds as if the row source query for the second combo box has an error in
it. Hard to suggest how to fix it as you don't tell us much info here.

 

[fish]

the table I'm trying to make this work on has four fields
(among several).
Each of the four fields are related to another table,
becacause this one has several repeating values in it to
show the relation between one object and several others.

I'll try to give an idea:

primary | specific | detail | fineDetail
--------|--------------|------------|--------------
A | Blade Strike | Main Rotor |
A | Blade Strike | Tail Rotor |
F | Spill | Discovered | Co. Personnel
F | Spill | Discovered | Non-Co Person

What I'd like to have as an end result is a form with much
more detail, and to have four fields refine the next one
(like the example above), to help avoid mistakes by the
whoever might be entering it. Because improper position
reporting can not be caused by a fuel tank, etc...
These four fields will eventually appear on the report.

Perhaps you have a better idea than I do on how to set
this up, and make things work the way I'd like?

Thanks in advance

 

[Ken Snell]

If I understand you correctly, you want to have four combo boxes on a form,
with the first being a filter for the second, the first and second being a
filter for the third, and the first three being a filter for the fourth?

If this is correct, see The ACCESS Web for info on how to make one combo box
depend upon another for its filter:
http://www.mvps.org/access/forms/frm0028.htm

Your table structure appears to be unnormalized in some respects. Typically,
you would not repeat Blade Strike as a text phrase in each record. Instead,
you'd create a table that holds the type of accident and assign numbers to
each type, and then use the number in the first table's field (linking the
two together).

For example:

tblAccident
AccidentID
AccidentTypeID
FirstDetailID
FineDetailID

tblAccidentType
AccidentTypeID
AccidentTypeDescription

tblFirstDetail
FirstDetailID
FirstDetailDescription

tblFineDetail
FineDetailID
FineDetailDescription

etc.

This way, if you ever change the description, all tables will get the same
description; you won't need to make the change in many records.

 

[fish]

That is how I have the table set up, I have four actual
tables with the id and a field with the information
entered once. Everytning else is simply id's in the table
that has the relations.
I don't know if the problem I'm having is in the query,
for the combo on the form. I tried not to mention too much
detail with the first post, cuz I was unsure about how to
explain what I had, but you seem to understand what I have
so far.

Here is the query I'm using on the second combo, maybe you
could help me understand where the problem is?

SELECT tblSpecific.id, tblSpecific.grpSpecific, tblCode.id
FROM tblSpecific INNER JOIN (tblCode RIGHT JOIN tblDetails
ON tblCode.id = tblDetails.grpCODES) ON tblSpecific.id =
tblDetails.grpSPECIFICS
WHERE (((tblCode.id) Like [forms]![frmOccRep]![cboCode]))
ORDER BY tblSpecific.grpSpecific;

Should I not have the relationship in the query? and if
not, what would you suggest?

Without it, all I had was numbers in the combos.

 

[Ken Snell]

Your row source query for the second combo box appears to have the correct
syntax, including the reference to the cboCode control.

The way you've written the query assumes that the bound column of the
cboCode combo box is the column that contains the value of the codeID. Is
that correct? If yes, then describe in more details what is not working in
the second combo box.

Additionallly, in order for this row source query for the second combo box
to "run" after you make the selection in the first combo box, you must
requery the second combo box in the After Update event of the first combo
box.

The VBA code for the first combo box would be something like this for the
AfterUpdate event:

Private Sub cboCode_AfterUpdate()
Me.SecondComboBoxName.Requery
End Sub

By running this code, you then tell the second combo box to requery using
the value in the first combo box. Otherwise, the second combo box's row
source query will "run" when the form loads, and at that time the first
combo box has no value, thus the second combo box will have nothing in its
dropdown list.

--
Ken Snell
<MS ACCESS MVP>

That is how I have the table set up, I have four actual
tables with the id and a field with the information
entered once. Everytning else is simply id's in the table
that has the relations.
I don't know if the problem I'm having is in the query,
for the combo on the form. I tried not to mention too much
detail with the first post, cuz I was unsure about how to
explain what I had, but you seem to understand what I have
so far.

Here is the query I'm using on the second combo, maybe you
could help me understand where the problem is?

SELECT tblSpecific.id, tblSpecific.grpSpecific, tblCode.id
FROM tblSpecific INNER JOIN (tblCode RIGHT JOIN tblDetails
ON tblCode.id = tblDetails.grpCODES) ON tblSpecific.id =
tblDetails.grpSPECIFICS
WHERE (((tblCode.id) Like [forms]![frmOccRep]![cboCode]))
ORDER BY tblSpecific.grpSpecific;

Should I not have the relationship in the query? and if
not, what would you suggest?

Without it, all I had was numbers in the combos.

-----Original Message-----
If I understand you correctly, you want to have four combo boxes on a form,
with the first being a filter for the second, the first and second being a
filter for the third, and the first three being a filter for the fourth?

If this is correct, see The ACCESS Web for info on how to make one combo box
depend upon another for its filter:
http://www.mvps.org/access/forms/frm0028.htm

Your table structure appears to be unnormalized in some respects. Typically,
you would not repeat Blade Strike as a text phrase in each record. Instead,
you'd create a table that holds the type of accident and assign numbers to
each type, and then use the number in the first table's field (linking the
two together).

For example:

tblAccident
AccidentID
AccidentTypeID
FirstDetailID
FineDetailID

tblAccidentType
AccidentTypeID
AccidentTypeDescription

tblFirstDetail
FirstDetailID
FirstDetailDescription

tblFineDetail
FineDetailID
FineDetailDescription

etc.

This way, if you ever change the description, all tables will get the same
description; you won't need to make the change in many records.

--
Ken Snell
<MS ACCESS MVP>




.