Compile Error: Statement Invalid Outside Type Block

[Amit Lamba]

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:

 Sub check_quantity () 

I'd appreciate any help possible, THANKS!!

The entire code is as follows:

Option Compare Database
Option Explicit
 
Sub check_quantity()
On Error GoTo err_check_quantity
 
Dim material As Variant
Dim totalKg As Variant
queryString As String
queryString2 As String
Quantity As Variant
valid As Integer
strMsg As String
Dim Rs As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim Db As DAO.Database
 
 
Set Db = CurrentDb()
 
queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![Batchsheet Subform]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
 
Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
 
If Not (Rs.BOF And Rs.EOF) Then
Rs.MoveLast
Rs.MoveFirst
 
With Rs
Do While Not Rs.EOF
 
material = Rs.Fields("MaterialID").Value
totalKg = Rs.Fields("Total (kg)").Value
 
queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
 
Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
 
If Not (Rs2.BOF And Rs2.EOF) Then
Rs2.MoveLast
Rs2.MoveFirst
 
 
  Quantity = Rs2.Fields("InStock").Value
 
 
     If totalKg > Quantity Then
        valid = 0
     Else
        valid = 1
     End If
 
     Select Case valid
 
     Case 0
            strMsg = " Order cannot currently be completed" & _
                        vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
            MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
 
     Case 1
            Rs.MoveNext
 
     End Select
 
Loop
End With
 
 
   Rs.Close
   Rs2.Close
   Db.Close
 
   Set Rs = Nothing
   Set Rs2 = Nothing
   Set Db = Nothing
 
exit_check_quantity:
    Exit Sub
 
err_check_quantity:
    MsgBox Err.decsription
    Resume exit_check_quantity
 
End Sub
 
 

[Amit Lamba]

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?

[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!

[Amit Lamba]

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:

Option Compare Database
Option Explicit
 
Sub check_quantity()
On Error GoTo err_check_quantity
 
Dim material As String
Dim totalKg As Variant
Dim queryString As String
Dim queryString2 As String
Dim Quantity As Variant
Dim valid As Integer
Dim strMsg As String
Dim Rs As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim Db As DAO.Database
 
valid = 1
 
Set Db = CurrentDb()
 
queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![Batchsheet Subform]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
 
Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
 
If Not (Rs.BOF And Rs.EOF) Then
Rs.MoveLast
Rs.MoveFirst
 
With Rs
Do While Not Rs.EOF
 
material = Rs.Fields("MaterialID").Value
totalKg = Rs.Fields("Total (kg)").Value
 
queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
 
Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
 
If Not (Rs2.BOF And Rs2.EOF) Then
Rs2.MoveLast
Rs2.MoveFirst
 
  Quantity = Rs2.Fields("InStock").Value
 
 
     If totalKg > Quantity Then
        valid = 0
     Else
        valid = 1
     End If
 
       Select Case valid
 
       Case 0
              strMsg = " Order cannot currently be completed" & _
                        vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
              MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
              Exit Do
 
       Case 1
              Rs.MoveNext
 
       End Select
 
End If
 
Loop
End With
 
End If
 
   Rs.Close
   Rs2.Close
   Db.Close
 
   Set Rs = Nothing
   Set Rs2 = Nothing
   Set Db = Nothing
 
exit_check_quantity:
    Exit Sub
 
err_check_quantity:
    MsgBox Err.decsription
    Resume exit_check_quantity
 
End Sub
 

[NeoPa]

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.

[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.

[Amit Lamba]

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.

[Amit Lamba]

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:

Option Compare Database
Option Explicit
 
Sub check_quantity()
On Error GoTo err_check_quantity
 
Dim material As String
Dim totalKg As Variant
Dim queryString As String
Dim queryString2 As String
Dim Quantity As Variant
Dim valid As Integer
Dim strMsg As String
Dim Rs As DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim Db As DAO.Database
 
valid = 1
 
Set Db = CurrentDb()
 
queryString = "SELECT * FROM Query1 WHERE [ProductName] = '" & [Forms]![BatchsheetSubform]![Form]![ProductName] & "' AND [OrderID] = " & [Forms]![Batchsheet Subform]![OrderID] & ";"
 
Set Rs = Db.OpenRecordset(queryString, dbOpenDynaset)
 
If Not (Rs.BOF And Rs.EOF) Then
Rs.MoveLast
Rs.MoveFirst
 
With Rs
Do While Not Rs.EOF
 
material = Rs.Fields("MaterialID").Value
totalKg = Rs.Fields("Total (kg)").Value
 
queryString2 = "SELECT * FROM RawMaterials WHERE [ItemName] = '" & material & ";"
 
Set Rs2 = Db.OpenRecordset(queryString2, dbOpenDynaset)
 
If Not (Rs2.BOF And Rs2.EOF) Then
Rs2.MoveLast
Rs2.MoveFirst
 
  Quantity = Rs2.Fields("InStock").Value
 
 
     If totalKg > Quantity Then
        valid = 0
     Else
        valid = 1
     End If
 
       Select Case valid
 
       Case 0
              strMsg = " Order cannot currently be completed" & _
                        vbCrLf & " Please verify you have enough " & material & "inventory to complete this order."
              MsgBox strMsg, vbInformation, "INVALID Raw Material Level"
              Exit Do
 
       Case 1
              Rs.MoveNext
 
       End Select
 
End If
 
Loop
End With
 
End If
 
   Rs.Close
   Rs2.Close
   Db.Close
 
   Set Rs = Nothing
   Set Rs2 = Nothing
   Set Db = Nothing
 
exit_check_quantity:
    Exit Sub
 
err_check_quantity:
    MsgBox Err.Description
    Resume exit_check_quantity
 
End Sub
 

[Amit Lamba]

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!

[NeoPa]

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 :)