Hi Froth.
I only can say : your database is awesome !!! And you are awesome !
There is so many things which i can use in my model - I am excited!
Could we please explain your sample model step by step in details (questions below)?
I understand a lot but not all unfortunately.
Code to search within Client Form:
Private Sub cboFinder_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboFinder], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
I love it! This is very quickly method. I have learned a little bit about this and it is the most efficient way to lookup within table.
While I do often set things like that up manually, that particular trick was done by the combo box wizard. One of the options you're given when you place a combo box in a header or footer is to have the combo box navigate the form to a selected record. If you choose that option, the wizard writes the code you quoted.
This query is master level:
INSERT INTO tblClientDocuments ( ClientID, DocumentID, ReceiptDate )
SELECT [Forms]![frmClients]![txtClientID] AS ClientID, tblDocuments.DocumentID, Date() AS ReceiptDate
FROM tblDocumentStaging INNER JOIN tblDocuments ON tblDocumentStaging.DocumentID = tblDocuments.DocumentID
WHERE (((tblDocumentStaging.Assign)=True));
While I thank you for the compliment, it really isn't warranted. That query is just a basic one thrown together in the query editor. That thing is an amazing tool for both newer developers and just throwing together straightforward but long queries. I tend to use it all the time, just because it's usually faster than manually typing out the query.
This append query thanks to inner join is inserting all choosen documents.
I do not know exactly how it is working.
I understand inner join from QlikView:
https://community.qlik.com/thread/39177
INNER JOIN simply means that, in order for a record to be shown, both
fields must contain matching data. That is, for example, how you show both an invoice and the line items ON the invoice. In your case, it allows you to show Clients and assigned documents in the junction table.
So here inner join is cloning all Clients ID for each document.
So if you have client ID 1, and 3 documents for it, this query will add 3 rows:
ClientID, DocumentsID, RecipeDate
1 3 Now()
1 1 Now()
1 10 Now()
What is causing that ClientID and RecipeDate is cloned here? How this is working? Sorry for noob question - i have to understand it fully.
Basically, the query takes all the document IDs from the staging table where Assign was set to TRUE, then adds an entry to the junction table (tblClientDocuments) for each of those document IDs, along with the date they were added. The rest of the fields in the junction table are left blank for future editing.
Edit: The ClientID is cloned because it is on the 'ONE' side of a 'One-to-Many' relationship. For each ClientID, there can be multiple instances of DocumentID. As to the date, note that in the query, instead of referring to a saved value, I'm setting a specific value via the Date() function. Whenever you do something like that, the indicated value is set in all affected records.
Second Question:
I would like to add into tblCLientDocuments possibility to see also document name.
How it is the best way to do it?
Add to tblCLientDocuments DocumentName and Document ID and show only DocumentName ? Add it also in qryAssignDocuments?
You don't - doing so is a violation of normalization as well as a waste of space and energy. Any time you need to display the document name, you can use the relationships between the tables (
GO CHECK OUT THE RELATIONSHIP BUTTON ON THE DATABASE TOOLS TAB OR TOOLBAR MENU!!!) to display the document name.
In fact, drop everything else right now and go read up on database normalization. Some options might be
THIS or
THIS.
Uncle Gizmo might have a tutorial on it over on YouTube, too. And you can always ask questions here, but in a nutshell, it means that each table should be about one thing and only one thing, and data shouldn't be saved in multiple locations.
For example, in my setup, if you decide to change the stored name of a given form, you only need to update it in the documents table. If you put the name in the junction table as well, then you'd need to change it in every single record that contains it. At work, I deal with tables with tens of millions of records accessed by hundreds of users at the same time; imagine the impact if I had to constantly update millions of records dozens of times a day.
Anyway, the database I uploaded wasn't finished. The next step would have been to add code to the 'Assign Documents' button on the documents form that runs the append query you looked at, then closes the form and requeries the documents subform on the Client screen. Once the documents have been added to tblClientDocuments, the subform will display the new documents.
In fact, I'm attaching a completed, working copy to this post. Make sure to check out the Relationships tab - that helps you keep your data clean. Also, note the hoops I had to jump through to make VBA run the append query - unfortunately, when you're directly referencing a form in a saved query being run by VBA, you have to do what I did. (The other option is to create the query in VBA and parse in the values at runtime. I tend to avoid it due to database bloat, but it's a very minor amount of bloating and is really just personal preference.)
