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

Trying to open Form with a Filter in VBA

100+
P: 161
I'm trying to open a form that is filter by using VBA.

I have tried using the following:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdRabbitsDue_Click()
  5.  
  6. DoCmd.OpenForm _
  7.     FormName:="frmRedRabbitDueInput"
  8.     WhereCondition:="[DateToRecertify] <= #" & Format(Me![DateToRecertify].Value, "mm/dd/yyyy") & "#"
  9.  
  10. End Sub
But I get a syntax error. I think I have the structure way off but not sure.

Thanks for the help in pointing me into the right direction
2 Weeks Ago #1
Share this Question
Share on Google+
3 Replies


100+
P: 161
I give a little more background info...I'm trying to open a form in datasheet format but with records containing dates that are less than the following months date...Example: all records containing a date that is less than 9/1/2019. I can get it to work if I put it in the query as < #9/1/2019# but then I need to go into the query every month to change it. So I'm trying to do it with VBA similar to the code above, but I think I'm off by a little bit. Tried to Google it and was getting everything but what I needed.

Form Name: = "frmRedRabbitDueInput"
Date Control Name is:= "DateToRecertify"
2 Weeks Ago #2

100+
P: 161
Found a work around. I put <CDate([Enter Date]) in the query for this date and on button click i have:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdRabbitsDue_Click()
  5.  
  6. On Error GoTo Error
  7.  
  8. DoCmd.OpenForm _
  9.     FormName:="frmRedRabbitDueInput"
  10.  
  11. Error_Exit:
  12.     Exit Sub
  13.  
  14. Error:
  15.  
  16. Select Case Err.Number
  17.     Case 2501:
  18.         GoTo Cancel_Error
  19.     Case 3071:
  20.         GoTo NothingEntered_Error
  21. End Select
  22.  
  23. Cancel_Error:
  24.     Resume Next
  25.     Exit Sub
  26. NothingEntered_Error:
  27.     MsgBox "Please Enter a Date!", , "Forgot Date"
  28.     DoCmd.OpenForm _
  29.     FormName:="frmRedRabbitDueInput"
  30.     Exit Sub
  31. End Sub
  32.  
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,433
It looks like a simple case of a missing comma (,) DJ.

Nicely formatted but you still need a comma between the parameters ;-)
23 Hours Ago #4

Post your reply

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