Access Module

G

Guest

I would like to know what I needed to do to use this code again for a new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String, saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates date with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct locations and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1 table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1 table with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2 table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total table with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty Incident table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads Incident table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct month")

'Update incident to totals table with the amount of incidents per location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit 'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full ORDER BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION = Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
 
K

Ken Snell [MVP]

In the database window, go to the Modules window and create a new module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in your
database.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I would like to know what I needed to do to use this code again for a new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct locations and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1 table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1 table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2 table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total table with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct month")

'Update incident to totals table with the amount of incidents per location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit 'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
 
G

Guest

I got the the module saved but were do I find the subroutine?

Ken Snell said:
In the database window, go to the Modules window and create a new module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in your
database.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I would like to know what I needed to do to use this code again for a new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct locations and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1 table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1 table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2 table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total table with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct month")

'Update incident to totals table with the amount of incidents per location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit 'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I got the the module saved but were do I find the subroutine?

Ken Snell said:
In the database window, go to the Modules window and create a new module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in your
database.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I would like to know what I needed to do to use this code again for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1 table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit 'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The sub routine that I'm using is almost close to what I need. When I go to
run the module I get this " Compile Error: User defined type not defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new database?

Ken Snell said:
The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I got the the module saved but were do I find the subroutine?

Ken Snell said:
In the database window, go to the Modules window and create a new module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1 table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit 'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
The sub routine that I'm using is almost close to what I need. When I go
to
run the module I get this " Compile Error: User defined type not defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

Ken Snell said:
The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

Neal said:
I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a new
module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident =
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1,
rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident =
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "'
AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That got the module running but it stops again with error:"run time error 13
type mismatch", When I debug it it goes to line "For Each errLoop In Errors"
towards the very bottom of the module.

Ken Snell said:
You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
The sub routine that I'm using is almost close to what I need. When I go
to
run the module I get this " Compile Error: User defined type not defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

