Increment question

[radiaz via AccessMonster.com]

Hello,

I have a form that when opens up, it opens up in ADD Record Mode. How can set
it so when it opens up it looks for the last workorder_id PK entered then
Access automatically add/increment that id by one and shows it on the form.
For example
if the last workorder id entered was
425-07-001 ( This are the numbers that will be entered in the database)

And someone wants to add another record, when that someone either opens up
the form or hit the add command button on the form,
I want Access to automatically increment the last three digit numbers by one.
For example;
425-07-002

I don't want to have to enter that number everytime a record will be added. I
want Access to do it for me.

Thank for the help

 

[John]

If you feel comfortable in code modify the following to fit your needs

NextWorkOrderID= Format(Replace([workorder_id], "-", "")+1,
"###-##-###")


If you dont feel comfortable in code modify the following in the query
by design window

Format(Max(Mid([workorder_id],1,3) & Mid([workorder_id],5,2) &
Mid([workorder_id],8,3))+1,"###-##-###") AS NextWorkOrderID



John
Access Programmer
'Code Monkey'

 

[radiaz via AccessMonster.com]

Thanks John for your quick response.
The form is connected to a table and not a query. I'mm confused.
No, I'm not comfortable writing code. I'm new to Access. Can I do it on the
form?

 

[radiaz via AccessMonster.com]

John,

I tried doing this,
I created a new module and added the following;
Function Find()
Dim NextWorkOrderID As String

NextWorkOrderID = Format(Replace([workorder_id], "-", "") + 1, "###-##-###")


End Function

Then I went to the form in design view, right clicked on workorder_id, then
set the default property ID to Find()
It's still does not work.

I'll keep trying. Again this is new to me so I'll be patience
Thanks for your help though

Thanks John for your quick response.
The form is connected to a table and not a query. I'mm confused.
No, I'm not comfortable writing code. I'm new to Access. Can I do it on the
form?

Hello,

[quoted text clipped - 15 lines]

Thank for the help

 

[John]

(Work on this in a test environment till you get it working)

1) Open your form in design view

2) Create a combo box control on your form name it
'cboNextWorkOrder_ID'

3) Set the row Source =
SELECT
Format(Max(Replace([TableName]![workorder_id],"-",""))+1,"###-##-###")
AS [NextWorkOrderID]
FROM [TableName];

(Make sure to change TableName to the name of your table that has your
workorder_id field)

4) Goto the form where this combo box is and look at the form
properties, Click the event tab and then Then Look at the 'On Load
Event'

5) If it has nothing on that Line then put your cursor on that line
and Click the Ellipses that appears at the end of that line. Click
'Code Builder' in the pop up window and then paste the following
between the "Private Sub Form_Load()" and the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

6) If the 'On Load' says '[Event Procedure]' then put your cursor on
that line and Click the Ellipses that appears at the end of that line.
and then paste the following between the "Private Sub Form_Load()" and
the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

7) If the 'On Load' is a macro then open that macro and add the
following to that macro

a) In the action field type SetValue
b) At the bottom in the Item field Type
[Forms]![FormName]![cboNextWorkOrder_ID]
c) In the Expression Field Type
[Forms]![FormName]![cboNextWorkOrder_ID].[ItemData](0)
(On B and C Change FormName to the name of your form.)

8) Save Everything

9) Open the form and the next number should appear in the combo box.

In one of my Clients Policies databases the Policy Numbers were stored
as '## ### ###' with spaces in them and they wanted these numbers
incremented by one. I used the same procedure outlined above to give
them what they wanted.

John
Access Programmer
'Code Monkey'

<Notes>

1) If you use a macro and the macro does not work then Look in the
access help for 'Examples of referring to values on forms and reports'
and secondly make sure the "Espression Type" field ends with
..[ItemData](0)

<End Notes>

 

[radiaz via AccessMonster.com]

Thanks John for your help.
I tried doing it by going to the load event of the form and did not work. I
tried doing it as a macro and it worked, but it only increments the number
when the form is open
For example I open the form to enter information for one record
it shows 425-07-005 and that's fine i finish up entering information
pertaining to that 425-07-005 then, click the command button named Add ( to
add that record)
and the cbocboNextWorkOrder_ID will not update to show
425-07-006.
It will increment everytime I close the form and re-open it. So if I have 20
different records to add. I have to close/re-open the form 20 times.
Is there a way to make it so everytime I click the Add button to update the
combo box to the next number.

Thanks so much for your help.

(Work on this in a test environment till you get it working)

1) Open your form in design view

2) Create a combo box control on your form name it
'cboNextWorkOrder_ID'

3) Set the row Source =
SELECT
Format(Max(Replace([TableName]![workorder_id],"-",""))+1,"###-##-###")
AS [NextWorkOrderID]
FROM [TableName];

