Microsoft Access

[5bhaya]

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.

 

[p27br]

try rst.Fields("LastChecked") or if you know the position of the field in the table rst.fields(position) where position is an integer value

 

[DoubleD]

These two lines should only reference the field name in the query.
LastDate = rst.Fields("LastChecked").Value
DueDate = rst.Fields("DueDate").Value

Why are you defining strSQL if you're not using it in your code?
If qryFindLastDate is supposed to be your strSQL statement, then it is not updateable because you have a Group By.

I am what I am based on the decisions I have made.

DoubleD

 

[5bhaya]

p27Br:

Your suggestion to use rst.field(position) works. However, further on it gives an error that cannot update -database is read only. I think DoubleD is pointing out the same thing. If using Group By makes it not updatable, how could I go about doing this? i.e. getting the last check date from the table?

DoubleD:

I am using strSQL in the code. the qry part has been commented out. But you are right about the not updatable. How could I solve this?

Thanks in advance.

 

[DoubleD]

5bhaya,

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.

This doesn't sound like normalized data. That date should only be in one of the two tables, not both.


I would try something like this:

  Set dbs = CurrentDb()
    
  Set rst = dbs.OpenRecordset("tblEquipmentCheckList").Updatable
  With rst
    Do While Not .EOF
      rst.MoveFirst
      If nz(DMax("[LastChecked]","tblCheckDates","[CardNo] = " & rst!CardNo)) <= !DueDate Then
        .AddNew
        !LastChecked = txtUpdateDate
        .Update
      End If
      .MoveNext
    Loop
   
  End With
    
  rst.Close
    
  Set rst = Nothing

This assumes your looking to add a record for each CardNo where the DueDate <= Max(LastChecked).

I am what I am based on the decisions I have made.

DoubleD

 

[5bhaya]

Hi DoubleD:

It gives type mismatch error at

dbs.OpenRecordset("tblEquipmentCheckList").Updatable

 

[DoubleD]

I don't work with DAO very often, but I believe this is right:

dbs.OpenRecordset("tblEquipmentCheckList",dbOpenDynaset)

I am what I am based on the decisions I have made.

DoubleD

 

[5bhaya]

This is what worked.... I appreciate your help. Thanks again.

Dim dbs As Database

Dim rst As Recordset
Dim strSQL As String
Dim LastDate As Variant
Dim DueDate As Variant
Dim CurrentCardNo As Integer
Dim LastLineNo As Integer
Dim rst2 As Recordset

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("qryFindLastDate")

With rst
rst.MoveFirst
Do While Not .EOF

CurrentCardNo = rst.Fields(0)
LastDate = rst.Fields(1)
DueDate = rst.Fields(2)
LastLineNo = rst.Fields(3)
If LastDate <= DueDate Then
Set rst2 = dbs.OpenRecordset("tblCheckDates", dbOpenDynaset)
rst2.AddNew
rst2!LastChecked = txtUpdateDate
rst2!CardNo = CurrentCardNo
rst2.Update
rst2.Close
Set rst2 = Nothing
End If
.MoveNext
Loop

End With

rst.Close

Set rst = Nothing