HELP! Inexperienced Database Building - Filtering and Printing Forms (2 Viewers)

[Typer96]

Hello!

I am currently building a database that will store legal citations in an easily searchable manner. This database also needs to have the capability to save certain citations to a table under a specific client and project name, which will be pulled up later for reviewing, editing, and printing either to PDF or exported to excel. Attached is a stripped down sample of what I have so far.

I have gotten through making a search form (frmCitationSearch) as well as the ability to save the specific citations to a new table (tblMatrix); however, I am having some problems getting the rest of it done even though I have tried everything I could find. :banghead:

• My database objects are:

• 
  4 tables

• 
  tblCitations - this table that holds all of the legal citations that I want to search.

• 
  tblClients - this table contains a list of client information (names, etc.)

• 
  tblDefinitions (not applicable for this question)

• 
  tblMatrix - this table holds the data that I want to be able format into a matrix which we can send to clients.

• 
  1 Query

• 
  qryMatrix

• 
  4 forms

• 
  frmCitationSearch - this forms allows the user to search through tblCitations for the item they are looking for, print specific citations, and add citations to to the table tblMatrix.

• 
  frmNewRecord - This form opens from a button on frmCitationSearch and allows the user to easily add citations to tblCitations

• 
  frmMatrix_Record - This form also opens from a button on frmCitationSearch, but it allows the user to add a citation to a specific client and project in tblMatrix.

• 
  frmMatrixFormatter - This troublesome form is supposed to allow the user to pull up the data from tblMatrix and filter it by client (cboClientName) and project (cboproject) in order to print to PDF or export the filtered data to excel.

• Other Details:

• 
  frmMatrixFormatter is a continuous form with the record source set to qryMatrix.

• 
  cboClientName's data source is from a different table with a list of the client's information (tblClients)

• 
  cboProject's data source is from the matrix table (tblMatrix) and is determined by the cboClientName value.

The first and most important thing I need help with is that I cannot get the form (frmMatrixFormatter) to display the information from tblmatrix filtered by two combo boxes: the client (cboClientName) and the project (cboProject). I want to be able to filter the results by the client first, giving the option to see all of the project data for that client, and then by the project with the project combo box only showing the projects relating to that specific client.

Second is that I cannot get my combo boxes to group by name. Somehow I got it to work on the form frmCitationSearch, but it will not work on frmMatrix formatter.

Third, I need to be able to edit the information displayed on the form and save it back into the table (tblMatrix).

Finally, once the form is filtered and edits saved, I need to have the option to do one of two things: print the filtered form or export the information to a formatted excel file. I have not tried to do a print report yet, but I think I have figured out the export to excel portion (please see button vba code in attached database);however, I have not been able to test it out, because of the form filtering issue. When it exports to excel or prints, I would like for it to have three header lines. The top would say the client's name, the middle would say the project name, and the bottom would have "Prepared by:" Name of preparer "," our company name

If you need any other information or have any suggestions that would make this more user friendly, please feel to let me know--any help is appreciated!

Sincerely,

Typer96

 

[Mark_]

For your filtering needs, I would suggest searching on this forum for "Cascading Combo Boxes". You should find several threads covering this.

Are you at all familiar with using VBA in events in ACCESS?

 

[Typer96]

Somewhat. I have been learning as I go for the past few months.

Regarding the cascading combo boxes, I have tried several methods found both on this forum as well as on other forums and youtube and still cannot get it to work. There must be something I am missing . . . I will keep digging some more.

Thank you!

Sincerely,

Typer96

 

[Mark_]

Can you post what you have tried that failed? Please use the "#" button above to enclose code for easier reading.

 

[Typer96]

Sure thing!

The first thing I tried was on youtube titled

MS Access Cascading Combo Boxes Part 1 by Database Missions

(Cannot post links yet)

This is what I used to establish the combo boxes. It had me go into the query (qryMatrix) and in the project field insert
#[Forms]![frmMatrixFormatter]![cboProject].[value]#

After putting that in the query, I would go to frmMatrixFormatter and
#Me.requery#
the form after updating cbo.project.



Also, I have tried creating a subform filtered by the comboboxes as done on youtube:

Creating Cascading Combo Boxes and List Boxes by Microsoft Access Forms

Finally, I have tried to create a 'Where' statement in the SQL similar to this article:

Control MS Access Subform With MS Access Combobox by Microsoft Access Tutorial by Molly Pell, Senior Systems Analyst

.

