Trying to perform a sequence...

F

Frank

I have an field that's named Po Number, and this is my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field
 
M

Michel Walsh

Hi,


What is PONumber? a control? a field name? both? A control belong to the
form, a field belong to the table, to "match" a control to a field, you have
to assign the control property ControlSource to a field name, at design.

Where do you did the assignment? In the onCurrent Event of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING record, isn't it
Me.PONumberTheControl = Nz(DMax("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you make a constraint
(index) not allowing duplicated values on the FIELD of the table? And, in
addition, have you trap the possible error in the form onError event? That
would occur if two users try to append a new record at roughly the same
time... they would got the same PO number, but the slowest user would got an
error, telling a duplicated value would occur (since the fastest user got
its record save first, with the new PO number). In the onError event, you
would then tell the problem to the (slow) end user and recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP
 
F

Frank

First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has syntax
errors after i replace the field name and table name, with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A control belong to the
form, a field belong to the table, to "match" a control to a field, you have
to assign the control property ControlSource to a field name, at design.

Where do you did the assignment? In the onCurrent Event of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING record, isn't it
Me.PONumberTheControl = Nz(DMax ("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you make a constraint
(index) not allowing duplicated values on the FIELD of the table? And, in
addition, have you trap the possible error in the form onError event? That
would occur if two users try to append a new record at roughly the same
time... they would got the same PO number, but the slowest user would got an
error, telling a duplicated value would occur (since the fastest user got
its record save first, with the new PO number). In the onError event, you
would then tell the problem to the (slow) end user and recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



I have an field that's named Po Number, and this is my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field
Click to expand...


.
Click to expand...
 
M

Michel Walsh

Hi,


The line

Me.PoNumber = ...


was ending with the + 1. It may have been broken in two by the
newsreader.

Can you post the three lines of code ( one start with If, one with
Me.PoNumber and the last one is End If ) if you still have problems?



Vanderghast, Access MVP


Frank said:
First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has syntax
errors after i replace the field name and table name, with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A control belong to the
form, a field belong to the table, to "match" a control to a field, you have
to assign the control property ControlSource to a field name, at design.

Where do you did the assignment? In the onCurrent Event of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING record, isn't it
Me.PONumberTheControl = Nz(DMax ("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you make a constraint
(index) not allowing duplicated values on the FIELD of the table? And, in
addition, have you trap the possible error in the form onError event? That
would occur if two users try to append a new record at roughly the same
time... they would got the same PO number, but the slowest user would got an
error, telling a duplicated value would occur (since the fastest user got
its record save first, with the new PO number). In the onError event, you
would then tell the problem to the (slow) end user and recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



I have an field that's named Po Number, and this is my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field
Click to expand...


.
Click to expand...
Click to expand...
 
F

Frank

Michel now it says a Compilation error.
This is what I wrote:

If Me.NewRecord Then ' we do not want modify EXISTING
record, isn't it

Me.PO Number = Nz(DMax("PO Number PO Number", "PO Log
Table"), 0) + 1

End If

TIA
Frank
-----Original Message-----
Hi,


The line

Me.PoNumber = ...


was ending with the + 1. It may have been broken in two by the
newsreader.

Can you post the three lines of code ( one start with If, one with
Me.PoNumber and the last one is End If ) if you still have problems?



Vanderghast, Access MVP


First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has syntax
errors after i replace the field name and table name, with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A control belong to the
form, a field belong to the table, to "match" a control to a field, you have
to assign the control property ControlSource to a field name, at design.

Where do you did the assignment? In the onCurrent
Click to expand...
Event
of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING
Click to expand...
record,
isn't it
Me.PONumberTheControl = Nz(DMax ("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you make a constraint
(index) not allowing duplicated values on the FIELD of the table? And, in
addition, have you trap the possible error in the form onError event? That
would occur if two users try to append a new record at roughly the same
time... they would got the same PO number, but the slowest user would got an
error, telling a duplicated value would occur (since
Click to expand...
the
fastest user got
its record save first, with the new PO number). In the onError event, you
would then tell the problem to the (slow) end user and recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



I have an field that's named Po Number, and this is my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field


.
Click to expand...
Click to expand...


.
Click to expand...
 
M

Michel Walsh

Hi,


Having a space in the control name is not very healthy for your code.
Try to remove that space (and in the VBA code, and in the control name).

The first argument of DMax seems strange. The field name is really

PO Number PO Number


(twice) ?




Hoping it may help,
Vanderghast, Access MVP



Frank said:
Michel now it says a Compilation error.
This is what I wrote:

If Me.NewRecord Then ' we do not want modify EXISTING
record, isn't it

Me.PO Number = Nz(DMax("PO Number PO Number", "PO Log
Table"), 0) + 1

End If

TIA
Frank
-----Original Message-----
Hi,


The line

Me.PoNumber = ...


was ending with the + 1. It may have been broken in two by the
newsreader.

Can you post the three lines of code ( one start with If, one with
Me.PoNumber and the last one is End If ) if you still have problems?



Vanderghast, Access MVP


First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has syntax
errors after i replace the field name and table name, with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A
control belong to the
form, a field belong to the table, to "match" a control
to a field, you have
to assign the control property ControlSource to a field
name, at design.

Where do you did the assignment? In the onCurrent Event
of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING record,
isn't it
Me.PONumberTheControl = Nz(DMax
("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you
make a constraint
(index) not allowing duplicated values on the FIELD of
the table? And, in
addition, have you trap the possible error in the form
onError event? That
would occur if two users try to append a new record at
roughly the same
time... they would got the same PO number, but the
slowest user would got an
error, telling a duplicated value would occur (since the
fastest user got
its record save first, with the new PO number). In the
onError event, you
would then tell the problem to the (slow) end user and
recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



message
I have an field that's named Po Number, and this is my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field


.
Click to expand...


.
Click to expand...
Click to expand...
 
F

Frank

Sorry Michel, This is the last try I wrote down this:

If Me.NewRecord Then ' we do not want modify
EXISTING record, isn't it

Me.Po Number = Nz(DMax("PO Number", "PO Log Table"), 0) +
1

End If


PO Number is The Field, PO Log Table the Table
Now in the form in the PO Number Control source I wrote
=Max([Po Number])+1
And it works but it doesn't store it in the table and
since the field is required the program wont continue

Thanks for your patience and sorry for causing any trouble
Frank





-----Original Message-----
Hi,


Having a space in the control name is not very healthy for your code.
Try to remove that space (and in the VBA code, and in the control name).

The first argument of DMax seems strange. The field name is really

PO Number PO Number


(twice) ?




Hoping it may help,
Vanderghast, Access MVP



Michel now it says a Compilation error.
This is what I wrote:

If Me.NewRecord Then ' we do not want modify EXISTING
record, isn't it

Me.PO Number = Nz(DMax("PO Number PO Number", "PO Log
Table"), 0) + 1

End If

TIA
Frank
-----Original Message-----
Hi,


The line

Me.PoNumber = ...


was ending with the + 1. It may have been broken in two by the
newsreader.

Can you post the three lines of code ( one start with
Click to expand...
If,
one with
Me.PoNumber and the last one is End If ) if you still have problems?



Vanderghast, Access MVP


First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has syntax
errors after i replace the field name and table name, with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A
control belong to the
form, a field belong to the table, to "match" a control
to a field, you have
to assign the control property ControlSource to a field
name, at design.

Where do you did the assignment? In the onCurrent Event
of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING record,
isn't it
Me.PONumberTheControl = Nz(DMax
("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you
make a constraint
(index) not allowing duplicated values on the FIELD of
the table? And, in
addition, have you trap the possible error in the form
onError event? That
would occur if two users try to append a new record at
roughly the same
time... they would got the same PO number, but the
slowest user would got an
error, telling a duplicated value would occur (since the
fastest user got
its record save first, with the new PO number). In the
onError event, you
would then tell the problem to the (slow) end user and
recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



message
I have an field that's named Po Number, and this
Click to expand...
is
my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
M

Michel Walsh

Hi,


Your form is bound to the table? the RecordSource of the form is
assigned to the table?


Other fields are saved and just that one is not? Or no field are
modified, neither saved?



You assigned the ControlSource property of the control to the field name
(or it is empty, it should not) ?


Note that this procedure will affect only NEW records, not the existing
ones.



Hoping it may help,
Vanderghast, Access MVP


Frank said:
Sorry Michel, This is the last try I wrote down this:

If Me.NewRecord Then ' we do not want modify
EXISTING record, isn't it

Me.Po Number = Nz(DMax("PO Number", "PO Log Table"), 0) +
1

End If


PO Number is The Field, PO Log Table the Table
Now in the form in the PO Number Control source I wrote
=Max([Po Number])+1
And it works but it doesn't store it in the table and
since the field is required the program wont continue

Thanks for your patience and sorry for causing any trouble
Frank





-----Original Message-----
Hi,


Having a space in the control name is not very healthy for your code.
Try to remove that space (and in the VBA code, and in the control name).

The first argument of DMax seems strange. The field name is really

PO Number PO Number


(twice) ?




Hoping it may help,
Vanderghast, Access MVP



Michel now it says a Compilation error.
This is what I wrote:

If Me.NewRecord Then ' we do not want modify EXISTING
record, isn't it

Me.PO Number = Nz(DMax("PO Number PO Number", "PO Log
Table"), 0) + 1

End If

TIA
Frank

-----Original Message-----
Hi,


The line

Me.PoNumber = ...


was ending with the + 1. It may have been broken in
two by the
newsreader.

Can you post the three lines of code ( one start with If,
one with
Me.PoNumber and the last one is End If ) if you still
have problems?



Vanderghast, Access MVP


message
First of all, thanks Michel.
PO Number is a Field and also a control, I reaaly don't
know how to use codes, but code you wrote down has
syntax
errors after i replace the field name and table name,
with
the correct names.
TIA
Frank
-----Original Message-----
Hi,


What is PONumber? a control? a field name? both? A
control belong to the
form, a field belong to the table, to "match" a control
to a field, you have
to assign the control property ControlSource to a field
name, at design.

Where do you did the assignment? In the onCurrent
Event
of the form? with
what kind of code:


If Me.NewRecord Then
' we do not want modify EXISTING
record,
isn't it
Me.PONumberTheControl = Nz(DMax
("PoNumberTheField",
"TheTableNameHere") , 0 ) + 1

End If



Are you in a multi-users environment? If so, have you
make a constraint
(index) not allowing duplicated values on the FIELD of
the table? And, in
addition, have you trap the possible error in the form
onError event? That
would occur if two users try to append a new record at
roughly the same
time... they would got the same PO number, but the
slowest user would got an
error, telling a duplicated value would occur (since
the
fastest user got
its record save first, with the new PO number). In the
onError event, you
would then tell the problem to the (slow) end user and
recompute 1+DMAx(
.... ) as new PO, for that user.




Hoping it may help,
Vanderghast, Access MVP



message
I have an field that's named Po Number, and this is
my
Primary filed .
How can I make that number add itslf and continue a
sequence, for the next page.
Ex: Po Number=40052 , Next page Po Number=40051

I tried the following:
PO Number=Max([Po Number])+1
But it doesn't store the info in th field


.



.
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trying to create a Sequence... 1
Help with Number sequence function 1
Assign unique PO# to a new record in a form 1
HOw to inslert a formula into a report 7
Sequence number To "Dale Fye" 2
Union query converting date to text 2
How do I make a auto sequence num field in Word that counts up? 2
Function to put worksheet name in cell returns text, need number 5

Top