Make Code to Set Default On Open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to make a code that will prompt the user (dialog box possibly)
to enter a date when they open the form and then put that date as the default
value property in a textbox named Year. Any help would be appreciated.
 
Tandy said:
I would like to make a code that will prompt the user (dialog box
possibly) to enter a date when they open the form and then put that
date as the default value property in a textbox named Year. Any help
would be appreciated.
Click to expand...

Here's some "air code":

'----- start of code -----
Private Sub Form_Open(Cancel As Integer)

Dim strDefault As String
Dim fGotDefault As Boolean

Do
strDefault = InputBox("Enter the default date:")
If Len(strDefault) = 0 Then
fGotDefault = True
Else
If IsDate(strDefault) Then
fGotDefault = True
End If
End If
Loop Until fGotDefault

If Len(strDefault) > 0 Then
Me!Year.DefaultValue = "#" & strDefault & "#"
End If

End Sub
'----- end of code -----

It's not clear to me, though, whether a field named "Year" should be
having a default value that is a full date. Also, be advised that
"Year" is not a good name for a field or control, because there's a
built-in function of that name.
 
Dirk,

I used your formula, but it would repeatedly ask my to set the default
date. By the way, I would like the default value to be a year for this form,
such as 2006. However, I would also like to know how to do this for a long
date, such as 1/1/2006.
Thank you for your help!
 
Dirk,

Even when I get the dialog box to stop asking me for the date, when I
set the form Data Entry property to Yes, it doesn't enter it anyway. If I do
not set the Data Entry property to Yes, it will not allow me to change any of
the data.

Sorry for the inconvience. I really appreciate your help!
 
Tandy said:
Dirk,

I used your formula, but it would repeatedly ask my to set the
default date. By the way, I would like the default value to be a year
for this form, such as 2006. However, I would also like to know how
to do this for a long date, such as 1/1/2006.
Click to expand...

I expect you were trying to enter a year, like 2006, but I had the
function set up to reject any entry that wasn't a whole date, because
you said you wanted to set a default date, not a default year. Here's a
revised version that works for me to require entry of a year as the
default:

'----- start of code -----
Private Sub Form_Open(Cancel As Integer)

Dim strDefault As String
Dim fGotDefault As Boolean

Do
strDefault = InputBox("Enter the default year:")
If Len(strDefault) = 0 Then
fGotDefault = True
Else
If IsNumeric(strDefault) Then
fGotDefault = True
Else
MsgBox "That's not a valid year!"
End If
End If
Loop Until fGotDefault

If Len(strDefault) > 0 Then
Me!Year.DefaultValue = strDefault
End If

End Sub

'----- end of code -----

Note that this version doesn't really do any editing of the year value
entered except require that it be numeric. You might well want to do a
more comprehensive edit.

The code I originally posted should work for a real date field, though
now that I look at it again I see that I should have put in an error
message if the user's entry was not a valid date.
 
Tandy said:
Dirk,

Even when I get the dialog box to stop asking me for the date,
when I set the form Data Entry property to Yes, it doesn't enter it
anyway. If I do not set the Data Entry property to Yes, it will not
allow me to change any of the data.

Sorry for the inconvience. I really appreciate your help!
Click to expand...

I'm not sure what you're saying here, but try the revision I posted and
then if it's not behaving the way you want, we'll work it out.
 
Dirk Goldgar,

Hi! Thank you for the help, it is exactly what I wanted. I would also
like to know what you change to have it enter a short date (1/1/2006) and a
month and year date (1/2006). Thank you so much for your time and effort. I
really appreciate it!
 
Tandy said:
Dirk Goldgar,

Hi! Thank you for the help, it is exactly what I wanted. I
would also like to know what you change to have it enter a short date
(1/1/2006) and a month and year date (1/2006). Thank you so much for
your time and effort. I really appreciate it!
Click to expand...

Assuming that the field that will hold these default values is a
date/time field, the *simple* code for both would be something like
this:

'----- start of code -----
Private Sub Form_Open(Cancel As Integer)

Dim strDefault As String
Dim fGotDefault As Boolean

Do
strDefault = InputBox("Enter the default date:")
If Len(strDefault) = 0 Then
fGotDefault = True
Else
If IsDate(strDefault) Then
fGotDefault = True
Else
MsgBox "That's not a valid date!"
End If
End If
Loop Until fGotDefault

If Len(strDefault) > 0 Then
Me!YourDateField.DefaultValue = "#" & strDefault & "#"
End If

End Sub
'----- end of code -----

That's using "YourDateField" as the name of the field.

As I said, this doesn't make any distinction between a "short date" and
a "month and year" date. That's because there *is* not such thing as a
"month and year" date, internally -- at least, not if you store the
value in a date/time field. The date data type always includes month,
day, and year, even if the day isn't displayed due to an input mask or
format property.

In the code above, I took the easy way of validating the user's entry:
I just said that it had to be interpretable as a date. That's what the
IsDate() function does; it tells whether the argument that was passed
to it can be understood as a date. Using this as the edit, both
"1/1/2006" and "1/2006" can both be entered, and both will be accepted
and understood as the same date, as would "Jan 1, 2006". Beware,
though: depending on the regional date settings, "1/5/2006" may be
interpreted as January 5th on one system, and as May 1st on another.
Also, if the user enters "1/06" instead of "1/2006", Access will
interpret that as January 6th of the current year, not as January 1st of
2006.

If you wanted to pin down the user's default date entry with more
precise editing, to eliminate any ambiguity and require some particular
format, that would be possible. It wold take more complicated code,
though.
 
Dirk,

Thank you again! The code you gave me is perfect for the database I am
working on. Maybe you can help me with a few other obstacles I am facing.
This is a questions I posted in the Access Database Forms Discussion Group:

"Hi! I am working on a data entry form to enter employees and their
annual salaries for the year. The Record Source is a table called Annual
Salary with the fields Employee ID, Salary Year and Annual Salary. I also
have a table called Employees with the fields Employee ID, Last Name, First
Name and Middle Initial. Right now I have a form On Open event procedure that
asks for the year I am entering and sets it as the default for my Salary Year
textbox. I also have a combo box in my form that drops down to list the
Employee ID in one column and "Last Name, First Name Middle Initial" in the
other. However, I would like for the form to somehow automatically generate a
continuous data entry form that lists each Employee ID and "Last Name, First
Name Middle Initial", and their Annual Salary for the year before the year
entered for the On Open event procedure (2005 if I enter 2006). This is what
I am thinking:

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

Salary Year: (Automatic, 2006)
Employee ID/Name: Employee A (2005)
Annual Salary: (Shows default, but can be changed, 2005)

All help will be greatly appreciated!"

I would really like to keep working with you if possible, but if this is
outside of coding I understand!
 
Tandy said:
Dirk,

Thank you again! The code you gave me is perfect for the
database I am working on. Maybe you can help me with a few other
obstacles I am facing. This is a questions I posted in the Access
Database Forms Discussion Group:
Click to expand...
[snip]

I'll look at that thread. It's best not to piggy-back one thread on
another.
 
Back
Top