Posted by: Jdb_1229 Jan 11 2017, 09:43 AM
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
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!
Posted by: ranman256 Jan 11 2017, 10:05 AM
I don't think you want Dlookups, instead use a query to do the lookups,
And pull all the reminders.
Posted by: Jdb_1229 Jan 11 2017, 10:30 AM
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!