Link to home
Start Free TrialLog in
Avatar of D J
D JFlag for United States of America

asked on

MS Access How can I have 2 check boxes interact with each other?

I have a table of thousands of documents, and when they get revised we need to track the relation between other applicable documents that reside in the same table.

If I input the first line below how can I automatically create the second line?
I.E. If I input document A revises document B, how can I automate document B revised by document A

Please see test database enclosed.

Documents      ID      Revised      Revisedby      DocumentsMod
123123               2            -1             0                       121454
121454               1             0             -1                      123123  - how can I auto-generate this line after inputting the first line?
Updatedbytest---Copy.accdb
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

how are you saving the first one?  Might be useful to see what your screen looks like.

I would assume that you would already have a line that looks like:

Documents      ID     Revised      RevisedBy        DocumentsMod
1121454            1            0                  0                      

in your database, and that when you create the "New" record, you would already have identified this as the sources

Generally, after saving the first record, I would do something like:

UPDATE yourTable SET RevisedBy = -1, DocumentsMod = [forms]![yourFormName].txtNewDoc
WHERE Documents = 121454

You might also be able to setup a data macro to update this record.  Or create it if it doesn't already exist.  But if it didn't already exist, you would not have a modification, would you?
Avatar of D J

ASKER

Thanks for your reply Dale.

how are you saving the first one?  Might be useful to see what your screen looks like.
Did you download the test database?

UPDATE yourTable SET RevisedBy = -1, DocumentsMod = [forms]![yourFormName].txtNewDoc
WHERE Documents = 121454
Doesn't the document number have to be a variable? (121454)

You might also be able to setup a data macro to update this record.  Or create it if it doesn't already exist.  But if it didn't already exist, you would not have a modification, would you?
That is correct, both documents need to be in the table for the mod to occur.
I'm not sure that logging the reverse event will be an update.I think it should be an insert and even if there is an existing record, do you want to replace what it currently says, which might have nothing to do with the current change?
Avatar of D J

ASKER

Thanks Pat,

There should always be an existing record due to you have to select 2 documents from the the document table to perform the mod.
do you want to replace what it currently says, which might have nothing to do with the current change?
Not sure what you mean with "which might have nothing to do with the current change?"
Not all documents will have a mod only some of them.
If one doc revised another, then one has to be labeled as "revised" and the other "revised by" like a toggle switch.
The goal is to have the latter part performed automatically ( and the other "revised by").
You better rephrase this confusing question. First:

> 121454               1             0             -1                      123123  - how can I auto-generate this line after inputting the first line?

Then, contradicting:

> That is correct, both documents need to be in the table for the mod to occur.

/gustav
There should always be an existing record due to you have to select 2 documents from the the document table to perform the mod.

Open in new window

I assumed this was a history tracking requirement.  If what you are doing is always modifying the current (only) record, then there is no real history.  You only know the last change.  But look what happens.

A changed B --- so B was changed by A

C changed B -- so B was changed by C

You are left
A changed B
C changed B
B was changed by C

A still shows as changing B but B now says it was changed by C

If you don't keep a transaction log, some of the records will seem strange.
Avatar of D J

ASKER

Pat - the scenario would be a little different.
A changed B
C changed B -- C wouldn't change B due to B was changed by A, so A is the latest or more relevant and wouldn't be updated
B was changed by C

Updated scenario:
A changed B
B changed by A
C changed A
A changed by C

This will produce the following document report:
A: changed B, changed by C (telling the customer that C is the latest doc)
B: changed by A (telling the customer to view A)
C: changed A (telling the customer C is the latest due to no changed by listed in the mod)

See mod report attached.
modreport.pdf
As long as you understand it and as long as you have controls in place to ensure that the change logic makes sense.
Avatar of D J

ASKER

Can anybody assist making this logic work?
When you enter a mod for a doc as "revised", the modded doc will automatically be checked "revised by".

See attached database.
I haven't downloaded your database so I'm going to guess at the problem.  If you are expecting the modifications to the "other" record to show up immediately on the form, they won't.  There is a refresh interval and off hand I don't remember the default time so changes to records in the form's recordset will only show up after the recordset is refreshed.  You can force changes to appear by using Me.Refresh after running your update query.
Avatar of D J

ASKER

Pat,

I'm not worried about the refresh issue, I need assistance with the update query.
You will get help faster if we don't have to download the database.  Try posting the SQL that is failing and tell us the error message.
Avatar of D J

ASKER

My syntax is incorrect:

if [Revised] = yes then [DocumentsMod] = [Revisedby] = yes

if [Revisedby] = yes then [DocumentsMod] = [Revised] = yes
That isn't SQL so I'm still not sure what your update query looks like

And you are correct, the syntax is wrong.   You can't have two ='s in the same expression.   What is DocumentsMod?

If [Revised] = "yes" Then
    [RevisedBy] = "Yes"
End If

But based on your description, none of this makes sense.  You need to run an update query.  Use the AfterUpdate event of the form so you know that the "changing" record has been saved.  Then your update query will update the changed record.
Avatar of D J

