Access 2007 VBA, how to clear or reset variables

Warehouse13
Warehouse13 used Ask the Experts™
on
Hey all, i just needed to know how to reset 4 string variables to nothing at the end of a loop so it will be able to fill them with new stuff afterwards.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:


sVar1=""

and so on.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Or ... you can just fill them in with the 'new stuff"

mx

Author

Commented:
I tried doing the Var1="" but that was not resetting the variables...they still had the same value...doesn't matter if I do it at the beginning of the loop or the end. The result is the same. Its been forever since my last VB class and I am most definitely not  a programmer. Is there another way to do it?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
With String variables, you don't set them to Nothing ... just to an Empty String  ...which is ""   two double quotes.

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
What are the actual Names of your String variables?

Var1 was just an example.

YourStringVariableName = ""

"Is there another way to do it?"
I don't think so.

Can you post the related code?

mx

Author

Commented:
Sure...
    Set rstFiles = CurrentDb.OpenRecordset(strSQL)
    Set fso = New FileSystemObject
     
    Do Until rstFiles.EOF = True
   
   
    strProvName = rstFiles.Fields("Field1").Value
   
    strCopyToFilePath = "C:\CompanyImages\" & strProvName

   

    '-- Hold the source filename
        strFileName = rstFiles.Fields("ImagePath").Value
       
    '-- Get the source file folder
        If Val(rstFiles.Fields("Disk")) < 9 Then
        strSourceFileFolder = "ncvs.00" & rstFiles.Fields("Disk").Value
        Else
        strSourceFileFolder = "ncvs.0" & rstFiles.Fields("Disk").Value
        End If
   
    strTemp = "C:\Images\"
    strSourceFileLocation = strTemp & strSourceFileFolder


    '-- Get the foldername
        strSubFolderName = GetSubFolderName(rstFiles.Fields("Field3").Value)

    '-- Now copy the file (source, destination)
        FileCopy strSourceFileLocation & "\" & strFileName, _
                     strCopyToFilePath & "\" & strSubFolderName & "\" & strFileName

        rstFiles.MoveNext
       
    strSourceFileLocation = ""
    strFileName = ""
    strCopyToFilePath = ""
    strSubFolderName = ""
    Loop

It keeps erroring on this piece:
        FileCopy strSourceFileLocation & "\" & strFileName, _
                     strCopyToFilePath & "\" & strSubFolderName & "\" & strFileName
with a path error. I believe that is because it is not clearing out the variables before it goes again. The path itself seems to work, as it does move the files to one provider folder.

Author

Commented:
Bumping up to 500 pts
Database Architect / Systems Analyst
Top Expert 2007
Commented:
Well, this is certainly correct:

    strSourceFileLocation = ""
    strFileName = ""
    strCopyToFilePath = ""
    strSubFolderName = ""
    Loop ' end of loop

As far as the error ... put a breakpoint on that line and examine the variable values.

You might try change

Do Until rstFiles.EOF = True

to

Do

and

Loop

to

Loop Until rstFiles.EOF

Author

Commented:
I changed the loop but that hasn't helped it...it breaks into the debugger after the error. What I can see is that the variable is caling for Smith Joe R. but the folders name is Smith Joe R     How would I get it to drop the period off of the end of the strProv variable if there is one, or do nothing if there isn't? I am thinking an If Then Else statement...but not sure how to code it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
Use the Replace function:

Replace(YourStringVariableName,".","")

replace period with ""

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
strProvName = Replace(rstFiles.Fields("Field1").Value, "." , "")

I guess.

mx

Author

Commented:
We figured it out...it had to be the last period only, the others had to remain intact...the variables are still not resetting though...I can see where the period is not there so it should have no problem transferring but it is still having trouble. I don't think that it is necessarily related to the original issue though so I will award points. Thank you so much for your time!
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
You are welcome.

But ... the code you have to reset the variables *will* 'reset' them ... assuming that code executes.

Put a breakpoint at

strSourceFileLocation = ""

and confirm your code stops at this line.  Then hit F8 to single step through that line.  Check the value before and after ...

mx

Author

Commented:
Will do. Thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial