Database Not Responding

[Subwind]

So, I have built a little pricing tool (still tidying it up) and I have an issue. I have an unbound field box with some code in it. This code is to work out the part number of the finished item based on the selections in lists boxes.

When the database is opened, it should load the one form I have, however it hangs.

I know the code that makes it hang.

=IIf([lstFrequency] Is Null,"",IIf([lstCable] Is Null,"",IIf([lstArmour] Is Null,"",IIf([lstEndA] Is Null,"",IIf([lstEndB] Is Null,"",IIf([txtLength]=0,"","SL" & [Forms]![frmSilverLineCalculator]![lstArmour].[Column](1) & [Forms]![frmSilverLineCalculator]![lstFrequency].[Column](1) & [Forms]![frmSilverLineCalculator]![lstCable].[Column](1) & "-" & IIf([Forms]![frmSilverLineCalculator]![lstEndA].[Column](3)<[Forms]![frmSilverLineCalculator]![lstEndB].[Column](3),[Forms]![frmSilverLineCalculator]![lstEndA].[Column](1),[Forms]![frmSilverLineCalculator]![lstEndB].[Column](1)) & IIf([Forms]![frmSilverLineCalculator]![lstEndB].[Column](3)>[Forms]![frmSilverLineCalculator]![lstEndA].[Column](3),[Forms]![frmSilverLineCalculator]![lstEndB].[Column](1),[Forms]![frmSilverLineCalculator]![lstEndA].[Column](1)) & "-" & Format([Forms]![frmSilverLineCalculator]![txtLength],"00.00") & "M"))))))

If I open the database with the shift key, edit the form, remove this code, the database works fine. If I then place this code back in the field box, works fine. Compact and Repair, works fine. Close and re-open, and now it hangs.

Is there anyway I can shorten the code I have used?




Please see attached >> Amphenol SilverLine Calculator.zip <<

Also, this is not finished yet so no error handling on the VBA code yet.

~Matt

[Missinglinq]

The main syntax

[lstFrequency] Is Null

is valid in SQL but is not valid in Access VBA. The correct syntax here would be

IsNull([lstFrequency])

Linq ;0)>

[ranman256]

If you have complex if statements, try making a function and let IT do the decisions.

[Subwind]

Public Function strSilverLinePartNumber()
On Error GoTo strSilverLinePartNumber_Err

    Dim strSilverLinePartNumberCable As String
        strSilverLinePartNumberCable = "SL" & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstArmour]") & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstCable]") & DLookup("[FrequencyCode]", "tblFrequencyList", "FrequencyID = [Forms]![frmSilverLineCalculator]![lstFrequency]")
    Dim strSilverLinePartNumberSeparator As String
        strSilverLinePartNumberSeparator = "-"
    Dim strSilverLinePartNumberConnectors As String
        If DLookup("[ComponentSequence]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]") < DLookup("[ComponentSequence]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]") Then
            strSilverLinePartNumberConnectors = DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]") & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]")
        Else
            strSilverLinePartNumberConnectors = DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]") & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]")
        End If
    Dim strSilverLinePartNumberLength
        strSilverLinePartNumberLength = (Format([Forms]![frmSilverLineCalculator]![txtLength], "00.00")) & "M"

    If (Eval("[Forms]![frmSilverLineCalculator]![lstFrequency] Is Null")) Then
            strSilverLinePartNumber = "Please Select Frequency"
    ElseIf (Eval("[Forms]![frmSilverLineCalculator]![lstCable] Is Null")) Then
            strSilverLinePartNumber = "Please Select Cable"
    ElseIf (Eval("[Forms]![frmSilverLineCalculator]![lstArmour] Is Null")) Then
            strSilverLinePartNumber = "Please Select Armour"
    ElseIf (Eval("[Forms]![frmSilverLineCalculator]![lstEndA] Is Null")) Then
            strSilverLinePartNumber = "Please Select End A"
    ElseIf (Eval("[Forms]![frmSilverLineCalculator]![lstEndB] Is Null")) Then
            strSilverLinePartNumber = "Please Select End B"
    ElseIf (Eval("[Forms]![frmSilverLineCalculator]![txtLength] = 0")) Then
            strSilverLinePartNumber = "Please Enter Length"
    Else
            strSilverLinePartNumber = strSilverLinePartNumberCable & strSilverLinePartNumberSeparator & strSilverLinePartNumberConnectors & strSilverLinePartNumberSeparator & strSilverLinePartNumberLength
    End If
    
strSilverLinePartNumber_Exit:
    Exit Function

strSilverLinePartNumber_Err:
    MsgBox Error$
    Resume strSilverLinePartNumber_Exit
    
End Function

I have the above code now, however when I reset the list boxes I now get an "Invalid Use of Null" message box. When all selections are made, the code works fine.

Am wondering, do I need my Dim As Strings as part of the last If statement? IE, only work out the part number IF all selections made?

[Subwind]

    Dim strSilverLinePartNumberConnectors As String
        If DLookup("[ComponentSequence]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]") < DLookup("[ComponentSequence]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]") Then
            strSilverLinePartNumberConnectors = DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]") & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]")
        Else
            strSilverLinePartNumberConnectors = DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndB]") & DLookup("[ComponentCode]", "tblComponentList", "ComponentID = [Forms]![frmSilverLineCalculator]![lstEndA]")
        End If

Okay, so I tested each section and it is only the section above that gives the Null error....

[Subwind]

Update, nested the Dim As Strings in the final If statement and it works now. Will post the final code here once I have finished testing.

[Subwind]

Okay, so I have this working now. I created it as an install package. Icon and everything.

Does anyone know of a way of making it so I do not get the security warning at the application start?

This is going out to customers and I know the code is safe, there is not alot there, but I don't want the security warning at the front.

SilverLineCalculator.zip

[Subwind]

Okay, so I have this working now, but I am not sure the registry key is what I really want. It removes ALL access warnings.

Has anyone had any luck with the install package and creating a key to add the location to the trusted locations on install?

[Subwind]

So, I think I have this working now. Would appreciate if someone could install the package and let me know if they find any bugs. I have also attached the full copy for any suggestions/improvements in my coding.

SilverLine Calculator.zip
SilverLineCalculator Installer.zip

I have a much larger database than this working, but it is used in house and the attached one is something I want to be able to send out to customers, hence the install package and request for bug finding.

~Matt