Default Value from Last Record

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

Guest

Hello,

I found something like this the other day doing a search, but I can't seem
to find it again.

I have a very simple database--one table, one form, and a few reports.
On the form, there are a few fields that I would like to default in the value
from the last record. For example, date, shift, product name, and lot,
usually stay the same for each record entered during a twelve hour period.
How can I get those fields in the form to default in from the last record so
the operators don't have to keep typing them in, but they can still change
them on a new record if they need to?

I'm not familiar with editing code in Access, but I'm not afraid to try
it! I've found a few lines of code in this forum that may help, but I'm not
sure exactly where to put it.

Thank you in advance,
Sharon
 
Hi, Sharon.

Set the DefaultValue property of each control in its AfterUpdate event
procedure. If you've never coded before, click on a control in Design view.
Display the Properties with View, Properties. Click on the Event tab and put
the cursor in the AfterUpdate property. Then click the ellipsis to the right
of the data window, and choose Code Builder if necessary.

Access will create a shell of a procedure for you, which will execute each
time the value in this control is changed. If it is a text control, insert
the following code between the Private Sub and End Sub lines, substituting
the name of your control for "MyControl":

Me![MyControl].DefaultValue = "'" & Me![MyControl] & "'"

If it is a numeric control, omit the quotes:

Me![MyControl].DefaultValue = Me![MyControl]

Save the procedure and the form. That's it.

Hope that helps.
Sprinks
 
Sprinks,

It worked great! Thank you!

Next question is similar. I'd like the default for another field to be one
greater than the previous record. Is that possible? It is a text field
though, because I need it to be 01, 02, 03, etc, and as a numeric field, it
came out as 1, 2, 3, etc.

Thank you,
Sharon


Sprinks said:
Hi, Sharon.

Set the DefaultValue property of each control in its AfterUpdate event
procedure. If you've never coded before, click on a control in Design view.
Display the Properties with View, Properties. Click on the Event tab and put
the cursor in the AfterUpdate property. Then click the ellipsis to the right
of the data window, and choose Code Builder if necessary.

Access will create a shell of a procedure for you, which will execute each
time the value in this control is changed. If it is a text control, insert
the following code between the Private Sub and End Sub lines, substituting
the name of your control for "MyControl":

Me![MyControl].DefaultValue = "'" & Me![MyControl] & "'"

If it is a numeric control, omit the quotes:

Me![MyControl].DefaultValue = Me![MyControl]

Save the procedure and the form. That's it.

Hope that helps.
Sprinks

Sharon said:
Hello,

I found something like this the other day doing a search, but I can't seem
to find it again.

I have a very simple database--one table, one form, and a few reports.
On the form, there are a few fields that I would like to default in the value
from the last record. For example, date, shift, product name, and lot,
usually stay the same for each record entered during a twelve hour period.
How can I get those fields in the form to default in from the last record so
the operators don't have to keep typing them in, but they can still change
them on a new record if they need to?

I'm not familiar with editing code in Access, but I'm not afraid to try
it! I've found a few lines of code in this forum that may help, but I'm not
sure exactly where to put it.

Thank you in advance,
Sharon
Click to expand...
Click to expand...
 
Hi, Sharon.

If its really a number, can you simply use an AutoNumber field, and format
it with a custom function?

Function FormatMyNumber(intNumber as Integer) as String
If intNumber<10 Then
FormatMyNumber = "0" & LTrim(Str(intNumber))
Else
FormatMyNumber = LTrim(Str(intNumber))
End If
End Function

Then, display the result in a textbox:

=FormatMyNumber([MyAutoNumberField])

Hope that helps.
Sprinks

Sharon said:
Sprinks,

It worked great! Thank you!

Next question is similar. I'd like the default for another field to be one
greater than the previous record. Is that possible? It is a text field
though, because I need it to be 01, 02, 03, etc, and as a numeric field, it
came out as 1, 2, 3, etc.

Thank you,
Sharon


Sprinks said:
Hi, Sharon.

Set the DefaultValue property of each control in its AfterUpdate event
procedure. If you've never coded before, click on a control in Design view.
Display the Properties with View, Properties. Click on the Event tab and put
the cursor in the AfterUpdate property. Then click the ellipsis to the right
of the data window, and choose Code Builder if necessary.

Access will create a shell of a procedure for you, which will execute each
time the value in this control is changed. If it is a text control, insert
the following code between the Private Sub and End Sub lines, substituting
the name of your control for "MyControl":

Me![MyControl].DefaultValue = "'" & Me![MyControl] & "'"

