[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

update status bar in access

Posted on 2011-05-01
7
Medium Priority
?
990 Views
Last Modified: 2013-11-27
I have updated the status bar before but in my new db it updates but is not visible its as if each docmd is affecting it.

not sure how to proceed i have 14 processes run and i wanted to indicate to the user where the process status was via the status bar.

I am starting at 14 and decrementing the counter after each process
ProcessCounter = 14
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

am in access vba 10
Private Sub BtnGetDerivedData_Click()

Dim db As DAO.Database
Set db = CurrentDb
Dim rsDerived As DAO.Recordset
Set rsDerived = db.OpenRecordset("Tclient")
DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000
Dim strtest As String
DoCmd.SetWarnings False
ProcessCounter = 14
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'empty abi results table
DoCmd.RunSQL "DELETE TblAbiTestResults.*" _
& " FROM TblAbiTestResults;"

''get bhp derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'    .Edit
'    .Fields("ClientBHPDerived").Value = GetDigits(.Fields("model_description").Value)
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
''get valves derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'        If (createtestclass(.Fields("model_description").Value, 0, "GetValvesStr")) = "0" Then
'            .Edit
'            .Fields("ClientValvesDerived").Value = Null
'            .Update
'        Else
'            .Edit
'            .Fields("ClientValvesDerived").Value = createtestclass(.Fields("model_description").Value, 0, "GetValvesStr")
'            .Update
'        End If
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
''get drive derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'    .Edit
'    .Fields("ClientDriveDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrDrive")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
'
'
'
'
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'     If (createtestclass(.Fields("model_description").Value, 0, "GetstrNom")) = "0" Then
'       .Edit
'            .Fields("ClientNomDerived").Value = Null
'            .Update
'     Else
'
'    .Edit
'    .Fields("ClientNomDerived").Value = createtestclass(.Fields("model_description").Value, 0, "GetstrNom")
'    .Update
'    End If
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientCabDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrCab")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientRoofDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrRoof")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientWheelbaseDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrWheelbase")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With

ProcessCounter = ProcessCounter - 1
'append records to process to test table
DoCmd.RunSQL "INSERT INTO TblAbiTestResults ( AbiCodeMvris, abiCode, CWCODE, ValidatorType, VehicleCategoryCode )" _
& " SELECT [abimatch] & [qryabimatch]![mvris code] AS AbiCodeMvris, tClient.abiCode, QryAbiMatch.[MVRIS CODE], 1 AS ValidatorType, SMMT.[VEHICLE CATEGORY CODE]" _
& " FROM (QryAbiMatch LEFT JOIN tClient ON QryAbiMatch.AbiMatch = tClient.abiCode) LEFT JOIN SMMT ON QryAbiMatch.[MVRIS CODE] = SMMT.[MVRIS CODE];"

ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'bhp test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.BhpResult = IIf((IsNull([clientbhpderived])=True" _
& " Or IsNull([calc bhp])=True),-1,createtestclass([ClientBHPDerived],[Calc Bhp],""BHP""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'cab test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.CabResult = IIf((IsNull([clientcabderived])=True" _
& " Or IsNull([cab TYPE])=True Or [clientcabderived]=""""),-1,createtestclass([clientcabderived],[cab type],""cab""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

' cc test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.CCResult = IIf((IsNull([engine_cc])=True Or IsNull([cc])=True),-1,createtestclass([engine_cc],[cc],""CC""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

' doors test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.DoorsResult = IIf((IsNull([abidoors])=True" _
& " Or IsNull([doors])=True),-1,createtestclass([abidoors],[doors],""Doors""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'drive test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.DriveResult = IIf((IsNull([clientdrivederived])=True" _
& " Or IsNull([drive type])=True Or [clientdrivederived]=""""),-1,createtestclass([clientdrivederived],[drive type],""DriveFwd""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'Fuel test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.fuelResult = IIf((IsNull([engine_type])=True Or IsNull([fuel])=True),-1,createtestclass([engine_type],[fuel],""fuel"")); "
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'Roof test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.RoofResult = IIf((IsNull([clientroofderived])=True Or IsNull([VAN ROOF CONFIG])=True " _
& " Or [clientroofderived]=""""),-1,createtestclass([clientroofderived],[VAN ROOF CONFIG],""roof""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'Transmission test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.TransmissionResult = IIf((IsNull([transmission_type])=True" _
& " Or IsNull([transmission])=True),-1,createtestclass([transmission_type],[transmission],""Transmission""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'Valves test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.ValvesResult = IIf((IsNull([clientvalvesderived])=True Or IsNull([no cylinders])=True" _
& " Or IsNull([valves per cylinder])=True),-1,createtestclass([clientvalvesderived],[no cylinders],""Valves"",[valves per cylinder]));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'Wheelbase test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.WheelbaseResult = IIf((IsNull([clientwheelbasederived])=True" _
& " Or IsNull([WHEELBASE TYPE])=True Or [clientwheelbasederived]=""""),-1,createtestclass([clientwheelbasederived],[wheelbase type],""WheelBase""));"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'delete existing abi validator data from main validator table prior to append
DoCmd.RunSQL "DELETE *" _
& " FROM TblCWCodesValidatedAll" _
& " WHERE (TblCWCodesValidatedAll.validatorType='1');"
ProcessCounter = ProcessCounter - 1
strtest = SysCmd(acSysCmdSetStatus, "Process Remaining: " & CStr(ProcessCounter))

