Solved

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

Posted on 2004-10-06
9
968 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

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.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

628 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