Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-10-06
9
Medium Priority
?
986 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

926 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