Object required error in If statement

[monardasprite]

Hello,

I have a form that when a box is checked (a selection made), I'd like the after-update code to run an append query to a table. Just running the append query works fine, but I'm trying to add an If statement afterwards that is bringing up an "object required" error. I know my table name and column name are typed correctly, where else am I going wrong?

Here's my code:

Option Compare Database
Private Sub Check24_AfterUpdate()
Set db = CurrentDb
DoCmd.RefreshRecord
DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable"


If Tables![tblMediaIngredientstoAdd]![TotalLiters] > 31 Then DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable" Else
DoCmd.OpenReport "RptCheckListChooseFromMediaList"
End Sub

[ssanfu]

Option Compare Database
Option Explicit  ' <<-- should be at the top of EVERY code module

Private Sub Check24_AfterUpdate()
  Set db = CurrentDb
  DoCmd.RefreshRecord
  DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable"
  If Tables![tblMediaIngredientstoAdd]![TotalLiters] > 31 Then
     DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable" 
  Else
     DoCmd.OpenReport "RptCheckListChooseFromMediaList"
  End if
End Sub

AFAIK, you can't refer to a field in a table like that. The table could have many records; which record should be checked to see if it is > 31?

[monardasprite]

Of course, that makes sense. Thanks Steve. In this particular case, this is a temporary table and the value for [TotalLiters] would be the same, so I wasn't thinking. I think I'll try to solve this by either using a query or setting a variable.

[monardasprite]

I'm still having trouble with this. I get a runtime error 13 Type Mismatch message

Here's my code. Any thoughts?
Option Compare Database
Option Explicit
Private Sub Check24_AfterUpdate()
Dim TotalLiters As String
TotalLiters = "Select LitersNeeded From [tblMediaList] WHERE [Select Media] = true"
'There would only be one line in the table where Select Media =True'
DoCmd.RefreshRecord
DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable"
If TotalLiters > 31 Then DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable" Else
DoCmd.OpenReport "RptCheckListforDaveChooseFromMediaList", acViewReport
End Sub

[ssanfu]

  If TotalLiters > 31 Then

You have delcared "TotalLiters" as a STRING, but the statement above is comparing a string to a number.
"TotalLiters" is a string , 31 is a number.
31 (no quotes - a number) is different than "31" (with quotes - a string)

TotalLiters = "Select LitersNeeded From [tblMediaList] WHERE [Select Media] = true"

You cannot set a variable equal to a SQL statement and have a value returned.
You can open a recordset

Option Compare Database
Option Explicit

Private Sub Check24_AfterUpdate()
   Dim TotalLiters As String
   Dim rs as DAO.Recordset

   Set rs = Currentdb.Openrecordset("Select LitersNeeded From [tblMediaList] WHERE [Select Media] = true")
   'There would only be one line in the table where Select Media =True'
   If not rs.BOF and not rs.EOF Then
      TotalLiters = rs!LitersNeeded 
   End If

   'DoCmd.RefreshRecord
   DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable"
   If TotalLiters > 31 Then 
       DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable" 
   Else
       DoCmd.OpenReport "RptCheckListforDaveChooseFromMediaList", acViewReport
   End If
End Sub

or you could use DLookup()

Option Compare Database
Option Explicit

Private Sub Check24_AfterUpdate()
  Dim TotalLiters As String

  TotalLiters = DLookup("LitersNeeded","tblMediaList", "[Select Media] = true")
  'There would only be one line in the table where Select Media =True'
  DoCmd.RefreshRecord
  DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable"
  If TotalLiters > 31 Then 
     DoCmd.OpenQuery "qryCalculateIngredientsToAddAppendTable" 
  Else
     DoCmd.OpenReport "RptCheckListforDaveChooseFromMediaList", acViewReport
  End If
End Sub