Get Last

[Rusty]

I need to simplify data entry for my cowboys. Rather than change the defalt
entries for a field before we start a run of data collection. I would like to
be able to enter a piece of data in a given field and have the next new
record retrieve the data from the last saved record, some several specific
fields.

In other words. I havae a form/ table with 10 fields. Only 3 fields revieve
unique data. The other 7 fields will remain the same. However rather than
re-typing the data to each field the form should be able to retrieve the data
for those specific fields from the last record.

A "Get LAST" function wouild be great.

 

[Al Camp]

Rusty,
You can use the AfterUpdate event of each of the 7 fields to change the Default Value
for each field "on the fly". Those values will remain in effect until a new value is
entered into the respective fields.
For Text...
Private Sub Field1_AfterUpdate()
Field1.DefaultValue = "'" & [Field1] & "'"
End Sub

For Numeric...
Private Sub Field1_AfterUpdate()
Field1.DefaultValue = Field1
End Sub

Each time you start a new "data session", once one record has been manually filled, the
default will be set for all the others in that session.

 

[Steve Schapel]

Rusty,

This probably indicates a flaw in your database design. Probably these
7 fields with repeating data in reality do represent an entity on the
"one" side of a one-to-many relationship, and as such should be in a
separate table.

Anyway, one thing you can do here is to put code on the After Update
event of each of the 7 controls, along these lines...
If IsNull(Me.NameOfField) Then
' do nothing
Else
Me.NameOfField.DefaultValue = Me.NameOfField
End If

Or, for a text data type field...
If IsNull(Me.NameOfField) Then
' do nothing
Else
Me.NameOfField.DefaultValue = """" & Me.NameOfField & """"
End If

 

[Steve Schapel]

Rusty,

I just realised you are the same person I gave the same answer to a
similar question a couple of weeks ago. Don't you like what I (and Al
Camp) are suggesting here, for some reason?

 

[Rusty]

I'm pretty green at this stuff, and I'm afraid you are assuming I can read
between some of the assumptions in your code. I thought I typed everything as
you writ, but can't get it to work.

Each record is a different animal with a 2 unique Id's. But a bunch of
animals go to the same "Pen_No", come from the same "Manifest_No" and belong
to the same "Lot_No" and are inducted on the same "Date".

Maybe this will expalin my delema better. Thanks for your patience.

 

[G. Vaught]

Why not set the default value for those 7 fields at the form level to the
most likely value and then your cowboys only have to change it when it is
different.


Hav

 

[Steve Schapel]

Rusty,

Ok, well, let's just try it with one field for a start. Let's say
Lot_No. Ok, in design view of your form, select the Lot_No textbox, and
view the Properties. Go to the After Update property, and enter [Event
Procedure], and then click the little button with the ellipsis [...] to
the right. This will open the VB Editor window, and the cursor should
already be placed between lines that look like this...
Private Sub Lot_No_AfterUpdate()

End Sub

Right, is Lot_No a text data type or a number data type? If it's a
number data type, put this information in there, so it ends up looking
like this...
Private Sub Lot_No_AfterUpdate()
Me.Lot_No.DefaultValue = Me.Lot_No
End Sub

However, if it's a text data type, put this information in there, so it
ends up looking like this...
Private Sub Lot_No_AfterUpdate()
Me.Lot_No.DefaultValue = """" & Me.Lot_No & """"
End Sub

Close out of there, save the form, open in form view, and give it a try.
Let us know how it goes. You will need to enter an initial record.
Should be that after you enter a record, and then go to a second new
record, the Lot_No for the second record will show the same as the first
by default.

 

[Rusty]

I just keep getting "Syntax Errors"? Is there a space on both sids of the = ,
and & etc.

Sorry for the complications. It is a text field.
--
Rusty

Rusty,

Ok, well, let's just try it with one field for a start. Let's say
Lot_No. Ok, in design view of your form, select the Lot_No textbox, and
view the Properties. Go to the After Update property, and enter [Event
Procedure], and then click the little button with the ellipsis [...] to
the right. This will open the VB Editor window, and the cursor should
already be placed between lines that look like this...
Private Sub Lot_No_AfterUpdate()

End Sub

Right, is Lot_No a text data type or a number data type? If it's a
number data type, put this information in there, so it ends up looking
like this...
Private Sub Lot_No_AfterUpdate()
Me.Lot_No.DefaultValue = Me.Lot_No
End Sub

However, if it's a text data type, put this information in there, so it
ends up looking like this...
Private Sub Lot_No_AfterUpdate()
Me.Lot_No.DefaultValue = """" & Me.Lot_No & """"
End Sub

Close out of there, save the form, open in form view, and give it a try.
Let us know how it goes. You will need to enter an initial record.
Should be that after you enter a record, and then go to a second new
record, the Lot_No for the second record will show the same as the first
by default.

--
Steve Schapel, Microsoft Access MVP

I'm pretty green at this stuff, and I'm afraid you are assuming I can read
between some of the assumptions in your code. I thought I typed everything as
you writ, but can't get it to work.

Each record is a different animal with a 2 unique Id's. But a bunch of
animals go to the same "Pen_No", come from the same "Manifest_No" and belong
to the same "Lot_No" and are inducted on the same "Date".

Maybe this will expalin my delema better. Thanks for your patience.

 

[Rusty]

daaa - I copied and pasted you line and it WORKED. I'd buy you a steak
dinner tommorrow if I could. Thank you, Thank you!!

 

[Steve Schapel]

Rusty,

I don't think it matters whether or not there are spaces around the = in
fact I think if you type it in without spaces they will automatically be
put in for you when you finish the entry in the code. However, you
definitly need spaces around the & .
The """" is 4 double quotes "
What is the exact wording of the error message you are getting, and when
is the error happening?

 

[Steve Schapel]

What Pen_No would that steak be out of, Rusty?

Well, very happy that you got it to work. Excellent.

Now, I notice you have a date field there too. First of all, 'date' is
a Reserved Word (i.e. has a special meaning) in Access, and as such
should not be used as the name of a field or control, so I would
recommend changing this if you can. And if you are doing the default
value thing with it, there is another consideration. I think you will
need either of these...

Me.YourDateField.DefaultValue = "#" & Me.YourDateField & "#"

or....
Me.YourDateField.DefaultValue = CLng(Me.YourDateField)

 

[Rusty]

The date is always "today" so in the table, under the general tab, I typed
the default value "Date()". And that works.
However I would rather have the form open up the calander so I could clikc
the right date and then have it be re-entered with each new blank form?