Solved Correct way to update data

[lacampeona]

Hello experts,

I need your opinion how would you make this in the database. I have problems with the statuses. I am not sure if I plan that correct or not.

You have 2 tables. Table A and Table B.

Table A = contains specific information about your item ( name, date arrrived, serial number, status of the item, etc...)

Item Name Status
1 A New

Table B = contains specific information about the usage for the specific item ( date of usage, where was used, status of the item)

Item Name Purpose if usage Status
1 A Testing method InUse

Example:
Users starts entering new data into database. This mean he entered first record and his item 1 has status New. Every time he will enter new item he will put status New.
So in the table A Item 1 has status New. That is correct.

Then user make the usage for the first time for the Item 1. He makes some testing and he choose from the combobox status InUuse.
So if we now want to check the table B and see the history of the item we will see that he make first use and he put the status InUse. That is all correct. So that mean that item 1 is no longer New becouse was used, item 1 has now status Inuse.

Now I need that the status for item 1 who has status InUse and which is stored in table B will be the same for the item 1 in the table A.

My Solution:
when user will choose from the combox the status inUse , I put code

DoCmd.RunSQL "UPDATE tblA SET tblA.Status= '(2) InUse ' WHERE tblA.ID = " & Me.ID
DoCmd.SetWarnings (True)

with that the status will also be updated in table A. That is correct? Does exist another way to do that better? hmm

My problem:
Lets say user change the status from New to InUse...then he say ohhh I will close the form...I dont want to save the record..

I have code that ask user if he want to save the record or not, problem is when users choose the value from the combobox then the update code already change the status and record is not saved, So the record is not saved ( that is correct) but the status has changed. ( Not correct, if he close the form the status must be the same from the beggining in this case New).

If the status was new and user put InUse and then he close the form - the status is now InUse, = Is not correct. The status must be New again. This is correct.

How can I cancel the update command if users will close the form? how to prevent that. In which event I have to put the code? Before Update? On click?

How would yo do that? Can someone show me some example?

Thanks in advance

 

[arnelgp]

Status field should Not be in Separate table.
you incorporate it in TableA so there is no need to update query.
unless you are Adding records (when status changes) to the status table.

 

[lacampeona]

Hello

Status is in the table A and in the table B. Becouse the item always has to have the status with him. Is like library system but I dont know how to do it. How to prevent updating data when user say no to save or close the form. I just doont know how to say to vba cancel update? Where to put the code? before on unload event?

And yes when you use the item from table A you are entering data of his usage in the table B.

 

[arnelgp]

are the two tables need to have Same status?
if yes, then on Unload event of your form, synchronize the "2nd" table with the first?

currentdb.execute "UPDATE tblA SET tblA.Status= '" & Me!Status & "' WHERE tblA.ID = " & Me.ID

 

[lacampeona]

yes is important to have the same status, becouse in that way user will know if item is in use or not.

hmm which form you mean?
form A you enter items, form B you enter usage data for the items from table A
the form B which user use to enter usage data? form B table B is automaticly synchronize with the data, I have to synchronize with form B the first table A?
hmmm

 

[arnelgp]

You create a Query (Query1) for TableB:

SELECT TableB.ID, Last(TableB.Status) AS LastOfStatus
FROM TableB
GROUP BY TableB.ID;


Then on the Unload event of your form, perform the Update:

currentdb.Execute "Update TableA Set Status = DLookup('LastOfStatus','Query1','ID = ' & [ID])"

 

[lacampeona]

Hi again
I make some example what is my problem. If you can check.

 

[arnelgp]

see the Undo Event of the Form.

 

[The_Doc_Man]

First, let me suggest that you are getting excellent help from arnelgp.

However, you seem uncertain, so perhaps I can explain it differently so you can better understand what is being suggested. I will throw in another viewpoint but want to emphasize that arnelgp isn't wrong either.

You have a common enough problem - items in table A, transactions in table B. You are updating two tables to reflect status changes because you store something in B to reflect the specifics of the status change but then you update A to show the updated status there. To my way of thinking, the status doesn't belong in the item table. It belongs in the transaction table.

