Autofill field

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

Guest

I have posed this question before and I tried the answer that was given and
it didn't work. I have a form that goes back to a single table. The form
is used for data entry and I want to simplify the process. I have a field on
the form call "Box #" I don't want the person who is doing data entry to
have to type the box # for each item she puts in. I would like the field to
autofill with the data the was entered in the previous record. So every
time time a new recorded is started the "box #" field will have the data the
was entered from the last record. Now when a NEW box # is entered into the
field I want it to keep autofilling with that one until it is changed.
Please help! I am an Access novice and I am not familiar with Macros, VB
script, or SQL statements.
 
The simplest way I can think for you to do this is with a little bit of VBA
code. You will need to put the form in design mode. Click on the Code
button on the tool bar. Just under the words “Option Compare Database†add
this line:
Public current_boxnum as integer (or long or string depending upon the field
type Box # is)

Then go back to the form in design mode. Click on the Box # field and then
click on the Properties button on the tool bar. Select the Event tab. Click
on the field next to the “After Update†event. Then click on the button with
the three dots that appears to the right of this field. A little window pops
up, choose “Code Builder.†This brings you to the Visual Basic Window again.
It will have automatically generated something similar to what you see below
but it will actually have the name of your Box # field instead of BoxNum.

Private Sub BoxNum_AfterUpdate()

End Sub

Put the following code in so it now looks like this:

Private Sub BoxNum_AfterUpdate()

current_boxnum = BoxNum.value

End Sub

What the above does is saves a copy of the box # recently entered in the
form in a public variable that will have data for as long as the form is
open. When the form closes, the variable is erased. So, this procedure will
require the user to enter in the box number to the form each time the form is
opened for use.

Next, you will need to tell the form to autofill the Box # each time the
user is in a new record. So, you will need to go back to the form in design
mode, click on the square in the top left-hand side of the form window (to
the left of the ruler above the form header bar). If the Properties window
is not open still, click on the Properties button again. Make sure the Event
tab is clicked. Find the form’s On Current event, click on the field next to
it, then on the button with three dots, and choose “Code Builder.†Then
enter in the following code:

Private Sub Form_Current()

if me.newrecord and not isnull(current_boxnum) then
BoxNum.value = current_boxnum
end if

End Sub

What the above does is check to see if the user is in a new records (because
you don’t want to change the data in an existing record) and also check to
make sure that a value has been saved in the current_boxnum variable. Given
these two situations, you will then want to have the BoxNum value to be the
value that is being held in the current_boxnum variable.

Well, I think that should work. If there are any errors let me know and
I’ll see if I can trouble shoot it with you.
 
It doesn't work completely. Does it matter that I have an input mask on the
field so it won't get rid of the Zero that the numbers begin with and they
field type is "Text"?
It doesn't replicate the number that is entered. It only replicates the 0
from the input mask. Should I change the Line " Public current_boxnum as
integer" to "Public current_buxnum as text"?

I appreciate your help your example is better than any I have received with
this problem.
 
It is working now. I changed my data type to "Long Integer" and edited the
public statement to "as Long". However, I need my box numbers to have a 0
in the front. example "083838". The input mask I had made automatically put
the 0 in. Now it no longer does that. Is there anyway to do this and keep
the zero in the front? I greatly appreciate your help.
 
Try putting adding this line in the Form's On Current code, after the code
you already have there:

BoxNum.Format = "000000"

Use the number of zeros the same way you would for an input mask.

I think that will work. I tried simply putting this format in the
properties of the field but that didn't work. I think it's because assigning
the value in using VBA code doesn't trigger the field to use the formatting
that is stored in the field's properties. So, after you use the code to
manually tell that field what it's value is, this next line of code then
triggers the field to think about the formatting and format it the way you
want it to.

I probably won't be checking my e-mail again until tomorrow but let me know
how it goes.
 
It worked perfectly. Thank yo

Andrea M said:
Try putting adding this line in the Form's On Current code, after the code
you already have there:

BoxNum.Format = "000000"

Use the number of zeros the same way you would for an input mask.

I think that will work. I tried simply putting this format in the
properties of the field but that didn't work. I think it's because assigning
the value in using VBA code doesn't trigger the field to use the formatting
that is stored in the field's properties. So, after you use the code to
manually tell that field what it's value is, this next line of code then
triggers the field to think about the formatting and format it the way you
want it to.

I probably won't be checking my e-mail again until tomorrow but let me know
how it goes.
Click to expand...
 
Back
Top