How to get current record read reliably from a combo control

[Green Biro]

I have a form that is made up from a numbe of combo boxes each sourced by
different queries.

I have a 'submit' button on the form which (amongst other stuff) has the
following code

Set rsPlayer1 = Me.[cmbPlayer1].Recordset
Set rsPlayer2 = Me.[cmbPlayer2].Recordset
Set rsResult = Me.[cmbResult].Recordset

intPlayer1_Score = rsPlayer1.Fields("Points")
intPlayer2_Score = rsPlayer2.Fields("Points")
strPlayer1_Id = rsPlayer1.Fields("Player_Id")
strPlayer2_Id = rsPlayer2.Fields("Player_Id")
strResult = rsResult.Fields("Result")


strPlayer_Id always picks up the value that has been chosen in the combo box
but strResult >sometimes< returns a different record than that currently set
in the combo box and I don't know why. I wander if I'm making some
incorrect assumptions about the way Access handles recordsets and current
records but it really does seem to be random when the result combo box
misbehaves so I can't work it out.

Can anyone guide me please?

NB just for background info the rest of my code updates a hidden control on
the form and performs a 'new record' operation thus updating the table that
is the source of the form. If I try to save or exit the form without
clicking on my submit button, it wont save because of the outstanding
compulsory field. This is exactly the behaviour I want though it's possible
I'm not doing it the best way.

Any pointers gratefully received.

GB

 

[Douglas J. Steele]

There's no need to resort to recordsets!

To get the details of the bound column of the currently selected row of a
combo box, simply refer to the combo box:

Me.cmbResult

To get the details of any other column for the currently selected row of a
combo box, use its Column collection. For example,

Me.cmbResult.Column(1)

will give you the value of the second column of the currently selected row
of the combo box (the Column collection starts numbering at 0)

 

[Green Biro]

Yes that was a lot easier. Thank you. All working now.

BTW Could I trouble you with a general question that I've never quite
sussed? What are the rules in Access VBA for when you use the different
delimeters: full stop, explanation mark and square brackets? A lot of the
time they seem to be interchangeable but I bet there's some 'rules'
somewhere!

GB

There's no need to resort to recordsets!

To get the details of the bound column of the currently selected row of a
combo box, simply refer to the combo box:

Me.cmbResult

To get the details of any other column for the currently selected row of a
combo box, use its Column collection. For example,

Me.cmbResult.Column(1)

will give you the value of the second column of the currently selected row
of the combo box (the Column collection starts numbering at 0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have a form that is made up from a numbe of combo boxes each sourced by
different queries.

I have a 'submit' button on the form which (amongst other stuff) has the
following code

Set rsPlayer1 = Me.[cmbPlayer1].Recordset
Set rsPlayer2 = Me.[cmbPlayer2].Recordset
Set rsResult = Me.[cmbResult].Recordset

intPlayer1_Score = rsPlayer1.Fields("Points")
intPlayer2_Score = rsPlayer2.Fields("Points")
strPlayer1_Id = rsPlayer1.Fields("Player_Id")
strPlayer2_Id = rsPlayer2.Fields("Player_Id")
strResult = rsResult.Fields("Result")


strPlayer_Id always picks up the value that has been chosen in the combo
box but strResult >sometimes< returns a different record than that
currently set in the combo box and I don't know why. I wander if I'm
making some incorrect assumptions about the way Access handles recordsets
and current records but it really does seem to be random when the result
combo box misbehaves so I can't work it out.

Can anyone guide me please?

NB just for background info the rest of my code updates a hidden control
on the form and performs a 'new record' operation thus updating the table
that is the source of the form. If I try to save or exit the form
without clicking on my submit button, it wont save because of the
outstanding compulsory field. This is exactly the behaviour I want
though it's possible I'm not doing it the best way.

Any pointers gratefully received.

GB

 

[Mike Painter]

Yes that was a lot easier. Thank you. All working now.

BTW Could I trouble you with a general question that I've never quite
sussed? What are the rules in Access VBA for when you use the
different delimeters: full stop, explanation mark and square
brackets? A lot of the time they seem to be interchangeable but I
bet there's some 'rules' somewhere!

Brakets are required when there is an (evil) space in a name [My Field]
The Bang (!) is that a bang is used to seperate an object from the
collection and the dot(.) is used to seperate an object from a property.

They are not always interchangable, and should not be.

If you open an Access VBA window when tracing, you will see that
me.somefield works but you get nothing if you hover over it.
Change it to a bang and you see the value for somefield.

 

[Green Biro]

Thank you. Most informative.

GB