How to set up an InputMask (or Format) for 2 diff't ID numbers!

[mickeyred]

Hi ya'll!

First of all, thanks in advance for your time and assistance!

While designing a tblProducts table, we stumbled at the fact that the pkProductID primary key field follows either a default "8#-######" format or an "NS8#-######", for example, 81-234567 & NS88-901234. To make it more interesting, if ProductID starts with N..., the fldRegularStock field is to be designed to change from default "Yes" to "No". If any other letter is pressed, a message telling the user something like 'Enter numbers or N followed by numbers' is displayed.

Going back to ...ID, if a number is pressed, say 4, we'd like the mask to be displayed as "84-######" so the user need to enter just the following six (6) numbers (for the time being, the ProductID shall begin with "85-######" so only the last digits need to be entered). Finally, if the user presses the letter "N", we'd rather have the mask display "NS8#-######" - again, initially this ID shall start with "NS85-######". This fldProductID field is also the fkForeingKey for several other tables!

The (super)condensed tblProducts table looks like~~
fn[FieldName]::FieldType::Short note:

pk[ProductID]::Text::Unique num/ltr-num primary key
%%%
fld[RegularStock]::Yes/No::Yes=Default=Checked

(%%%=other Product-related fields)

Once again, thanks!

All the best,

mickeyred

 

[danvlas]

You'll have to give up on trying this directly on the table.
Do it in the data entry form, you have so many events that could help you: Change, KeyPress, KeyDown, AfterUpdate, BeforeUpdate...

Just one example:

Private Sub AID_KeyDown(KeyCode As Integer, Shift As Integer)
Static trigger As Boolean
If trigger Then Exit Sub
AID.InputMask = ">LLLLLLL"
trigger = True
End Sub

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com