Set ComboBox Limit to List propery on form load

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.
Aaron GreeneProgrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

If Me.fEWaste.Enabled= False Then
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
>> Me.strCUST_ADDR

That IS a combo box, right ?

mx
0
jefftwilleyCommented:
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
0
Aaron GreeneProgrammerAuthor 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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you post the entire OnLoad code ... so we can see the definition of CBO, etc.

thx

mx

0
TextReportCommented:
Can you please also advise what the error is you are receiving.
Cheers, Andrew
0
Aaron GreeneProgrammerAuthor 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
0
TextReportCommented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
Aaron GreeneProgrammerAuthor 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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.