To simplify this from a logical standpoint, you build a query such as arnelgp showed you as Query1 in post #6. Then you build another query that uses table A in a JOIN with Query1 based on the ID fields. In that query, you would have the ID, the item name (from table A) and the most recent transaction on that item (from table B) all in the same place. That last transaction holds the current status that tells you whether it is NEW or IN USE or RETURNED FROM SERVICE or whatever status. If you include a date in table B then rather than depending on LAST (which is sometimes not completely reliable) you can use MAX(date), which is far more reliable.

The only difference between this approach and your earlier description is that you would have to create the NEW transaction at the same time you created the descriptive entry in table A. Thereafter, when you want to see the status of the item, you open the JOIN query and you can see everything at once. But table B is still there when you need it to report on item A's history of usage. If you include the dates, you can see the changes in chronological order.

Understand, there are many ways to approach this problem - but the least amount of record updating is what I described, because you never change the record in A again. You let the query pull things together when you need them. You only INSERT a new transaction into B; you never actually UPDATE any record.

 

[lacampeona]

Hi
yes i see it. thank you.
ok this is almost perfect, but let say last status of the item was sended? you want to put in use and then you cancel? the status must be sended becouse that was the last status? how to do that ?

CurrentDb.Execute "Update tblA Set Status = '" & Nz(DLookup("LastOfStatus", "Query1", "ID = " & Nz(Me.cboItem, 0)), "(1) New") & "'"

 

[lacampeona]

Hi The Doc Man,
i see your idea . Yes I know is better to have status in transacion table but is also important for table A becouse there is where all started.
Can you show me some example how to do that? I never did join query?
if you show me some example then i will try do it on my problem?
Thank you

 

[GPGeorge]

Hi The Doc Man,
i see your idea . Yes I know is better to have status in transacion table but is also important for table A becouse there is where all started.
Can you show me some example how to do that? I never did join query?
if you show me some example then i will try do it on my problem?
Thank you

PMFJI, as well, but this point doesn't seem to be clear yet. Maybe another explanation will help round out the picture.

Based on what we see here, there is NO need to duplicate the Status in two places. Status is an attribute of the transaction. Each transaction changes the status.

Transaction 1 is the "New" transaction. I.e. it has the status "New".
Transaction 2 is the "InUse" transaction. i.e. it has the status "Update".

And so on, each transaction creates a new status. Statuses do not change independently of a transaction.

That status is an attribute of the transaction. If you want to SEE the history of transaction status(es) for items in Table A, you do not do that by adding a new field in Table A. If you want to SEE the transaction status(es), you do that by creating a query.

True, status is important for Table A, but ONLY in the sense that you need a way to show the history of statuses for records in Table A. Creating a join on the two tables provides that.

Think of it this way, when a table design forces the user to update a field in one table in order to keep the value in that field in synch with a value in a field in a second table, you have a design flaw in the tables. "One time in one place."

All of that said, it is possible to incur the risk and extra work of synching fields in two tables, and if you are willing to run that risk and put in the extra work to manage it, that's a choice anyone can make. Make it deliberately, though, and accept that it is extra work that is also higher risk.

Arnel is showing you, I believe, how to go about that. Keep in mind the potential cost of that approach if you insist on implementing it.

 

[The_Doc_Man]

Can you show me some example how to do that? I never did join query?

I will write a hypothetical example.

Table A: ID, ItemName, other descriptive data always specific to the item
Table B: AID, XDate (of transaction), XStatus, other descriptive data always specific only to that one transaction. (I.e. could be "Who used it.")

Query 1:
SELECT BX.AID, BX.XDate, BX.XStatus FROM B AS BX WHERE BX.XDate = ( SELECT BB.MAX( XDate ) FROM B AS BB WHERE BB.AID = BX.AID ) ;

The sub-query selects the maximum date (most recent date) from B and then uses it to select the matching record, ALSO from B. This is an example where you use the same recordset twice (under two different names) because that way you tell Access what specifically to match. If you think of this as having two "pointers" to the same recordset at the same time, you would be right - and the pointers would be BX and BB (two arbitrary "alias" names to represent the two pointers). The WHERE clause matches up records across the two recordset pointers, i.e. matches records by dates.

Query 1 can be modified to include extra fields from B and you can impose ordering e.g. ORDER BY BX.AID if either is desirable. This query could be used to list the current statuses of EVERY item (by ID only, since the name is in A) - but then see Query 2 below.

Query 2:
SELECT A.AID, A.ItemName, Q1.XStatus, Q1.XDate FROM A JOIN Query1 AS Q1 ON A.AID = Q1.AID ;

This query gives you the ID, ItemName, most recent status for that item, and the date that status was declared via an INSERT, all in one query. You can add ORDER BY A.AID to make a list of every item BY NAME (in ID order) to show status and date of status. In this query, the two recordset sources are A and QUERY1 but I gave a shorter alias name to simplify the typing required.

The history of all items is in table B where a suitable query could pick it out in any of several ways.

 

[lacampeona]

Hi
Yes I all understand that status must be always in one field and in this case in table B.
But becouse all starts from table A when you first enter the item and here you first time set the status i have to have that table A also updated when the status changed in table B.
ohh I make it all so complicated...but this is only way to show to the user. If he will look one form ( datasheet view) he will see one status and if he will look another form (datasheet view) he will see another status. becouse of that i need to have the status from table B also in the table A updated
becouse he look the items he will choose in form from table A and then he go for use it in form B table B.

ohh ok I will try to implement that and i will see if it is ok.
thanks for all the explication.
If i will be in problems i came again.
thank you

 

[GPGeorge]

Hi
Yes I all understand that status must be always in one field and in this case in table B.
But becouse all starts from table A when you first enter the item and here you first time set the status i have to have that table A also updated when the status changed in table B.
ohh I make it all so complicated...but this is only way to show to the user. If he will look one form ( datasheet view) he will see one status and if he will look another form (datasheet view) he will see another status. becouse of that i need to have the status from table B also in the table A updated
becouse he look the items he will choose in form from table A and then he go for use it in form B table B.

ohh ok I will try to implement that and i will see if it is ok.
thanks for all the explication.
If i will be in problems i came again.
thank you

Typically, a good way to manage display of data in this sort of relationship is a main form/sub form design.

The main form is bound to Table A. The sub form is bound to Table B. The sub form is in continuous view so that you see ALL of the transaction records available, including the statuses. And once again, Status "New" is created when a transaction record creates that status by instantiating it. Status "New" is not created by describing the project in Table A.

 

[lacampeona]

User will put the status New ( when he will enter the item into database for the first time ) in form A and the status will go in table A. Then he will not touch the form A and status in table A anymore.

He will then only use form B and item status that he will use will be stored in tableB and updated to table A.
that was my plan..and is almost working but problem is my code who updated the record.
becouse if user updated the record and close the form then I have wrong status.. i cant prevent the updating code?

 

[GPGeorge]

User will put the status New ( when he will enter the item into database for the first time ) in form A and the status will go in table A. Then he will not touch the form A and status in table A anymore.

He will then only use form B and item status that he will use will be stored in tableB and updated to table A.
that was my plan..and is almost working but problem is my code who updated the record.
becouse if user updated the record and close the form then I have wrong status.. i cant prevent the updating code?

That's your plan and no amount of explanation can change your mind about the plan, so my only option is to back out and wish you the best.

 

[The_Doc_Man]

There is nothing to stop you from taking the data from the form adding A's data and as an after thought, doing a separate

INSERT INTO B (AID, XDate, XStatus ) VALUES (Me.AID, Date(), "NEW" ) ;

done after you save the A record before you finish up with the FORM_AFTERUPDATE event. And by then, even an autonumbered ID is available.

 

[lacampeona]

Hi
yes I will try also that and another idea you suggest me and arnel.
thank you very much

 

[Pat Hartman]

Yes I know is better to have status in transacion table but is also important for table A becouse there is where all started.

Not if you understand how relational databases work. Doc's solution is the better choice. If you want to know the status of an item, you can find it in the transaction table.

But becouse all starts from table A when you first enter the item and here you first time set the status i have to have that table A also updated when the status changed in table B.

And you double down. Two experts have explained in great detail why your opinion is wrong and how to implement the functionality correctly. Good luck with the inferior and potentially dangerous solution.