'append abi validator data to main validator table
DoCmd.RunSQL "INSERT INTO TblCWCodesValidatedAll ( ClientCodeMvris, ValidatorType, BhpResult, CabResult, CCResult, DoorsResult, DriveResult, FuelResult, RoofResult, TransmissionResult, ValvesResult, WheelbaseResult, [MVRIS CODE] )" _
& " SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.ValidatorType, TblAbiTestResults.BhpResult, TblAbiTestResults.CabResult, TblAbiTestResults.CCResult," _
& " TblAbiTestResults.DoorsResult, TblAbiTestResults.DriveResult, TblAbiTestResults.FuelResult, TblAbiTestResults.RoofResult, TblAbiTestResults.TransmissionResult," _
& " TblAbiTestResults.ValvesResult, TblAbiTestResults.WheelbaseResult, TblAbiTestResults.CWCode" _
& " FROM TblAbiTestResults" _
& " WHERE (((TblAbiTestResults.BhpResult)=0)) OR (((TblAbiTestResults.CabResult)=0)) OR (((TblAbiTestResults.CCResult)=0)) OR (((TblAbiTestResults.DoorsResult)=0))" _
& " OR (((TblAbiTestResults.DriveResult)=0)) OR (((TblAbiTestResults.FuelResult)=0)) OR (((TblAbiTestResults.RoofResult)=0)) OR (((TblAbiTestResults.TransmissionResult)=0))" _
& " OR (((TblAbiTestResults.ValvesResult)=0)) OR (((TblAbiTestResults.WheelbaseResult)=0));"


  








DoCmd.SetWarnings True
End Sub

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 4
  • 3
7 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 2000 total points
ID: 35501662
You need to update the meter, not just set the status bar text.  See the attached code.
'Initialize the progress bar (using an arbitrary division of 4 units)
   varReturn = SysCmd(acSysCmdInitMeter, _
      "Creating output file ...", 4)

   'Update the progress bar
   varReturn = SysCmd(acSysCmdUpdateMeter, 1)
   varReturn = SysCmd(acSysCmdSetStatus, "Status bar message")

====================
   'Update the progress bar
   varReturn = SysCmd(acSysCmdUpdateMeter, 2)
   varReturn = SysCmd(acSysCmdSetStatus, "Status bar message")


====================
   'Update the progress bar
   varReturn = SysCmd(acSysCmdUpdateMeter, 3)
   varReturn = SysCmd(acSysCmdSetStatus, "Status bar message")


====================
   'Update the progress bar
   varReturn = SysCmd(acSysCmdUpdateMeter, 4)
   varReturn = SysCmd(acSysCmdSetStatus, "Status bar message")

====================

   'Remove the progress bar
   varReturn = SysCmd(acSysCmdRemoveMeter)

Dynamic slice numbering:

   varReturn = SysCmd(acSysCmdUpdateMeter, i)
   i = i + 1

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35501664
Also, usually the progress bar would start empty and gradually fill up, so having it start full and gradually become empty might be confusing to the users.
0
 

Author Comment

by:PeterBaileyUk
ID: 35502841
I have started as you suggested but it halts here


varReturn = SysCmd(acSysCmdUpdateMeter, 2)
varReturn = SysCmd(acSysCmdSetStatus, "Creating records to process ...")

It says illegal function call 7952

do i have to remove the meter after each process?

Private Sub BtnGetDerivedData_Click()

Dim db As DAO.Database
Set db = CurrentDb
Dim rsDerived As DAO.Recordset
Set rsDerived = db.OpenRecordset("Tclient")
DAO.DBEngine.SetOption dbMaxLocksPerFile, 100000

DoCmd.SetWarnings False
'Initialize the progress bar (using an arbitrary division of 4 units)
   varReturn = SysCmd(acSysCmdInitMeter, _
      "Deleting old data", 4)

