Accessing recordset field on form

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

Answer 1

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

Answer 2

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

Answer 3

You could always use a =DLookUp expression in the textbox Control Source as well.

Answer 4

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

Answer 5

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.

Answer 6

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

Answer 7

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.