Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a problem and I'm sure Access has a solution; I'm just not sure how to get it.

    I have a heat number and a heat code is assigned to each heat number. There are some cases where the same heat number will be duplicated and it gets the same heat code that was already assigned. If it is a new heat number then it get the next heat code, (ex A, B, C, D......).

    What I would like to do is for someone to type in the Heat number and if it already exists for the corresponding Heat Code to be assigned, if it does not exist I would like the record to automatically assign the next sequential heat code.

    Please let me know if you have any suggestions on how to do this.

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MSTERNAD' post='764566' date='10-Mar-2009 13:47'][/quote]
    Could you post a stripped down and zipped sample database for Loungers to experiment with?

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='MSTERNAD' post='764566' date='10-Mar-2009 13:47'][/quote]

    I suppose that you use a form to enter the data.
    Then a propose the following :
    In the AfterUpdate of HeatNumber, enter the following code :
    Code:
    Me.HeatCode = DLookup("Heatcode", "YourTable", "Heatnumber = " & Me.HeatNumber)
    The code suppose that HeatNumber is a number. If it is text then use :
    Code:
    Me.HeatCode = DLookup("Heatcode", "YourTable", "Heatnumber = '" & Me.HeatNumber & "'")
    Replace the names where appropriate.
    Francois

  4. #4
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The only thing Ihave in the database so far is the table, which I attached.

    I was trying to think of some ideas on how to do this before I made the form.

    Thanks,

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MSTERNAD' post='764642' date='10-Mar-2009 17:47']The only thing Ihave in the database so far is the table, which I attached.[/quote]
    I don't see an attachment yet. Don't forget to zip the database, and don't forget to click UPLOAD after clicking Browse... and selecting the zip file.

  6. #6
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Let me try this....
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What's the meaning of the code "WE" in the last record?

    What should happen if a user edits a heat number in an existing record (that already has a heat code)?

    Added: what should happen at the end of the alphabet? Wouldn't it be better to use a number field?

  8. #8
    Lounger
    Join Date
    Nov 2005
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='764650' date='10-Mar-2009 18:10']What's the meaning of the code "WE" in the last record?

    What should happen if a user edits a heat number in an existing record (that already has a heat code)?

    Added: what should happen at the end of the alphabet? Wouldn't it be better to use a number field?[/quote]


    The alphabet has actually already been used up and we have started to use two letters (Ex AA, AB, AC). The "WE" is the two letters. I just deleted a bunch of the record in between.

    A user will not be able to edit a heat number, once a heat code is issued to a heat number neither records can be edited.

    A number field would be a lot easier, but unfortunately it is industry standard to use letters.

    Thanks,

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='MSTERNAD' post='764659' date='10-Mar-2009 18:44'][/quote]
    Using a text field causes complications, for "AA" comes before "B" when sorting alphanumerically. To find the "next" available heat code would take a lot of calculations.
    Therefore I propose to store the heat code as a number, but to use a query to display it as text: 1 > "A", 26 > "Z", 27 > "AA" etc.

    The attached database shows how to do this in a form. There are two bits of VBA code involved:

    1) An After Update event procedure for the HeatNumber text box on the form, to decide whether an existing HeatCode can be used, or a new one created:
    Code:
    Private Sub HeatNumber_AfterUpdate()
      Dim varCode As Variant
      Dim i As Integer
      Dim c As Long
      If Me.NewRecord Then
    	If Not IsNull(Me.HeatNumber) Then
    	  varCode = DLookup("HeatCode", "Heat Code", "HeatNumber=" & _
    		Chr(34) & Me.HeatNumber & Chr(34))
    	  If Not IsNull(varCode) Then
    		Me.HeatCode = varCode
    	  Else
    		varCode = DMax("HeatCode", "Heat Code")
    		Me.HeatCode = Nz(varCode, 0) + 1
    	  End If
    	Else
    	  Me.HeatCode = Null
    	End If
      End If
    End Sub
    and a public function used in the query to calculate the display value:
    Code:
    Public Function DisplayA(n As Variant) As Variant
      Dim m As Long
      If IsNull(n) Then
    	DisplayA = Null
      Else
    	m = Val(n) - 1
    	DisplayA = ""
    	Do While m >= 0
    	  DisplayA = Chr(65 + m Mod 26) & DisplayA
    	  m = m \ 26 - 1
    	Loop
      End If
    End Function
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •