Simple: What is the syntax to reference a field value in Vb?

[Phil]

Dim db As DAO.Database
Dim List50 As DAO.Recordset
Dim NextNoteID As Long
Dim SQLSTR As String
Dim The_Item_ID As Long
Set db = CurrentDb()
Set List50 = db.OpenRecordset("Retire lowercased Items List",
dbOpenSnapshot)

Do While Not List50.EOF
The_Item_ID = ??????????????

OK, the query (Retire lowercase Items List) contains a field called
item_ID. I want to assign the value of item_ID from the current record
to The_Item_ID. I am missing something, not sure what.

Thanx

 

[Tom Ellison]

Dear Phil:

I threw this together. Perhaps it will help:

Public Function MyTest() As Integer
Dim db As DAO.Database
Dim List50 As DAO.Recordset
Dim NextNoteID As Long
Dim SQLSTR As String
Dim The_Item_ID As Long

Set db = CurrentDb()
Set List50 = db.OpenRecordset("X1", dbOpenDynaset)

Do While Not List50.EOF
List50.Edit
List50.Fields("x").Value = "M"
List50.Update
List50.MoveNext
Loop
List50.Close
Set List50 = Nothing
db.Close
Set db = Nothing
End Function

I left in some variables you declared wihich I didn't use.

The Close and Nothing stuff at the end is a good way of "cleaning up" after
using objects. It prevents "memory leaks".

I used the name of a table X1 which I had handy. Put your own in there.
This actually changes the column named "x" to "M" all the way through the
table. Tested and working.

Tom Ellison

 

['69 Camaro]

Hi, Phil.

Try:

Do While Not List50.EOF
The_Item_ID = List50.Fields("item_ID").Value

' . . . and the rest of your code follows.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Tom Ellison]

Dear Phil:

I did not really understand this correctly at first. How about:

Public Function MyTest() As Integer
Dim db As DAO.Database
Dim List50 As DAO.Recordset
Dim NextNoteID As Long
Dim SQLSTR As String
Dim The_Item_ID As Long

Set db = CurrentDb()
Set List50 = db.OpenRecordset("X1", dbOpenDynaset)

Do While Not List50.EOF
The_Item_ID = List50.Fields("x").Value
List50.MoveNext
Loop
List50.Close
Set List50 = Nothing
db.Close
Set db = Nothing
End Function

Change X1 to the name of a table and "x" to the name of a field you wish to
reference. How is that?

Tom Ellison