itemdata property used with cascading combo boxes

[Guest]

Hi. First of all, they give me A2K to work with here.....

There are three combo boxes on my form which I guess qualify as having the
'cascading' quality assigned to their inter-relationship(s). The 'first' is
called 'Primary', the 2nd 'Title' and the final/3rd one is called
'IRB_Number'. It probably needs to be remembered that when 'Primary' and
'Title' have been selected by a user, that 'IRB_Number' is uniquely solved
for (i.e. there will only be one IRB_Number the user can choosed from), so it
seems like a good idea to obviate the user having to choose from the last
named list of the 'IRB_Number' combo box.

Anyway, enough talk......here's the three SQL queries I have behind the
three controls I just enumerated in order:

SELECT DISTINCT Primary.PrimDescription
FROM [Primary]
ORDER BY Primary.PrimDescription;

SELECT Protocol.Description
FROM [Primary] INNER JOIN Protocol ON Primary.PrimaryID = Protocol.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]));

SELECT Protocol.[IRB #]
FROM Protocol INNER JOIN [Primary] ON Protocol.PrimaryID = Primary.PrimaryID
WHERE (((Primary.PrimDescription)=[Forms]![Temporary Look Up]![Primary]) AND
((Protocol.Description)=[Forms]![Temporary Look Up]![Title]));

followed by their relevant VBA Event code:

Option Compare Database
Option Explicit

Private Sub Primary_AfterUpdate()
Me.Primary.Requery
Me.Title.SetFocus
Me.Title = "Enter"
Me.Title.Requery
Me.IRB_Number = "Enter"
Me.IRB_Number.Requery
End Sub

Private Sub Title_AfterUpdate()
Me.Title.Requery
Me.IRB_Number.SetFocus
Me.IRB_Number.Requery
Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
End Sub


This configuration 'sort of' works.....The ‘weird’ thing about the way it
works is that sometimes it will enter an IRB number in the control and other
times it won’t even though there are IRB numbers found in the ‘Protocol’
table for the Primary and Title combination the user chooses. When the IRB
number is not entered, clicking on the combo box arrow results in what looks
like a blank value.

Would anyone care to speculate about what's keeping this from working as it
should?

-Ted

 

[Tim Ferguson]

Private Sub Primary_AfterUpdate()
Me.Primary.Requery

Huh? What is the point of requerying the control that is currently
AfterUpdating... Surely this should be Title.ReQuery

Me.Title.SetFocus

why setfocus? It's very unnerving for the user to have the focus jumping
about without his or her doing it.

Me.Title = "Enter"

What is this? How do you know that there is an "Enter" in the combo list
here? You could set Title.Selected(0) = True so that it'll select the
first item in the list.

Me.Title.Requery
Aha.

Me.IRB_Number = "Enter"

Ditto... but you should be able to let Access ripple its own events down
the cascade.

Me.IRB_Number.Requery

End Sub

Private Sub Title_AfterUpdate()
Me.Title.Requery

Ditto. Requery is only needed when you have changed the underlying
RowSource property and you haven't here.

Me.IRB_Number.SetFocus

Ditto: let the user use the form.

Me.IRB_Number.Requery

This is correct

Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)

Okay... or use the .Selected() method

End Sub

Would anyone care to speculate about what's keeping this from
working as it should?

Simplify it: let Access do the work instead of you!

Hope that helps


Tim F

 

[Guest]

i see that i guess i'm just a bit confused about how to do what i'm trying to
do (broadly speaking, make it ez for the user to do their job) but i think
you have misunderstood my approach, so let me 'try' to explicate where it
seems there a disjuncture.

i don't think that the "Me.Title.SetFocus" is unnerving to users -- i have
used it elsewhere to good effect. no complaints spell no worries (i'll grant
you not always, but usually).

the Me.Title = "Enter" is intended to populate the elected control with the
'Enter' value (which i conditionally format turn to display in red text) to
get the user's attention to remind them to 'Enter' something in there. seems
intuitively obvious to me and it's worked in other controls. now on to "You
could set Title.Selected(0) = True so that it'll select the first item in the
list." -- not relevant really, since there are usually many many 'Title's on
the list from which user needs to choose. the only time there's no choice but
one's when user finally 'arrives' at the IRB_Number field by which point it
ought to have been 'automatically' pre-filled.

to reprise, the form sometimes prefills the IRB_Number and at times it does
not besides driving me bonkers makes no sense, since when i research the
values of IRB_Number when it fails to do so, i find there are actually
values which should be used to enter into the IRB_Number control. instead
what i observe in those instances is a blank value when clicking the combo
box's downward facing arrow.

-ted

 

[Tim Ferguson]

to reprise, the form sometimes prefills the IRB_Number and at times it
does not besides driving me bonkers makes no sense, since when i
research the values of IRB_Number when it fails to do so,

As far as I can see, there are four lines that can alter the displayed
value of the IRB_Number control:

Private Sub Primary_AfterUpdate()
....
Me.IRB_Number = "Enter"
Me.IRB_Number.Requery

End Sub

Private Sub Title_AfterUpdate()
....
Me.IRB_Number.Requery
Me.IRB_Number.Value = Me.IRB_Number.ItemData(0)
End Sub

It is far from clear what order these occur in, or even how often they
call eachother.

I would guess that the only way to check this out thoroughly is to step
through the code and find out what is actually displayed at each line.
Remember, from this end I cannot see what you are doing, what you are
trying to achieve, nor what else is going on. My feeling, expressed
previously, is that these subs are overcomplicated and falling over
eachother; but I cannot know that, so all I can offer is suggestions.

Best of luck


Tim F

 

[Guest]

thank you for your considered opinions and advisory thoughts. i have a
feeling that (in the immortal words of one inspector cluseau) "there are
nefarious forces at work here". when the code works as expected it resolves
the IRB_Number; however there are instances when after selecting "Primary"
and "Title" it does not (it will either display the previous value of the
"IRB_Number" that got selected OR blank). i have tried to uncover some
undelying pattern that would account for the successes and the failures but
my feeling is that yes it's quite puzzling but i think i have come up with a
work around which will do the job (albeit not quite as roundly).

all the best,

-ted