Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1013
  • Last Modified:

VBA for Access Form: dynamically setting combobox rowSource to a date field causes error

I have a combobox that when it is changed, dynamically sets the rowsource for a second combobox. Everything works fine, except when the second combobox has a bound value that is a date. If i happen to make a choice in the first combobox that sets the second combobox's bound value to something other than a date and then change the second combobox's rowsource to a date value I get an error message. Yet,  if the first choice I make in the first combobox sets the second combobox to a date,  and then I change the bound of the second combobox  to any thing else I don't ever get the error message, even when I change it to a date.

In other words, as long as I dynamically set the combobox to a date field, it doesn't matter what subsequent values are entered into it, but if I dynamically set the combobox to a string or number field, it gives me an error when I try to set the combobox to a date field. The error message:

"The value you entered isn't valid for this field.
For example yoy may have entered text in a numeric field or a number that is larger than the FieldSize setting permits"

Here is my code... note that Case "Program ID" binds an integer to the rowSources bound column, Case "IBD" binds a date and Case "Shell" binds a string.

Private Sub cbox_Lookup_By_AfterUpdate()
Dim cbox As ComboBox
Set cbox = cbox_Lookup_Value
       
cbox.RowSourceType = "Table/Query"
       
Select Case cbox_Lookup_By
    Case "Program ID"
        cbox.ColumnCount = 1
        cbox.BoundColumn = 1
        cbox.ColumnWidths = "2"""
        cbox.rowsource = "SELECT DISTINCT [Program ID] FROM [Program Logs]"
    Case "IBD"
        cbox.ColumnCount = 2
        cbox.BoundColumn = 1
        cbox.ColumnWidths = "0;2"""
        cbox_Lookup_Value.rowsource = "SELECT DISTINCT [IBD], Format([IBD],""mm/dd/yyyy"") AS Expr1 FROM [Program Logs]"
   
    Case "Shell"
        cbox.ColumnCount = 1
        cbox.BoundColumn = 1
        cbox.ColumnWidths = "2"""
        cbox.rowsource = "SELECT DISTINCT [Shell] FROM [Program Logs]"
       
End Select

End Sub


 I really like to use dynamicly bound comboboxes to set up values for processing records, etc. and this problem continually plagues me. I want to be able to change a combobox's rowSource binding from strings, to integers, to dates, and visa versa, and be able to select them without this error.

Thank you for any help!
0
majnun
Asked:
majnun
  • 4
  • 3
1 Solution
 
majnunAuthor Commented:
I've been able to get around it by specifying the following properties on the form before "run time":

RowsourceType = "Field List"
RowSource = #1/1/2004#;#1/1/2004

Why does this work? Is there a way I can avoid this monkey business with setting form control properties in the designer when I have code to change it anyway?

Anyone have any thoughts as to why this is such a problem, and any other less "vulgar" solutions?
0
 
shanesuebsahakarnCommented:
Make sure that you haven't set the second combo's Format property to a date-type format. If you have, then you will need to unset it at the same time as changing the rowsource.
0
 
majnunAuthor Commented:
The format property is blank.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
shanesuebsahakarnCommented:
In that case, what is the datatype of the field to which the second combo is bound?
0
 
majnunAuthor Commented:
That's just it... it is unbound, and I set the rowsource property dynamically through VBA... if I first set it to a date, then to a string or integer, everything works fine, but it i set it first to a string or integer, and then to a date, it gives me an error.

I'm looking for a way to dissociate the combobox's "memory" of the previously bound field type, which I would have thought would have been accomplished by changing the rowsource, and it is odd that if I do a date field first then it doesn't matter what type it gets switched to later, but it is something other than a date field first then i get error after switching it to a date field.
0
 
shanesuebsahakarnCommented:
Hmm - how about clearing it first with:

Me!MyComboBox=Null

before you change the rowsource?
0
 
majnunAuthor Commented:
Well, now everything seems to be working, without the work around I needed earlier, and without needing the clearing with Null...

grrr...

I don't like not knowing why something didn't work... maybe there was some value bound prior to the execution of the code that changed the rowsource, but I didn't see it (sometimes dynamically set data gets "saved" to the form during the debugging process I've found)...

Let me see if I can reproduce the problem.

Thanks
0
 
moduloCommented:
PAQed with points refunded (500)

modulo
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now