Solved

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

Posted on 2004-10-06
9
962 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

830 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