Hi everybody:
After pulling my hair for several hours I have come to tek-tip forum for help.
I have a simple database and it is maintenance program which shows outstanding machines due for maintenance depending upon established frequency.
Primarily it has two tables, tblEquipmentChecklist and tblCheckDates. tblEauipmentChecklist has in addition to other fileds, two fileds “LastRechekDate” and “DueDate”. tblCheckDates has a field “LastChecked” which is basically last rechecked date.
Subform gets the data from this table and the user inserts the last rechecked date and other data on this form and the underlying table tblCheckDates (fields LastChecked, Initials and Comments) is updated and so are the fields LastRecheckDate and DueDate in the table tblEquipmentCheckList.
Both tables are linked by the field CardNo which is a key field for tblEquipmentCheckList. The tblCheckDates has a primary field called LineNo which is simply an autonumber. So this table has all the dates of the equipment check.
Everything works fine. However rather than going through individual card (equipment) to update records, I would like to add a button that would get the date from the text box and add new record i.e. field LastChecked with the correstponding dates if the card (equipment) is due.
To get the last date checked and and compare it to the corresponding due date so that I can update card is it is due (i.e. if today’s date is greater than or equal to the due date), I have the query called, qryLastDate as follows:
SELECT tblCheckDates.CardNo, Max(tblCheckDates.LastChecked) AS LastDate, tblEquipmentCheckList.DueDate
FROM tblEquipmentCheckList INNER JOIN tblCheckDates ON tblEquipmentCheckList.CardNo = tblCheckDates.CardNo
GROUP BY tblCheckDates.CardNo, tblEquipmentCheckList.DueDate;
I used the above query in the command button click event as follows:
Dim dbs As Database
'Dim qry As QueryDef
Dim rst As Recordset
Dim strSQL As String
Dim LastDate As Variant
Dim DueDate As Variant
'Dim srtQueryName As String
strSQL = " SELECT tblCheckDates.[CardNo], Max(tblCheckDates.[LastChecked]) AS LastDate, tblEquipmentCheckList.[DueDate] " & _
" FROM tblEquipmentCheckList INNER JOIN tblCheckDates ON tblEquipmentCheckList.[CardNo] = tblCheckDates.[CardNo] " & _
" GROUP BY tblCheckDates.[CardNo], tblEquipmentCheckList.[DueDate];"
Set dbs = CurrentDb()
'Set qry = dbs.OpenRecordset("qryFindLastDate").Updatable
Set rst = dbs.OpenRecordset("qryFindLastDate")
'Set rst = qry.OpenRecordset
With rst
Do While Not .EOF
rst.MoveFirst
LastDate = rst.Fields("tblCheckDates.[LastChecked]").Value
DueDate = rst.Fields("tblEquipmentCheckList.[DueDate]").Value
If LastDate <= DueDate Then
rst.AddNew
!LastChecked = txtUpdateDate
.Update
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
When I run it, the program halts at the line LastDate = rstFields……………………..
With the error “Item not found in this collection”
Obviously I am doing something wrong and I would appreciate help on this.
Thanks in advance.
After pulling my hair for several hours I have come to tek-tip forum for help.
I have a simple database and it is maintenance program which shows outstanding machines due for maintenance depending upon established frequency.
Primarily it has two tables, tblEquipmentChecklist and tblCheckDates. tblEauipmentChecklist has in addition to other fileds, two fileds “LastRechekDate” and “DueDate”. tblCheckDates has a field “LastChecked” which is basically last rechecked date.
Subform gets the data from this table and the user inserts the last rechecked date and other data on this form and the underlying table tblCheckDates (fields LastChecked, Initials and Comments) is updated and so are the fields LastRecheckDate and DueDate in the table tblEquipmentCheckList.
Both tables are linked by the field CardNo which is a key field for tblEquipmentCheckList. The tblCheckDates has a primary field called LineNo which is simply an autonumber. So this table has all the dates of the equipment check.
Everything works fine. However rather than going through individual card (equipment) to update records, I would like to add a button that would get the date from the text box and add new record i.e. field LastChecked with the correstponding dates if the card (equipment) is due.
To get the last date checked and and compare it to the corresponding due date so that I can update card is it is due (i.e. if today’s date is greater than or equal to the due date), I have the query called, qryLastDate as follows:
SELECT tblCheckDates.CardNo, Max(tblCheckDates.LastChecked) AS LastDate, tblEquipmentCheckList.DueDate
FROM tblEquipmentCheckList INNER JOIN tblCheckDates ON tblEquipmentCheckList.CardNo = tblCheckDates.CardNo
GROUP BY tblCheckDates.CardNo, tblEquipmentCheckList.DueDate;
I used the above query in the command button click event as follows:
Dim dbs As Database
'Dim qry As QueryDef
Dim rst As Recordset
Dim strSQL As String
Dim LastDate As Variant
Dim DueDate As Variant
'Dim srtQueryName As String
strSQL = " SELECT tblCheckDates.[CardNo], Max(tblCheckDates.[LastChecked]) AS LastDate, tblEquipmentCheckList.[DueDate] " & _
" FROM tblEquipmentCheckList INNER JOIN tblCheckDates ON tblEquipmentCheckList.[CardNo] = tblCheckDates.[CardNo] " & _
" GROUP BY tblCheckDates.[CardNo], tblEquipmentCheckList.[DueDate];"
Set dbs = CurrentDb()
'Set qry = dbs.OpenRecordset("qryFindLastDate").Updatable
Set rst = dbs.OpenRecordset("qryFindLastDate")
'Set rst = qry.OpenRecordset
With rst
Do While Not .EOF
rst.MoveFirst
LastDate = rst.Fields("tblCheckDates.[LastChecked]").Value
DueDate = rst.Fields("tblEquipmentCheckList.[DueDate]").Value
If LastDate <= DueDate Then
rst.AddNew
!LastChecked = txtUpdateDate
.Update
End If
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
When I run it, the program halts at the line LastDate = rstFields……………………..
With the error “Item not found in this collection”
Obviously I am doing something wrong and I would appreciate help on this.
Thanks in advance.