How to get a record into a string

[uk_boy]

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.

 

[gromit]

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

 

[uk_boy]

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

 

[gromit]

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

 

[uk_boy]

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

 

[gromit]

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

 

[uk_boy]

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

 

[Bat17]

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

 

[uk_boy]

That worked, Thank you