Subtracting a value on the current record from the value of a previous records issues

I get Run-time error '94'
Invalid use of Null

When first network number is entered, not sure why it relates to the following line of code.

Code: 
SysHopSpacing2 = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID))

I also can this be applied to already existing accounts that want to add a third base?
 
Make sure you place the code after you assign sysHop1
 
Here where I put the code, still get the same error message :confused:

Code: 
Dim PrevSysConID, SndPrevSysConID As Long, sysHop2 As Integer

  sysHop1 = DLookup("[hpHop1]", "tblHoppingPatterns", "[hpNetworkID] = " _
    & [Forms]![frmTempestCoordination]![frmSubSystem]![sysNetworkNumber])
    
    sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysSystemConfigID] <" & Me.sysSystemConfigID), 0)))
    
        'previous record
        PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)
        'next previous record
        SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & PrevSysConID), 0)
        '3rd network subtracted from the first
        sysHop2 = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID))
 
Replace:
sysHop2 = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID))
Click to expand...
With:
Code: 
 SysHop2 = Abs([sysHop1] - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
To eliminate the null error.

By the way, what is the DB trying to keep track of since you can't have these values if there is only 2 or 1 entries?
 
The user wants to subtrack the third entry network number by the first. He wants to ensure that the hop spacing is greater than 4. The way the database works is that a school or account can have up to 5 base stations 3 of one frequence and 2 of another, He wants to first and third base to be subtracted to ensure that the hop spacing is greater than 4 to ensure proper communication between the basestations. The database is to hold the schools system information and allow to enter new information to validate the hop spacing before the base stations are configured. We still have to subtract base 2 from base 1 then base 1 from base 3 base 4 from base 3 and base 5 from base 4. Its confusing as hell but thats what they want. The information was stored in an excel sheet but the data has out grown the spread sheet hence a access database to hold and validate the data. I know this is the most inefficient way to handle this but this is the tools we have available.
 
The null is gone now but sysHop2 is not being populated. I created a field in my systemconfiguration table called syshop2 to hold the value. Also I need to apply this to an existing records. Here is the entire code for calculation.

Code: 
Private Sub sysNetworkNumber_BeforeUpdate(Cancel As Integer)
'declared variables
Dim PrevSysConID, SndPrevSysConID As Long, sysHop2 As Integer
If CurrentProject.AllForms("frmSearchSchools").IsLoaded Then
 
    If Me.sysNetworkNumber < 0 Or Me.sysNetworkNumber > 63 Then
        Beep
        Forms!frmSearchSchools!lblMessage2.Caption = "Network Number is out of range, the Network Number must be greater than or equal to 0 or less than or equal to 63! "
        Cancel = True
        Me!sysNetworkNumber.Undo
        Forms!frmSearchSchools!cmdUpdateRecord.Enabled = False
    Else
        Forms!frmSearchSchools!cmdUpdateRecord.Enabled = True
        Forms!frmSearchSchools!lblMessage2.Caption = ""
    End If
    
    sysHop1 = DLookup("[hpHop1]", "tblHoppingPatterns", "[hpNetworkID] = " _
    & [Forms]![frmSearchSchools]![frmSubSystem]![sysNetworkNumber])
    
    'Calculates hop spacing for an existing account
    sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)))
    If Me.sysHopSpacing < 4 And Me.sysHopSpacing > 0 Then
        Beep
        If MsgBox("The hop spacing is less than 4, please select another network number. Do you want to view the hopping list? ", _
            vbYesNo + vbQuestion, "Select Another Network Number!") = vbYes Then
            DoCmd.OpenForm "frmFrequencyList"
        End If
        
        Cancel = True
        Me.sysNetworkNumber.Undo
        Forms!frmSearchSchools!lblMessageHop2.Caption = "Hop spacing is less than 4, please select another network number! "
        Forms!frmSearchSchools!cmdUpdateRecord.Enabled = False
    Else
        Forms!frmSearchSchools!cmdUpdateRecord.Enabled = True
        Forms!frmSearchSchools!lblMessageHop2.Caption = ""
    End If
    
