Replacing a field name in the With statement with a variable name

L

LisaB

If I want to run this function where I bring in the field name, how do I
substitute the fieldname with the variable name. For example: in the
function below I want to replace !Title with the variable FieldName. How do
I reference it
--------------------------
Function ChangeFieldValue (FieldName as string, Value as string)

Set dbs = OpenDatabase("Northwind.mdb")
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset)

With rst
.Edit
!Title = Value
.Update
End With

I want it this but I get an error

With rst
.Edit
!FieldName = Value
.Update
End With
 
D

Dirk Goldgar

LisaB said:
If I want to run this function where I bring in the field name, how
do I substitute the fieldname with the variable name. For example:
in the function below I want to replace !Title with the variable
FieldName. How do I reference it
--------------------------
Function ChangeFieldValue (FieldName as string, Value as string)

Set dbs = OpenDatabase("Northwind.mdb")
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset)

With rst
.Edit
!Title = Value
.Update
End With

I want it this but I get an error

With rst
.Edit
!FieldName = Value
.Update
End With
Click to expand...

With rst
.Edit
.Fields(FieldName) = Value
.Update
End With

You probably shouldn't name your argument "Value", though, just in case
Access/VBA might confuse it with the property named "Value".
 
D

DBarker

I tried this code, I have multiple fields to change names
on and this code did not work. Do I have to do a separate
one for each?
-----------------------------------------------------------

Function ChangeFieldValue(FieldName As String, Value As
String)

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("STi", dbOpenDynaset)

With rst
.Edit
.Fields(SWDATECREATED) = EntryDate
.Update
.Edit
.Fields(SUCODE) = Region
.Update
.Edit
.Fields(SUDEALERCODE) = Dealer
.Update
.Edit
.Fields(SWNAME) = DealerName
.Update
.Edit
.Fields(SWADDRESS1) = Address1
.Update
.Edit
.Fields(SWADDRESS2) = Address2
.Update
.Edit
.Fields(SWCITY) = City
.Update
.Edit
.Fields(SWSTATE) = State
.Update
.Edit
.Fields(SWZIP) = ZipCode
.Update
.Edit
.Fields(SWASSETTAG) = VINPrefix
.Update
.Edit
.Fields(SWSERIALNUMBER) = VIN(Last8)
.Update
.Edit
.Fields(SUMILEAGE) = Mileage
.Update
.Edit
.Fields(SUPRODDATE) = ProdDate
.Update
.Edit
.Fields(SUFAILURECODE) = FailCode
.Update
.Edit
.Fields(Expr1001) = Condition
.Update
.Edit
.Fields(Expr1002) = Journals
.Update
.Edit
.Fields(SWCREATEDBY) = UserID
.Update
.Edit
.Fields(SUENGINECYLINDERDESC) = EngineSize
.Update
.Edit
.Fields(SUENGINENBR) = EngineNum
.Update
.Edit
.Fields(SUTRANSMISSION) = TransNum
.Update
.Edit
.Fields(SWCASEID) = CaseNum
.Update
.Edit
.Fields(SUSECTION) = Section
.Update
.Edit
.Fields(SUSUBSECTION) = SubSection
.Update
.Edit
.Fields(SWKEYWORDS) = Keywords
.Update
.Edit
.Fields(SULASTMODIFYDATE) = LastModified
.Update

End With
 
G

Guest

Help shows the example as: .Fields("name") <- double quote

'---- Snip ---
With rs
.Edi
.Fields("SWDATECREATED") = EntryDat
.Updat
.Edi
.Fields("SUCODE") = Regio
.Updat
.Edi
.Fields("SUDEALERCODE") = Deale
'---- Snip ---

Stev
 
D

Dirk Goldgar

DBarker said:
I tried this code, I have multiple fields to change names
on and this code did not work. Do I have to do a separate
one for each?
-----------------------------------------------------------

Function ChangeFieldValue(FieldName As String, Value As
String)

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("STi", dbOpenDynaset)

