How can I get the string from a record in a table and then place it in a string in VBA. I have this table, it only has one record in it and I want the string in the record to be put in a string name tbString.

Hi - (and welcome!)

The most simple way is to use the DLOOKUP function. E.g.
tbString = DLOOKUP ("[Field]", "tblData", "[Field] = <some match criteria>")

Note: if you are matching a string criteria then you have to put extra quotes around the criteria so that Access recognizes it as a string. E.g.
tbString = DLOOKUP ("[Field]", "tblData", "[Field] = ""This String""")

The other approach is to declare a recordset and query the database using a SQL statement. Unless speed is a crucial element, use DLOOKUP for ease.

hth,

- g

Is it possible to use dlookup to change the record in the table?

No, you will need to use SQL to do that.

So how can I use SQL to do that???

Here is some example code that steps through each (existing) record in a table. The table has at least two fields: Name (text) and Number (number).


' Declare variables
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim intCounter As Integer
intCounter = 1

' Establish connection to database
Set db = DBEngine(0)(0)

' Open a record set
Set rs = db.OpenRecordset("tblData", dbOpenTable)

' Loop through records
While Not rs.EOF
rs.Edit ' Edit the record

' Insert some information
rs!Name = "Name " & intCounter
rs!Number = intCounter

rs.Update
rs.MoveNext

' Increment Counter
intCounter = intCounter + 1
Wend

' Clean up and close
rs.Close
Set rs = Nothing

db.Close
Set db = Nothing


hth,

- g

I tried running the code but for some reason it brings up an error in the dim section, its says that 'User-defined type not defined'. What should I do? I am currently running Access 2000

in any module goto Tools>References...
Scroll down and find "Microsoft DAO 3.6 Object Libary" (Number may vary) and select it.
If you tell us what you are actualy trying to achive we may be able to answer you more helpfully :)

Peter

That worked, Thank you