varReturn = SysCmd(acSysCmdUpdateMeter, 1)
varReturn = SysCmd(acSysCmdSetStatus, "Deleting old data ...")

'empty abi results table
DoCmd.RunSQL "DELETE TblAbiTestResults.*" _
& " FROM TblAbiTestResults;"

''get bhp derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'    .Edit
'    .Fields("ClientBHPDerived").Value = GetDigits(.Fields("model_description").Value)
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
''get valves derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'        If (createtestclass(.Fields("model_description").Value, 0, "GetValvesStr")) = "0" Then
'            .Edit
'            .Fields("ClientValvesDerived").Value = Null
'            .Update
'        Else
'            .Edit
'            .Fields("ClientValvesDerived").Value = createtestclass(.Fields("model_description").Value, 0, "GetValvesStr")
'            .Update
'        End If
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
''get drive derived
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'    .Edit
'    .Fields("ClientDriveDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrDrive")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
'
'
'
'
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'     If (createtestclass(.Fields("model_description").Value, 0, "GetstrNom")) = "0" Then
'       .Edit
'            .Fields("ClientNomDerived").Value = Null
'            .Update
'     Else
'
'    .Edit
'    .Fields("ClientNomDerived").Value = createtestclass(.Fields("model_description").Value, 0, "GetstrNom")
'    .Update
'    End If
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientCabDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrCab")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientRoofDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrRoof")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With
'
'With rsDerived
'.MoveFirst
'     Do Until .EOF
'    If rsDerived.BOF And rsDerived.EOF = True Then
'    ' no records
'    Else
'
'
'    .Edit
'    .Fields("ClientWheelbaseDerived").Value = createtestclass(.Fields("model_description").Value, "Abi", "GetStrWheelbase")
'    .Update
'
'    End If
'    .MoveNext
'    Loop
'End With

varReturn = SysCmd(acSysCmdUpdateMeter, 2)
varReturn = SysCmd(acSysCmdSetStatus, "Creating records to process ...")

'append records to process to test table
DoCmd.RunSQL "INSERT INTO TblAbiTestResults ( AbiCodeMvris, abiCode, CWCODE, ValidatorType, VehicleCategoryCode )" _
& " SELECT [abimatch] & [qryabimatch]![mvris code] AS AbiCodeMvris, tClient.abiCode, QryAbiMatch.[MVRIS CODE], 1 AS ValidatorType, SMMT.[VEHICLE CATEGORY CODE]" _
& " FROM (QryAbiMatch LEFT JOIN tClient ON QryAbiMatch.AbiMatch = tClient.abiCode) LEFT JOIN SMMT ON QryAbiMatch.[MVRIS CODE] = SMMT.[MVRIS CODE];"

varReturn = SysCmd(acSysCmdUpdateMeter, 3)
varReturn = SysCmd(acSysCmdSetStatus, "Testing BHP ...")

'bhp test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.BhpResult = IIf((IsNull([clientbhpderived])=True" _
& " Or IsNull([calc bhp])=True),-1,createtestclass([ClientBHPDerived],[Calc Bhp],""BHP""));"


varReturn = SysCmd(acSysCmdUpdateMeter, 4)
varReturn = SysCmd(acSysCmdSetStatus, "Testing CAB ...")

'cab test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.CabResult = IIf((IsNull([clientcabderived])=True" _
& " Or IsNull([cab TYPE])=True Or [clientcabderived]=""""),-1,createtestclass([clientcabderived],[cab type],""cab""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 5)
varReturn = SysCmd(acSysCmdSetStatus, "Testing CC ...")

' cc test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.CCResult = IIf((IsNull([engine_cc])=True Or IsNull([cc])=True),-1,createtestclass([engine_cc],[cc],""CC""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 6)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Doors ...")

' doors test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.DoorsResult = IIf((IsNull([abidoors])=True" _
& " Or IsNull([doors])=True),-1,createtestclass([abidoors],[doors],""Doors""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 7)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Drive ...")

'drive test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.DriveResult = IIf((IsNull([clientdrivederived])=True" _
& " Or IsNull([drive type])=True Or [clientdrivederived]=""""),-1,createtestclass([clientdrivederived],[drive type],""DriveFwd""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 8)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Fuel ...")

'Fuel test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.fuelResult = IIf((IsNull([engine_type])=True Or IsNull([fuel])=True),-1,createtestclass([engine_type],[fuel],""fuel"")); "

varReturn = SysCmd(acSysCmdUpdateMeter, 9)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Roofs ...")