With rst
.Edit
.Fields(SWDATECREATED) = EntryDate
.Update
.Edit
.Fields(SUCODE) = Region
.Update
.Edit
.Fields(SUDEALERCODE) = Dealer
.Update
.Edit
.Fields(SWNAME) = DealerName
.Update
.Edit
.Fields(SWADDRESS1) = Address1
.Update
.Edit
.Fields(SWADDRESS2) = Address2
.Update
.Edit
.Fields(SWCITY) = City
.Update
.Edit
.Fields(SWSTATE) = State
.Update
.Edit
.Fields(SWZIP) = ZipCode
.Update
.Edit
.Fields(SWASSETTAG) = VINPrefix
.Update
.Edit
.Fields(SWSERIALNUMBER) = VIN(Last8)
.Update
.Edit
.Fields(SUMILEAGE) = Mileage
.Update
.Edit
.Fields(SUPRODDATE) = ProdDate
.Update
.Edit
.Fields(SUFAILURECODE) = FailCode
.Update
.Edit
.Fields(Expr1001) = Condition
.Update
.Edit
.Fields(Expr1002) = Journals
.Update
.Edit
.Fields(SWCREATEDBY) = UserID
.Update
.Edit
.Fields(SUENGINECYLINDERDESC) = EngineSize
.Update
.Edit
.Fields(SUENGINENBR) = EngineNum
.Update
.Edit
.Fields(SUTRANSMISSION) = TransNum
.Update
.Edit
.Fields(SWCASEID) = CaseNum
.Update
.Edit
.Fields(SUSECTION) = Section
.Update
.Edit
.Fields(SUSUBSECTION) = SubSection
.Update
.Edit
.Fields(SWKEYWORDS) = Keywords
.Update
.Edit
.Fields(SULASTMODIFYDATE) = LastModified
.Update

End With
Click to expand...

The original code was intended to update a field whose name is passed as
a string argument. In your case, you are hard-coding the names of the
fields. Try it like this:

With rst
.Edit
.Fields("SWDATECREATED") = EntryDate
.Fields("SUCODE") = Region
.Fields("SUDEALERCODE") = Dealer
.Fields("SWNAME") = DealerName
.Fields("SWADDRESS1") = Address1
.Fields("SWADDRESS2") = Address2
.Fields("SWCITY") = City
.Fields("SWSTATE") = State
.Fields("SWZIP") = ZipCode
.Fields("SWASSETTAG") = VINPrefix
.Fields("SWSERIALNUMBER") = VIN("Last8")
.Fields("SUMILEAGE") = Mileage
.Fields("SUPRODDATE") = ProdDate
.Fields("SUFAILURECODE") = FailCode
.Fields("Expr1001") = Condition
.Fields("Expr1002") = Journals
.Fields("SWCREATEDBY") = UserID
.Fields("SUENGINECYLINDERDESC") = EngineSize
.Fields("SUENGINENBR") = EngineNum
.Fields("SUTRANSMISSION") = TransNum
.Fields("SWCASEID") = CaseNum
.Fields("SUSECTION") = Section
.Fields("SUSUBSECTION") = SubSection
.Fields("SWKEYWORDS") = Keywords
.Fields("SULASTMODIFYDATE") = LastModified
.Update
End With

You only need one .Edit and one .Update to edit all the fields in the
current record of the recordset and then save it. Do you really have
fields in your table named Expr1001 and Expr1002?
 
D

DBarker

This is a little new to me. I have written code based on
an event. I added the code you provided with the
corrected formatting. I tried running it from the code
window but nothing happened. This is a module that I
created and I am not sure why it is not working. Any
help would be greatly appreciated.

Debbie
 
D

Dirk Goldgar

DBarker said:
This is a little new to me. I have written code based on
an event. I added the code you provided with the
corrected formatting. I tried running it from the code
window but nothing happened. This is a module that I
created and I am not sure why it is not working. Any
help would be greatly appreciated.

