Posting MS Access dBase

[HS_1]

LengthCal Forum.zipI wonder if I can post here database for the experts to view, comment on and point me in the right direction to fix the problems I am encountering?
It took me few months to create database which should calculate the total lengths of pipe and cable. The problem I have with it is that more often that not I am getting "#Error" msg. and not the values I am expecting plus the database is slow and I would say unstable. I wouldn't call myself armature programmer but more code assembler as the majority of the codes I found on the web or were written/corrected on this forum?
Thanks

[davegri]

I wonder if I can post here database for the experts to view, comment on and point me in the right direction to fix the problems I am encountering?

Sure. I wish more people would upload a DB early on, rather than trying to describe a problem such that we have to guess at the underlying reasons.

[pbaldy]

This may help:

https://www.accessforums.net/showthread.php?t=70301

[HS_1]

davegri, thanks for the replay,
I have attached the database to initial post.
My biggest problem is the #Error message
I also have few questions regarding the database
1. Is there a way to purge unused codes?
2. Some of the calculation codes are repeated, wonder if there is better way to re-write these.
3. When "Checker" logs into the database every change he makes show in red color, in order to do it I had to add 28 columns to the table plus bunch of conditional formatting for each column to achieve this, is there a better way to do it?
4. Do I need to store "Allowances" in tblLenCalc in order for the code to work?
I would really appreciate your help

[June7]

#Error message where?

1. purge? delete it
2. VBA procedures in a general module
3. Which table?
4. Which code?

Why do you have multiple tables for Clients and Suppl?

Advise to not use parentheses in naming convention. Only punctuation that should be used is underscore.

[davegri]

Sums in footers of continuous forms are very picky. Any code error anywhere in the module gives Access an opportunity to choke on them.
SO, don't get your sums that way. Here's some code in the attached DB that gets the sums via VBA.

LengthCal Forum-davegri-v01.zip

Option Compare Database
Option Explicit
Dim strUserName As String
Dim strInitial As String
Dim strUserPCNo As String
Dim strPipIso, strZone, StrSegment As String
Dim CmbSize As New FindAsYouTypeCombo
'<<<<<<<frmSingleLenCal
Private Function fcnGetFooterTots()
    Call fcnMakeNamedQuery("qFooterTots", Me.RecordSource)
    Me.txtTotalPipeLenght = DSum("P_lgth", "qFooterTots")
    Me.txtTotalTLN = DSum("TLN", "qFooterTots")
End Function




'Public Sub ClearClipBoard()
'    Dim oData   As New DataObject 'object to use the clipboard

And here's the fcnMakeNamedQuery in Mod_davegri:

Option Compare Database
Option Explicit


'---------------------------------------------------------------------------------------
' Procedure : fcnMakeNamedQuery
' DateTime  : 9/26/2006 20:57
' Author    : davegri
' Purpose   : Attach new SQL property to an existing querydef. If the Query doesn't exist,
'           : create it with the passed SQL.
'---------------------------------------------------------------------------------------
'
Function fcnMakeNamedQuery(qName As String, strPassedSQL As String)
    Dim qthisQuery As DAO.QueryDef
    If DCount("Name", "MSysObjects", "[Name] = " & Chr$(39) & qName & Chr$(39)) = 0 Then
        Set qthisQuery = CurrentDb.CreateQueryDef(qName, strPassedSQL)
    Else
        Set qthisQuery = CurrentDb.QueryDefs(qName)
        qthisQuery.SQL = strPassedSQL
    End If
    Application.RefreshDatabaseWindow
    Set qthisQuery = Nothing
End Function

And here's what kicks off the process:

Public Sub FilterForm()
    Dim strsql As String
    Dim numbertoshow As Integer
    Dim TotalRecords As Integer


    TotalRecords = DCount("*", "qryLenCalc")
    strsql = "Select * from qryLenCalc Order By LenID"
    If cmboNumber <> "<All>" And Not IsNull(Me.cmboNumber) Then
        numbertoshow = CInt(Me.cmboNumber)
        'Debug.Print TotalRecords - numbertoshow
        If TotalRecords > numbertoshow Then
            strsql = "Select * from qryLenCalc where LenID NOT IN (Select Top " & (TotalRecords - numbertoshow) & " LenID from qryLenCalc Order By LenID) Order By LenID"
        End If
    End If
    Me.RecordSource = strsql
    Call fcnGetFooterTots
    'Debug.Print strSQL
End Sub

[HS_1]

davegri, Thank you, the codes you wrote fixed my problem with the "#Error" msg.
I wonder if it would be possible to update the code so when any of the filters is applied the sum function updates as well?


I really appreciate your help

[HS_1]

June7
Thank you for your response.
1. Yes I would like to delete the unused codes in modules. I copied some codes for functions I deleted later on but now I am not sure which module codes are no longer needed.
2. Yes the the modules,
3. tblLenCalc, all the columns I added to change the color start from "Lsc" all the way to "TLNc"
4. "Public Function cablen", "Public Function fcnSLen" & Private Sub CalculateLength() I use to calculate the length.

The Client table is used to store names of the client we do projects for and the supplier is the company we use the material for that project (each supplier has slightly different table allowances and also client has its own,) My idea was that the user choose the client and 2 allowance tables based on which the length calculations have been calculated.

Thanks for the advise on parentheses, the reason I used () naming the table was that the user can put whatever text he wants in the brackets when creating new table as long as the table name starts with tblPipeAllow or tblPipeSup and the code behind it will still update tables for calculations:
"If tdf.Name Like "tblPipeAllow(*)" Then"

[davegri]

davegri, Thank you, the codes you wrote fixed my problem with the "#Error" msg.
I wonder if it would be possible to update the code so when any of the filters is applied the sum function updates as well?

OK. replace the function with this code:

'Private Function fcnGetFooterTots()
'    Call fcnMakeNamedQuery("qFooterTots", Me.RecordSource)
'    Me.txtTotalPipeLenght = DSum("P_lgth", "qFooterTots")
'    Me.txtTotalTLN = DSum("TLN", "qFooterTots")
'End Function


Private Function fcnGetFooterTots()
    Dim rsc As DAO.Recordset
    Dim nPL As Long, nTLN As Long
    If Me.RecordsetClone.RecordCount = 0 Then
        Me.txtTotalPipeLenght = 0
        Me.txtTotalTLN = 0
        Exit Function
    End If
    Set rsc = Me.RecordsetClone
    With rsc
        .MoveLast: .MoveFirst
        Do While Not .EOF
            nPL = nPL + !P_lgth
            nTLN = nTLN + !TLN
            .MoveNext
        Loop
    End With
    Me.txtTotalPipeLenght = nPL
    Me.txtTotalTLN = nTLN
    Set rsc = Nothing
End Function

Then add the function call from the other two procedures, CmboFilterForm() and TxtSearchForm()

    If Len(strsql) = 0 Then
        Me.FilterOn = False    'if empty then turn off filter
    Else
        Me.Filter = strsql    'otherwise put it on!
        Me.FilterOn = True
        Call fcnGetFooterTots
    End If

[HS_1]

davegri,
Thank you very much

[June7]

Can do a search in VBA to see if a procedure is used elsewhere. However, that won't help if functions are called from queries or other objects. There are 3rd party add-ins offering functionality of searching object properties. V-Tools is one popular freeware https://www.skrol29.com/us/vtools.php

A well-designed db should not need to nor allow users to routinely modify structure.

[HS_1]

June7, thank you for the link and tips, I will set an administrative right to the db so only one person can modify it.