We help IT Professionals succeed at work.

Set ComboBox Limit to List propery on form load

Aaron Greene
Aaron Greene asked
on
I need to set some ComboBox properties based on existing values of a record when the form loads.  This is the code that I am trying to use:
If Me.fEWaste = False Then
Set CBO = Me.strCUST_ADDR
With CBO
    .RowSourceType = "Value List"
    .RowSource = vbNullString
    .ColumnCount = 1
    .ColumnWidths = "2.5 in;"
    .LimitToList = False
End With
Else
Set CBO = Me.strCUST_ADDR
With CBO
    .RowSourceType = "Table/Query"
    .RowSource = "vData_Accounts_Ewaste"
    .ColumnCount = 8
    .ColumnWidths = "0 in;0 in;0 in;0 in;0 in;2.5 in;0 in;0 in;"
    .LimitToList = True
End With
End If

Everything seems to be working fine, except that I get an error when fEWaste = false and I try to set the Limit to List property to false.  Any help would be greatly appreciated.
Comment
Watch Question

Mike EghtebasDatabase and Application Developer

Commented:
property is missing--v
     If Me.fEWaste.Visible = False Then
Tested in new form in Access 2000 OK, can you recreate in simple form and also advise what error is returned.
Cheers, Andrew
Mike EghtebasDatabase and Application Developer

Commented:
or something else

If Me.fEWaste.Enabled= False Then
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
I just emulated that with no problem.
Private Sub Command7_Click()
    Me.Combo5.RowSourceType = "Value List"
    Me.Combo5.RowSource = vbNullString
    Me.Combo5.ColumnCount = 1
    Me.Combo5.ColumnWidths = "2.5 in"
    Me.Combo5.LimitToList = False
End Sub

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
>> Me.strCUST_ADDR

That IS a combo box, right ?

mx
Top Expert 2006

Commented:
The issue is rather an interesting one in my opinion. Unless your underlying data for your form changes, or the order in which your data is viewed changes, then the On_Load event will fire for the same record every time. If that's the case, why bother with an IF statement at all. Now, if you move that to Form_Current, then your code will fire on each new record, including the first.
Setting the LimitToList property shouldn't throw an error. It's something else. Maybe that field does'nt have a value during the On_Load event?
J
Aaron GreeneProgrammer

Author

Commented:
The form is unbound.  I am using the OnLoad event to load the values of the selected record to the form.  If fEWaste is true then, the user can select a limited number of addresses.  If fEWaste is false, then the user can enter in any address.  I need the form to be able to make this distinction when it is loaded.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Can you post the entire OnLoad code ... so we can see the definition of CBO, etc.

thx

mx

Can you please also advise what the error is you are receiving.
Cheers, Andrew
Aaron GreeneProgrammer

Author

Commented:
This is the OnLoad Event code and the related procedures and variables.  The error that I'm getting states that the database can't set the LimitToList property to No right now.

Private strAPPT_KEY As String

Private Sub Form_Load()
Dim CBO As ComboBox
strAPPT_KEY = Me.OpenArgs
Call procLOAD_VALUES
If Me.fEWaste = False Then
Set CBO = Me.strCUST_ADDR
With CBO
    .RowSourceType = "Value List"
    .RowSource = vbNullString
    .ColumnCount = 1
    .ColumnWidths = "2.5 in;"
    .LimitToList = False
End With
Else
Set CBO = Me.strCUST_ADDR
With CBO
    .RowSourceType = "Table/Query"
    .RowSource = "vData_Accounts_Ewaste"
    .ColumnCount = 8
    .ColumnWidths = "0 in;0 in;0 in;0 in;0 in;2.5 in;0 in;0 in;"
    .LimitToList = True
End With
End If
End Sub


Private Sub procLOAD_VALUES()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strSQL As String

Set cnt = CurrentProject.Connection

strSQL = "SELECT tblHHW.KEY_NO, tblHHW.fEWaste, tblHHW."
strSQL = strSQL & "lngEVENT_ID, tblHHW.datAPPT_DATE, tblHHW."
strSQL = strSQL & "datAPPT_TIME, tblHHW.intAPPT_NUMBER, tblHHW."
strSQL = strSQL & "strENTEREDBY, tblHHW.strACCOUNT, tblHHW."
strSQL = strSQL & "strCUST_FIRST, tblHHW.strCUST_LAST, tblHHW."
strSQL = strSQL & "strCUST_AC, tblHHW.strCUST_TEL, tblHHW."
strSQL = strSQL & "strCUST_ADDR, tblHHW.fITEM_PAINT, tblHHW."
strSQL = strSQL & "fITEM_BATTERY, tblHHW.fITEM_ANTIFREEZE, tblHHW."
strSQL = strSQL & "fITEM_OIL, tblHHW.fITEM_PESTICIDE, tblHHW."
strSQL = strSQL & "strITEM_OTHER, tblHHW.fSHOP, tblHHW.intDESKTOP, "
strSQL = strSQL & "tblHHW.intLAPTOP, tblHHW.intMONITOR, tblHHW."
strSQL = strSQL & "intMONITOR_CRT, tblHHW.intSCANNER, tblHHW."
strSQL = strSQL & "intINKJET, tblHHW.intLASERPRINTER, tblHHW."
strSQL = strSQL & "intTELEVISION, tblHHW.intOTHER_ELEC, tblHHW."
strSQL = strSQL & "fACCESSORIES, tblHHW.fVERIFIED, tblHHW."
strSQL = strSQL & "strVERIFIEDBY, tblHHW.fSIGNED FROM tblHHW WHERE ("
strSQL = strSQL & "((tblHHW.KEY_NO)=" & "'" & strAPPT_KEY & "'" & "));"

