Synchronised ComboBox Issues - Very Stuck, Please Help!!!

[Clarkyboy420]

Hi All,

I'm struggling with synchronising combo boxes. Below is an in depth analysis
of my problem, to assist anyone kind and generous enough to help.

I have a software incident system, consisting at present of an Incident Form
and underlying Incident Table. The database is in a normalised structure. Two
of the fields within my Incident Form are cboProduct and cboVersion, with
Version being a child of Product, each of these have the respective control
source ProductID or VersionID, which are FK's within the Incident Table,
related to Product and Version Tables.

I have set these up as combo boxes and am attempting to synchronise them
with the following code in the AfterUpdate property of the first combo, as in
the training sample db;

Private Sub cboProductID_AfterUpdate()

Me.cboVersionID.RowSource = "SELECT VersionID FROM" & _
" Version WHERE ProductID = " & Me.cboProductID
& _
" ORDER BY VersionID"

End Sub

There was a line to enter a default value, but as this is not solely data
entry, but also a form to look at/amend old records, I removed the line
(...itemdata(0))

This first box cboProduct has a row source linked to the Product table,
which using the column count and widths (count 3, width 0,0,3), I have
managed to display the ProductName as opposed to the index. So, at this point
I have the correct information displayed in the first combobox cboProduct for
all records and in the second cboVersion I have just the index displayed for
all records.

I tried changing the column count and width properties in the second combo
cboVersion, but it shows only one column, VersionID. I guessed this was
because this relies on the row source property, which is populated by the
answer from the previous box.

I took a flying leap in vb (which I do not really know any of) and in the
SELECT part of the vb code, added in the VersionName field I wished to be
displayed;

Private Sub cboProductID_AfterUpdate()

Me.cboVersionID.RowSource = "SELECT VersionID, VersionName FROM" & _
" Version WHERE ProductID = " & Me.cboProductID
& _
" ORDER BY VersionID"

End Sub

This gave me the extra associated column I wished, but now highlights two
major issues;

1) When the form is opened, if I click straight to the second combo
cboVersion's dropdown arrow, there is NO dropdown data returned from the row
source on ANY of the records I scroll through, however, as soon as I select
something from the first combo cboProduct (even if its the same item as
already there), there is now data populating the cboVersion dropdown,
filtered to my first combo selection.

If I move to another record though, there IS now data populating the
cboVersion dropdown, but pertaining to the previous record I adjusted. I
figured out this is because for each record it would need to requery the
first combo box cboProduct to filter the selection within the second one
cboVersion. I guessed there is a property I could call my event procedure
against each time I move to another record, but have tried FormOpen and
FormBeforeUpdate, but to no avail. I think this might be related to the
initial problem with nothing being available until you manually query the
first box cboProduct. Any advices here would be appreciated, because I want
to avoid a user being able to apply an incorrect version against incorrect
product, which is very conceivable in this setup - any advices welcomed on
strategy here please.

2) The second problem is related, but more important. I now have the column
data I was looking for in the second combobox cboVersion, but it still
displays the index number. If I then change the column count and width
properties to highlight the correct column (count 2 width 0,3), I can see the
information I wish to, as opposed to the index. However, I can ONLY see this
once I have selected from the first combo cboProduct, as with problem one. If
I then scroll through to the other records, cboVersion is blank, except for
those occurences where it's the same product in cboProduct as in the record I
first adjusted to bring the value up in the first place. (ie if I changed
Product 1 in record 1, then info shows in cboVersion, but if record 2 is
Product 2, there is nothing in cboVersion, yet if record 3 is Product 1, the
information IS displayed...and correctly pertaining to the underlying
Incident table the form stores to)

I would really appreciate any help on this. I suspect I need something to
effectively requery the cboProduct box and run the AfterUpdateEvent everytime
I enter/move records, but there are also another 4/5 pairs of boxes to synch
on the same form, which would also require the same I guess and I'm worried
that loads of code might slow the form response, so that may be another
consideration. Another option would be for the EventProcedure to enter a
defaultvalue in cboVersion, which would reference the ACTUAL value in the
underlying Incident table for existing records and leave Null in a new
record, but I have no idea how to even start coding this!!!

I've been banging my head against this for a week now intensively
researching and testing and though I've progressed through to the point I'm
at, I'm totally stuck.

I offer my kindest and most grovelling Regards to anyone who can help me
bash my head through this!!!

Chris

 

[John Smith]

You need to put the same code for setting the cboVersionID RowSource into the
Current Event of the form so that it updates as you move from record to record.
You could do it by just copying the code but it is better to put it into a
separate Sub which you call from both events, otherwise sooner or later you will
change one and not the other!

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