Can I have more than one input mask on the same field ?

[giggly]

Hello all and thank you for viewing my problem, I'm stuck with Access 97, the question I have is, can I have more than one input mask in the same text box ? for example I have a database with a form bound to a table, I have a combo box to select the different electrical wholesalers which have different formats for entering their invoice details e.g if i select "Kew electrical" there invoice format would be "TRO/34567" but if I select "Edmunsons" there invoice format would be 290/7890654 and so on ....


what I want to be able to do is select wholesaler from combo box and have a text box alter its inputmask via my selection in wholesale combo box..is this possible ? many thanks in advance
Steve

[Ruegen]

yes.

VBA

on after update, change the property of the control

I would use an if or a case statement depending on what you want.

That way if you change it back to the other choice from the combo box, it will give back the other format.

also to speed things up

you could have after you change the format

Me.control = "TRO/"

that way you wouldn't have to type in "TRO/"

[giggly]

Hi Ruegen, many thanks for the reply. Unfortunately I am not brilliant at VBA I am just starting to get my head around it with lots of book reading and Internet browsing to get me through the basics, Would you be kind enough to elaborate on the VBA side ?

something like
If [wholesaler]= "Kew Electrical" Then Invoice_No.inputmask= "TRO/aaaaaaa"
Elseif [wholesaler]= "Edmunsons" Then Invoice_No.inputmask= "290/aaaaaaa"
Elseif [wholesaler]= "Wilts wholesale" Then Invoice_No.inputmask= "POP/aaaaaaa"
End If

I have the first bit working when I select "kew Electrical" but there it stops ???

[Ruegen]

Hi Ruegen, many thanks for the reply ....both of them ! Unfortunately I am not brilliant at VBA I am just starting to get my head around it with lots of book reading and Internet browsing to get me through the basics, Would you be kind enough to elaborate on the VBA side ?

something like
If [wholesaler]= "Kew Electrical" Then Invoice_No.inputmask= "TRO/aaaaaaa"
Elseif [wholesaler]= "Edmunsons" Then Invoice_No.inputmask= "290/aaaaaaa"
Elseif [wholesaler]= "Wilts wholesale" Then Invoice_No.inputmask= "POP/aaaaaaa"
End If

I have the first bit working when I select "kew Electrical" but there it stops ???

Does each wholesaler have their own ID?

When you refer to say "Kew Electrical" <that is a string (a string/rope/collection/bunch of characters)

and ID is just a whole number (integer) and it's generally better to work from that rather than a string - it's less work for the computer.

If you said:

If [wholesalerID] = 1 then
me.invoice_no.inputmask = "#####"

etc

sorry to confuse you earlier. The inputmask controls what you put in. It doesn't put the data in, it just controls the way you view it.

If you have data you can add it to the invoice_no control's value, but it will be masked by your input mask. Don't try "TRO/aaaaa" as I doubt that will work.

[giggly]

"Kew electrical" is pulled from a combobox with ID "1" but i can see where you are going with this, that actually makes sense, could i also add
If [wholesalerID] = 1 then
me.invoice_no.inputmask = "#####"
Elseif [wholesalerID] = 2 then
me.invoice_no.inputmask = "#####"
Elseif [wholesalerID] = 3 then
me.invoice_no.inputmask = "#####"
End If
would this work as I have several wholesalers who format there invoice numbers differently ?

[ItsMe]

That seems like it would work. Another approach is to use a Select Case statement.

Select Case Me![wholesalerID]
    Case 1
        Me.invoice_no.InputMask = "#####"
    Case 2
        Me.invoice_no.InputMask = "#####"
    Case 3
        Me.invoice_no.InputMask = "#####"
    Case Else
        Me.invoice_no.InputMask = ""
End Select

OR

Dim strMask As String
strMask = ""
Select Case Me![wholesalerID]
    Case 1
        strMask = "#####"
    Case 2
        strMask = "#####"
    Case 3
        strMask = "#####"
        
End Select
Me.invoice_no.InputMask = strMask