If it is a numeric control, omit the quotes:

Me![MyControl].DefaultValue = Me![MyControl]

Save the procedure and the form. That's it.

Hope that helps.
Sprinks

Sharon said:
Hello,

I found something like this the other day doing a search, but I can't seem
to find it again.

I have a very simple database--one table, one form, and a few reports.
On the form, there are a few fields that I would like to default in the value
from the last record. For example, date, shift, product name, and lot,
usually stay the same for each record entered during a twelve hour period.
How can I get those fields in the form to default in from the last record so
the operators don't have to keep typing them in, but they can still change
them on a new record if they need to?

I'm not familiar with editing code in Access, but I'm not afraid to try
it! I've found a few lines of code in this forum that may help, but I'm not
sure exactly where to put it.

Thank you in advance,
Sharon
Click to expand...
Click to expand...
Click to expand...
 
Sprinks,

I didn't use an auto-number because I only want it to default the next
number, but still give them the ability to change it. If they switch
products or lot number, the roll number will start over at 01 again. Plus, I
wasn't sure if for example, they start entering a record, and decide they
entered a mistake, and delete the record, if they'd still be able to use that
number again.

Thank you,
Sharon


Sprinks said:
Hi, Sharon.

If its really a number, can you simply use an AutoNumber field, and format
it with a custom function?

Function FormatMyNumber(intNumber as Integer) as String
If intNumber<10 Then
FormatMyNumber = "0" & LTrim(Str(intNumber))
Else
FormatMyNumber = LTrim(Str(intNumber))
End If
End Function

Then, display the result in a textbox:

=FormatMyNumber([MyAutoNumberField])

Hope that helps.
Sprinks

Sharon said:
Sprinks,

It worked great! Thank you!

Next question is similar. I'd like the default for another field to be one
greater than the previous record. Is that possible? It is a text field
though, because I need it to be 01, 02, 03, etc, and as a numeric field, it
came out as 1, 2, 3, etc.

Thank you,
Sharon


Sprinks said:
Hi, Sharon.

Set the DefaultValue property of each control in its AfterUpdate event
procedure. If you've never coded before, click on a control in Design view.
Display the Properties with View, Properties. Click on the Event tab and put
the cursor in the AfterUpdate property. Then click the ellipsis to the right
of the data window, and choose Code Builder if necessary.

Access will create a shell of a procedure for you, which will execute each
time the value in this control is changed. If it is a text control, insert
the following code between the Private Sub and End Sub lines, substituting
the name of your control for "MyControl":

Me![MyControl].DefaultValue = "'" & Me![MyControl] & "'"

If it is a numeric control, omit the quotes:

Me![MyControl].DefaultValue = Me![MyControl]

Save the procedure and the form. That's it.

Hope that helps.
Sprinks

:

Hello,

I found something like this the other day doing a search, but I can't seem
to find it again.

I have a very simple database--one table, one form, and a few reports.
On the form, there are a few fields that I would like to default in the value
from the last record. For example, date, shift, product name, and lot,
usually stay the same for each record entered during a twelve hour period.
How can I get those fields in the form to default in from the last record so
the operators don't have to keep typing them in, but they can still change
them on a new record if they need to?

I'm not familiar with editing code in Access, but I'm not afraid to try
it! I've found a few lines of code in this forum that may help, but I'm not
sure exactly where to put it.

Thank you in advance,
Sharon
Click to expand...
Click to expand...
Click to expand...
 
IC.

I know this topic has been covered more than once here, and as I recall it
would do as I suggested--use a real number for the field itself, but display
it as a formatted string representation on the form, reports, etc.

I don't recall, however, how it's done. It probably uses the DMax()
function in the On Current event. You'd need to check to see if the field is
Null first before assigning the value. Perhaps search the backlog or with
Google. If not, repost this specifically since many MVP's don't screen posts
that have already been answered.

Good luck.
Sprinks

Sharon said:
Sprinks,

I didn't use an auto-number because I only want it to default the next
number, but still give them the ability to change it. If they switch
products or lot number, the roll number will start over at 01 again. Plus, I
wasn't sure if for example, they start entering a record, and decide they
entered a mistake, and delete the record, if they'd still be able to use that
number again.

Thank you,
Sharon


Sprinks said:
Hi, Sharon.

If its really a number, can you simply use an AutoNumber field, and format
it with a custom function?

Function FormatMyNumber(intNumber as Integer) as String
If intNumber<10 Then
FormatMyNumber = "0" & LTrim(Str(intNumber))
Else
FormatMyNumber = LTrim(Str(intNumber))
End If
End Function

