none
Accessing recordset field on form RRS feed

  • Question

  • Is there any relatively simply way to read a field in a form's underlying recordset (current record) using VBA without assigning the field to a control (eg editbox)?

    Sounds like I'm missing something really obvious.


    Andrew Gabb, Adelaide, South Australia


    • Edited by Gabby22 Friday, March 25, 2016 1:50 AM
    Friday, March 25, 2016 1:32 AM

Answers

  • Usually, something like

    Me.YourField = SomeValue

    works fine. The field YourField must be explicitely contained in the record source.

    Matthias Kläy, Kläy Computing AG

    • Marked as answer by Gabby22 Sunday, March 27, 2016 12:06 AM
    Saturday, March 26, 2016 12:58 PM

All replies

  • Usually, something like

    Me.YourField = SomeValue

    works fine. The field YourField must be explicitely contained in the record source.

    Matthias Kläy, Kläy Computing AG

    • Marked as answer by Gabby22 Sunday, March 27, 2016 12:06 AM
    Saturday, March 26, 2016 12:58 PM
  • Ah, it was the "explicitly contained" which got me. Any idea how to do it if it's implicit (ie using *)?

    Andrew


    Andrew Gabb, Adelaide, South Australia

    Saturday, March 26, 2016 11:03 PM
  • You could always use a =DLookUp expression in the textbox Control Source as well.

    Saturday, March 26, 2016 11:58 PM
  • Not sure I understand. What I'd like to do is access a field from "SELECT * from Xtable" without assigning it to a control (eg to an editbox).

    Andrew


    Andrew Gabb, Adelaide, South Australia

    Sunday, March 27, 2016 8:24 AM
  • Not sure I understand. What I'd like to do is access a field from "SELECT * from Xtable" without assigning it to a control (eg to an editbox).

    Andrew


    Andrew Gabb, Adelaide, South Australia

    Hi Andrew,

    You can assing a field value to a variable using:

      myvar = CurrentDb.OpenRecordset("SELECT ThisField FROM Xtable)!ThisField

    Imb.

    Sunday, March 27, 2016 12:56 PM
  • The problem with this approach is that it's likely to be very inefficient (and hence often slow), particularly if the field - or more than one field - is being used in a calculated 'field' in a list form or report.

    For example I may have a calculated field which shows '<surname>, <given1> <given2>' with a little logic to cater for situations of no surname or given names. I probably *could* do this in the SQL, but not always.

    Andrew


    Andrew Gabb, Adelaide, South Australia

    Monday, March 28, 2016 2:12 AM
  • For example I may have a calculated field which shows '<surname>, <given1> <given2>' with a little logic to cater for situations of no surname or given names. I probably *could* do this in the SQL, but not Always.

    Hi Andrew,

    You could use a function to construct the full name:

         myvar = CurrentDb.OpenRecordset("SELECT Make_name(surname,given1,given2) AS FullName FROM Xtable)!FullName

    Imb.

    Monday, March 28, 2016 6:35 AM