It looked something like this:
#SELECT tblMatrix.MatrixProject, tblMatrix.MatrixClientName, tblMatrix.MatrixCategory, tblMatrix.MatrixState, tblMatrix.MatrixTaxability, tblMatrix.MatrixDescription, tblMatrix.MatrixCitation, tblMatrix.MatrixComments
FROM tblMatrix
WHERE (tblMatrix.MatrixProject = (forms!frmMatrixFormatter!cboproject) OR (forms!frmMatrixFormatter!cboproject) IS NULL)
AND (tblMatrix.MatrixClientName = (forms!frmMatrixFormatter!cboClientName) OR (forms!frmMatrixFormatter!cboClientName) IS NULL);#.



Every time I try choosing an item from cboProject all it does is return a blank page . . . . There are a couple other methods I tried, but did not save the link to where I found the methods.



Thank you!

Sincerely,

Typer96

 

[JHB]

You can only use a value shown in a control for a reference to a criteria, so if you've a combobox and need to use a value in the 1. column you put a hidden control on your form a set the control source to the 1. column.
You need criteria in the query to which the form is bound.
The above is for your problem 1. and 2.
For problem 3. look at the code I've added to your "Edit Entries" button.

Remember to have the same field type for same values.
Ex. In table "tblMatrix" you've a field with the name "MatrixClientName" it is set to text, but you store the ID (which is number) from the "tblClient". (Do correction because I haven't done it).
I haven't check them all, it could be more such cases.
I think you need to read about normalization, because you've fields like "Citation", "Citation1", "Citation2" etc., such fields need a table for them self.
Database attached.

 

[isladogs]

I haven't looked at your database.
There's a nice example of cascading combo boxes here
http://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html

 

[Typer96]

Hi Mark_!

My post must not have gone through yesterday regarding your question (was having some internet connectivity troubles), but here goes again.

Here are the sources I used:

Articles​

• "Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms" by Molly Pell, Senior Systems Analyst
• "Using A ComboBox To Specify Query Criteria" by ChimpyTech

Videos​

• "MS Access Cascading Combo Boxes Part 1" by Database Missions
• "Control MS Access Subform With MS Access Combobox" by Microsoft Access Tutorial

Sorry no hyperlinks, have not made enough posts yet.

~Typer96

 

[Typer96]

JHB,

That is exactly what I need this database to do. I cannot tell you how much I appreciate your help on this. I have been trying to figure this out for months! I will definitely look into Normalization as you suggested.

Thank you!

Sincerely,

Typer96

 

[Typer96]

All,

Thank you all for your quick responses! As I said to JHB, I have been trying to figure this out for months and you all were able to get this solved within a day!

Have a fantastic day!

Typer96

 

[Typer96]

I haven't looked at your database.
There's a nice example of cascading combo boxes here

Thank you so much!

 

[Mark_]

Hi Mark_!

My post must not have gone through yesterday regarding your question (was having some internet connectivity troubles), but here goes again.

Here are the sources I used:

Articles​

• "Creating Cascading Combo Boxes and List Boxes on Microsoft Access Forms" by Molly Pell, Senior Systems Analyst
• "Using A ComboBox To Specify Query Criteria" by ChimpyTech

Videos​

• "MS Access Cascading Combo Boxes Part 1" by Database Missions
• "Control MS Access Subform With MS Access Combobox" by Microsoft Access Tutorial

Sorry no hyperlinks, have not made enough posts yet.

~Typer96

While you quote your sources, this does not list what you have done in your application nor what errors (if any) you receive. We need to see what YOU have done, not what other's have done.

Most often when you post a question like this, you would include

Here is my code
Here is more of my code
[COLOR="Red"]here is my code <- gives this error[/COLOR]
here is yet more of my code

Followed by
"I'm getting error "What ever error" highlighted in red above. Can anyone help me work through hot to fix this?

This would show up what in your application you are having issues with and allow us to quickly help identify and correct issues.

If you still need help please let us know.

 

[Typer96]

OK, Thank you! I will do that from now on.

Yes! I will most definitely let you know if there is anything else I need help on. Thanks again!

~Typer96

 

[JHB]

JHB,

That is exactly what I need this database to do. I cannot tell you how much I appreciate your help on this. I have been trying to figure this out for months! I will definitely look into Normalization as you suggested.

Thank you!

Sincerely,

Typer96

You're welcome, good luck.
Normalization is not that difficult, a good identifier is when you start creating fields with almost the same names as "Citation", "Citation1".
Another good identifier is when you have empty fields in your table.

 

[pbaldy]

Looks like it's too late, but post 5 was moderated, now approved.

 

[Typer96]

Thank you pbaldy!

 

[pbaldy]

No problemo!