Access VBA Loop

MS Access VBA – Looping through records


Sub LoopRecExample()
On Error GoTo Error_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim iCount As Integer

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName") 'open the recordset for use (table, Query, SQL Statement)

With rs
If .RecordCount <> 0 Then 'Ensure that there are actually records to work with
'The next 2 line will determine the number of returned records
rs.MoveLast 'This is required otherwise you may not get the right count
iCount = rs.RecordCount 'Determine the number of returned records

Do While Not .BOF
'Do something with the recordset/Your Code Goes Here
.MovePrevious
Loop
End If
End With

rs.Close 'Close the recordset

Error_Handler_Exit:
On Error Resume Next
'Cleanup after ourselves
Set rs = Nothing
Set db = Nothing
Exit Sub

Error_Handler:
MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
Err.Number & vbCrLf & "Error Source: LoopRecExample" & vbCrLf & "Error Description: " & _
Err.Description, vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Sub

Night Porter Email (Pyinstaller, PiP etc)

1. Install Python 2.7

2. Install PIP (from http://stackoverflow.com/questions/4750806/how-to-install-pip-on-windows)
Python 2.x and Python ≤ 3.3

Flying in the face of its ‘batteries included’ motto, Python ships without a package manager. To make matters worse, Pip was–until recently–ironically difficult to install.

Official instructions

Per http://www.pip-installer.org/en/latest/installing.html

Download get-pip.py, being careful to save it as a .py file rather than .txt. Then, run it from the command prompt.

python get-pip.py
You possibly need an administrator command prompt to do this. Follow http://technet.microsoft.com/en-us/library/cc947813(v=ws.10).aspx

Alternative instructions

The official documentation tells users to install Pip and each its dependencies from source. That’s tedious for the experienced, and prohibitively difficult for newbies.

For our sake, Christoph Gohlke prepares Windows installers (.msi) for popular Python packages. He builds installers for all Python versions, both 32 and 64 bit. You need to

Install setuptools http://www.lfd.uci.edu/~gohlke/pythonlibs/#setuptools
Install pip http://www.lfd.uci.edu/~gohlke/pythonlibs/#pip
For me, this installed Pip at C:Python27Scriptspip.exe. Find pip.exe on your computer, then add its folder (eg. C:Python27Scripts) to your path (Start / Edit environment variables). Now you should be able to run pip from the command line. Try installing a package:

pip install httpie
There you go (hopefully)! Solutions for common problems are given below:

Proxy problems

If you work in an office, you might be behind a HTTP proxy. If so, set the environment variables http_proxy and https_proxy. Most Python applications (and other free software) respect these. Example syntax:

http://proxy_url:port
http://username:password@proxy_url:port
If you’re really unlucky, your proxy might be a Microsoft NTLM proxy. Free software can’t cope. The only solution is to install a free software friendly proxy that forwards to the nasty proxy. http://cntlm.sourceforge.net/

Unable to find vcvarsall.bat

Python modules can be part written in C or C++. Pip tries to compile from source. If you don’t have a C/C++ compiler installed and configured, you’ll see this cryptic error message.

Error: Unable to find vcvarsall.bat

You can fix that by installing a C++ compiler such as MinGW or Visual C++, but again it’s often easier to check Christoph’s site for your package http://www.lfd.uci.edu/~gohlke/pythonlibs/

3. Install Pyinstaller
http://pythonhosted.org/PyInstaller/#installing-using-pip

4. Apply updated to porters.py via BitBucket

5. Using updated porters.py run
pyinstaller –onefile –noconsole –icon=portericon.ico porters.py

Access VBA > MySQL Update TimeSheetRates

Private Sub UpdateTimeSheetCalculations()
On Error GoTo Err_UpdateTimeSheetCalculations
‘this simple subroutine looks at the MySQL View “time_sheet_calc_dynamic” and copies the calculated values therein to the time_sheet table
‘The “time_sheet_calc_dynamic” does not include records where the “ts_calculation_completed” column is set to 1. The DB Manager can switch this flag
‘on and off via the Time Sheet Management form or when running a report for the timesheet. This means calculations made are “frozen in time”
‘with the rates as they were so subsequent changes to mileage or on-call rates are not re-calculated.

Dim strSQLUpdate As String

strSQLUpdate = “UPDATE time_sheet AS x INNER JOIN time_sheet_calc_dynamic AS y ON y.ts_id = x.ts_id SET ”

strSQLUpdate = strSQLUpdate & “x.ts_MonMileage = y.MonMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_TueMileage = y.TueMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileage = y.wedMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileage = y.thuMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileage = y.friMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileage = y.satMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileage = y.sunMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileage = y.mamMileage,”
strSQLUpdate = strSQLUpdate & “x.ts_MonStandbyRate = y.MonStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_tueStandbyRate = y.tueStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_wedStandbyRate = y.wedStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_thuStandbyRate = y.thuStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_friStandbyRate = y.friStandbyRate,”
strSQLUpdate = strSQLUpdate & “x.ts_MonTotalHours = y.MonTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_tueTotalHours = y.tueTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_wedTotalHours = y.wedTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_thuTotalHours = y.thuTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_friTotalHours = y.friTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_satTotalHours = y.satTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_sunTotalHours = y.sunTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_mamTotalHours = y.mamTotalHours,”
strSQLUpdate = strSQLUpdate & “x.ts_MonMileageCosts = y.MonMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_tueMileageCosts = y.tueMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_wedMileageCosts = y.wedMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_thuMileageCosts = y.thuMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_friMileageCosts = y.friMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_satMileageCosts = y.satMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_sunMileageCosts = y.sunMileageCosts,”
strSQLUpdate = strSQLUpdate & “x.ts_mamMileageCosts = y.mamMileageCosts;”

CurrentDb.Execute strSQLUpdate, dbFailOnError

Exit_UpdateTimeSheetCalculations:
Exit Sub
Err_UpdateTimeSheetCalculations:
MsgBox Err.description
Resume Exit_UpdateTimeSheetCalculations
End Sub

Dynamic MySQL View for Calculated fields

the field on it’s own:
SELECT IF(z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user=’MinHourPerJourney’) > IfNull(z.ts_mon_hours,0), z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user=’MinHourPerJourney’), z.ts_mon_hours) TotalHours