(Make sure to change TableName to the name of your table that has your
workorder_id field)

4) Goto the form where this combo box is and look at the form
properties, Click the event tab and then Then Look at the 'On Load
Event'

5) If it has nothing on that Line then put your cursor on that line
and Click the Ellipses that appears at the end of that line. Click
'Code Builder' in the pop up window and then paste the following
between the "Private Sub Form_Load()" and the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

6) If the 'On Load' says '[Event Procedure]' then put your cursor on
that line and Click the Ellipses that appears at the end of that line.
and then paste the following between the "Private Sub Form_Load()" and
the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

7) If the 'On Load' is a macro then open that macro and add the
following to that macro

a) In the action field type SetValue
b) At the bottom in the Item field Type
[Forms]![FormName]![cboNextWorkOrder_ID]
c) In the Expression Field Type
[Forms]![FormName]![cboNextWorkOrder_ID].[ItemData](0)
(On B and C Change FormName to the name of your form.)

8) Save Everything

9) Open the form and the next number should appear in the combo box.

In one of my Clients Policies databases the Policy Numbers were stored
as '## ### ###' with spaces in them and they wanted these numbers
incremented by one. I used the same procedure outlined above to give
them what they wanted.

John
Access Programmer
'Code Monkey'

<Notes>

1) If you use a macro and the macro does not work then Look in the
access help for 'Examples of referring to values on forms and reports'
and secondly make sure the "Espression Type" field ends with
.[ItemData](0)

Thanks John for your quick response.
The form is connected to a table and not a query. I'mm confused.

[quoted text clipped - 20 lines]

 

[John]

Two more steps need to be added. Requery then Show the new value.

If you used code 'To Add that record' then In the code AFTER the new
record is added:


FormName.cbocboNextWorkOrder_ID.Requery
Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

1) The First line of code will requery the combo box ie. Get the next
number
2) The Second line will show the value combo box.



If you used a macro 'To add that record':
In the Macro AFTER the record is added

1) In the action Field Type Requery
At the bottom in the control name add
[Forms]![FormName]![cboNextWorkOrder_ID]

2) In the action field type SetValue
At the bottom in the Item field Type
[Forms]![FormName]![cboNextWorkOrder_ID]
In the Expression Field Type
[Forms]![FormName]![cboNextWorkOrder_ID].[ItemData](0)
(On B and C Change FormName to the name of your form.)



In your case the code is added after the record is saved. This will
ensure that the Work Order ID you just assigned is in the table when
the combo box requeries. Then the code shows that requeried value.

John
Access Programmer
'Code Monkey'

Thanks John for your help.
I tried doing it by going to the load event of the form and did not work. I
tried doing it as a macro and it worked, but it only increments the number
when the form is open
For example I open the form to enter information for one record
it shows 425-07-005 and that's fine i finish up entering information
pertaining to that 425-07-005 then, click the command button named Add ( to
add that record)
and the cbocboNextWorkOrder_ID will not update to show
425-07-006.
It will increment everytime I close the form and re-open it. So if I have 20
different records to add. I have to close/re-open the form 20 times.
Is there a way to make it so everytime I click the Add button to update the
combo box to the next number.

Thanks so much for your help.

(Work on this in a test environment till you get it working)

1) Open your form in design view

2) Create a combo box control on your form name it
'cboNextWorkOrder_ID'

3) Set the row Source =
SELECT
Format(Max(Replace([TableName]![workorder_id],"-",""))+1,"###-##-###")
AS [NextWorkOrderID]
FROM [TableName];

(Make sure to change TableName to the name of your table that has your
workorder_id field)

4) Goto the form where this combo box is and look at the form
properties, Click the event tab and then Then Look at the 'On Load
Event'

5) If it has nothing on that Line then put your cursor on that line
and Click the Ellipses that appears at the end of that line. Click
'Code Builder' in the pop up window and then paste the following
between the "Private Sub Form_Load()" and the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

6) If the 'On Load' says '[Event Procedure]' then put your cursor on
that line and Click the Ellipses that appears at the end of that line.
and then paste the following between the "Private Sub Form_Load()" and
the 'End Sub Line

Me.cboNextWorkOrder_ID.Value = Me.cboNextWorkOrder_ID.ItemData(0)

7) If the 'On Load' is a macro then open that macro and add the
following to that macro

a) In the action field type SetValue
b) At the bottom in the Item field Type
[Forms]![FormName]![cboNextWorkOrder_ID]
c) In the Expression Field Type
[Forms]![FormName]![cboNextWorkOrder_ID].[ItemData](0)
(On B and C Change FormName to the name of your form.)

8) Save Everything

