Read a field value without making its record current?

[Sverk]

Hi,
Normally, I guess, the way to access a field in code is to first make the
relevant record current, then read the field's value.
But is it possible to do it without making its record current?
How?
Would be convenient, and more practical because it wouldn't make the listing
of the table skip back and forth.
Or is there a way to temporarily freeze and unfreeze the display of the table?

 

[Rick Brandt]

Hi,
Normally, I guess, the way to access a field in code is to first make
the relevant record current, then read the field's value.

Actually, that is probably the most non-normal way to do it. Forms are the
interface to data for people, not for code.

You could use DLookup().
You could open a Recordset that grans just the required row and field.
You could use the Form's RecordsetClone and FindFirst.

None of those would require that you position the form to that record.

 

[Sverk]

Got it to work OK with DLook(), THANKS!

Took some twisting though because the field I wanted to access is a text
field, and when it is empty DLook() returns a Null, not an empty string as I
expected.
I fixed it this way:
-------------------------
Dim TestfieldVari As Variant 'necessary to allow for empty field (Null)
Dim TestfieldValue As String

TestfieldVari = DLookup("[Place]", "tblMyTable", "Val([FilmNR]) = 1")
If IsNull(TestfieldVari) Then
TestfieldValue = "" 'set to nullstring
Else
TestfieldValue = TestfieldVari
End If

 

[Steve Sanford]

You might want to read this page:

http://www.allenbrowne.com/ser-42.html

(the ELookUp() function..

I haven't tested these, but here are two other ways:

'Sample #1:
-------------------------
Dim TestfieldVari As Variant 'necessary to allow for empty field (Null)
Dim TestfieldValue As String

TestfieldValue = "" 'set to nullstring

TestfieldVari = DLookup("[Place]", "tblMyTable", "Val([FilmNR]) = 1")
If Not IsNull(TestfieldVari) Then
TestfieldValue = TestfieldVari
End If
------------------


'Sample#2
-------------------------

Dim TestfieldValue As String

TestfieldValue = NZ(DLookup("[Place]", "tblMyTable", "Val([FilmNR]) =
1"),"")

------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Got it to work OK with DLook(), THANKS!

Took some twisting though because the field I wanted to access is a text
field, and when it is empty DLook() returns a Null, not an empty string as I
expected.
I fixed it this way:
-------------------------
Dim TestfieldVari As Variant 'necessary to allow for empty field (Null)
Dim TestfieldValue As String

TestfieldVari = DLookup("[Place]", "tblMyTable", "Val([FilmNR]) = 1")
If IsNull(TestfieldVari) Then
TestfieldValue = "" 'set to nullstring
Else
TestfieldValue = TestfieldVari
End If
------------------
Would there be a simpler or more compact way?
--
Sverk


"Rick Brandt" skrev:

Actually, that is probably the most non-normal way to do it. Forms are the
interface to data for people, not for code.

You could use DLookup().
You could open a Recordset that grans just the required row and field.
You could use the Form's RecordsetClone and FindFirst.

None of those would require that you position the form to that record.

 

[Sverk]

the Nz() function is the easiest way for me now, works just fine, thanks!
Thanks also for the link to the Allan Browne's, very useful for me as
acasual Access programmer.

Sequel Question: Now I have a way to fetch a field value without making the
record current,
so the natural question now becomes:
How do I write to a field in a non-current record?


--
Sverk


"Steve Sanford" skrev:

You might want to read this page:

http://www.allenbrowne.com/ser-42.html

(the ELookUp() function..

I haven't tested these, but here are two other ways:

'Sample #1:
-------------------------
Dim TestfieldVari As Variant 'necessary to allow for empty field (Null)
Dim TestfieldValue As String

TestfieldValue = "" 'set to nullstring

TestfieldVari = DLookup("[Place]", "tblMyTable", "Val([FilmNR]) = 1")
If Not IsNull(TestfieldVari) Then
TestfieldValue = TestfieldVari
End If
------------------


'Sample#2
-------------------------

Dim TestfieldValue As String

TestfieldValue = NZ(DLookup("[Place]", "tblMyTable", "Val([FilmNR]) =
1"),"")

------------------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Got it to work OK with DLook(), THANKS!

Took some twisting though because the field I wanted to access is a text
field, and when it is empty DLook() returns a Null, not an empty string as I
expected.
I fixed it this way:
-------------------------
Dim TestfieldVari As Variant 'necessary to allow for empty field (Null)
Dim TestfieldValue As String

TestfieldVari = DLookup("[Place]", "tblMyTable", "Val([FilmNR]) = 1")
If IsNull(TestfieldVari) Then
TestfieldValue = "" 'set to nullstring
Else
TestfieldValue = TestfieldVari
End If
------------------
Would there be a simpler or more compact way?
--
Sverk


"Rick Brandt" skrev:

Sverk wrote:
Hi,
Normally, I guess, the way to access a field in code is to first make
the relevant record current, then read the field's value.

Actually, that is probably the most non-normal way to do it. Forms are the
interface to data for people, not for code.

You could use DLookup().
You could open a Recordset that grans just the required row and field.
You could use the Form's RecordsetClone and FindFirst.

None of those would require that you position the form to that record.

But is it possible to do it without making its record current?
How?
Would be convenient, and more practical because it wouldn't make the
listing of the table skip back and forth.
Or is there a way to temporarily freeze and unfreeze the display of
the table?

 

[Rick Brandt]

the Nz() function is the easiest way for me now, works just fine,
thanks! Thanks also for the link to the Allan Browne's, very useful
for me as acasual Access programmer.

Sequel Question: Now I have a way to fetch a field value without
making the record current,
so the natural question now becomes:
How do I write to a field in a non-current record?

As you have seen DLookup() is only good for reading, not for writing. The
other two options I gave you (creating a Recordset or using the Form's
RecordsetClone), can be used for both reading and writing. In the case
where you only want to write and don't need to read you can execute an
UPDATE SQL statement.

CurrentDB.EXECUTE "UPDATE TableName SET FieldName = Value WHERE..."
,dbFailOnError