Incrementing Number

[Maggie]

ok, which part of this code is actually writing to the [FileNumber] field?
Should there be some sort of SetValue command that actually writes it to the
table. I applied the code to the Before Update prop on the Form but if it's
calculating the most current value +1, it doesn't appear to be putting it
anywhere.

I can feel it too ;-)

You first post was clear and I understood it just fine.
Unfortunately, I typed = when I meant <>

Using your names, change two lines to:

If Nz(Me.[State Name], "") <> "" Then 'must have state
Me.filenumber = DMax("filenumber", "Property Table", _
"[State Name] = """ & Me.[State Name] & """ "), 0) + 1
--
Marsh
MVP [MS Access]

We're so close...I can feel it...

I'm afraid I probably haven't been as precise with my explanation of my need
as I should have been. The code itself should generate a [filenumber] and
populate it in the [filenumber] field in the "Property Table". (btw, I can't
get around the spaces in the table and file names)

The User will just be entering into basic text boxes that populate the
Property table. Eventually they come to a field called [State Name] and they
will simply be typing a 2-letter abbreviation like "CA" or "NY", etc... so
step by step I need
1. the code to assess the value entered into the [State Name] field on the
form
1.1 if this is the 1st occurence of that state, then the [filenumber] for
that record should = "1"
ELSE
2. lookup the max [filenumber] value associated with that [State Name]
3. Add 1 to that [filenumber]
4. Populate that new value to the record's [filenumber] field in the table

As I look back on my first question, I'm feeling that this is a better
description of what I'm looking to do. Again, any thought are greatly
appreciated.

The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub

 

[Marshall Barton]

Because we are using the **form** BeforeUpdate event (unless
we cancel the event) the data is in the process of being
written back to the record source table. All we have to do
in the event procedure is assign the new value to either the
field or the text box bound to the field. The
Me.filenumber= ...
line is supposed to calculate the new value and assign it to
the field. The next thing that Access will do after the
procedure ends is save the data to the table.

A possible point of confusion is that the new value will not
appear until you navigate back to that record. This is the
price of using a technique that leaves a vanishingly small
window of exposure to multiple user collisions. You can
also open the table in sheet view and look at the filenumber
field to verify its correctness.

Note that SetValue is a macro action and has no relevance in
a VBA procedure. VBA uses an assignment statement instead
(e.g. x=3)
--
Marsh
MVP [MS Access]

ok, which part of this code is actually writing to the [FileNumber] field?
Should there be some sort of SetValue command that actually writes it to the
table. I applied the code to the Before Update prop on the Form but if it's
calculating the most current value +1, it doesn't appear to be putting it
anywhere.

:

Using your names, change two lines to:

If Nz(Me.[State Name], "") <> "" Then 'must have state
Me.filenumber = DMax("filenumber", "Property Table", _
"[State Name] = """ & Me.[State Name] & """ "), 0) + 1


Maggie wrote: []

:
The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub

 

[Maggie]

Will be re-trying this shortly....and can I say, thank god for you Marshall.
Your time and effort is saving me.

Because we are using the **form** BeforeUpdate event (unless
we cancel the event) the data is in the process of being
written back to the record source table. All we have to do
in the event procedure is assign the new value to either the
field or the text box bound to the field. The
Me.filenumber= ...
line is supposed to calculate the new value and assign it to
the field. The next thing that Access will do after the
procedure ends is save the data to the table.

A possible point of confusion is that the new value will not
appear until you navigate back to that record. This is the
price of using a technique that leaves a vanishingly small
window of exposure to multiple user collisions. You can
also open the table in sheet view and look at the filenumber
field to verify its correctness.

Note that SetValue is a macro action and has no relevance in
a VBA procedure. VBA uses an assignment statement instead
(e.g. x=3)
--
Marsh
MVP [MS Access]

ok, which part of this code is actually writing to the [FileNumber] field?
Should there be some sort of SetValue command that actually writes it to the
table. I applied the code to the Before Update prop on the Form but if it's
calculating the most current value +1, it doesn't appear to be putting it
anywhere.

:

Using your names, change two lines to:

If Nz(Me.[State Name], "") <> "" Then 'must have state
Me.filenumber = DMax("filenumber", "Property Table", _
"[State Name] = """ & Me.[State Name] & """ "), 0) + 1


Maggie wrote: []

:
The exit event leaves a potentially long time for another
user to do the same thing and you could get duplicate file#
values. For this reason the Form's BeforeUpdate event is
the simplest relatively safe place to do this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'only for new records
If Nz(Me.State, "") = "" Then 'must have state value
Me.[File#] = DMax("[File#]", "yourtable", _
"State = """ & Me.State & """ "), 0) + 1
Else
MsgBox "Enter the State"
Cancel = True 'prevent saving record
End If
End If
End Sub