'Roof test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.RoofResult = IIf((IsNull([clientroofderived])=True Or IsNull([VAN ROOF CONFIG])=True " _
& " Or [clientroofderived]=""""),-1,createtestclass([clientroofderived],[VAN ROOF CONFIG],""roof""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 10)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Transmission ...")

'Transmission test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.TransmissionResult = IIf((IsNull([transmission_type])=True" _
& " Or IsNull([transmission])=True),-1,createtestclass([transmission_type],[transmission],""Transmission""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 11)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Valves ...")

'Valves test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.ValvesResult = IIf((IsNull([clientvalvesderived])=True Or IsNull([no cylinders])=True" _
& " Or IsNull([valves per cylinder])=True),-1,createtestclass([clientvalvesderived],[no cylinders],""Valves"",[valves per cylinder]));"

varReturn = SysCmd(acSysCmdUpdateMeter, 12)
varReturn = SysCmd(acSysCmdSetStatus, "Testing Wheelbase ...")

'Wheelbase test
DoCmd.RunSQL "UPDATE (TblAbiTestResults LEFT JOIN tClient ON TblAbiTestResults.AbiCode = tClient.abiCode)" _
& " LEFT JOIN SMMT ON TblAbiTestResults.CWCODE = SMMT.[MVRIS CODE] SET TblAbiTestResults.WheelbaseResult = IIf((IsNull([clientwheelbasederived])=True" _
& " Or IsNull([WHEELBASE TYPE])=True Or [clientwheelbasederived]=""""),-1,createtestclass([clientwheelbasederived],[wheelbase type],""WheelBase""));"

varReturn = SysCmd(acSysCmdUpdateMeter, 13)
varReturn = SysCmd(acSysCmdSetStatus, "Deleting old abi records from master table ...")

'delete existing abi validator data from main validator table prior to append
DoCmd.RunSQL "DELETE *" _
& " FROM TblCWCodesValidatedAll" _
& " WHERE (TblCWCodesValidatedAll.validatorType='1');"

varReturn = SysCmd(acSysCmdUpdateMeter, 14)
varReturn = SysCmd(acSysCmdSetStatus, "Appending new validator records to master table ...")

'append abi validator data to main validator table
DoCmd.RunSQL "INSERT INTO TblCWCodesValidatedAll ( ClientCodeMvris, ValidatorType, BhpResult, CabResult, CCResult, DoorsResult, DriveResult, FuelResult, RoofResult, TransmissionResult, ValvesResult, WheelbaseResult, [MVRIS CODE] )" _
& " SELECT TblAbiTestResults.AbiCodeMvris, TblAbiTestResults.ValidatorType, TblAbiTestResults.BhpResult, TblAbiTestResults.CabResult, TblAbiTestResults.CCResult," _
& " TblAbiTestResults.DoorsResult, TblAbiTestResults.DriveResult, TblAbiTestResults.FuelResult, TblAbiTestResults.RoofResult, TblAbiTestResults.TransmissionResult," _
& " TblAbiTestResults.ValvesResult, TblAbiTestResults.WheelbaseResult, TblAbiTestResults.CWCode" _
& " FROM TblAbiTestResults" _
& " WHERE (((TblAbiTestResults.BhpResult)=0)) OR (((TblAbiTestResults.CabResult)=0)) OR (((TblAbiTestResults.CCResult)=0)) OR (((TblAbiTestResults.DoorsResult)=0))" _
& " OR (((TblAbiTestResults.DriveResult)=0)) OR (((TblAbiTestResults.FuelResult)=0)) OR (((TblAbiTestResults.RoofResult)=0)) OR (((TblAbiTestResults.TransmissionResult)=0))" _
& " OR (((TblAbiTestResults.ValvesResult)=0)) OR (((TblAbiTestResults.WheelbaseResult)=0));"

'Remove the progress bar
   varReturn = SysCmd(acSysCmdRemoveMeter)
  








DoCmd.SetWarnings True
End Sub

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35506129
Generally speaking, you only need to update the status bar text when you create the progress bar, and when you remove it, so you can delete the other SetStatus lines.  You only need to remove the progress bar at the end.

You need to declare varReturn as Variant.

0
 

Author Comment

by:PeterBaileyUk
ID: 35506222
Hi Helen
done the declaration but i dont understand entirely what you mean:

you only need to update the status bar text when you create the progress bar, and when you remove it,

it still says illegal function call at

varReturn = SysCmd(acSysCmdUpdateMeter, 2)
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35712171
Perhaps it would be helpful to see a progress bar in action -- download my Access Archon #181 and look at the code in the sample database.   Here is a link:

http://www.helenfeddema.com/Files/accarch181.zip
0
 

Author Comment

by:PeterBaileyUk
ID: 35713130
thx for the link will have a look tomorrow morning.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question