Solved Validation of a combination of text values in a form (1 Viewer)

[AnilBagga]

I have a table where I store the specifications of the object. There are 6 specifications, 4 of which are combo boxes and 2 are textboxes

The combination of these 6 values needs to be unique. I therefore added an ComboCode in the table to store the conjugated values of these 6 textboxes and set the validation of this combo code

To store this combination of 6 text boxes, I added an unbound text box with a formula =[Txtbox1]&"-"&[Txtbox2]... [txtbox6]. This unbound text box control name is txtCombo.

The question is optimal way to transfer the txtCombo value to ComboCode. One way is to add the expression combocode = txtcombo in the AfterUpdate event of all the 6 text boxes. This does the job but I need to set the values 6 times in the form. This gets more complicated with some objects where the number of text boxes are higher

I added this expression in the On Change and AfterUpdate event of the txtCombo unbound text box and 'On Dirty' event of the form without result!

I also tried a control button with the code as below without any result

If me.dirty = True then
combocode = txtcombo
Endif
me.dirty=False

 

[arnelgp]

you can do it Once, on Form's BeforeUpdate.
or you can Create a Public Function in your Form
and call it in each combo/textbox beforeUpdate:


public function fnConcat()
me!ComboCode = [Txtbox1]&"-"&[Txtbox2]... [txtbox6]
end function


on Each textbox/combo add this on their On Before Update:

=fnConcat()

 

[Galaxiom]

For extra security, have you considered putting an composite index on the six fields in the table? There can be up to ten fields in an index.

 

[jumnhy]

Hey @Galaxiom , can you elaborate on what a composite index is in this context? Sounds worth knowing.

 

[Pat Hartman]

And then there's the way that doesn't violate second normal form as alluded to by @Galaxiom

Make each of the six fields required and if they are text, set the AllowZeroLengthStrings to false.
Create a unique index that contains all six fields. You need to use the Indexes dialog to do this since that is the only way to create a multi-column index (except for the PK).

Open the indexes dialog.
On the first completely blank line, add a name for the index and select the first column. Set the unique property to yes.
On the next five rows, leave the name blank and select one of the additional fields