Different way to Autofill previous record

[Guest]

Below are instructions to autofill a simplier way than what is posted on
Microsoft Knowledge Base Article ID : 210236.

My problem is as I listed on a earlier post the Microsoft way I get all
fields autofilled (please see earlier post AUTOFILL PREVIOUS RECORD REPOST)
or this listed below which autofills proper field but the result is always 0
ie.. CustomerID 12 result in new record 0.

Hopefully somebody knows the answer to one of these delima's
TIA

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.

 

[Guest]

You could achieve (almost) the same result by setting the DefaultValue
property of the control:

Me.BoxNum.DefaultValue = Chr(34) & Me.BoxNum & Chr(34)

If you want this to be done after a new record is entered put it in the
Form's AfterInsert event procedure, if you want it after any record is
changed put it in the form's AfterUpdate event procedure. Note that the
DefaultValue property is a string expression regardless of the data type of
the field, hence the Chr(34) quotes characters.

The advantage of setting the DefaultValue property of a control is that it
does not Dirty the form when the user goes to a new record as setting its
Value property does, so the user can back out of entering the new record by
pressing Esc.

With your method you could achieve the same with:

Private Sub Form_Current()

Me.BoxNum.DefaultValue = Chr(34) & current_boxnum & Chr(34)

End Sub

If you declared the current_boxnum variable as Public in a standard module
rather than the form's Class module then it would be persistent for the
current session when the form is closed so if the form is opened again within
the session the value will be set.

Another way of dong this sort of thing is to create a custom property of the
form and use this to hold the value. This is also persistent, in this case
even if the application is closed and reopened. This does require quite a
bit more code, however. Probably the simplest ay to make the value
persistent between sessions, though, is to store it in a separate table. You
can then look it up with the DLookup function.