Text box format problem

[deadyankee]

Hi all,

I have three text boxes in my form which allocate three different parts of an ID automatically. The three parts are numeric and have the following format:

Part 1: 1234 (4 characters)
Part 2: 123 (3 characters)
Part 3: 123 (3 characters)

If the number of characters are less than this, then they are padded with leading zeros. This is easy to achieve by changing the format property of each of the text boxes. However, these three parts then need to be put together connected by hyphens to allocate a new full ID but when I do this they lose the leading zeros, e.g. 0124 001 002 in the three text boxes becomes 124-1-2. My problem is that I need to retain the leading zeros in the final ID.

Any suggestions gratefully recieved!

 

[JimHorton]

Use the .text property of the textbox, ie

Instead of:
x = me!IdPart2
use:
x = me!idPart2.text

--Jim

 

[deadyankee]

Thanks for the tip. Unfortunately, it doesn't appear to work. The full ID is put together in the Control Source of the text box property sheet which doesn't seem to like the text property. Would it be better to code the controlsource?

 

[JimHorton]

I think it's best to not calculate this in the controlsource, but just have a textbox showing the raw, final field (which is stored with padding & hyphens, I'm assuming form your post). That field should be either disabled or locked, and you would only write to it on insert of the record. I'm not sure of the buisness rules here but since this field is the result of 3 other fields in the same table, then those should be locked after insert as well.
The calculation should be a function that is part of the form module, and references the 3 fields, ie:

Function NewID() as string
on error goto errNewID
NewID = me!txtPart1.text & "-" & me!txtPart2.text & "-" & me!txtPart3.text
'Obviously, for error trapping you might want to read each textbox separately..
'...and get more detailed about what & where the error was.
exNewID:
Exit Function
errNewID:
msgbox "Invalid entry in part 1,2 or 3"
newid = ""
resume exNewID
End Function

Another bit of advice, I'm not sure if this will help the current .text property issue, but in general, with regards to Form controls, my personal advice is to *always* name them differently from the controlsource. This is one of my pet peeves of the Form Wizard--that it names them the same. There are a few--granted not many, but enough to make it annoying--cases where the difference is critical, especially with regards to Null values and the priority Access uses when dereferencing the ambiguous reference to the ControlSource/TextBox.
--Jim