Auto number

[jrd3476]

I have a form with 2 combo boxes that I need users to pick from, based on their selection I need a new code created that is the combination of both boxes plus an incrementing number based on the input. I have it working 1/2 way but I'm stuck.
Box 1 Blue Box 2 Shirts
Green Pants
Red Sweaters
Orange Vests
If the user chooses blue and shirts (for the first time) the output of the new code to be BlueShirt1, if they choose blue shirts again it should say blueshirts2, if then they choose blue vest it should say bluevest1, if they choose blue shirts again it should say blueshirts3. How can I increment on the selection criteria?
Thanks!
Julia

 

[Premy]

Well, in order to create blueshirts2 after blueshirt1 has already been chosen, the value blueshirt1 has to be saved somewhere so the app can look it up then increment it. For one to be able to start thinking about some approach, u'll first have to make clear where u store these already chosen values? in a field or just in local variables?

HTH

 

[jrd3476]

Premy,

I have 2 tables attached to the form, table 1 contains the values for the drop down items. The values the user chooses are stored within table2...
Thanks,
Julia

 

[Premy]

Well, for starters, I can't figure out how u manage to put clothing items and colours in the same table: it'd be easier to have just the clothes in your table and the colours could come from a list of values, but anyway here's what u could do:

Concatenate the values from the 2 boxes, and put it in a variable. So Blue and Shirt becomes BleuShirt and goes into varColourAndItem. Then run a sub which is triggered after the user chooses the item (the colour has already been picked: make sure to only enable the items combo after the colour has been picked, or vice versa) to search your 2nd table (let's call it tblChosenProducts for now) and see if there's already a matching string for this particular customer. Your code could be something like this and goes into the afterupdate event of the items combo (if the colours combo comes last, than this code goes in the colour combo's after update event, of course):

Private Sub cboItem_Afterupdate()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim sSQL As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblChosenProducts ", dbOpenDynaset)

sSQL = "CustomerID =" & Me.cboCustomerID & " AND ChosenProduct Like'" & varColourAndItem & "*'"
rs.FindLast sSQL

IF rs.NoMatch Then
varColourAndItem = varColourAndItem & "1"
Else
varColourAndItem = varColourAndItem & Cbyte(Right(rs("ChosenProduct"),1))+1
rs.AddNew
rs!CustomerID = Me.cboCustomerID
rs!ChosenProduct = varColourAndItem
rs.Update
rs.Close
Set db = Nothing
Set rs = Nothing
End Sub

Your customer id has to somewhere on the form also, of course, possibly another combo.

This code will only allow you to increment up to 9. If this is not enough, u will need some additional code.

HTH

 

[missinglinq]

This should do what you want. The line For i = 1 To 100 in the first two subs limits the number of blueshirts, for instance, to 100, but you can set it whatever upper limit you need by simply changing the 100. Setting a higher number will cause no performance problem, as the For...Next is exited when the target number is found.

Private Sub ClothingCombo_AfterUpdate()
 Dim CodePrefix As String
 
 If Not IsNull(Me.ColorCombo) Then
  CodePrefix = Me.ColorCombo & Me.ClothingCombo
  
[B]    For i = 1 To 50[/B]
     If DCount("NewCode", "CodeStoringTable", "[NewCode] = '" & CodePrefix & Trim(Str(i)) & "'") < 1 Then
     Me.NewCode = CodePrefix & Trim(Str(i))
     Exit Sub
     End If
    Next
  End If
 
End Sub

Private Sub ColorCombo_AfterUpdate()
  Dim CodePrefix As String

If Not IsNull(Me.ClothingCombo) Then
  CodePrefix = Me.ColorCombo & Me.ClothingCombo
   
[B]    For i = 1 To 50[/B]
     If DCount("NewCode", "CodeStoringTable", "[NewCode] = '" & CodePrefix & Trim(Str(i)) & "'") < 1 Then
     Me.NewCode = CodePrefix & Trim(Str(i))
     Exit Sub
     End If
    Next
   End If
End Sub

Private Sub Form_Current()
 ClothingCombo = Null
 ColorCombo = Null
End Sub

Just substitute your actual combobox names for ColorCombo and ClothingCombo, your table name storing the results for CodeStoringTable, and your actual field name for NewCode.

The first two bits of code do the actual work. The code has to be repeated in both AfterUpdate subs because you can't predict which box they'll select from first. The last bit of code, when you move to another record, simply resets both comboboxes so that they show no values.