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

Compile Error: Statement Invalid Outside Type Block

P: 6
I am trying to run a module from a button on a form where I populate a subform from Query1 with a product formula. The module is supposed to check whether the product formula can be implemented given enough raw material inventory. So, I'm trying to loop through my product formula based on OrderID and MaterialID(Material Name) and check against the current stock inventory table. If any of the ingredients required for the product to be made is over the available stock inventory on hand, then I want an error to be displayed, otherwise I don't want any errors.

The problem I'm having is with the Compile Error at the line where it states:

Expand|Select|Wrap|Line Numbers
  1.  Sub check_quantity () 
I'd appreciate any help possible, THANKS!!

The entire code is as follows:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub check_quantity()
  5. On Error GoTo err_check_quantity
  6.  
  7. Dim material As Variant
  8. Dim totalKg As Variant
  9. queryString As String
  10. queryString2 As String
  11. Quantity As Variant
  12. valid As Integer
  13. strMsg As String
  14. Dim Rs As DAO.Recordset
  15. Dim Rs2 As DAO.Recordset
  16. Dim Db As DAO.Database
  17.  
  18.  
  19. Set Db = CurrentDb()
  20.  
  21. queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![Batchsheet Subform]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
  22.  
  23. Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
  24.  
  25. If Not (Rs.BOF And Rs.EOF) Then
  26. Rs.MoveLast
  27. Rs.MoveFirst
  28.  
  29. With Rs
  30. Do While Not Rs.EOF
  31.  
  32. material = Rs.Fields("MaterialID").Value
  33. totalKg = Rs.Fields("Total (kg)").Value
  34.  
  35. queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
  36.  
  37. Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
  38.  
  39. If Not (Rs2.BOF And Rs2.EOF) Then
  40. Rs2.MoveLast
  41. Rs2.MoveFirst
  42.  
  43.  
  44.   Quantity = Rs2.Fields("InStock").Value
  45.  
  46.  
  47.      If totalKg > Quantity Then
  48.         valid = 0
  49.      Else
  50.         valid = 1
  51.      End If
  52.  
  53.      Select Case valid
  54.  
  55.      Case 0
  56.             strMsg = " Order cannot currently be completed" & _
  57.                         vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
  58.             MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
  59.  
  60.      Case 1
  61.             Rs.MoveNext
  62.  
  63.      End Select
  64.  
  65. Loop
  66. End With
  67.  
  68.  
  69.    Rs.Close
  70.    Rs2.Close
  71.    Db.Close
  72.  
  73.    Set Rs = Nothing
  74.    Set Rs2 = Nothing
  75.    Set Db = Nothing
  76.  
  77. exit_check_quantity:
  78.     Exit Sub
  79.  
  80. err_check_quantity:
  81.     MsgBox Err.decsription
  82.     Resume exit_check_quantity
  83.  
  84. End Sub
  85.  
  86.  
Jul 21 '10 #1

✓ answered by NeoPa

NeoPa: Your Do...Loops, and If...End Ifs, and With...End Withs do not balance up correctly.

You need to get these sorted before continuing, and certainly before it will work.

Welcome to Bytes!
I would add :
  1. Line #75 closes CurrentDB. Not a good idea. If your code opens an object then it should close it. If it finds it already open, then it should leave it open. There may be exceptions to this rule, but they're rare.
  2. Line #81 is probably the offending property. You want Err.Description.
    Remember next time to include the line number though please.

Share this Question
Share on Google+
9 Replies


P: 6
Ok, it looks like I forgot some Dim's. Fixed that but now my new error is "Loop without Do" Hmmm, I see a Do, but maybe I'm not closing or opening the loop properly?
Jul 21 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
Your Do...Loops, and If...End Ifs, and With...End Withs do not balance up correctly.

You need to get these sorted before continuing, and certainly before it will work.

Welcome to Bytes!
Jul 21 '10 #3

P: 6
Ok, looks like I've fixed the loop problems. Now, I am getting a message box popup that states, "Object Doesn't Support This Property or Method".

I'm guessing the problem is either with a type mismatch where I may be comparing a string to a number or vice versa. Or maybe somehow the fact that I am retrieving values from a Query is causing the problem?

Can anyone offer any insight?

The feedback so far is really appreciated.

Thanks!

Updated Code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub check_quantity()
  5. On Error GoTo err_check_quantity
  6.  
  7. Dim material As String
  8. Dim totalKg As Variant
  9. Dim queryString As String
  10. Dim queryString2 As String
  11. Dim Quantity As Variant
  12. Dim valid As Integer
  13. Dim strMsg As String
  14. Dim Rs As DAO.Recordset
  15. Dim Rs2 As DAO.Recordset
  16. Dim Db As DAO.Database
  17.  
  18. valid = 1
  19.  
  20. Set Db = CurrentDb()
  21.  
  22. queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![Batchsheet Subform]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
  23.  
  24. Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
  25.  
  26. If Not (Rs.BOF And Rs.EOF) Then
  27. Rs.MoveLast
  28. Rs.MoveFirst
  29.  
  30. With Rs
  31. Do While Not Rs.EOF
  32.  
  33. material = Rs.Fields("MaterialID").Value
  34. totalKg = Rs.Fields("Total (kg)").Value
  35.  
  36. queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
  37.  
  38. Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
  39.  
  40. If Not (Rs2.BOF And Rs2.EOF) Then
  41. Rs2.MoveLast
  42. Rs2.MoveFirst
  43.  
  44.   Quantity = Rs2.Fields("InStock").Value
  45.  
  46.  
  47.      If totalKg > Quantity Then
  48.         valid = 0
  49.      Else
  50.         valid = 1
  51.      End If
  52.  
  53.        Select Case valid
  54.  
  55.        Case 0
  56.               strMsg = " Order cannot currently be completed" & _
  57.                         vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
  58.               MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
  59.               Exit Do
  60.  
  61.        Case 1
  62.               Rs.MoveNext
  63.  
  64.        End Select
  65.  
  66. End If
  67.  
  68. Loop
  69. End With
  70.  
  71. End If
  72.  
  73.    Rs.Close
  74.    Rs2.Close
  75.    Db.Close
  76.  
  77.    Set Rs = Nothing
  78.    Set Rs2 = Nothing
  79.    Set Db = Nothing
  80.  
  81. exit_check_quantity:
  82.     Exit Sub
  83.  
  84. err_check_quantity:
  85.     MsgBox Err.decsription
  86.     Resume exit_check_quantity
  87.  
  88. End Sub
  89.  