mileagecosts:
(journeys * mileageforuser) * pricepermile
(IfNull(z.ts_mon_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code=’PricePerMile’) MileageCosts

FINAL STATEMENT:

CREATE VIEW time_sheet_calc_dynamic As SELECT z.ts_id, 
IfNull(z.ts_mon_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) MonMileage, 
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_mon_standby),0) MonStandbyRate, 
IfNull((SELECT IF(z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_mon_hours,0), z.ts_mon_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_mon_hours)),0) MonTotalHours, 
(IfNull(z.ts_mon_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") MonMileageCosts,
IfNull(z.ts_tue_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) TueMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_Tue_standby),0) TueStandbyRate,
IfNull((SELECT IF(z.ts_Tue_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_Tue_hours,0), z.ts_Tue_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_Tue_hours)),0) TueTotalHours,
(IfNull(z.ts_Tue_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") TueMileageCosts,

IfNull(z.ts_wed_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) wedMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_wed_standby),0) wedStandbyRate,
IfNull((SELECT IF(z.ts_wed_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_wed_hours,0), z.ts_wed_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_wed_hours)),0) wedTotalHours,
(IfNull(z.ts_wed_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") wedMileageCosts,

IfNull(z.ts_thu_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) thuMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_thu_standby),0) thuStandbyRate,
IfNull((SELECT IF(z.ts_thu_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_thu_hours,0), z.ts_thu_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_thu_hours)),0) thuTotalHours,
(IfNull(z.ts_thu_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") thuMileageCosts,

IfNull(z.ts_fri_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) friMileage,
IfNull((SELECT tsr_payment_rate from time_sheet_rates where time_sheet_rates.tsr_call_out_code=z.ts_fri_standby),0) friStandbyRate,
IfNull((SELECT IF(z.ts_fri_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_fri_hours,0), z.ts_fri_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_fri_hours)),0) friTotalHours,
(IfNull(z.ts_fri_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") friMileageCosts,

IfNull(z.ts_sat_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) satMileage,
IfNull((SELECT IF(z.ts_sat_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_sat_hours,0), z.ts_sat_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_sat_hours)),0) satTotalHours,
(IfNull(z.ts_sat_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") satMileageCosts,

IfNull(z.ts_sun_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) sunMileage,
IfNull((SELECT IF(z.ts_sun_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_sun_hours,0), z.ts_sun_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_sun_hours)),0) sunTotalHours,
(IfNull(z.ts_sun_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") sunMileageCosts,

IfNull(z.ts_mam_journeys,0) * IfNull((SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid),0) mamMileage,
IfNull((SELECT IF(z.ts_mam_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney") > IfNull(z.ts_mam_hours,0), z.ts_mam_journeys * (SELECT tsm_mileage from time_sheet_mileage where tsm_user="MinHourPerJourney"), z.ts_mam_hours)),0) mamTotalHours,
(IfNull(z.ts_mam_journeys,0) * (SELECT y.tsm_mileage from time_sheet_mileage y where y.tsm_user=z.ts_userid)) * (SELECT tsr_payment_rate from time_sheet_rates WHERE tsr_call_out_code="PricePerMile") mamMileageCosts,

z.ts_ts


FROM time_sheet z WHERE z.ts_calculation_completed<>1 or IsNull(z.ts_calculation_completed) ; 

Maintenance MySQL – JobSheets Android: Jobsheet_History

CREATE VIEW jobsheet_history AS
SELECT x.jr_id, x.jr_jobid, x.jr_userid, x.jr_user_assigned, x.jr_pdaid, x.jr_datedue, x.jr_scandt, x.jr_priloc, x.jr_secloc, x.jr_terloc, x.jr_workrqd, x.jr_workcomp, x.jr_workleft, x.jr_jobcomp, x.jr_timetaken,
x.jr_pu1barcode, p1.il_description pu1desc, x.jr_pu1qty,
x.jr_pu2barcode, p2.il_description pu2desc, x.jr_pu2qty,
x.jr_pu3barcode, p3.il_description pu3desc, x.jr_pu3qty,
x.jr_pu4barcode, p4.il_description pu4desc, x.jr_pu4qty,
x.jr_pu5barcode, p5.il_description pu5desc, x.jr_pu5qty,
x.jr_pu6barcode, p6.il_description pu6desc, x.jr_pu6qty,
x.jr_pu7barcode, p7.il_description pu7desc, x.jr_pu7qty,
x.jr_pu8barcode, p8.il_description pu8desc, x.jr_pu8qty,
x.jr_pu9barcode, p9.il_description pu9desc, x.jr_pu9qty,
x.jr_pu10barcode, p10.il_description pu10desc, x.jr_pu10qty, x.jr_ts
FROM (((((((((jobsheet_report x
LEFT JOIN item_list as p1 on x.jr_pu1barcode=p1.il_barcode)
LEFT JOIN item_list as p2 on x.jr_pu2barcode=p2.il_barcode)
LEFT JOIN item_list as p3 on x.jr_pu3barcode=p3.il_barcode)
LEFT JOIN item_list as p4 on x.jr_pu4barcode=p4.il_barcode)
LEFT JOIN item_list as p5 on x.jr_pu5barcode=p5.il_barcode)
LEFT JOIN item_list as p6 on x.jr_pu6barcode=p6.il_barcode)
LEFT JOIN item_list as p7 on x.jr_pu7barcode=p7.il_barcode)
LEFT JOIN item_list as p8 on x.jr_pu8barcode=p8.il_barcode)
LEFT JOIN item_list as p9 on x.jr_pu9barcode=p9.il_barcode)
LEFT JOIN item_list as p10 on x.jr_pu10barcode=p10.il_barcode;