carry over value to new record and increment by 1

[durtywhytboy via AccessMonster.com]

Hello;

I have searched the database here and have used some code I found to address
the following:

I have a form bound to a table. The primary key of the table is the field
[RCN]. I have a New Record button that I want the user to press, initiating a
new record with the previous value of [RCN] and incrementing by + 1. This is
not a autonumber field.
For example: Current Record Value = RCN-08-0001
Desired New Record Value = RCN-08-0002

The code behind the button click so far (which I can't get to work):

Private Sub newrecord_Click()
DoCmd.GoToRecord acDataForm, "frmFORM", acNewRec
Const cQuote = """" 'Thats two quotes
Me!RCN.DefaultValue = cQuote & Me!RCN.Value & cQuote
End Sub

Any help given is most aprreciated
dwb

 

[Rui]

Hi,

I think you have chosen the wrong way to do this as I can see a couple of
problems emerging in the near future. i.e - what happens if it is not RCN-08 ?

I may get some stick for this but here is a solution based on your current
code:

Private Sub newrecord_Click()
DoCmd.GoToRecord , , acLast ' let's go to the last record inserted

Dim strTmp As String
Dim tmpValue As Integer
strTmp = DLookup("[your RCN field]","[table name]", "[your RCN field] =
'" & _
Me![your text field] & "'") ' this will get the last
record data note the
' single quotes
between double quotes
tmpValue = Mid(strTmp , 8) ' get the last four digits
strTmp = Left(strTmp , 7) ' we only want the begining of
the string

tmpValue = tmpValue + 1 'add 1 to the value

DoCmd.GoToRecord , "", acNewRec 'New record
Me![your text field].Value = strTmp & Format(tmpValue , "0000") ' need to
' format it because it is a number
End Sub


I would use other ways to achieve this. Maybe a table to store the last or
next record so I could access it later and modify it without changing the
code.

it is up to you. take care Rui

 

[John W. Vinson]

Hello;

I have searched the database here and have used some code I found to address
the following:

I have a form bound to a table. The primary key of the table is the field
[RCN]. I have a New Record button that I want the user to press, initiating a
new record with the previous value of [RCN] and incrementing by + 1. This is
not a autonumber field.
For example: Current Record Value = RCN-08-0001
Desired New Record Value = RCN-08-0002

The code behind the button click so far (which I can't get to work):

Private Sub newrecord_Click()
DoCmd.GoToRecord acDataForm, "frmFORM", acNewRec
Const cQuote = """" 'Thats two quotes
Me!RCN.DefaultValue = cQuote & Me!RCN.Value & cQuote
End Sub

Any help given is most aprreciated
dwb

Is the RCN a constant, or a variable value (some records have RCN, some have
HCL?)

Is the 08 a year?

Do you want the number to start over at 1 on January 1? or when a new text
value is used? or both?

Will this be a one-user system, or might you have multiple users all sharing
the same database and competing for new RCN numbers?

This kind of composite, intelligent key should be avoided unless it's needed
for compatiblity with an existing manual system. You can do it with some
fairly snarky VBA code... but you may want to consider whether you need this
field in just this format!

 

[durtywhytboy via AccessMonster.com]

Rui:

DUDE! You were so on the money, it ain't funny! Thanks a ton!

Near future not a problem!

Hi,

I think you have chosen the wrong way to do this as I can see a couple of
problems emerging in the near future. i.e - what happens if it is not RCN-08 ?

I may get some stick for this but here is a solution based on your current
code:

Private Sub newrecord_Click()
DoCmd.GoToRecord , , acLast ' let's go to the last record inserted

Dim strTmp As String
Dim tmpValue As Integer
strTmp = DLookup("[your RCN field]","[table name]", "[your RCN field] =
'" & _
Me![your text field] & "'") ' this will get the last
record data note the
' single quotes
between double quotes
tmpValue = Mid(strTmp , 8) ' get the last four digits
strTmp = Left(strTmp , 7) ' we only want the begining of
the string

tmpValue = tmpValue + 1 'add 1 to the value

DoCmd.GoToRecord , "", acNewRec 'New record
Me![your text field].Value = strTmp & Format(tmpValue , "0000") ' need to
' format it because it is a number
End Sub

I would use other ways to achieve this. Maybe a table to store the last or
next record so I could access it later and modify it without changing the
code.

it is up to you. take care Rui

Hello;

[quoted text clipped - 18 lines]

Any help given is most aprreciated
dwb

 

[durtywhytboy via AccessMonster.com]

Thx John for your reply. Rui solved it. This is a temp fix to a problem in an
existing multiuser db. When 08 becomes 09, the numbers will need to start at
0001 again, but the after the first entry, this again will work. I am
satisfied! This database is due to be replaced by a huge Oracle app in FY09,
so I just need to baby it until then.

Hello;

[quoted text clipped - 18 lines]

Any help given is most aprreciated
dwb

Is the RCN a constant, or a variable value (some records have RCN, some have
HCL?)

Is the 08 a year?

Do you want the number to start over at 1 on January 1? or when a new text
value is used? or both?

Will this be a one-user system, or might you have multiple users all sharing
the same database and competing for new RCN numbers?

This kind of composite, intelligent key should be avoided unless it's needed
for compatiblity with an existing manual system. You can do it with some
fairly snarky VBA code... but you may want to consider whether you need this
field in just this format!

 

[Rui]

Glad you liked it.

Rui:

DUDE! You were so on the money, it ain't funny! Thanks a ton!

Near future not a problem!

Hi,

I think you have chosen the wrong way to do this as I can see a couple of
problems emerging in the near future. i.e - what happens if it is not RCN-08 ?

I may get some stick for this but here is a solution based on your current
code:

Private Sub newrecord_Click()
DoCmd.GoToRecord , , acLast ' let's go to the last record inserted

Dim strTmp As String
Dim tmpValue As Integer
strTmp = DLookup("[your RCN field]","[table name]", "[your RCN field] =
'" & _
Me![your text field] & "'") ' this will get the last
record data note the
' single quotes
between double quotes
tmpValue = Mid(strTmp , 8) ' get the last four digits
strTmp = Left(strTmp , 7) ' we only want the begining of
the string

tmpValue = tmpValue + 1 'add 1 to the value

DoCmd.GoToRecord , "", acNewRec 'New record
Me![your text field].Value = strTmp & Format(tmpValue , "0000") ' need to
' format it because it is a number
End Sub

I would use other ways to achieve this. Maybe a table to store the last or
next record so I could access it later and modify it without changing the
code.

it is up to you. take care Rui

Hello;

[quoted text clipped - 18 lines]

Any help given is most aprreciated
dwb