General discussion

Locked

ACCESS2000 and VBA variable usage

By Mike52 ·
Function getit() As Variant
Dim myrs1 As DAO.Recordset
Dim myrs2 As DAO.Recordset
Dim mydb As DAO.Database
Set mydb = CurrentDb
Set myrs1 = mydb.OpenRecordset("query5")
Set myrs2 = mydb.OpenRecordset("query4")
Dim infstr As Variant
Dim fldname( As String
fldname(1)="field1"
fldname(2)="field2"
myrs2.AddNew
infstr = " "
infstr = myrs1![combine]
myrs2![toutxx] = infstr
myrs2.Update
End Function

This is a sample of my code. What I want to be able to do is place the 'addnew' to'update' portion into a loop that will handle up to 9 fields defined in an array. By then changing the value of the logstr I should be able to update variable fields.
When I set the statement myrs2!fldname(1)=textstr, I get an error stating that field name is not part of that recordset. In debug mode it shows that the array contains the proper field name. There must be something in the statement that will not allow the use of the variable and I can only use actual field names from the query within brackets. I have tried putting quotes and/or brackets around the array name to no avail.
If you have any ideas you can reply here or e-mail me at michaeldj_52@hotmail.com.

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

ACCESS2000 and VBA variable usage

by Peyison In reply to ACCESS2000 and VBA variab ...

Try:

myrs2.Fields(fldname(1))=textstr

Also - something else that you might want to look into is using an SQL Update statement. You can update multiple values at once, and even for updating single values it is faster. You can build an Update string in a loop the same way you're doing this:

sSql = "Update MyTable Set "

For I = 1 to 8
sSql = sSql & "fldname(I) & "='" & textstr(1) & "',"
Next

'--remove the last comma
sSql = Left(sSql,Len(sSql)-1)

mydb.Execute sSql

The stringcreated would look like:

Update MyTable Set Field1='val1', Field2='val2' ...

You can also add a Where clause to update only specific records.

Hope this helps.

Collapse -

ACCESS2000 and VBA variable usage

by Mike52 In reply to ACCESS2000 and VBA variab ...

Poster rated this answer

Collapse -

ACCESS2000 and VBA variable usage

by alex_bamos In reply to ACCESS2000 and VBA variab ...

Try
myrs2!fldname(1).value=textstr
and better declare textstr as variant if you don't want to bother with type incompatibilities.

Collapse -

ACCESS2000 and VBA variable usage

by Mike52 In reply to ACCESS2000 and VBA variab ...

Very simple, and it worked.

Collapse -

ACCESS2000 and VBA variable usage

by donq In reply to ACCESS2000 and VBA variab ...

'DECLARE AND INITIALIZE THE CURRENT DATABASE AS THE DATABASE TO USE.
Dim DB As Database
Set DB = CurrentDb()

DoCmd.Hourglass True

'DECLARE TEMPORARY RECORDSET VARIABLES TO REPRESENT THE VARIOUS RECORDS TO ARCHIVE.
Dim GL As Recordset 'Object Model's "GenLedger" (target) TABLE.
Dim GJ As Recordset 'Object Model's "GenJournal" (source) TABLE.
Dim TransID As Recordset 'PostJournal DRIVER (or Journal records to be posted).

'OPEN THE TARGET RECORDSET USED WITHIN THISPROCEDURE.
Set GL = DB.OpenRecordset("GenLedger", dbOpenDynaset)

'DECLARE PROCEDURE STRING MANAGEMENT VARIABLES.
Dim Sqlstr As String 'SQL Strings.
Dim MBstr As String 'Message Box Strings.

'*****************************************************
'****** Get all completed entries ready to post ******
'*****************************************************
'PUT SQL STATEMENT IN STRING FORM TO LOAD THE PROCEDURE DRIVER BEFORE OPENING THE SOURCE RECORDSET.
Sqlstr = " SELECT * FROM GenJournal WHERE (((GenJournal.gjOAct1) Is Not Null) And ((GenJournal.gjOAmt1) <> 0) And ((GenJournal.gjDAct1) Is Not Null) And ((GenJournal.gjDAmt1) <> 0) And ((GenJournal.gjDAct2) Is Not Null)) ORDER BY GenJournal.gjPK;"
Set TransID = DB.OpenRecordset(Sqlstr, dbOpenDynaset) 'WITH UNRESTRICTED CURSOR MOVEMENT.

'FOR EACH BATCH RECORD ADDED SINCE THE LAST TIME THIS PROCEDURE EXECUTED ADD A NEW "BatchHist" RECORD.
TransID.MoveFirst
While Not TransID.EOF
GL.AddNew 'CREATE A NEW RECORD.

'OPEN THE SOURCE RECORDSET AND INDEX IT TO THE RECORD BEING PROCESSED.
Sqlstr = "SELECT * FROM GenJournal WHERE (((GenJournal.gjOAct1) Is Not Null) And ((GenJournal.gjOAmt1) <> 0) And ((GenJournal.gjDAct1) Is Not Null) And ((GenJournal.gjDAmt1) <> 0) And ((GenJournal.gjDAct2) Is Not Null)) ORDER BY GenJournal.gjPK;"
Set GJ = DB.OpenRecordset(Sqlstr) 'EXPOS

Collapse -

ACCESS2000 and VBA variable usage

by Mike52 In reply to ACCESS2000 and VBA variab ...

Poster rated this answer

Collapse -

ACCESS2000 and VBA variable usage

by whyong In reply to ACCESS2000 and VBA variab ...

maybe you can try this if this is what you are looking for.

For I = 1 to 9
myrs2("fldname" & CStr(I)) = textstr
Next I

Collapse -

ACCESS2000 and VBA variable usage

by Mike52 In reply to ACCESS2000 and VBA variab ...

Poster rated this answer

Collapse -

ACCESS2000 and VBA variable usage

by Mike52 In reply to ACCESS2000 and VBA variab ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums