Could not Append Table from Sub-Routine Function?

[silvrerado]

Hi, my routine shows an error message "Runtime error '2465' Database can't find the field '|' referred to in your expression." The routine is place in the Startup Form when it is loaded.

[Tbl_WkSch]![NextDate] = Table with a field in it.
Date - Weekday(Date) + 9) = Next week Monday

The "(Date - Weekday(Date) + 9)" is copied from the SQL view
which I guess is incorrect since the Date does not have a bracket

Tha Action query is suppose to fire up if the condition met.

Private Sub Form_Load()
If ([Tbl_WkSch]![NextDate] <= (Date - Weekday(Date) + 9)) Then
DoCmd.SetWarnings False
DoCmd.RunSQL &quot;SELECT Action query .....&quot;
DoCmd.SetWarnings True

Else
End If
End Sub

Any help will be appreciated. Thanks

silvre_rado

 

[TheAceMan1]

How are ya silvrerado . . . . .

Wow . . . . . Hmmmmmmm . . .

1) There's no object reference to the table. This is what raised the error.

2) If you did have a reference, you do not specify your comparison record of interest(unless its the first record).

3) Where are you getting the &quot;Date&quot; from?

What you need is below. I'll leave you to resolve the &quot;Date&quot;.

Private Sub Form_Load()
   Dim db As Database, rst As Recordset
   
   Set db = CurrentDb()
   Set rst = db.OpenRecordset("YourTableName", dbOpenTable)
   
   If rst.BOF Then
      'What to do if no records!
   ElseIf rst!NextDate <= (Date - Weekday(Date) + 9) Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL &quot;SELECT Action query .....&quot;
        DoCmd.SetWarnings True
        
    Else
    End If
    
    Set rst = Nothing
    Set db = Nothing
End

Remember, the above assumes the first record is your record of interest. If not, you'll have to use the SEEK method to move the record pointer there.


TheAceMan [wiggle]