Combo box and recordset problem

  • Thread starter Thread starter Corey-g via AccessMonster.com
  • Start date Start date
C

Corey-g via AccessMonster.com

Hi All,

I have a form that uses 4 combo boxes to pick items from. They are all
syncronized together, and for the most part this seems to work. But I have
added a test to see if there is only one value in the combo box after each
'after_update' event, and if so, set the value of the combo box to that value.


With FORM_frmRequest_Add
.cboPartNumber.RowSource = sSQL
.cboPartNumber.Requery
If .cboPartNumber.ListCount = 1 Then
.cboPartNumber.Value = .cboPartNumber.Recordset(0)
' .cboPartNumber = .cboPartNumber.recordset(0) - Tried this - no
luck
' .cboPartNumber = .cboPartNumber.itemdata(0) - Tried this - no
luck
End If
End With

I thought this was working, but I am getting
"Run-time error '2113':
The value you entered isn't valid for this field

I can't figure out why this is happening. When I look at the combo box in
the watch window, the 'recordset' property show <Object Invalid or no longer
set >

I also have a 'clear' button that sets all of the combo boxes to Null then re-
assigns the query to the rowsource and requeries it - if that helps.

Any thoughts on where I've gone wrong?

Corey
 
Corey-g via AccessMonster.com said:
I have a form that uses 4 combo boxes to pick items from. They are all
syncronized together, and for the most part this seems to work. But I have
added a test to see if there is only one value in the combo box after each
'after_update' event, and if so, set the value of the combo box to that value.


With FORM_frmRequest_Add
.cboPartNumber.RowSource = sSQL
.cboPartNumber.Requery
If .cboPartNumber.ListCount = 1 Then
.cboPartNumber.Value = .cboPartNumber.Recordset(0)
' .cboPartNumber = .cboPartNumber.recordset(0) - Tried this - no
luck
' .cboPartNumber = .cboPartNumber.itemdata(0) - Tried this - no
luck
End If
End With

I thought this was working, but I am getting
"Run-time error '2113':
The value you entered isn't valid for this field

I can't figure out why this is happening. When I look at the combo box in
the watch window, the 'recordset' property show <Object Invalid or no longer
set >

I also have a 'clear' button that sets all of the combo boxes to Null then re-
assigns the query to the rowsource and requeries it - if that helps.
Click to expand...


First, setting the RowSource automatically requires the
combo/list box so it's a waste of time to requery it again.

This should work:

With FORM_frmRequest_Add.cboPartNumber
.RowSource = sSQL
If .ListCount = 1 Then
.Value= .ItemData(0)
End If
End With

Double check the row source query to make sure the fields in
the query agrees with the combo box's ColumnCount and
BoundColumn properties.
 
That worked like a charm Marshall - Thanks again. I'm sure I had tried that,
but I must have had it wrong...

Corey
 
Corey-g via AccessMonster.com said:
That worked like a charm Marshall - Thanks again. I'm sure I had tried that,
but I must have had it wrong...
Click to expand...


I think I had a brain fault when I posted that code :-(

The line:
With FORM_frmRequest_Add
is incorrect. It looks like you are referring to the form
object that contains the combo boxes and that the form
object is named frmRequest_Add

While that will work in many situations, it will fail in
some cases. The correct way to refer to a form's default
instance is Forms!frmRequest_Add

OTOH, if the code is in the same form as the combo boxes, it
is preferable to use Me instead:
With Me
 
I did have quite a few references using Me. as the prefix, but someone told
me that this can 'confuse' access if using subforms. Although I didn't agree
or think it would, I didn't have the energy to argue. Do you know in what
types of cases when this will fail? I would like to change this, but I'm a
lttle afraid... :-) Seems like every time I make a little change, things
blow up... :-(

Thank you for the help again. You have been great. I have wished for a
mentor for quite some time, and with this absense, you and some of the others
have been great. I can't wait until I get this project done so that I may
spend some time helping others as well. But for now I have to spend most of
my time getting this thing working and installed...

Corey
 
I have never even heard of a case where Access got confused
about the meaning of Me. I have seen lots of people that
were confused about it though ;-)

Regardless of who/what might be confused, Me ***always***
refers to the class module that contains the line of code.
For forms/reports, Me also refers to the form/report that
contains the module.

Some of the reasons why Me is preferable are:
* It is more efficient than processing a full reference
* It will still work if you change the name of the form
* It is portable if you should ever want to copy the code
to another form/report
* It is a heck of lot easier to type
* It is much shorter than a full reference, making your
code easier to read
 
Back
Top