How to get current record read reliably from a combo control "Green Biro" <dummygreenbiro[ at ]breenbiro.com> 11/18/2008 11:51:18 PM 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

Re: How to get current record read reliably from a combo control "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/19/2008 12:51:30 PM 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 http://I.Am/DougSteele (no e-mails, please!) "Green Biro" <dummygreenbiro[ at ]breenbiro.com> wrote in message news:XxIUk.947$iQ2.51[ at ]newsfe23.ams2... [Quoted Text] >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 >

Re: How to get current record read reliably from a combo control "Green Biro" <dummygreenbiro[ at ]breenbiro.com> 11/20/2008 12:23:46 AM 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 "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> wrote in message news:%23RPaMWkSJHA.1160[ at ]TK2MSFTNGP02.phx.gbl... [Quoted Text] > 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 > http://I.Am/DougSteele > (no e-mails, please!) > > > "Green Biro" <dummygreenbiro[ at ]breenbiro.com> wrote in message > news:XxIUk.947$iQ2.51[ at ]newsfe23.ams2... >>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 >> > >

Re: How to get current record read reliably from a combo control "Mike Painter" <mddotpainter[ at ]sbcglobal.net> 11/20/2008 3:04:44 AM Green Biro wrote: [Quoted Text] > 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.

Re: How to get current record read reliably from a combo control "Green Biro" <dummygreenbiro[ at ]breenbiro.com> 11/21/2008 6:08:55 PM Thank you. Most informative. GB [Quoted Text] > > 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. > > >