A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Auto fill a field every row?



 
 
Thread Tools Display Modes
  #1  
Old August 1st, 2005, 06:44 AM
Brian
external usenet poster
 
Posts: n/a
Default Auto fill a field every row?

Here it is, step by step. I called the form Form1 and the date control on the
form YourDate for example. Change that line below to be the name of the date
field. I am assuming that it is bound to a field in the table that is a
date/time field. See my note at the bottom, though about datasheet vs.
continuous form:

1. Open your form in design view.
2. Create an unbound (no control source) text box in the form's header (this
way, it will not appear in datasheet view). Call it Date1 and set its format
to Short Date.
3. Open the form's properties using the Properties button or right-click the
form's title bar and click "Properties".
4. Click the Event tab.
5. Click inside the On Open even box until it says [Event Procedure].
6. Click the Ellipsis (...) to the right of the On Open event. This opens
the VBA window
7. Paste this underneath the Private Form_Open(Cancel As Integer):

Do While IsNull(Date1) Or Date1 = ""
Date1 = InputBox("Enter date")
Loop

The entire sub should now look like this:

Private Sub Form_Open(Cancel As Integer)
Do While IsNull(Date1) Or Date1 = ""
Date1 = InputBox("Enter date")
Loop
End Sub

8. Close the VBA window.
9. Set the default value of the YourDate box to [Forms]![Form1]![Date1]

The effect is this:

