Creating UpDate Rules for Multivalue fields

[xxx]

Ok... Hopefully I'll get a response for this issue. I'll try to keep it simple.

I have two multivalue fields. One field indicates who should be trained on a particular document/procedure. The second indicates who has not yet been trained on the document/procedure.

So I've successfully created several "variable" queries which will show individualized reports (based on employee selection via combobox/form) showing Individual Training Plan (which shows all documents regardless of training status), Training Overdue, and Training Required (but not overdue).

So with these queries/reports I can track IF an employee has been trained, but not when. I've tried creating before and after update rules which will "audit" the changes made to these two multivalue fields, compare the data before the change and after. Then depending on the comparison, assign a date when an employee has been unchecked in the "Untrained" field. I will also need the ability to store and display this date for each document/employee in the reports.

I keep getting errors saying that multivalue fields cannot be read. Anyone have any other suggestions for accomplishing the same end? PLEASE?!! Thank you in advance

 

[Galaxiom]

Serious developers don't use multivalue fields and instead use related tables.

Multivalue fields look attractive to the inexperienced but you will quickly learn they are far more trouble than they are worth. Easy to set up and enter data on a form but everything else about them is difficult.

Moreover they are an Access only feature and will have to be completely replaced if you later want to upsize your application to a server based backend.

 

[xxx]

Serious developers don't use multivalue fields and instead use related tables.

Multivalue fields look attractive to the inexperienced but you will quickly learn they are far more trouble than they are worth. Easy to set up and enter data on a form but everything else about them is difficult.

Moreover they are an Access only feature and will have to be completely replaced if you later want to upsize your application to a server based backend.

Yeah, so I've heard.

So, I am inexperienced. I know there would be an alternate way... I've tried creating a link to the original document table, but I don't think I can selectively link specific records in a table, nor can I add additional fields. I'm trying to avoid having to manually enter this information in for each employee because it will require constant, tedious maintenance.

The way one acquires experience is generally from thoughtful and experienced ideas. So... any actual solutions/ideas (regardless of difficulty level so long as they are elucidated for the "layman") would be appreciated.

 

[Galaxiom]

I assume the multivalue fields are in the document/procedure table.

Make a junction table. This contains fields for the DocumentID and the EmployeeID. It has one record for each combination of Document and Employee to indicate that the employee either needs or has been trained.

It is possible to use the same junction table for the both the NeedTraining and HadTraining records by including another field that indicates this. Typically a person would not have been trained yet yet have no need to be trained so there is no need for them to have a junction record for each. Indeed your original structure is potentially a breach of normalization on thiese grounds.

If there are only two states use a boolean field. A query that checked for who had been trained would be able to select the True record and those who still needed it as False. Those who don't need it won't even have a record in the junction table so a Null in the join between Employee and the junction table would show that.

If there are more states then use a number to indicate the progress level.

 

[xxx]

I didn't particularly follow your last comment 100%. However, your first comment motivated me to explore other options. I'm on my way. I created a series of queries that will append and update a separate table based on form selection combo boxes, one selection for the document and one that allows you to select which employee must be trained on it.

Anyhow, maybe you'd be willing to now help me with another issue.

I am now trying to create an event procedure/vba code that will execute when a command button in the form is pushed. I want to command button to execute my append query and then the update query immediately after. My append query is called "DocumenListQuery1Query" and the update query is called "Table1Query".

The firsty query adds the selected document to my separate training table. The second query adds the selected employee in the field next to the document that was appended when the first query ran.

Anyhow... I've tried several combinations and methods of structuring the vba command (based on examples found online), but have been unsuccessful. Can you provide any help there?

I will also have to have it somehow check for duplicate entries and delete duplicates. Couldn't figure out the union query thing... so yeah... Any assistance IMMENSELY appreciated.

 

[xxx]

This is what I tried last


Private Sub Command6_Click()
DoCmd.OpenQuery (DocumentListQuery1Query), acViewNormal, acReadOnly
DoCmd.OpenQuery (Table1Query), acViewNormal, acReadOnly

End Sub

Keep getting an error "The action of method requires a query name argument"

 

[xxx]

NM... yay! Fixed! I guess some examples I was looking at had parentheses, but it actually should have had quotations.

 

[Galaxiom]

OpenQuery is a Method of the DoCmd Object. That method has a number of arguments, the first one being a string which is the name of a saved query.

As a string it must be delimited by quotation marks. When you see something in that position without quotations it is the name of a variable that holds a string.

Dim queryname As String
queryname = "Myquery"
DoCmd.OpenQuery queryname

Sometimes developers put parentheses around an argument.

 

[xxx]

The explanation is much appreciated...

So... Another issue of sorts. A bit complicated perhaps. So my command button will append and update the selected information . Now I have been attempting to create a query that will prevent duplicate records from being added to the training table.

Here is what I am trying to do:

I've created a query [AlreadyInTrainingPlan] of the Traing Table [Table1] (the table that is appended and updated). It is supposed to use the employee/document selections made in [Form1] as its criteria. I intend to run the query prior to the update&append queries.Then, if the [AlreadyInTrainingPlan] query yields a record, a message will pop-up and the sub will end. If the results return null, the sub will continue on to execute the update&append queries.

So the issue I'm running into is this. When I execute my [AlreadyInTrainingPlan] query with [Forms]![Form1]![List0] as my employee criteria and [Forms]![Form1]![List4] as my document criteria, the query returns null (although the criteria selected DOES indeed exist in the table to test the query). What is odd is that if I omit the document criteria, it WILL return all documents assigned to the selected employee. If I omit the employee criteria and use only the document criteria, it again will yield null results. So... it is obviously an issue with the document criteria, but I have no clue what. It has worked in previous queries for other uses.

More details may be required, but I'm not sure if you're still w/ me! Trying to be thorough.

Optional Additional details:

Thinking the issue may reside w/ the way the data is stored/retrieved through the list box selections and how it is actually appended? Not positive though...

This may be unnecessary info, but I did notice that:
(1) The employee name and the document info is displayed in their respective [Form1] List selection boxes as text.
(2) BUT when the information is appended to [Table1], the employee name is identified by the ID associated with the employee's name in the original [Employee List] while the document is associated by the actual text description.

 

[xxx]

Here is a copy of my text database if that helps.

 

[Galaxiom]

I don't know it is your problem but one thing that catches many new developers is testing for a criteria like this:
WHERE somefield <> whatever.

This will NOT find the records where the somefield is Null. To do this you must use:
WHERE somefield <> whatever OR somefield Is Null.

Usually avoiding duplicates in an append query can be achieved using an Outer Join between the source and destination table/query on the fields that must not be duplicated. An Is Null criteria is added to the fields on the destination side so only records that don't match are inserted.