ASKER

What is DocumentsMod?
This is the document that is being updated to "revisedby"

When a user inputs a mod:
Selects 2 documents (1 document) & (1 document mod) then checks the box for "revised"
Now I have a mod record with the following info:
document revised document modded
How do I create the update query to create another record?
document mod revisedby document
How do I create the update query to create another record?
Updates do not create records.  They modify existing records.  Inserts add new records.  We had this discussion earlier and you said that the record that should be updated will always already exist.  If the record never exists you would always use an append query.  If the record might or might not exist, your code would have to determine that and run whichever query is necessary.

I really cannot download the database now and I won't be home this evening.  Try building the update query using the QBEUser generated imageIn SQL view, this query is:

UPDATE TEMP_AssignSequence
SET TEMP_AssignSequence.CaptionText = "2",
       TEMP_AssignSequence.MonthCode = "3",
       TEMP_AssignSequence.ProductionScheduleID = 4
WHERE (((TEMP_AssignSequence.SeqNum)=1));

I simply used numbers to indicate where your arguments will go.
The where clause needs to specify the PK  (or unique identifier) of the record you want to update.  The Set part updates each specified field with a specified value.  I chose three fields to update.

Once you build the query shell using the QBE, switch to SQL view and copy the code and paste it into a variable in your VBA procedure.  You will need to substitute something for each of the four numbers (or three if you are only updating two fields)

If you use form field references, you might have less trouble building the query.  If you understand how to concatenate literals with variables, you can use variables.

A form field reference looks like:

[Forms]![yourformname]![yourfieldname]  so your query might look like:

strSQL = "UPDATE TEMP_AssignSequence  SET TEMP_AssignSequence.CaptionText = [Forms]![yourformname]![yourfieldname2]         TEMP_AssignSequence.MonthCode = [Forms]![yourformname]![yourfieldname3] ,  TEMP_AssignSequence.ProductionScheduleID = [Forms]![yourformname]![yourfieldname4]  WHERE (((TEMP_AssignSequence.SeqNum)=[Forms]![yourformname]![yourfieldname1] ));"


Then to run it:

DoCmd.OpenQuery strSQL
Give it a shot
Avatar of D J

ASKER

Edit: Is there a better method to get the job done: In lieu of creating or updating a record, is it possible to just label the related (Revised by) record after 1 input into the mod table?
We have 2 tables: Documents & Mods
If I input a mod: A revised B (relationship with the Documents table)
How can we use just this record to label document B as well as A
A "Revised" B
B "Revised By" A





We had this discussion earlier and you said that the record that should be updated will always already exist.
The document record exists on another table and the mod may exist on the mod table.
The mod table fields document & documentsmod are connected to a documents table.

I'm trying to create a select statement first in order to find the documents I want to update, before I move to update or appending the mod table.
I'm having trouble finding the record I want to update:
Where documents = documentsmod
I have 2 records in the mod table:
1 - Document A Documentmod B revised
2 - Document B Documentmod A
I'm trying to find the 2 record with a successful select statement then try the update statement and update it to revised by = yes
Then move to the VBA part.
User generated image
Let's stand back and think about this.  I've been going along with your logic but what you are doing actually violates the principles of normalization.  Primarily that data in relational databases should exist in one and only one place.

When you have a table with two columns  --

DocumentID
ModifiedByDocumentID

You can read the data forward or backward depending on what you need to know.

Has Document A ever been modified?  Yes, if a record exists at all.
Which document modified document A? Select all rows which have DocumentA as DocumentID
Which document was modified by Document A?  Select all rows which have Document A as ModifiedByDocumentID

The table can include a time stamp as well as a changedBy field so you know when and by whom a document was changed.  You can even keep a comment explaining the modification.

Using this model, each modification adds a new row to the mods table - but only one row.  You just have to decide which perspective makes sense and all the rows in the table conform.  So the records are either A modifies B or A is modified by B.  Both make sense but you only need one record to see both sides of the relationship.
Avatar of D J

ASKER

Yes Pat, this method sounds more logical, but I'm having trouble creating the reverse logic.
I have a query which displays all the docs with the check box and displaying "Revised" with an IIF statement. See below.
How can I display "Revised By" A for record B?
Logic: If doc B is in the Mod table.documnetsMod display "Revised By" A
I tried inputting this statement in the DocumentsMod query column and receive and error:
IIf([Documents].[Docnum]=Yes,"Revised By"[documents],"")
User generated imageUser generated image
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of D J

ASKER

Thanks Pat,
Using this scenario:
A revised B (B revised by A)
C revised B (B revised by C)
D revised C (C revised by D)
Will produce the following query output below.
One issue you mentioned about 1 to many, you will notice 2 b records being displayed - Should I create a mod sub-form in order to display 1 document at a time, with all the mods below in the sub-form?
User generated imageUser generated image
Looks OK to me.  Is that what you were expecting?  You might want to do two separate queries and you might want to include dates.
Avatar of D J

ASKER

Thanks Pat!