When you open the form, a box pops up asking the user to enter a date (have
the user enter it as six digits with the slashes, like: 07/31/05. It will
continue to pop up until he enters something.

The date entered becomes the default value for the YourDate field. Be sure
to explain to the user that every new row will have this number, even if
nothing else is entered; however, if the user exits the form, the newest row
that has only the YourDate filled in does not get saved (Default Value alone
does not make the row a new record; it must be changed or something else
entered before it becomes a new record.)

If the user enters something that is not a valid date, "#Error" will show up
in the YourDate field. That's only because I haven't gone through all the
additional steps to make sure that the input box is a valid date, since it
would be much easier to simply convert your form to a Continuous Form view
than to do this. If you can give up datasheet view and forcing a correct date
format is important, re-post, and we can go over simplifying things using
Continuous Form as the default view. Someone may have an easier way to do
what you want with a datasheet view, but I stay away from datasheet view and
stick with Continuous or Single form because I fell that I have more control
over what goes on in the form.

"Sapper" wrote:

I'm not a vba man, so I'm not sure as to how or where this all happens,
help?
Colin.

"Brian" wrote in message
...
Use the default value for the date field, and make it equal to something
entered once by the user. You can do it this way:

Declare Date as a variable to hold the user-input date (in the General
section at the top of the form's module)

Private Date1 As Date

Ask the user for the date when the form is opened:

Private Sub Form_Open(Cancel As Integer)
Date1 = InputBox("Enter date")
End Sub

Assign that date to the first field on all new records:

Private Sub Form_Current()
If Form.NewRecord Then YourDate = Date1
End Sub

You could even ask the user to input the date on the form that is used to
open this form, and simply make the default value of the YourDate field as
[Forms]![ThatForm]![Date1].

In Single or Continuous Form view, it would be easier to assign the value
to
a hidden text box on the form and then refer to is as [Forms]... as above
in
the default value of your date text field. However, in datasheet view,
even
an unbound hidden text box shows up (and I don't know how to hide it).


"Sapper" wrote:

TIA - Novice at work.
I have a datasheet form, which I cylce thru entering data, the first
field
is a date field which is the same date for each row, now I know I can set
up
a combobox for the first field, but that means I have to select the date
for
each cycle thru the data entry. How can I set it up so I select a date (a
combobox) at the top of the form and all data entry from there on the
first
field is filled in using the selected date, that is untill I close the
form?
Then the next time I do data entry, I can select a new date etc. I know
I'm
missing something here, but my brian refuses to kick in.
TIA, Colin






  #2  
Old August 1st, 2005, 07:35 PM
Sapper
external usenet poster
 
Posts: n/a
Default

Thx, Brian it works as you said.
Colin.

"Brian" wrote in message
...
Here it is, step by step. I called the form Form1 and the date control on
the
form YourDate for example. Change that line below to be the name of the
date
field. I am assuming that it is bound to a field in the table that is a
date/time field. See my note at the bottom, though about datasheet vs.
continuous form:

1. Open your form in design view.
2. Create an unbound (no control source) text box in the form's header
(this
way, it will not appear in datasheet view). Call it Date1 and set its
format
to Short Date.
3. Open the form's properties using the Properties button or right-click
the
form's title bar and click "Properties".
4. Click the Event tab.
5. Click inside the On Open even box until it says [Event Procedure].
6. Click the Ellipsis (...) to the right of the On Open event. This opens
the VBA window
7. Paste this underneath the Private Form_Open(Cancel As Integer):

Do While IsNull(Date1) Or Date1 = ""
Date1 = InputBox("Enter date")
Loop

The entire sub should now look like this:

Private Sub Form_Open(Cancel As Integer)
Do While IsNull(Date1) Or Date1 = ""
Date1 = InputBox("Enter date")
Loop
End Sub

8. Close the VBA window.
9. Set the default value of the YourDate box to [Forms]![Form1]![Date1]

The effect is this:

When you open the form, a box pops up asking the user to enter a date
(have
the user enter it as six digits with the slashes, like: 07/31/05. It will
continue to pop up until he enters something.

The date entered becomes the default value for the YourDate field. Be sure
to explain to the user that every new row will have this number, even if
nothing else is entered; however, if the user exits the form, the newest
row
that has only the YourDate filled in does not get saved (Default Value
alone
does not make the row a new record; it must be changed or something else
entered before it becomes a new record.)

If the user enters something that is not a valid date, "#Error" will show
up
in the YourDate field. That's only because I haven't gone through all the
additional steps to make sure that the input box is a valid date, since it
would be much easier to simply convert your form to a Continuous Form view
than to do this. If you can give up datasheet view and forcing a correct
date
format is important, re-post, and we can go over simplifying things using
Continuous Form as the default view. Someone may have an easier way to do
what you want with a datasheet view, but I stay away from datasheet view
and
stick with Continuous or Single form because I fell that I have more
control
over what goes on in the form.

"Sapper" wrote:

I'm not a vba man, so I'm not sure as to how or where this all happens,
help?
Colin.

"Brian" wrote in message
...
Use the default value for the date field, and make it equal to
something
entered once by the user. You can do it this way:

Declare Date as a variable to hold the user-input date (in the General
section at the top of the form's module)

Private Date1 As Date

Ask the user for the date when the form is opened:

Private Sub Form_Open(Cancel As Integer)
Date1 = InputBox("Enter date")
End Sub

Assign that date to the first field on all new records:

Private Sub Form_Current()
If Form.NewRecord Then YourDate = Date1
End Sub

You could even ask the user to input the date on the form that is used
to
open this form, and simply make the default value of the YourDate field
as
[Forms]![ThatForm]![Date1].

In Single or Continuous Form view, it would be easier to assign the
value
to
a hidden text box on the form and then refer to is as [Forms]... as
above
in
the default value of your date text field. However, in datasheet view,
even
an unbound hidden text box shows up (and I don't know how to hide it).


"Sapper" wrote:

TIA - Novice at work.
I have a datasheet form, which I cylce thru entering data, the first
field
is a date field which is the same date for each row, now I know I can
set
up
a combobox for the first field, but that means I have to select the
date
for
each cycle thru the data entry. How can I set it up so I select a date
(a
combobox) at the top of the form and all data entry from there on the
first
field is filled in using the selected date, that is untill I close the
form?
Then the next time I do data entry, I can select a new date etc. I
know
I'm
missing something here, but my brian refuses to kick in.
TIA, Colin








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I auto fill a field based on info input into another field. Brenda F Using Forms 1 April 20th, 2005 03:29 AM
Follow up Question – Convert one field into three fields Doug General Discussion 2 April 9th, 2005 10:21 PM
Syntax needed to get needed reports Frank Lueder New Users 15 January 6th, 2005 09:39 AM
Auto fill field in a table Brent General Discussion 2 November 12th, 2004 09:52 PM
auto fill based on another field bill Setting Up & Running Reports 3 July 15th, 2004 06:25 PM


All times are GMT +1. The time now is 06:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.