Reading field contents

[NWTrust]

Hi
Having had my previous query on inserting data into a given field of a table successfully answered, I would now like to be able to do the reverse, ie read the contents of a given field in a given row. As an example: find the contents of field 'Printer type' in row 4 of table 'tblPrinters'.
As always help would be greatly appreciated.
NWTrust

 

[MajP]

This is a strange request. Not sure why you would be looking for a specific "row". There is no such thing as a "row" in a relational database. There are fields and records. A row is meaningless, and the only way you can guarantee that you will get a specific "row' if it comes from a query with an "order by" clause.

so this is how to get the x record from an ordered query.

Public Function getFieldRow(fld As String, domain As String, intRow As Integer) As Variant
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "select " & fld & " from " & domain
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  
  'Make sure you have records then then
  'cycle to ensure accurate recordcount
  If Not (rs.EOF And rs.BOF) Then
   rs.MoveFirst
   rs.MoveLast
   rs.MoveFirst
  Else
   MsgBox "There are no records."
  End If
  
  'Make sure you are not going beyond the amount of records
  If rs.RecordCount >= intRow Then
    rs.Move (intRow - 1)
    getFieldRow = rs.Fields(fld)
  Else
    MsgBox "There are only" & rs.RecordCount & " records."
  End If

End Function

you would call this

= getFieldRow("[Printer Type]","queryPrinters",4)

 

[NWTrust]

Thanks, MajP, this is what I needed.
The reason for my query is this: I have a table which needs to be returned to a basic state on an annual basis, and any earlier attempt to refresh prevented; to ensure this I needed to store the date of the last refresh. A non-volatile variable would have been the answer but I can find no reference to such an animal so decided on storing the date in a table of one field in one record. This is where your answers to my two queries made this possible.
Thanks again.