Then, display the result in a textbox:

=FormatMyNumber([MyAutoNumberField])

Hope that helps.
Sprinks

Sharon said:
Sprinks,

It worked great! Thank you!

Next question is similar. I'd like the default for another field to be one
greater than the previous record. Is that possible? It is a text field
though, because I need it to be 01, 02, 03, etc, and as a numeric field, it
came out as 1, 2, 3, etc.

Thank you,
Sharon


:

Hi, Sharon.

Set the DefaultValue property of each control in its AfterUpdate event
procedure. If you've never coded before, click on a control in Design view.
Display the Properties with View, Properties. Click on the Event tab and put
the cursor in the AfterUpdate property. Then click the ellipsis to the right
of the data window, and choose Code Builder if necessary.

Access will create a shell of a procedure for you, which will execute each
time the value in this control is changed. If it is a text control, insert
the following code between the Private Sub and End Sub lines, substituting
the name of your control for "MyControl":

Me![MyControl].DefaultValue = "'" & Me![MyControl] & "'"

If it is a numeric control, omit the quotes:

Me![MyControl].DefaultValue = Me![MyControl]

Save the procedure and the form. That's it.

Hope that helps.
Sprinks

:

Hello,

I found something like this the other day doing a search, but I can't seem
to find it again.

I have a very simple database--one table, one form, and a few reports.
On the form, there are a few fields that I would like to default in the value
from the last record. For example, date, shift, product name, and lot,
usually stay the same for each record entered during a twelve hour period.
How can I get those fields in the form to default in from the last record so
the operators don't have to keep typing them in, but they can still change
them on a new record if they need to?

I'm not familiar with editing code in Access, but I'm not afraid to try
it! I've found a few lines of code in this forum that may help, but I'm not
sure exactly where to put it.

Thank you in advance,
Sharon
Click to expand...
Click to expand...
Click to expand...
 
IC.

I know this topic has been covered more than once here, and as I recall it
would do as I suggested--use a real number for the field itself, but display
it as a formatted string representation on the form, reports, etc.

I don't recall, however, how it's done. It probably uses the DMax()
function in the On Current event. You'd need to check to see if the field is
Null first before assigning the value. Perhaps search the backlog or with
Google. If not, repost this specifically since many MVP's don't screen posts
that have already been answered.

Good luck.
Sprinks
Click to expand...

PMFJI but Sprinks did ask... <g>

If I get what you're trying to do here, use the AfterUpdate event of
the lot number control. If you want a new roll number starting with 1
for each product/lot, the code would be something like

Private Sub txtLot_AfterUpdate()
If IsNull(Me!txtRollNumber) Then ' don't stomp on existing roll
Me!txtRollNumber = NZ(DMax("[RollNumber]", "[tablename]", _
"[ProductID] = " & Me!ProductID & " AND LotNumber = " & _
Me!LotNumber)) + 1
End If
End Sub

This assumes that ProductID and LotNumber are numeric fields; you'll
need quotemarks ' around them if they're text. IT also assumes that
you'll always have a ProductID filled in before you assign a lot.

John W. Vinson[MVP]
 
John,

Thanks for tipping my rebound in. You're awesome.

Sprinks

John Vinson said:
IC.

I know this topic has been covered more than once here, and as I recall it
would do as I suggested--use a real number for the field itself, but display
it as a formatted string representation on the form, reports, etc.

I don't recall, however, how it's done. It probably uses the DMax()
function in the On Current event. You'd need to check to see if the field is
Null first before assigning the value. Perhaps search the backlog or with
Google. If not, repost this specifically since many MVP's don't screen posts
that have already been answered.

Good luck.
Sprinks
Click to expand...

PMFJI but Sprinks did ask... <g>

If I get what you're trying to do here, use the AfterUpdate event of
the lot number control. If you want a new roll number starting with 1
for each product/lot, the code would be something like

Private Sub txtLot_AfterUpdate()
If IsNull(Me!txtRollNumber) Then ' don't stomp on existing roll
Me!txtRollNumber = NZ(DMax("[RollNumber]", "[tablename]", _
"[ProductID] = " & Me!ProductID & " AND LotNumber = " & _
Me!LotNumber)) + 1
End If
End Sub

This assumes that ProductID and LotNumber are numeric fields; you'll
need quotemarks ' around them if they're text. IT also assumes that
you'll always have a ProductID filled in before you assign a lot.

John W. Vinson[MVP]
Click to expand...
 
Back
Top