A complete program

 

‘VBA File handling – Make new or update existing Records
Dim dbase As DAO.Database
Dim Importset As DAO.Recordset
Dim ResultSet As DAO.Recordset
Dim Checkset As DAO.Recordset

Set dbase = CurrentDb
Set Importset = dbase.OpenRecordset(“ImportTable”)
Set ResultSet = dbase.OpenRecordset(“T-Wages”)
Set Checkset = dbase.OpenRecordset(“T-Checklist”)
‘*************************************************
‘Read the first record from the Import table and compare it with the value in the CheckList table if a match then bomb out
‘Otherwise add a record to the Checklist table
Importset.MoveFirst
w$ = Importset![week_no]
y$ = Importset![year_no]
Checkset.MoveFirst
Do Until Checkset.EOF

If w$ = Checkset![week_no] Then
If y$ = Checkset![year_no] Then
result = MsgBox(“Do you wish to overwrite this week’s data”, vbYesNo + vbQuestion, “User reply required”)
If result = vbNo Then
MsgBox (“No import has taken place”)
Exit Sub
Else
Dim qdf As DAO.QueryDef
‘ Delete records from the Wages Table
Set qdf = CurrentDb.QueryDefs(“Q-DeleteWageRecords”)
qdf![“weeknumber”] = w$
qdf![“yearnumber”] = y$
qdf.Execute
Set qdf = Nothing
‘ Delete record from the Checklist table
Set qdf = CurrentDb.QueryDefs(“Q-Delete_Checklist_Record”)
qdf![“weeknumber”] = w$
qdf![“yearnumber”] = y$
qdf.Execute
Set qdf = Nothing
End If
End If
End If

Checkset.MoveNext
Loop
Checkset.AddNew
Checkset![week_no] = w$
Checkset![year_no] = y$
Checkset.Update
‘*************************************************
‘Read through the Import table sequentially
Importset.MoveFirst
Do Until Importset.EOF

weekno$ = Importset![week_no]
If weekno$ = “YTD” Then
GoTo SKIP_A_LINE ‘Skip a line if it is YTD figures
End If

ResultSet.AddNew
ResultSet![Cost Centre] = Importset![Cost Centre]
ResultSet![Cost head] = Importset![Cost head]
ResultSet![Employee Code] = Importset![Employee Code]
ResultSet![Dept] = Importset![Dept]
ResultSet![Forenames] = Importset![Forenames]
ResultSet![Hours] = Importset![Hours]
ResultSet![Hrs Worked] = Importset![Hrs Worked]
ResultSet![Surname] = Importset![Surname]
ResultSet![week_no] = Importset![week_no]
ResultSet![year_no] = Importset![year_no]
ResultSet![BH-Basic-Value] = Importset![BH-Basic-Value]
ResultSet![BH-Basic-Input] = Importset![BH-Basic-Input]
ResultSet![BN-Bonus-Value] = Importset![BN-Bonus-Value]
ResultSet![GR-Gross-Value] = Importset![GR-Gross-Value]
ResultSet![NR-Ers-Value] = Importset![NR-Ers-Value]
ResultSet![NT-Nett-Value] = Importset![NT-Nett-Value]
ResultSet![O1-Overtime-Value] = Importset![O1-Overtime-Value]
ResultSet![O1-Overtime-Input] = Importset![O1-Overtime-Input]
ResultSet![O2-Overtime-Value] = Importset![O2-Overtime-Value]
ResultSet![O2-Overtime-Input] = Importset![O2-Overtime-Input]
ResultSet![PL-SPP-Value] = Importset![PL-SPP-Value]
ResultSet![SB-Special-Value] = Importset![SB-Special-Value]
ResultSet![SSP-SSP-Value] = Importset![SSP-SSP-Value]
ResultSet![TX-Tax-Value] = Importset![TX-Tax-Value]
ResultSet![VB-Variable-Value] = Importset![VB-Variable-Value]

ResultSet.Update

SKIP_A_LINE:
Importset.MoveNext
Loop
MsgBox (“Import has now finished”)

End Sub

Leave a Reply