[Ruegen]

"Kew electrical" is pulled from a combobox with ID "1" but i can see where you are going with this, that actually makes sense, could i also add
If [wholesalerID] = 1 then
me.invoice_no.inputmask = "#####"
Elseif [wholesalerID] = 2 then
me.invoice_no.inputmask = "#####"
Elseif [wholesalerID] = 3 then
me.invoice_no.inputmask = "#####"
End If
would this work as I have several wholesalers who format there invoice numbers differently ?

Sure you could do it that way, another way is case statement as it is less code instead of an if statement and is easier to read in some ways.

The combo box should be two fields, the id field and the field you want to see. (2 columns). Check the row source of the combo box (property) to see if that is correct. It doesn't have to be a full table or query, you can have a select statement which means you can customise what field you want to see in the combo box. Generally since I only need to see two fields I pull in the id field from a table and the field I want to see (you do this in the row source property).

If the control is going to all have the same input mask - 3 characters, a slash then other characters - is changing it necessary?

then you can also set the first column to 0 and 2nd to a number above 0, say 6. That way you can hide the id even though it is there in the combo box.

[Ruegen]

That seems like it would work. Another approach is to use a Select Case statement.

Select Case Me![wholesalerID]
    Case 1
        Me.invoice_no.InputMask = "#####"
    Case 2
        Me.invoice_no.InputMask = "#####"
    Case 3
        Me.invoice_no.InputMask = "#####"
    Case Else
        Me.invoice_no.InputMask = ""
End Select

OR

Dim strMask As String
strMask = ""
Select Case Me![wholesalerID]
    Case 1
        strMask = "#####"
    Case 2
        strMask = "#####"
    Case 3
        strMask = "#####"
        
End Select
Me.invoice_no.InputMask = strMask

lol too quick (finished my post and you already had yours done)

[Ruegen]

Go with ItsMe's suggestion - it's far more practical.

[giggly]

Many thanks Ruegen your a star, I will give it a try and let you know the outcome when done

[giggly]

and many thanks to you ItsMe's for your input, I have never come across case statment before can I use this in Access 97 ?

[giggly]

Hi guys I am really sorry but i cant get this to work at all, i have checked combo box settings as suggested they seem Ok, it keeps saying "compile error label not defined" any help would be great...
here's what i have at the moment InvoiceID is the textbox which i wanna have the different masks in ----

Private Sub InvoiceID_AfterUpdate()
On Error GoTo err_InvoiceID_AfterUpdate
Select Case Me![wholesalerID]
Case 1
Me.InvoiceID.InputMask = "TR\000000;0"
Case 2
Me.InvoiceID.InputMask = "290\-000000;0"
Case 3
Me.InvoiceID.InputMask = "INVKIN\00000;0"
Case Else
Me.InvoiceID.InputMask = ""
End Select

[giggly]

I have to pop out now but will be back on sunday for another head ramming session, hope you guys have a great weekend and thank you for your help
Steve

[ItsMe]

Try it without the error trap and see which line is causing the issue. Also, why are you setting the input mask for your control InvoiceID during an event for InvoiceID? Use an event that will occur when focus is not on InvoiceID.

Private Sub InvoiceID_AfterUpdate()

Select Case Me![wholesalerID]
Case 1
Me.InvoiceID.InputMask = "TR\000000;0"
Case 2
Me.InvoiceID.InputMask = "290\-000000;0"
Case 3
Me.InvoiceID.InputMask = "INVKIN\00000;0"
Case Else
Me.InvoiceID.InputMask = ""
End Select

End sub

[giggly]

Hello ItsMe, you are spot on, I changed the event to Onchange in the wholesaleID control which seems to work lovely, it was just my lack of understanding how events work, thankyou so much for pointing me in the right direction, I'm just looking for a way to start the cursor where i need to stat typing as opposed to backspacing to required position ?? what i get in the textbox is "TR_______" with cursor at the end , cant find a lot of info on that, would there be a solution to have the cursor to start after the "TR" then input invoice number ?