Search and update form field query (i think)

viperpurple

Registered User.
Local time
Today, 19:48
Joined
Oct 19, 2010
Messages
11
Hey,

Objective
I am creating an equipment database and this includes recording when the equipment is checked for damage etc. I have a field on the form which records when the last check was carried out.

I would like to keep this field up to date so the latest date appears whever the user view the record.

Current Plan
I currently have the equipment checks in a table and for every check the following is recorded:

check_Date, Checked_By, Equipment_Checked (memo with equipment seperated by a new line)

The 'equipment' form has a field called 'last_check' which i would like the last date to appear in. I am trying to write code to update the field when the event OnCurrent occurs for each record, the code is below but it is far off finished.

I have created a query that searches the tbl_equipment_checks table for the equipment but i'm unsure how to modify this for each seperate record, make sure the result is the most recent and then get this entered into the 'last_check' field on the form

Code:
SELECT tbl_equipment_checks.[equipment_checked], tbl_equipment_checks.[check_by], tbl_equipment_checks.[check_date]
FROM tbl_equipment_checks;
Code:
Private Sub Form_Current()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim kit_search As String
kit_search = Me.description

Set db = CurrentDb
Set qdf = db.QueryDefs("tbl_equipment_checks_query")
qdf.Parameters("WHERE tbl_equipment_checks.equipment_checked") = kit_search
Set rs = qdf.OpenRecordset

End Sub
I hope that i have given you all the information you need to help me, please don't hesitate to ask for more info.

Thanks

Adam
 
You're wasting your time trying to bring your saved query into VBA. (Generally this is practical only if you don't need to add further criteria or parameters). So do something like this.

Code:
Private Sub Form_Current()
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
set qdf = CurrentDB.CreateQueryDEf("", "")
qdf.SQL = "SELECT TOP 1 equipment_checked, check_by, check_date FROM tbl_equipment_checks WHERE equipment_checked = @equipment_Checked ORDER BY check_Date DESC "
qdf.Parameters["@equipment_Checked"].Value = kit_search
SET rs = qdf.OpenRecordset
last_check = rs["check_date"].value
end sub
 
There may be some syntax errors there - it' s untested.
 
Thanks Jal,

I've taken that code and fixed it for syntax and got it nearly working but i keep getting a fault on the last line involving the below info.

Problem: 'rs.check_date.Value'
Error: Method or check data not found
Thoughts: I guess that it's not finding the data in the query, could the problem to be with a NULL return?


Code:
Private Sub Form_Current()
    Dim rs          As DAO.Recordset
    Dim qdf         As DAO.QueryDef
    Dim strsql      As String
    Dim strname     As String
    Dim kit_search  As String
    
        kit_search = Me.description
        strname = "last_check_query"
        strsql = "SELECT TOP 1 tbl_equipment_checks.equipment_checked, tbl_equipment_checks.check_by, tbl_equipment_checks.check_date FROM tbl_equipment_checks WHERE tbl_equipment_checks.equipment_checked LIKE " & kit_search & " ORDER BY check_Date DESC "
        Set qdf = CurrentDb.CreateQueryDef(strname, strsql)
        Set rs = qdf.OpenRecordset
        Me.last_check = rs.check_date.Value
End Sub

I'm gonna carry on playing with it but if anyone has any ideas i'd welcome them to speed it all up :-).

Regards

Adam
 
Just for your interest i managed to solve the problem, just got to tune it now but the correct code that works is below.

Thanks for your help :-)

Code:
Private Sub Form_Current()
    Dim rs          As DAO.Recordset
    Dim qdf         As DAO.QueryDef
    Dim strsql      As String
    Dim strname     As String
    Dim kit_search  As String
    Dim response    As String
    
        kit_search = Me.description
        strname = "last_check_query"
        strsql = "SELECT TOP 1 equipment_checked, check_by, check_date " & _
                    "FROM tbl_equipment_checks " & _
                    "WHERE equipment_checked " & _
                    "LIKE '*" & kit_search & "*' ORDER BY check_Date DESC "
        Set qdf = CurrentDb.QueryDefs(strname)
        qdf.sql = strsql
        Set rs = qdf.OpenRecordset
        Me.last_check = rs("check_date").Value
End Sub

Regards

Adam
 

Users who are viewing this thread

Back
Top Bottom