Jul 21 '10 #4

NeoPa
Expert Mod 15k+
P: 31,768
The error message is good. Giving the line the error occurred on would also be a great help. The code box helps by numbering each line for you.
Jul 21 '10 #5

NeoPa
Expert Mod 15k+
P: 31,768
NeoPa: Your Do...Loops, and If...End Ifs, and With...End Withs do not balance up correctly.

You need to get these sorted before continuing, and certainly before it will work.

Welcome to Bytes!
I would add :
  1. Line #75 closes CurrentDB. Not a good idea. If your code opens an object then it should close it. If it finds it already open, then it should leave it open. There may be exceptions to this rule, but they're rare.
  2. Line #81 is probably the offending property. You want Err.Description.
    Remember next time to include the line number though please.
Jul 21 '10 #6

P: 6
Thanks NeoPa,

Unfortunately my error message didn't provide a line number, otherwise I would have definitely included it. Let me try your suggestions and get back to you.

Thanks so much for the help so far.
Jul 21 '10 #7

P: 6
Ok, I corrected the typo with description but yet again I have another error. I'm getting an error that states: "Microsoft Office Access can't find the form 'BatchsheetSubform' referred to in a macro expression or Visual Basic code." Again, no error number or line number, but it's obvious which line is the offending one here.

I changed the name of the Batchsheet subform to BatchsheetSubform thinking that the space was causing a problem because VB was automagically using an uppercase 'S' for Subform. And my actual form uses a lower case 's'. Now I've just renamed the form to 'BatchsheetSubform both in VB and in the actual form.

I also added .[Form] to reference the control in the subform since I believe a subform is a control in itself and needs a .Form to access it's controls. I also did away with Db.close but that didn't help.

Updated Code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub check_quantity()
  5. On Error GoTo err_check_quantity
  6.  
  7. Dim material As String
  8. Dim totalKg As Variant
  9. Dim queryString As String
  10. Dim queryString2 As String
  11. Dim Quantity As Variant
  12. Dim valid As Integer
  13. Dim strMsg As String
  14. Dim Rs As DAO.Recordset
  15. Dim Rs2 As DAO.Recordset
  16. Dim Db As DAO.Database
  17.  
  18. valid = 1
  19.  
  20. Set Db = CurrentDb()
  21.  
  22. queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![BatchsheetSubform]![Form]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
  23.  
  24. Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
  25.  
  26. If Not (Rs.BOF And Rs.EOF) Then
  27. Rs.MoveLast
  28. Rs.MoveFirst
  29.  
  30. With Rs
  31. Do While Not Rs.EOF
  32.  
  33. material = Rs.Fields("MaterialID").Value
  34. totalKg = Rs.Fields("Total (kg)").Value
  35.  
  36. queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
  37.  
  38. Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
  39.  
  40. If Not (Rs2.BOF And Rs2.EOF) Then
  41. Rs2.MoveLast
  42. Rs2.MoveFirst
  43.  
  44.   Quantity = Rs2.Fields("InStock").Value
  45.  
  46.  
  47.      If totalKg > Quantity Then
  48.         valid = 0
  49.      Else
  50.         valid = 1
  51.      End If
  52.  
  53.        Select Case valid
  54.  
  55.        Case 0
  56.               strMsg = " Order cannot currently be completed" & _
  57.                         vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
  58.               MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
  59.               Exit Do
  60.  
  61.        Case 1
  62.               Rs.MoveNext
  63.  
  64.        End Select
  65.  
  66. End If
  67.  
  68. Loop
  69. End With
  70.  
  71. End If
  72.  
  73.    Rs.Close
  74.    Rs2.Close
  75.    Db.Close
  76.  
  77.    Set Rs = Nothing
  78.    Set Rs2 = Nothing
  79.    Set Db = Nothing
  80.  
  81. exit_check_quantity:
  82.     Exit Sub
  83.  
  84. err_check_quantity:
  85.     MsgBox Err.Description
  86.     Resume exit_check_quantity
  87.  
  88. End Sub
  89.  
Jul 21 '10 #8

P: 6
Ahhh, ok I had to make a few changes but I got it to work!! Thanks a lot for guiding me in the right direction. Really appreciate it!
Jul 21 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
Well done for finding and fixing all the errors Amit.

I agree with your earlier comment. If no line number is reported, then you cannot pass it on. Always do so when you can though :)
Jul 22 '10 #10

Post your reply

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