Ken Snell said:
The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a new
module.
Paste the code into that module. Save the module (name it basMyCode or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit 'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit 'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1 ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident =
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1,
rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident =
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "'
AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

This probably is because the code doesn't define the errLoop variable, so
ACCESS assigns it to variant data type, while you need Error object type.

I take it that this isn't your code? Please note my earlier comment about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
That got the module running but it stops again with error:"run time error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

Ken Snell said:
You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
The sub routine that I'm using is almost close to what I need. When I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a new
module.
Paste the code into that module. Save the module (name it basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty
sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit
'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit
'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each
incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1
ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING
ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from
Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN
TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" &
saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1,
rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN Sheet1
ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress &
"'
AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This was code that was written by a previous administrator. I sort of
understand what the code is doing until it hits the the middle of the code!
Dim errloop As error is already written in the code towards the bottom

Ken Snell said:
This probably is because the code doesn't define the errLoop variable, so
ACCESS assigns it to variant data type, while you need Error object type.

I take it that this isn't your code? Please note my earlier comment about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
That got the module running but it stops again with error:"run time error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

Ken Snell said:
You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need. When I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a new
module.
Paste the code into that module. Save the module (name it basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String, samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit 'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty
sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit
'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit
'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal, acEdit

'Load RUNNING table for adding amounts and incidents after date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each
incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1
ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING
ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from
Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN
TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" &
saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1,
rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN Sheet1
ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress &
"'
AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume that it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


Neal said:
This was code that was written by a previous administrator. I sort of
understand what the code is doing until it hits the the middle of the
code!
Dim errloop As error is already written in the code towards the bottom

Ken Snell said:
This probably is because the code doesn't define the errLoop variable, so
ACCESS assigns it to variant data type, while you need Error object type.

I take it that this isn't your code? Please note my earlier comment about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

Neal said:
That got the module running but it stops again with error:"run time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need. When
I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String,
samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit
'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads
lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads
sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty
sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit
'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads
Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents
per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load
all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit
'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal,
acEdit

'Load RUNNING table for adding amounts and incidents after
date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each
incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1
ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING
ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from
Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS -
rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And
saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0",
IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident =
5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN
TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" &
saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1,
rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0",
IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident =
5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN
Sheet1
ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress
&
"'
AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Im getting a compile error 13 and I dont know exactly were it is comming from
but I have narrowed it down the this part,
when I debug it goes straight down to the error handling part.I dont
exactly know what all the coding means but how hard would it be if I just
rewrote it on step at a time until I found the error?
rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION = Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub


Ken Snell said:
Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume that it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


Neal said:
This was code that was written by a previous administrator. I sort of
understand what the code is doing until it hits the the middle of the
code!
Dim errloop As error is already written in the code towards the bottom

Ken Snell said:
This probably is because the code doesn't define the errLoop variable, so
ACCESS assigns it to variant data type, while you need Error object type.

I take it that this isn't your code? Please note my earlier comment about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

That got the module running but it stops again with error:"run time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a reference set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need. When
I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String,
samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit
'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads
lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads
sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty
sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal, acEdit
'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads
Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit 'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit 'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the correct
month")

'Update incident to totals table with the amount of incidents
per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal, acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit 'Load
all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal, acEdit
'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal,
acEdit

'Load RUNNING table for adding amounts and incidents after
date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each
incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from Sheet1
ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from RUNNING
ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from
Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS -
rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And
saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0",
IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident =
5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" &
samount
sSql = "UPDATE Running INNER JOIN
TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" &
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" &
saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Which line does the debugger highlight as the error?

--

Ken Snell
<MS ACCESS MVP>

Neal said:
Im getting a compile error 13 and I dont know exactly were it is comming
from
but I have narrowed it down the this part,
when I debug it goes straight down to the error handling part.I dont
exactly know what all the coding means but how hard would it be if I just
rewrote it on step at a time until I found the error?
rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub


Ken Snell said:
Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume that
it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


Neal said:
This was code that was written by a previous administrator. I sort of
understand what the code is doing until it hits the the middle of the
code!
Dim errloop As error is already written in the code towards the bottom

:

This probably is because the code doesn't define the errLoop variable,
so
ACCESS assigns it to variant data type, while you need Error object
type.

I take it that this isn't your code? Please note my earlier comment
about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

That got the module running but it stops again with error:"run time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a reference
set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need.
When
I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a
new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code
elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code
again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String,
samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty
temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads
temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit
'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads
lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
table
DoCmd.OpenQuery "Sheet1-Load", acViewNormal, acEdit 'Loads
sheet1
table
with
all data

'Load only 1 (last) record to the table for updating all
dates
DoCmd.OpenQuery "Sheet2-Empty", acViewNormal, acEdit 'empty
sheet2
table
DoCmd.OpenQuery "Sheet2-Append1record", acViewNormal,
acEdit
'Loads
sheet2
table with all data

'Load locations to the totals table
DoCmd.OpenQuery "TOTAL-LOAD", acViewNormal, acEdit 'Loads
Total
table
with
all data

'Load the Incident table with totals after clear
DoCmd.OpenQuery "Incident-Empty", acViewNormal, acEdit
'empty
Incident
table
DoCmd.OpenQuery "Incident-Load", acViewNormal, acEdit
'Loads
Incident
table
with all data

MsgBox ("Change Query:'Total-Update(Incident)' to the
correct
month")

'Update incident to totals table with the amount of
incidents
per
location
(all locations in now)
DoCmd.OpenQuery "Total-Update(Incident)", acViewNormal,
acEdit
'Loads
Incident table with all data

'Load all records for history purposes
DoCmd.OpenQuery "Total_Full-Load", acViewNormal, acEdit
'Load
all
records

'Update totals for all incidents in Total_Full table
DoCmd.OpenQuery "Total-Update(Totals)", acViewNormal,
acEdit
'Update
all
records

'Sheet2 update total incidents from Totals table
DoCmd.OpenQuery "Sheet2-Update(Incidents)", acViewNormal,
acEdit

'Load RUNNING table for adding amounts and incidents after
date
field
DoCmd.OpenQuery "RUNNING-Empty", acViewNormal, acEdit
DoCmd.OpenQuery "RUNNING-Load", acViewNormal, acEdit

'Puts the number of incidents and total amount due for each
incident
after
the date field.
Set rsSheet1 = CurrentDb.OpenRecordset("SELECT * from
Sheet1
ORDER
BY
address, location, last_date")
Set rsRunning = CurrentDb.OpenRecordset("SELECT * from
RUNNING
ORDER
BY
address, location")
Set rsTotalfull = CurrentDb.OpenRecordset("SELECT * from
Total_Full
ORDER
BY
address, location, date")

rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS -
rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And
saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0",
IIf(incident
=
2,
"0",
IIf(incident = 3, "0", IIf(incident = 4, "100",
IIf(incident =
5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & "
$" &
samount
sSql = "UPDATE Running INNER JOIN
TOTAL_FULL
ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND
(Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '"
&
sdatenew
&
"'" & _
"WHERE Total_Full.[ADDRESS] = '" &
saddress &
"'
AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It highlights the (For Each errLoop In Errors).

Ken Snell said:
Which line does the debugger highlight as the error?

--

Ken Snell
<MS ACCESS MVP>

Neal said:
Im getting a compile error 13 and I dont know exactly were it is comming
from
but I have narrowed it down the this part,
when I debug it goes straight down to the error handling part.I dont
exactly know what all the coding means but how hard would it be if I just
rewrote it on step at a time until I found the error?
rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5, "150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub


Ken Snell said:
Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume that
it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


This was code that was written by a previous administrator. I sort of
understand what the code is doing until it hits the the middle of the
code!
Dim errloop As error is already written in the code towards the bottom

:

This probably is because the code doesn't define the errLoop variable,
so
ACCESS assigns it to variant data type, while you need Error object
type.

I take it that this isn't your code? Please note my earlier comment
about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

That got the module running but it stops again with error:"run time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a reference
set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need.
When
I
go
to
run the module I get this " Compile Error: User defined type not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a
new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the subroutine?

:

In the database window, go to the Modules window and create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code
elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code
again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2 As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String,
samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit 'empty
temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads
temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit
'concatenates
date
with
format

'Loads Lookup to scrub the location and address for matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit 'Loads
lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit 'empty
sheet1
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

A few posts back, I indicated that both the ADO and DAO libraries have an
Errors collection. I suggested that you disambiguate the Dim statement with
DAO. You didn't do that.

Try these changes:

Dim errLoop As DAO.Error


For Each errLoop In DAO.Errors

--

Ken Snell
<MS ACCESS MVP>



Neal said:
It highlights the (For Each errLoop In Errors).

Ken Snell said:
Which line does the debugger highlight as the error?

--

Ken Snell
<MS ACCESS MVP>

Neal said:
Im getting a compile error 13 and I dont know exactly were it is
comming
from
but I have narrowed it down the this part,
when I debug it goes straight down to the error handling part.I dont
exactly know what all the coding means but how hard would it be if I
just
rewrote it on step at a time until I found the error?
rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub


:

Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume
that
it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


This was code that was written by a previous administrator. I sort
of
understand what the code is doing until it hits the the middle of
the
code!
Dim errloop As error is already written in the code towards the
bottom

:

This probably is because the code doesn't define the errLoop
variable,
so
ACCESS assigns it to variant data type, while you need Error object
type.

I take it that this isn't your code? Please note my earlier comment
about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

That got the module running but it stops again with error:"run
time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop
In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a
reference
set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need.
When
I
go
to
run the module I get this " Compile Error: User defined type
not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a
new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the
subroutine?

:

In the database window, go to the Modules window and
create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code
elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code
again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2
As
String,
saddress2
As String
Dim sdate As String, sSql As String, sdatenew As String,
samount
As
String
Dim incident As Integer
Dim count As Integer


On Error GoTo ErrorHandler
Set db = CurrentDb()

'Loads TEMP to parse and concatenate dates
DoCmd.OpenQuery "TEMP-Empty", acViewNormal, acEdit
'empty
temp
table
DoCmd.OpenQuery "TEMP-Load", acViewNormal, acEdit 'Loads
temp
table
with
date parsed
DoCmd.OpenQuery "TEMP-Update", acViewNormal, acEdit
'concatenates
date
with
format

'Loads Lookup to scrub the location and address for
matches
DoCmd.OpenQuery "Lookup-Load", acViewNormal, acEdit
'Loads
lookup
to
validate
MsgBox ("Validate Locations in Lookup Table")

'Load all records through scrubbed Lookup table for
correct
locations
and
addresses
DoCmd.OpenQuery "Sheet1-Empty", acViewNormal, acEdit
'empty
sheet1
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

If I were to disambiguate the dim statement with DAO would I just take out
the DAO in the statements? The changes that you suggest, do I put Dim errLoop
As DAO.Error
at the top in the first statement and the other at the bottom? If I have to
change something else I'll change what ever I have to for it to work!
Ken Snell said:
A few posts back, I indicated that both the ADO and DAO libraries have an
Errors collection. I suggested that you disambiguate the Dim statement with
DAO. You didn't do that.

Try these changes:

Dim errLoop As DAO.Error


For Each errLoop In DAO.Errors

--

Ken Snell
<MS ACCESS MVP>



Neal said:
It highlights the (For Each errLoop In Errors).

Ken Snell said:
Which line does the debugger highlight as the error?

--

Ken Snell
<MS ACCESS MVP>

Im getting a compile error 13 and I dont know exactly were it is
comming
from
but I have narrowed it down the this part,
when I debug it goes straight down to the error handling part.I dont
exactly know what all the coding means but how hard would it be if I
just
rewrote it on step at a time until I found the error?
rsRunning.MoveFirst
Do Until rsRunning.EOF
slocation = rsRunning!location
saddress = rsRunning!address
sdate = rsRunning!Date
incident = rsRunning!INCIDENT_TOTALS - rsRunning!incident
count = 1
If incident < 4 And incident <> 0 Then
rsTotalfull.MoveFirst
incident = 1
Do Until rsTotalfull.EOF
If slocation = rsTotalfull!location And saddress =
rsTotalfull!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsTotalfull!Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN TOTAL_FULL ON
(Running.ADDRESS = TOTAL_FULL.ADDRESS) AND (Running.LOCATION =
TOTAL_FULL.LOCATION) " & _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Total_Full.[ADDRESS] = '" & saddress & "' AND
Total_Full.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsTotalfull.MoveNext
Loop
Else
rsSheet1.MoveFirst
incident = IIf(incident = 0, 1, rsRunning!INCIDENT_TOTALS -
rsRunning!incident + 1)
Do Until rsSheet1.EOF
If slocation = rsSheet1!location And saddress =
rsSheet1!address Then
samount = IIf(incident = 1, "0", IIf(incident = 2, "0",
IIf(incident = 3, "0", IIf(incident = 4, "100", IIf(incident = 5,
"150",
"200")))))
sdate = rsSheet1!last_Date
sdatenew = sdate & " #" & incident & " $" & samount
sSql = "UPDATE Running INNER JOIN Sheet1 ON
(Running.ADDRESS = Sheet1.ADDRESS) AND (Running.LOCATION =
Sheet1.LOCATION) "
& _
"SET Running.DATE_" & count & " = '" & sdatenew &
"'" & _
"WHERE Sheet1.[ADDRESS] = '" & saddress & "' AND
Sheet1.[LOCATION] = '" & slocation & "'"
DoCmd.RunSQL sSql
incident = incident + 1
count = count + 1
End If
rsSheet1.MoveNext
Loop
End If
rsRunning.MoveNext
Loop


Set rsnew = Nothing
Set rsRunning = Nothing
Set db = Nothing

ErrorHandler:

Dim strError As String
Dim errLoop As Error

' Enumerate Errors collection and display properties of
' each Error object.
For Each errLoop In Errors
With errLoop
strError = _
"Error #" & .Number & vbCr
strError = strError & _
" " & .Description & vbCr
strError = strError & _
" (Source: " & .Source & ")" & vbCr
strError = strError & _
"Press F1 to see topic " & .HelpContext & vbCr
strError = strError & _
" in the file " & .HelpFile & "."
End With
MsgBox strError
Next

'Resume Next



End Sub


:

Sorry.. I didn't see that Dim step.

Errors is a collection in both the ADODB and DAO libraries. I assume
that
it
should be DAO.Error here.

Dim errLoop As DAO.Error
--

Ken Snell
<MS ACCESS MVP>


This was code that was written by a previous administrator. I sort
of
understand what the code is doing until it hits the the middle of
the
code!
Dim errloop As error is already written in the code towards the
bottom

:

This probably is because the code doesn't define the errLoop
variable,
so
ACCESS assigns it to variant data type, while you need Error object
type.

I take it that this isn't your code? Please note my earlier comment
about
understanding what code is doing when you add it to a module.

You'll need to add
Dim errLoop As Error

to the code.

--

Ken Snell
<MS ACCESS MVP>

That got the module running but it stops again with error:"run
time
error
13
type mismatch", When I debug it it goes to line "For Each errLoop
In
Errors"
towards the very bottom of the module.

:

You must be using ACCESS 2000 or 2002. It doesn't have a
reference
set
automatically to the DAO library.

Open Visual Basic Editor, click Tools | References.

Scroll down this list to find Microsoft Data Access Objects 3.x
library.
Select it. Close the window.

--

Ken Snell
<MS ACCESS MVP>

The sub routine that I'm using is almost close to what I need.
When
I
go
to
run the module I get this " Compile Error: User defined type
not
defined"?
Option Compare Database
Dim db As DAO.Database < It highlights this string
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Is there somthing else I should name these in order to start a
new
database?

:

The subroutine is in the code.....

Public Sub updatedatabase()

the above line is the beginning of it.

It's usually not advisable to just "plop" code in a database
without
having
some idea of what it does and how to use it....

--

Ken Snell
<MS ACCESS MVP>

I got the the module saved but were do I find the
subroutine?

:

In the database window, go to the Modules window and
create a
new
module.
Paste the code into that module. Save the module (name it
basMyCode
or
something that you want, but not "updatedatabase").

Then you'll need to call the subroutine from VBA code
elsewhere
in
your
database.

--

Ken Snell
<MS ACCESS MVP>

I would like to know what I needed to do to use this code
again
for a
new
database?I have never used vb or access before!

Option Compare Database
Dim db As DAO.Database
Dim rsSheet1 As DAO.Recordset
Dim rsTotalfull As DAO.Recordset
Dim rsRunning As DAO.Recordset

Public Sub updatedatabase()
Dim slocation As String, saddress As String, slocation2
As
String,
saddress2
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error #3021 1
VBA Code 1
Module Import error: Compile err 1
Module problems 2
Edit and Update of field not working 5
Module won't allow any pasting, additional variables etc? 1
Problem with format when printing to Word from Access 2

Top