Combo Box Choose Record Problem

[Hannah B]

Hi All,

Had a wee dig around on this forum but can't find how to fix this
problem - apologies if I've just missed the explanation! Apologies for
un-technical language/poor description - I'm a complete novice at all
this!

I've got a form with 4 cascading combo boxes on it and each narrows
down the user's choices until they've picked a record. That bit works
successfully. Where I've got the problem is when people use the scroll
wheel on the mouse, I want my combo boxes to reflect the new record
they've scrolled to. I've managed this to an extent (works perfect for
the first three combo boxes) but I can't seem to get the final combo
box (cmbPlot) to reflect the record it's on. Worse still, cmbPlot
remains consistently blank - even when you select a value in it, it
immediately goes blank straight after.

I think this must be something to do with the fact that the cmbPlot is
a 2 column combo box where the others are all single column combo
boxes. cmbPlot is the combo box where I have code to choose the record
(hence the 2 columns which are called SurveyID and Surveyed for info).
I know there's something wrong in my code but I just don't know what
it is! Does anybody know how I can overcome this problem? I've pasted
in the code I've used below and please just let me know if you need
anymore info - my explanations aren't always the best! Thanks so much
for all your help already, this is really bugging me!

Hannah

Private Sub Form_Current()
Me!cmbBeach = RefNo
Me!cmbYear = Year
Me!cmbPeriod = Period
Me!cmbPlot = SurveyID

'cmbBeach.Requery
'cmbYear.Requery
'cmbPeriod.Requery
'cmbPlot.Requery


End Sub
Private Sub cmbBeach_AfterUpdate()


Me.[cmbYear] = Null
Me.[cmbYear].Requery

Me.[cmbPeriod] = Null
Me.[cmbPeriod].Requery

Me.[cmbPlot] = Null
Me.[cmbPlot].Requery

End Sub

Private Sub cmbYear_AfterUpdate()

Me.[cmbPeriod] = Null
Me.[cmbPeriod].Requery

Me.[cmbPlot] = Null
Me.[cmbPlot].Requery

End Sub

Private Sub cmbPeriod_AfterUpdate()

Me.[cmbPlot] = Null
Me.[cmbPlot].Requery

End Sub

Private Sub cmbPlot_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SurveyID] = " & Str(Me![cmbPlot])
Me.Bookmark = rs.Bookmark

End Sub

 

[Hannah B]

Thanks for such a speedy reply. Changing the column width away from 0
means that the combo box now follows the record (yay!) but also means
that I now display the column I want to keep hidden instead (SurveyID
is there to find the record; Surveyed is the name of the field I want
displayed). Do you know how it would be possible to still hide
SurveyID but display Surveyed in cmbPlot so that it matches the
record? I'm pretty certain there's a flaw (probably multiple flaws) in
my code and/or general form design somewhere but I can't work out what
it is! Thanks again for all your help, I really really appreciate it!

Hannah

 

[Hannah B]

Thank you for bearing with me on this one - I'm really not sure what
the answer is. I tried switching the SurveyID column width back to
zero but that just took me back to the start of the problem.

With a bit of playing around, cmbPlot only seems to pick up the
SurveyID for the first record chosen using the combo boxes and will
display that. When you scroll to different records, however, it
doesn't seem to pick up the SurveyID at all. If you click on the combo
box, it only shows the options for the first record you chose, not
what's shown currently on the rest of the form.

I think therefore that there has to be some kind of problem with this
section of my code:

Private Sub Form_Current()
Me!cmbBeach = RefNo
Me!cmbYear = Year
Me!cmbPeriod = Period
Me!cmbPlot = SurveyID


'cmbBeach.Requery
'cmbYear.Requery
'cmbPeriod.Requery
'cmbPlot.Requery


End Sub

I'm pretty certain that there's something wrong with the bits that
refer to cmbPlot but I don't know what they are. I want cmbPlot to
reflect the SurveyID shown in a text box control on the form but
display the Surveyed field based on that record. At the moment that
doesn't seem to work and I'm not sure what to try next. Thanks again
for all your help - I'm not sure what us complete beginners would do
without forums like this!

Hannah

 

[Hannah B]

They're unbound combo boxes (so that they can cascade from one
another) and I'm afraid your code didn't work. Thanks ever so much
again for all your help, I really appreciate it.

Hannah

 

[Hannah B]

Just wanted to say thank you very much for all your help - I really
really appreciate it. I'd thought about just disabling the mouse wheel
but hoped there was another way but disabling the mouse might just be
the way to go. Thanks again for all your suggestions, they've really
helped me out.

Hannah