Help with Math in this function

[Mutdogus]

I need to write a function that outputs a value of 2 or 3 based on criteria, but I also need it to take into account the values that it has already assigned.

Basically I have 5 criteria to evaluate.

1. Item Count - the count of like items - always a number
2. AMD (average Monthly demand) - always a number
3. RFI (number of items ready to issue) - always a number
4. ERqty (number of items ordered that haven't been filled) - always a number
5. MCN (A control number used as a primary key to track items) - always a string

I have 2 Prioritys (the 2 values of the function) 2 and 3.
I need to assign a priority of 2 to an item if,

1. I have none RFI
2. I have some RFI but not enough to meet AMD

I need to assign a priority of 3 when,
1. I have enough RFI to meet AMD.
2. I have enough Pri 2's to meet ERQty and AMD.

I have written some code here that trys to go about it in 2 diffent ways.. One using a form to display all the information and calculate, but the math is wrong somehow and I can't figure it out.
The one using a recordset, I dont know how to pull data from the fields in the recordset to evaluate it.

Here's my code. Please help

Public Function EvalPri() As Long


    Dim db As DAO.Database          'Make sure you have tools>>references>>
                                    'Microsoft DAO 3.6 or highest checked
    Dim rs As DAO.Recordset         'A place to store the query recordset
    Dim strSQL As String            'A place to store the query SQL ext
    
    Dim codeFGCCount As Long        'A place to store the FGC count
    Dim codeNALCPri As Long         'A place to store Nalcomis Priority
    Dim codeComputedPri As Long     'A place to store Computed Priority
    Dim codeMCN As String           'A place to store the MCN for writing the Computed Pri to a table
    Dim codeRFIQty As Long          'A place to store the RFI Quantity
    'same as FGCCount***** Dim codeDIFMQty As Long     'A place to store the amount of items in the DIFM for that FGC
    Dim codePriorityControl As Long 'A place to store the math for DIFM - FGCCount math
    Dim codeCounter As Long         'A place to store a counter for looping
    Dim codeFGCSame As String
    Dim CodeFields As String
    Dim codeFGC As String
    
    codeCounter = 0
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("qryForceUpdates")
    
            
    Do Until rs.EOF
        If codePriorityControl < codeCounter Then
            codeComputedPri = 2
            EvalPri = codeComputedPri
            codeCounter = codeCounter + 1
        Else
            codeComputedPri = 3
            EvalPri = codeComputedPri
        End If
        
        rs.MoveNext
    
    Loop
         
    'Get the FGCCount from a control named ctlFGCCount for looping purposes
    
    
    
    'Get the mcn from a control named ctlMCN on a Form named frmForceUpdates
    codeMCN = [Forms]![frmForceUpdates]![ctlMCN]
    
    
    'Get the RFI Quantity from a control name ctlRFIQty on a form named frmForceUpdates
    codeRFIQty = [Forms]![frmForceUpdates]![ctlRFIQty]
    
    codePriorityControl = ([Forms]![frmForceUpdates]![ctlERQty] + 1 + [Forms]![frmForceUpdates]![ctlAMD]) - [Forms]![frmForceUpdates]![ctlRFIQty]
    
    'Right now I have no way to reset the counter.  I was going to use codeFGCSame to track the FGC from one record to the next but haven't figured it out yet.
    'Loop thru and write computed pri
    
    If codePriorityControl < codeCounter Then
      codeComputedPri = 2
      EvalPri = codeComputedPri
      codeCounter = codeCounter + 1
    Else
      codeComputedPri = 3
      EvalPri = codeComputedPri
    End If
    
    'This if doesnt work
    If [Forms]![frmForceUpdates]![ctlAMD] < [Forms]![frmForceUpdates]![ctlRFIQty] Then
        codeComputedPri = 3
        EvalPri = codeComputedPri
    End If
    'rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    End Function

 

[modest]

Your code is discouraging to look at. You shouldn't use forums to have other people do your work. It should mainly be used to ask questions and learn. That being said break your code down into one part. For instance, the logic behind two is going to parallel the logic behind 3 (they'll just have different conditions), so don't include 3 because once you find the answer to 2, you'll know how to solve it.

That being said, a recordset will only look at one record at a time, so you'll have to loop through each record for calculations (it looks like you've got this far)
=======================
Ways to pull fields from a recordset:
- rs![Field Name].Value
- rs.Fields("Field Name").Value
- you can also use a loop to go through all the fields
(Know that the .Value can be included or left out)

Heed my suggestions and repost.