Else
    
    If Me.sysNetworkNumber < 0 Or Me.sysNetworkNumber > 63 Then
        Beep
        Forms!frmTempestCoordination!lblMessage.Caption = "Network Number is out of range, the Network Number must be greater than or equal to 0 or less than or equal to 63! "
        Cancel = True
        Me!sysNetworkNumber.Undo
        Forms!frmTempestCoordination!cmdAddNew.Enabled = False
    Else
        Forms!frmTempestCoordination!cmdAddNew.Enabled = True
        Forms!frmTempestCoordination!lblMessage.Caption = ""
    End If
    
    sysHop1 = DLookup("[hpHop1]", "tblHoppingPatterns", "[hpNetworkID] = " _
    & [Forms]![frmTempestCoordination]![frmSubSystem]![sysNetworkNumber])
    
    'calculates hop spacing for a new account
    sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysSystemConfigID] <" & Me.sysSystemConfigID), 0)))
    
        'previous record
        PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)
        'next previous record
        SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & PrevSysConID), 0)
        '3rd network subtracted from the first
        sysHop2 = Abs([sysHop1] - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
    
    If Me.sysHopSpacing < 4 And Me.sysHopSpacing > 0 Then
        Beep
        If MsgBox("The hop spacing is less than 4, please select another network number. Do you want to view the hopping list? ", _
            vbYesNo + vbQuestion, "Select Another Network Number!") = vbYes Then
            DoCmd.OpenForm "frmFrequencyList"
        End If
        
        Cancel = True
        Me.sysNetworkNumber.Undo
        Forms!frmTempestCoordination!lblMessageHop.Caption = "Hop spacing is less than 4, please select another network number! "
        Forms!frmTempestCoordination!cmdAddNew.Enabled = False
    Else
        Forms!frmTempestCoordination!cmdAddNew.Enabled = True
        Forms!frmTempestCoordination!lblMessageHop.Caption = ""
    End If
End If
 
Your telling access that SysHop2 is only a variable declared in VBA:

Code: 
Dim PrevSysConID, SndPrevSysConID As Long[COLOR="Red"], sysHop2 As Integer[/COLOR]

If you now have a control on your form named SysHop2 then remove the declaration in your VBA.
 
The code works great! I need to find a way to implement it. I have a way but not sure how to keep it within the account. I thought about creating a field called base number and when the user reaches base 3, the code is executed to subtract the 3rd base from the 1st base. Can I use a auto number using DMax to imcrement by base number by 1 then reset itself when a new account is created?
 
If you don't want it to fire until there are 3 records, use the Dcount() function to count how many records were entered prior to the current record and only allow it to fire if there are 2 or more records prior to the current record.
Code: 
If DCount("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]) > 1 Then 'this means there are 2 or more previous records and the current record is at least the 3rd
PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)
SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & PrevSysConID), 0)
SysHop2 = Abs([sysHop1] - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
 
That works great, however can I preven the syshop2 from populating after the third record? Each account can have up to 5 base, the first three are in a different frequency ranage hence calculating base 3 from base 1. the last 2 base are different frequence so they will not interfer with the first 3 bases.

I can get it work by using auto increment field and firing the code once the field value equals 3, but i dont think that would be the optimal approach.
 
Change:
If DCount("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]) > 1 Then
Click to expand...
Code: 
If DCount("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    Forms!frmTempestCoordination!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]) = 2 Then 'will only fire on the 3rd record
 
Awesome! I am going to see If I can apply this to existing records! Thanks for you help!
 
I was able to get the code to work for existing records that have two systems which performed calculations perfectly when adding a 3rd system. The problem I am having now is how can I get the numbers to auto calucate for accounts that already have 3 or more systems? I tried the following code for an OnCurrent Event and tried OnLoad event for the system configuration subform but they did not work. Any suggestions?

Code: 
Dim PrevSysConID, SndPrevSysConID As Long
If CurrentProject.AllForms("frmSearchSchools").IsLoaded Then
 sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
    [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
    [Forms]![frmSearchSchools]![accAccountID] & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)))
    
        'previous record
        PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]), 0)
        'next previous record
        SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID] <" & PrevSysConID), 0)
    
    If DCount("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]) = 2 Then
        
        '3rd network subtracted from the first
        sysHop2 = Abs([sysHop1] - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
        
        sysHopSpacing = "-"
    Else
        sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysSystemConfigID] <" & Me.sysSystemConfigID), 0)))
        
        sysHop2 = "-"
    End If
End If
 
Here is a function you can run to get caught up. Only need to run this one time and then after that your form does the current records. You can place this code in a module and run it from the immediate window, Call SysHop2Up.
Code: 
Function SysHop2Up()
Dim Rst As DAO.Recordset, MySql As String
Dim PrevSysConID, SndPrevSysConID As Long
Dim X As Integer

For X = 1 To DMax("[sysAccountID]", "tblSystemConfiguration")
    If DCount("*", "tblSystemConfiguration", "[sysAccountID]=" & X) > 2 Then 'Calculate SysHop2
        MySql = "SELECT sysSystemConfigID, sysAccountID, sysHop1, sysHop2 FROM tblSystemConfiguration WHERE (((sysAccountID)=" & X & ")) ORDER BY sysSystemConfigID DESC;"
        Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenDynaset)
        Rst.MoveLast
        Rst.MoveFirst
        Do While Rst.EOF = False
            If DCount("*", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & Rst!sysSystemConfigID) = 2 Then 'this is the 3rd record
                'previous record
                PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & Rst!sysSystemConfigID), 0)
                'next previous record
                SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & PrevSysConID), 0)
                '3rd network subtracted from the first
                Rst.Edit
                Rst!SysHop2 = Abs(Rst!sysHop1 - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
                Rst.Update
            End If
        Rst.MoveNext
        Loop
        Rst.Close
        Set Rst = Nothing
    Else
        'Don't since there aren't at least 3 records
    End If
Next X

End Function
Let me know if I've forgotten something.
 
Thanks!! It works like a charm!!. I have one quesition, is there a way to append the database with your code that will leave hopspacing 1 empty on the third record and just populate sysHop2? example below. Just curious, if its too complex I would not worry about it. Overall this is the result the project sponsor wanted!! I really can't thank you enough for your advanced knowledge in VBA. I am learning a great deal from your code examples!!


This is how I use your code in the data entry to only populate third record spacing.

Code: 
If DCount("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID] <" & [sysSystemConfigID]) = 2 Then
        
        '3rd network subtracted from the first
        sysHop2 = Abs([sysHop1] - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
        
        sysHopSpacing = "-"
    Else
        sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & _
        Forms!frmSearchSchools!accAccountID & " And [sysSystemConfigID]=" & Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysSystemConfigID] <" & Me.sysSystemConfigID), 0)))
        
        sysHop2 = "-"
    End If
 
You can use the same procedure and adjust
Code: 
Function SysHop2Up()
Dim Rst As DAO.Recordset, MySql As String
Dim PrevSysConID, SndPrevSysConID As Long
Dim X As Integer

For X = 1 To DMax("[sysAccountID]", "tblSystemConfiguration")
    If DCount("*", "tblSystemConfiguration", "[sysAccountID]=" & X) > 2 Then 'Calculate SysHop2
        MySql = "SELECT sysSystemConfigID, sysAccountID, sysHop1, sysHop2[COLOR="Red"], sysHopSpacing[/COLOR] FROM tblSystemConfiguration WHERE (((sysAccountID)=" & X & ")) ORDER BY sysSystemConfigID DESC;"
        Set Rst = CurrentDb.OpenRecordset(MySql, dbOpenDynaset)
        Rst.MoveLast
        Rst.MoveFirst
        Do While Rst.EOF = False
            If DCount("*", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & Rst!sysSystemConfigID) = 2 Then 'this is the 3rd record
                'previous record
                PrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & Rst!sysSystemConfigID), 0)
                'next previous record
                SndPrevSysConID = Nz(DMax("[sysSystemConfigID]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID] <" & PrevSysConID), 0)
                '3rd network subtracted from the first
                Rst.Edit
                Rst!SysHop2 = Abs(Rst!sysHop1 - Nz(DLookup("[sysHop1]", "tblSystemConfiguration", "[sysAccountID]=" & X & " And [sysSystemConfigID]=" & SndPrevSysConID), 0))
                [COLOR="red"]Rst!sysHopSpacing = "-"[/COLOR]
                Rst.Update
            End If
        Rst.MoveNext
        Loop
        Rst.Close
        Set Rst = Nothing
    Else
        'Don't since there aren't at least 3 records
    End If
Next X

End Function

Rerun the function and it will make the adjustment. You can always rerun this function at any time and just add in whatever it is you've found you need updating.
 
Thanks again! I really appreciate all the help you have given me with this project! Everything works great!
 
You must log in or register to reply here.

Similar threads

A
Display a list of previous jobs with same serial number on current job form
Replies
13
Views
857
andymartin3186
A
A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
2,253
ebs17
E
D
Solved How to sort values (the highest first)? If duplicates values found, the sorting is based on the saved table for those values
Replies
13
Views
1,226
jdraw
jdraw
A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
580
AlliCarr
A
X
Is 32768 the maximum number of objects in a MS Access database?
2
Replies
25
Views
2,317
Edgar_
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom