By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Setting a recordset inside a while loop errors out?

P: 10
I've been working on an adb this past week and I've hit a rock. I'm not quite sure how to fix this or if I'm just running it in such an awkward way, it kills itself. Yesterday morning, I wrote vba in MS Access to create an excel document and format it. Testing the initial vba went well; however it was just a test and the code had to be adjusted to complete about 20 tasks versus just one. Because of how my company currently uses excel (like their religion), using access to create the document is easier than manually moving and adjusting the information inside excel. Right now, I have a while loop and this is the quick hand...

Expand|Select|Wrap|Line Numbers
  1. Do While Index1 < RecordCount 
  2.    CurrentMachine = Machines.Fields("MachineNumber")
  3.    If CurrentMachine = MC1 Then
  4.       SQL = "SELECT (ALL RELEVANT FIELDS) & _
  5.       "FROM qry_MachineRawData" & _
  6.       "WHERE MachineNumber = MC1"
  7.    ElseIf 'IF ELSE Statement continues to MC9, 
  8.           'As their are 9 Machines... 
  9.    End If
  10.    Set RawDataPerMCRS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot) 
  11.    Set xlApp = New Excel.Application
  12.    xlApp.Visible = False
  13.    Set xlBook = xlApp.Workbooks.Open("SHARE:\Path\Template.xls")
  14.    Set xlSheet = xlBook.Worksheets(SheetNum)
  15.    ''''' Actions to copy the information from the raw data queried by each Machine into their own sheets within excel.
  16.  
  17. Machines.MoveNext
  18. Index1 = Index1 + 1
  19. Loop 'Should loop 9 times, once for each machine
  20. NewFileName = "FileName " & Format(Now(), "yyyy mm dd")
  21. FilePath = "SHARE\Export\"
  22. ActiveWorkbook.SaveAs FileName:=NewFileName
  23. RawDataPerMCRS.Close
  24. Set RawDataPerMCRS = Nothing
  25.  
When I set RawDataPerMCRS to the query if faults out with too few parameters. Is the syntax on the WHERE portion of the query correct? Previously I had a single query with currentmachine in the WHERE but I wrote the if statement as a work around... or at least an attempt which has failed. How do I write a WHERE statement with a changing string ie WHERE MachineNumber = (CurrentMachine)? This may be my problem with no records being queried. Also for saving the information,
does it look correct after the loop?

''''''''''''''''
So the asterisk method worked like a charm. Thank you PhilOfWalton.

Expand|Select|Wrap|Line Numbers
  1. "WHERE MachineNum = " & Chr$(34) & CurrentMC & Chr$(34)
  2.  
Used the CurrentMC inside the loop allowing me to delete the IfThenElse Statement (Which occurred 9 times).

The only issue I have remaining is the save. I basically start the current vba with opening a excel file and alter it (I use the one opened as a template). Once the information is altered, I'm trying to save the "completed" file to a different path with a new file name. Is my syntax wrong?

Expand|Select|Wrap|Line Numbers
  1. Set xlBook = xlApp.Workbooks.Open("DRIVE:\PATHIMPORT\Template.xls")
  2. 'Do While Loop Occurs Successfully (Thanks for the help)
  3. FilePath = "DRIVE:\PATHEXPORT\"
  4. ActiveWorkbook.Path = FilePath 'Faults Here
  5. ActiveWorkbook.SaveAs NewFileName
  6. ActiveWorkbook.Close True
  7.  
Compile Error: Can't assign to read only property
Jan 6 '17 #1

✓ answered by PhilOfWalton

I suspect that your line
"WHERE MachineNumber = MC1"
Should be

"WHERE MachineNumber = " & Chr$(34) & "MC1" & Chr$(34)

Try that and if that works you could have a look at looping all the machines using something like
Expand|Select|Wrap|Line Numbers
  1. WHERE MachineNumber = " & Chr$(34) & "MC" & Cstr(Index) & Chr$(34)
  2.  
Phil

Share this Question
Share on Google+
6 Replies


PhilOfWalton
Expert 100+
P: 1,430
I suspect that your line
"WHERE MachineNumber = MC1"
Should be

"WHERE MachineNumber = " & Chr$(34) & "MC1" & Chr$(34)

Try that and if that works you could have a look at looping all the machines using something like
Expand|Select|Wrap|Line Numbers
  1. WHERE MachineNumber = " & Chr$(34) & "MC" & Cstr(Index) & Chr$(34)
  2.  
Phil
Jan 6 '17 #2

P: 2
Additionally it looks like the If statement needs quotes surrounding the MC1 if it is a string-literal and not a variable.

Alternatively you can use single-quotes in the SQL rather than ASCII character codes for double-quotes as in the following:
Expand|Select|Wrap|Line Numbers
  1. ...
  2. WHERE MachineNumber = '" & MC1 & "'"
  3. ...
  4.  
Eric
Jan 6 '17 #3

P: 10
Thanks PhilOfWalton and EJWilliams71
Jan 6 '17 #4

P: 2
The ActiveWorkbook has a path that cannot be modified, it is a read-only property. Your SaveAs should work to save the "completed" file to a different path.

In your code above you should be able to comment out line 4 and the process should complete as you intend it to.

Eric
Jan 6 '17 #5

P: 10
This would then save the new file name in the same path (Import). How would I make a new path = Export?
Jan 6 '17 #6

P: 10
Got it... Thanks.

Expand|Select|Wrap|Line Numbers
  1. ActiveWorkbook.SaveAs FilePath & NewFileName
  2.  
Jan 6 '17 #7

Post your reply

Sign in to post your reply or Sign up for a free account.