Default Values in Unbounded form

[Stu]

Using Access 2003 and an unbounded form/fields. The form collects parameters
for reports and has a Clear button. I want the Clear button to put in
default values into text/list fields as when the form is opened. I've tried
looping through the fields and using the statement "CTL=CTL.DEFAULTVALUE"
(and variations thereof) but the date field default values display with #
before and after the date and text/list boxes display quotes around the
default values. I tried closing the form and re-opening and that works fine,
but isn't what I want to do. Can someone provide a code snippet to put in
default values into fields without quotes and pound signs for dates?

 

[J_Goddard via AccessMonster.com]

Hi -

Try using the CStr function -

CTL=Cstr(CTL.DEFAULTVALUE )

John

 

[Stu]

Nope, I think I tried that before. Further I would need to test each form
field for data type.

 

[J_Goddard via AccessMonster.com]

Form controls don't have a data type, so you can't test for it. CStr can
accept any "data type" as a parameter.

If you test Cstr If in the immediate window (^G), you will see that

Cstr("Test") --> Test
Cstr(50) --> 50
Cstr(#01-29-2008#) --> 2008-01-29 (the format will match your date/time
settings)
Cstr(Date()) --> 2008-01-29

You will get an error of you try to reference the defaultvalue property of a
control that does not have that property, such as a label. If you loop
through all the controls and ignore the error with On Error Resume Next, it
should work fine:

Dim Ctl as control
On error Resume Next
for each ctl in me.controls
ctl = cstr(ctl.defaultvalue)
next

John

Nope, I think I tried that before. Further I would need to test each form
field for data type.

Hi -

[quoted text clipped - 13 lines]

 

[Stu]

The immediate window aside, I pasted your snippet into my clear routine. The
combo box upon startup contains: (select)
After running your snippit the combo box contains: "(select)"
The default value is: "(select)"
Likewise with dates, they contain the #'s after running the snippet.
Did you run a test? If so, then I need to figure out what the difference is.

Form controls don't have a data type, so you can't test for it. CStr can
accept any "data type" as a parameter.

If you test Cstr If in the immediate window (^G), you will see that

Cstr("Test") --> Test
Cstr(50) --> 50
Cstr(#01-29-2008#) --> 2008-01-29 (the format will match your date/time
settings)
Cstr(Date()) --> 2008-01-29

You will get an error of you try to reference the defaultvalue property of a
control that does not have that property, such as a label. If you loop
through all the controls and ignore the error with On Error Resume Next, it
should work fine:

Dim Ctl as control
On error Resume Next
for each ctl in me.controls
ctl = cstr(ctl.defaultvalue)
next

John

Nope, I think I tried that before. Further I would need to test each form
field for data type.

Hi -

[quoted text clipped - 13 lines]

but isn't what I want to do. Can someone provide a code snippet to put in
default values into fields without quotes and pound signs for dates?

 

[J_Goddard via AccessMonster.com]

Hi -

Sorry !! I didn't test it (Embarrass)

However, with a little hunting around and a bit of testing, I came up with
this:

Dim Ctl As Control
On Error Resume Next
For Each Ctl In Me.Controls
If Left(Ctl.DefaultValue, 1) = "=" Then
Ctl = Eval(Mid(Ctl.DefaultValue, 2))
Else
Ctl = Eval(Ctl.DefaultValue)
End If
Next

which uses Eval instead of CStr. This will cover the most commonly used
Default Value settings.

I tested it with 10, "Example", #2008-12-23" and =Now() as default
values and it worked fine.

John

The immediate window aside, I pasted your snippet into my clear routine. The
combo box upon startup contains: (select)
After running your snippit the combo box contains: "(select)"
The default value is: "(select)"
Likewise with dates, they contain the #'s after running the snippet.
Did you run a test? If so, then I need to figure out what the difference is.

Form controls don't have a data type, so you can't test for it. CStr can
accept any "data type" as a parameter.

[quoted text clipped - 28 lines]