Debbie
Click to expand...
[...]


As posted, this is defined as a function that requires parameters, and
so it won't run directly from the code window; it has to be called. As
I understand it, though, you don't need and aren't using those
parameters, and so you could change it from a Function to a Sub, with
this declaration:

Sub ChangeFields()

With that declaration, you could call it from code in the form's module
by writing

Call ChangeFields

Since the procedure appears to be in a form module and is not a Public
procedure, it can only be called from code behind that form.

If you're not experienced with code, maybe you'd like to post the
complete code from your form's module, including all declarations and
procedures, and we could help you sort the problem out. You'd better
also explain in words what you want the code to do.
 
D

DBarker

Below is the entire code. This is in a module not a
form. I want to just be able to run the code when I open
the module or I will create a macro to run this. Is that
the correct approach?
Debbie
-------------------------------------
Public Sub Combine()

Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & "; " _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub



-----Original Message-----
This is a little new to me. I have written code based on
an event. I added the code you provided with the
corrected formatting. I tried running it from the code
window but nothing happened. This is a module that I
created and I am not sure why it is not working. Any
help would be greatly appreciated.

Debbie
Click to expand...
[...]


As posted, this is defined as a function that requires parameters, and
so it won't run directly from the code window; it has to be called. As
I understand it, though, you don't need and aren't using those
parameters, and so you could change it from a Function to a Sub, with
this declaration:

Sub ChangeFields()

With that declaration, you could call it from code in the form's module
by writing

Call ChangeFields

Since the procedure appears to be in a form module and is not a Public
procedure, it can only be called from code behind that form.

If you're not experienced with code, maybe you'd like to post the
complete code from your form's module, including all declarations and
procedures, and we could help you sort the problem out. You'd better
also explain in words what you want the code to do.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
D

Dirk Goldgar

DBarker said:
Below is the entire code. This is in a module not a
form. I want to just be able to run the code when I open
the module or I will create a macro to run this. Is that
the correct approach?
Debbie
-------------------------------------
Public Sub Combine()

Dim strSQL As String
Dim dbs As DAO.Database
Dim rstFr As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim varNoteBatch As Variant

Set dbs = CurrentDb()
strSQL = "SELECT STi_Table.* " _
& "FROM STi_Table " _
& "ORDER BY CaseID"
Set rstTo = dbs.OpenRecordset(strSQL, dbOpenDynaset)
strSQL = "SELECT STi_Multiples.* " _
& "FROM STi_Multiples " _
& "ORDER BY CaseID"
Set rstFr = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do Until rstTo.EOF
varNoteBatch = Null
strSQL = "[CaseID]=" & rstTo!CaseID
rstFr.FindFirst strSQL
Do Until rstFr.NoMatch
varNoteBatch = varNoteBatch & "; " _
& rstFr!Journals
rstFr.FindNext strSQL
Loop
rstTo.Edit
rstTo!Journals = varNoteBatch
rstTo.Update
rstTo.MoveNext
Loop

rstFr.Close
rstTo.Close
Set rstFr = Nothing
Set rstTo = Nothing
End Sub
Click to expand...

This code seems to have nothing to do with the code we were talking
about before. It looks okay to me, for what that's worth, and assuming
it's really supposed to do what I think it's supposed to do.

Opening a module will *not* run the code in it. You can run it from
code behind a command button if you like, or you can use a macro to run
it, I guess, via the RunCode action. I'm not all that familiar with
macros because I don't use them; I use code instead. If you just want
to run this code once you could simply enter

Combine

in the Immediate window, or you could open the module in the VB Editor
window, click somewhere inside the Sub procedure, and press F5.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Replacing a field name in the 'With' statement with a variable name 1
Message from Access 2007 on sub function 3
Function not working 3
Use a string to define a field 1
Referring to a field name object 2
A Loop in a Loop 3
Need help with Variable substitution 2
Do I need a Nested Loop? 14

Top