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(Mate rial 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 'BatchsheetSubf orm' 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 BatchsheetSubfo rm 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 'BatchsheetSubf orm 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 :)