rst.Open strSQL, cnt, adOpenKeyset, adLockOptimistic

Me.fEWaste = rst.Fields("fEWaste")
Me.lngEVENT_ID.Value = rst.Fields("lngEVENT_ID").Value
Me.datAPPT_DATE.Value = rst.Fields("datAPPT_DATE").Value
Me.datAPPT_TIME.Value = rst.Fields("datAPPT_TIME").Value
Me.intAPPT_NUMBER.Value = rst.Fields("intAPPT_NUMBER").Value
Me.strENTEREDBY.Value = rst.Fields("strENTEREDBY").Value
Me.strACCOUNT.Value = rst.Fields("strACCOUNT").Value
Me.strCUST_FIRST.Value = rst.Fields("strCUST_FIRST").Value
Me.strCUST_LAST.Value = rst.Fields("strCUST_LAST").Value
Me.strCUST_AC.Value = rst.Fields("strCUST_AC").Value
Me.strCUST_TEL.Value = rst.Fields("strCUST_TEL").Value
Me.strCUST_ADDR.Value = rst.Fields("strCUST_ADDR").Value
Me.fITEM_PAINT.Value = rst.Fields("fITEM_PAINT").Value
Me.fITEM_BATTERY.Value = rst.Fields("fITEM_BATTERY").Value
Me.fITEM_ANTIFREEZE.Value = rst.Fields("fITEM_ANTIFREEZE").Value
Me.fITEM_OIL.Value = rst.Fields("fITEM_OIL").Value
Me.fITEM_PESTICIDE.Value = rst.Fields("fITEM_PESTICIDE").Value
Me.strITEM_OTHER.Value = rst.Fields("strITEM_OTHER").Value
Me.fSHOP = rst.Fields("fSHOP")
Me.intDESKTOP.Value = rst.Fields("intDESKTOP").Value
Me.intLAPTOP.Value = rst.Fields("intLAPTOP").Value
Me.intMONITOR.Value = rst.Fields("intMONITOR").Value
Me.intMONITOR_CRT.Value = rst.Fields("intMONITOR_CRT").Value
Me.intSCANNER.Value = rst.Fields("intSCANNER").Value
Me.intINKJET.Value = rst.Fields("intINKJET").Value
Me.intLASERPRINTER.Value = rst.Fields("intLASERPRINTER").Value
Me.intTELEVISION.Value = rst.Fields("intTELEVISION").Value
Me.intOTHER_ELEC.Value = rst.Fields("intOTHER_ELEC").Value
Me.fACCESSORIES = rst.Fields("fACCESSORIES")

rst.Close
cnt.Close
Set cnt = Nothing

End Sub
OK we know that you can set the limit to list property to no even if the values list has no records, therefore, it must be a conflict of the LoadEvent and the way you are loading the controls.

Try commenting out the Call procLOAD_VALUES

We need to try and isolate what is causing the problem.

Cheers, Andrew
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Just for fun, try this mod:

Private Sub Form_Load()
'  **** Dim CBO As ComboBox
strAPPT_KEY = Me.OpenArgs
Call procLOAD_VALUES
If Me.fEWaste = False Then
' ****** Set CBO = Me.strCUST_ADDR
With Me.strCUST_ADDR
    .RowSourceType = "Value List"
    .RowSource = vbNullString
    .ColumnCount = 1
    .ColumnWidths = "2.5 in;"
    .LimitToList = False
End With
Else
' *****   Set CBO = Me.strCUST_ADDR
With Me.strCUST_ADDR
    .RowSourceType = "Table/Query"
    .RowSource = "vData_Accounts_Ewaste"
    .ColumnCount = 8
    .ColumnWidths = "0 in;0 in;0 in;0 in;0 in;2.5 in;0 in;0 in;"
    .LimitToList = True
End With
End If
End Sub
Programmer
Commented:
Sorry that it has taken me a while to get back on this.  I set the bound column property before the Limit To List property, and everything worked fine.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.