Combo box and recordset problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Corey-g - 29 Mar 2007 17:16 GMT

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

Reply to this Message

Marshall Barton - 29 Mar 2007 17:48 GMT

>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
[quoted text clipped - 23 lines]
>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.

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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Corey-g - 29 Mar 2007 21:12 GMT

That worked like a charm Marshall - Thanks again. I'm sure I had tried that,
but I must have had it wrong...

Corey

Reply to this Message

Marshall Barton - 30 Mar 2007 00:58 GMT

>That worked like a charm Marshall - Thanks again. I'm sure I had tried that,
>but I must have had it wrong...

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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Corey-g - 30 Mar 2007 22:56 GMT

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

>>That worked like a charm Marshall - Thanks again. I'm sure I had tried that,
>>but I must have had it wrong...
[quoted text clipped - 14 lines]
>is preferable to use Me instead:
> With Me

Reply to this Message

Marshall Barton - 30 Mar 2007 23:52 GMT

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

Signature

Marsh
MVP [MS Access]

>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
[quoted text clipped - 27 lines]
>>is preferable to use Me instead:
>> With Me

Reply to this Message