Solved

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

Posted on 2004-10-06
9
959 Views
Last Modified: 2012-06-27
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
Comment
Question by:majnun
  • 4
  • 3
9 Comments
 

Author Comment

by:majnun
ID: 12242260
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12242280
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
 

Author Comment

by:majnun
ID: 12242702
The format property is blank.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12242809
In that case, what is the datatype of the field to which the second combo is bound?
0
 

Author Comment

by:majnun
ID: 12248760
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12248989
Hmm - how about clearing it first with:

Me!MyComboBox=Null

before you change the rowsource?
0
 

Author Comment

by:majnun
ID: 12249558
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
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12898236
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question