9) Open the form and the next number should appear in the combo box.

In one of my Clients Policies databases the Policy Numbers were stored
as '## ### ###' with spaces in them and they wanted these numbers
incremented by one. I used the same procedure outlined above to give
them what they wanted.

John
Access Programmer
'Code Monkey'

<Notes>

1) If you use a macro and the macro does not work then Look in the
access help for 'Examples of referring to values on forms and reports'
and secondly make sure the "Espression Type" field ends with
.[ItemData](0)

Thanks John for your quick response.
The form is connected to a table and not a query. I'mm confused.

[quoted text clipped - 20 lines]

Thank for the help

 

[John]

'see notes below

If you used a macro 'To add that record':
In the Macro AFTER the record is added

1) In the action Field Type Requery
At the bottom in the control name add
[Forms]![FormName]![cboNextWorkOrder_ID]

I noticed that in my policy number macro for the control name all that
needed to be added was the cotrol name not the form name. I seem to
remember that adding the form name to the requery would cause the
macro to halt. IF adding the form name doesnt work then just add the
Control name

in your case cboNextWorkOrder_ID

<snip>

 

[radiaz via AccessMonster.com]

Thanks John, yes it caused the macro to halt. I then went ahead and messed
with the code instead and I got it to work. It works good except one thing I
notice is that it increments the last number that was entered. For example if
I already have data which I do, it will increment the last, in this case the
form will show 425-07-021 and so on. It is overlooking the ones from -001,-
005- through -015. Those are missing from the list.
425-07-016
425-07-002
425-07-003
425-07-004
425-07-017
425-07-018
425-07-019
425-07-020

Is there any way to loop around and see which numbers are missing. I guess I
can save that much trouble and instead manually enter those, but what if I
get the numbers not in the same order to be added to the database. I am still
going to have that problem. What if I get the work order # 425-07-051 and 425-
07-053 to add to the DB,.... The work order # is the PK of the tbl. Is there
way to make the code understand that if a number already exist in the
database to skip over and to go a work order number that does not exist in
the DB.

Thanks anyway. You have been a lot of help.
Have a good weekend

'see notes below

If you used a macro 'To add that record':
In the Macro AFTER the record is added

1) In the action Field Type Requery
At the bottom in the control name add
[Forms]![FormName]![cboNextWorkOrder_ID]

I noticed that in my policy number macro for the control name all that
needed to be added was the cotrol name not the form name. I seem to
remember that adding the form name to the requery would cause the
macro to halt. IF adding the form name doesnt work then just add the
Control name

in your case cboNextWorkOrder_ID

<snip>

 

[John]

See Notes Below

Thanks John, yes it caused the macro to halt. I then went ahead and messed
with the code instead and I got it to work. It works good except one thing I
notice is that it increments the last number that was entered. For example if
I already have data which I do, it will increment the last, in this case the
form will show 425-07-021 and so on. It is overlooking the ones from -001,-
005- through -015. Those are missing from the list.
425-07-016
425-07-002
425-07-003
425-07-004
425-07-017
425-07-018
425-07-019
425-07-020

Is there any way to loop around and see which numbers are missing. I guess I
can save that much trouble and instead manually enter those,

With so few it is probable best to just enter them. If it was hundreds
then I say loop though them

but what if I
get the numbers not in the same order to be added to the database. I am still
going to have that problem. What if I get the work order # 425-07-051 and 425-
07-053 to add to the DB,.... The work order # is the PK of the tbl. Is there
way to make the code understand that if a number already exist in the
database to skip over and to go a work order number that does not exist in
the DB.

The code for the combo box to pull the work order number should always
grab the next number from the db. The query is Max() + 1, that is the
last number in the table + 1. If you always requery the combo box
after saving the record you should always have the next number

If you, when entering the data enter 051 and 051 already exist then
the combo box that was created should have the next available number.
Instead of entering 051 use the one in the combo box.

If you manually skip over one then you will probably have to manually
enter the skipped one.

Of course I'm getting a bit out of scope here. I'm not exactly sure
how your db is built.

Thanks anyway. You have been a lot of help.

Np, I just had a case of deja-vu when I read your first post. (My
policy number increment fix)

John
Access Programmer
'Code Monkey'

Have a good weekend

'see notes below

If you used a macro 'To add that record':
In the Macro AFTER the record is added

1) In the action Field Type Requery
At the bottom in the control name add
[Forms]![FormName]![cboNextWorkOrder_ID]

I noticed that in my policy number macro for the control name all that
needed to be added was the cotrol name not the form name. I seem to
remember that adding the form name to the requery would cause the
macro to halt. IF adding the form name doesnt work then just add the
Control name

in your case cboNextWorkOrder_ID

<snip>