Filtering a sub form based on information located in a completely different table

[jdb1229]

I am completely new to Access. I received some great help with another question I had so I was hoping to get some assistance with another. I have never coded before I started this project so I apologize, my verbage and usage of the information you give me may be in correct and I hope you can be patient with me. Thank you for anyone's time who gives me advice!

I have a reminder form that I created to remind any user who accesses the database about certain criteria not being met. For example, my Initial Survey Reminder uses code that counts how many invoices have the Survey box checked but no Sent date entered. The code then uses that number to let the user know, on the form, that there are X amount of invoices that need a survey sent. I originally had a very large table with all of my information stored on it but because of how large it got I rearranged my data in multiple tables. The problem I have is that before I rearranged I was able to insert a subform of my database form so that when they clicked on a button the form would filter to the the companies that had this criteria. Now that I have rearranged the information I was wondering if there was still a way to do this. All of my Tables have relationships to the main table. I would like for buttons to open the main form (it doesn't have to be the subform if it is better to just open the form) to these companies. I have and the relationship between Form FrmDatabaseInformation (Main Form) and Form frmInvoiceTracking is a one to many between the CompanyId fields.

In my Form FrmReminders here is the code I had on 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
 
End If 

And this is the code I had previously on the button that should filter the subreport

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

Any help is greatly appreciated, have a wonderful day!

[zmbd]

It sounds as if you have been sabotaged by the non-normalization Gremlin. - Don't worry, I've been beaten up by him too in my early design days, have you taken a look at
home > topics > microsoft access / vba > insights > database normalization and table structures

Once you have your database properly normalized, then you should be able to create a query against the related tables that pulls your data out, with any luck, you might be able to still use your form with little if any changes.

We'll need a bit more information about your tables and how the data is related between them. For now I think that all we should need is something like:

[table_1]
[table_1].[PrimaryKey]

[table_2]
[table_2].[PrimaryKey]
[table_2].[ForeignKey_table_1]

I use PK as short for primary key and FK for foreign key when naming fields :)

I am going to locate your other thread and read through that to see what else I can learn about your database. This may take me a little bit to do and I have an obligation tonight :)

In the meantime, I'm also going to PM you a copy of a boilerplate with some links to tutorials and other hopefully useful information to keep in your toolbox as you progress.
> Your Bytes.com Inbox <

[jdb1229]

zmbd,

Thank you very much! I will be unable to look through all of the references you have given me tonight due to time constraints but I have already saved them and can see, by skimming, that many of the information should prove very useful!
Also, thank you for taking the time to respond, I would like to try and answer your question regarding the primary keys and foreign keys but forgive me if it is in accurate, I will edit once I have the time to look through the data you sent.

Each of my tables has a primary key set up with an Autonumber field. In the cases of tables that have a 1 to many relation ship I have added a foreign key (I think is the terminology) that is just a simple numbered field. To use your example,
TblDatabaseInformation
primary key [CompanyID]

TblInvoiceTracking
primary key [InvoiceID]
foreign key [CompanyID]

(though I am unsure if I need to mark the foreign key as anything, right now it is just a column) This field is not marked as required. In my form I used some code in order to pull the CompanyID from the main form to the corresponding new invoice as it is entered.

In my reminders form the dcount uses the TblInvoiceTracking but I haven't been able to figure out how to open the main database with a filter based on the criteria in a different table.

I hope this helps while you take a look at my other post. Again, thank you for your time. I will look at what you sent me as soon as I have some time. Have a great day!

[jdb1229]

So I did look through the information you sent me and while some of it went over my head I think I understand what you were talking about in regards to normalization. I did check that all of my data was stored correctly and the only thing I may have a slight bit of trouble is in regards to my FrmDatabaseInformation and my FrmAdditionalContacts both having addresses entered. I do need this information separate and the data has not been entered twice, the address in FrmDatabaseInformation and in FrmAdditionalContacts are different. Maybe I can rephrase my question and if there is a way around this that would be amazing.

I am able to filter the survey information I need by using the TblInvoices table and filtering to get the number of surveys that need sent. What I would like to be able to do is to open my FrmDatabaseInformation, or subform if I can, to open to the Companies that correspond with the invoices that meet that criteria. I have used the where portion of an DoCmd.OpenForm before and it worked but I am not sure how to get the filter to run. I thought the code could look something like this but I am not sure.

Me.SubformName.Filter = "[Survey] = True AND [Send Date] is Null
DoCmd.OpenForm "FrmDatabaseInformation", AcNormal, WHERE: Me.CompanyID & = & CompanyID
 

I put the Where because I couldn't remember how many commas before the where option.
Something along those lines but I don't have access to my database today (working from home) to make sure my syntax is correct. Thank you for your time!

[zmbd]

Sorry for the long pause here, things have been extremely busy and I just have been pulled in 100 directions :(

I am hoping, despite my delay, that you've resolved this issue; however, I really do not like leaving a thread unresolved... not my best work.

In your Original code you had the filter string formatted correctly and you set the filter property of the form to true, you need to do that same kind of thing here after the form opens.

I usually use the named parameters method of writing code, a bit slower to type; however, you don't have to worry about the commas ( :-) ), the next person reading the code clearly understands what you're doing, and I also tend to form my strings first so that one can troubleshoot them later.

There appears to be an issue with
Me.CompanyID & = & CompanyID
you have a reference to the form control which will return the value of the data in that control (me.companyid) compared to a variable defined somewhere within your code. Did you intend:
"CompanyID=" & Me.CompanyID

So that we could have something like:

   Dim strWhereSQL As String
   strWhereSQL  = "CompanyID=" & Me.CompanyID
'<>
'Troubleshooting, run code, press <ctrl><g> to review the string
Debug.print strWhereSQL
'
'<>
   DoCmd.OpenForm FormName:="FrmDatabaseInformation", _
      WhereCondition:=strWhereSQL, _
      WindowMode:=acDialog
'I've omitted the window mode as the default is acNormal
'
'Here, I'm not sure if you wanted the following code in the command button still or filter on open
'
   Me.SubformName.Filter = "[Survey] = True AND [Send Date] is Null"
'Added a closing quote to the string.
'
   Me.FilterOn = True

Of course, all of the comment lines would be removed in your final code along with line 5. :)

[jdb1229]

zmbd,

Thank you very much for replying. I did find something that worked for me and it is similar to what you suggested. I do not have access to my database to show you exactly what I did but I used a series of hidden queries that became visible when the correct button was selected, with each button it filtered the queries to the correct companies. Lastly, when you select the company from the list it opens to that company in the database. It was not exactly as I had hoped for but it works out for my company for now. Thank you again and I will use the information you have provided for any future work I have. Have a wonderful day!