Add new version Letter (Alpha character) to a subform

[Guest]

I need to add version letter, A thru Z. On a subform on a new record how do I
look at the previous record and increment say D to E?

TIA
johnb

 

[Douglas J. Steele]

If it's strictly a single letter today, you can use:

Chr(Asc([VersionLetter]) + 1)

What do you want to happen when VersionLetter is Z though?

 

[strive4peace]

Next Letter using ASCII codes
---

Hi John,

you can use the ASCII codes -- provided you won't have more than 26
versions

The ASCII code for A is 65
(American Standard Code for Information Interchange)

chr(65) gives you 'A'
asc("a") gives you 65

'~~~~~~~~~~~~~~~~~~~~~~~
'find the first value that matches current record
'in a clone of the form recordset
Me.RecordsetClone.FindFirst "IDfield = " & me.IDfield

'if there is no previous record, assign 'A'
if me.RecordsetClone.bof then
me.Version_controlname = "A"
else
'go to previous record in RecordsetClone
'which is same as previous record in the form
Me.RecordsetClone. MovePrevious

'assign current version to next letter
me.Version_controlname = chr( asc(Version_controlname) + 1)
end if
'~~~~~~~~~~~~~~~~~~~~~~~

where
IDfield is the name of your primary key -- ie: SalesID, EstID, etc


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[strive4peace]

oops! just noticed an error...

change
chr( asc(Version_controlname) + 1) -->
-->
chr( asc(me.RecordsetClone!Version_fieldname) + 1)


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Doug and Crystal

Thanks for the prompt response. I did not expect a Sunday post!

Kind regards
johnb

 

[strive4peace]

you're welcome, John happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal
adding a new record work a treat when adding a complete new at the top form
level. But with an existing record and inserting say version "B" I am getting
no joy. I'm triggering this code using the On Current at the subform Level
but it failures

any suggestions please.

john

 

[strive4peace]

Hi John,

try saving the record as the first statement
(obviously, it has to be ok for version to not be filled)

if me.dirty then me.dirty = false

perhaps, since the record is not saved, it is not seeing it in the clone
of the recordset...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*