0

I have a form containing a combBox and a textBox. The comboBox is getting it's data from a table witch has only three values (JED,RUH and DMM). What I want is when the user pick any of the three values in the comboBox, the textBox will be filled automatically with a special format of autoNumbering. For example if the user picked JED the format will be j0000a ("j": is static and it's a shotcut of JED, "0000": is a regular number that increases sequentially, "a": is an alphabetical letter that will never change unless the 0000 reaches it's limit witch is 9999). Note that each value in the comboBox has it's special format of autoNumbering and it's unique.
How can I do it ?
Thanks in advance.

Mohannad.Z
  • 47
  • 1
  • 6
  • Actually it's not found yet, I didn't do it yet. – Mohannad.Z Aug 31 '12 at 16:51
  • 1
    This looks very like someone is trying to force an old-fashioned system on a new system. This may be the time to consider a new way of doing things. Why not just store the three letter code and an autonumber? Life would be a lot easier and a lot safer. – Fionnuala Aug 31 '12 at 18:32
  • @Remou I didn't get your point, could you be more specific and show us an example ? – Mohannad.Z Sep 01 '12 at 07:25
  • Just add two fields to your table, a regular autonumber ID and a code field. Set the combo Control Source to the code field. When a display is required, use format to display the code & the autonumber in any format you want. Do not store formats, just display them. `Code & Format(ID,"0000")`. Autonumbers are not sequential, however, if you need a sequential number, create a field for that, but leave it as a number, so it is future proof. I posted some notes on a sequential number here http://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1 – Fionnuala Sep 01 '12 at 08:15

1 Answers1

0

If you are storing the value in a table then some VBA code in the drop down's afterupdate() event would do it.

I will take some liberties here because I don't know your exact structure.

Table_ABRV
ID | ABRV
1  | JED
2  | RUH
3  | DMM

And another table.

Table_Auto
ID | CustomID | ABRVID
1  | j0000a   | 1

And the code to make this work is

Private Sub cmbABRV_AfterUpdate()
Dim seed, autoVal, autoRemainder, autoAlpha
'[ABRV][####][a]
seed = DCount("ID", "Table_Auto", "ABRVID = " & DLookup("ID", "Table_ABRV", "ABRV = '"  & cmbABRV.Text & "'"))
autoVal = Round(seed / 10000, 0) + 1 'this will be used for the alpha character
autoRemainder = seed Mod 10000 'this will be used for numeric value
autoRemainder = Format(autoRemainder, "000#") 'Add preceeding 0's
autoAlpha = ConvertToLetter(autoVal) 'convert the autoVal to Alpha
txtAuto.Value = Left(cmbABRV.Text, 1) & autoRemainder & autoAlpha 'Create string
End Sub

Function ConvertToLetter(Val) As String
'Convert a letter to a numeric equivalent
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(Val / 27)
iRemainder = Val - (iAlpha * 26)
If iAlpha > 0 Then
  ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
  ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function

The form text box will display the auto number after a value is picked from the drop down.

AxGryndr
  • 2,274
  • 2
  • 22
  • 45
  • 1
    I would not try this in a multi-user environment. – Fionnuala Aug 31 '12 at 18:29
  • I don't think I would go that far. The issue with a multi user environment is not going to be in displaying the field it would be at the point that you try to make edits to the data. If you are using a multi user environment then you should have the controls in place to ensure that two users are not editing the same record and that two users do not insert a record with the same ID. By making the ID unique you can prevent two of the same ID's and then by including an error trap you'll know how to handle when two users try to save with the same ID at the same time. – AxGryndr Aug 31 '12 at 19:12