X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Creating A Reminder For With Subform. Cannot Complete Filter, Access 2013    
post Jan 11 2017, 09:43 AM

Posts: 27
Joined: 13-July 16

I am really new to access and I have a database with a reminder form. In this reminder form I created a subform of my main information form. When I stored all of my data in the same table and form I was able to set some reminders as well as a button that would filter my subform to the correct companies.

I have since separated data because the form was getting much to big. I changed my dlookup functions so that they were searching for the correct information but I cannot figure out how to filter my subform now.

This is my example: I have a FrmDatabaseInformation with company information such as address, on this form I have buttons that open up another form to the corresponding information. Ex. Button CmdInvoices will open up up FrmInvoiceTracking to any invoices that have the same CompanyID as the selected record. This much was working.

The FrmReminders has a dlookup function like this:

Private Sub Form_Load()

Dim InitialSurvey As Integer

InitialSurvey = DCount("*", "TblInvoiceTracking", "[Survey] = Yes AND [Sent Date] Is Null")

Me.FrmDatabaseInformation.Visible = False

If InitialSurvey > 0 Then
Me.lblInitialSurvey.Caption = "There are " & InitialSurvey & " Initial Surveys that need to be sent!"
Me.lblInitialSurvey.ForeColor = vbRed
Me.lblInitialSurvey.FontWeight = 700

ElseIf InitialSurvey = 0 Then
Me.lblInitialSurvey.Caption = "No Outstanding Reminders"
Me.lblInitialSurvey.ForeColor = vbBlack
Me.lblInitialSurvey.FontWeight = 400

This code is working beautifully. The problem lies in when I select the button that should filter and make visible the subform. This is the code I had before i separated the information.

I have a 1 to many relationship between the CompanyID fields on each table/form.

Private Sub InitialSurveyCommand_Click()

    Me.FrmDatabaseInformation.Form.Filter = "[Survey] = Yes AND [Sent Date] Is Null"
    Me.FrmDatabaseInformation.Form.FilterOn = True
    Me.FrmDatabaseInformation.Visible = True

End Sub

Is there any way to make this run how I have it set up? I tried a docmd.Openform function with this filter and then tried filtering the subform to the corresponding company with "CompanyID =" & CompanyID but I knew that would be wrong and it was. Any help is greatly appreciated. Thank you!
Go to the top of the page
post Jan 11 2017, 10:05 AM

Posts: 875
Joined: 25-April 14

I don't think you want Dlookups, instead use a query to do the lookups,
And pull all the reminders.
Go to the top of the page
post Jan 11 2017, 10:30 AM

Posts: 27
Joined: 13-July 16

Any chance you could give me an example of how I would do that? I can pull queries but only the most basic of queries. Your help is very much appreciated!
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    21st September 2018 - 06:27 PM