Link to home
Start Free TrialLog in
Avatar of Ronald Malk
Ronald MalkFlag for Australia

asked on

VBA pass value between two fields different tables

Hi to all, please anyone can help, , I have been trying for hours on the internet without any hope and now is 2.30 in the morning, I gave up and I need someone to help me.
In table name   “Tbl1” I have two fields;    first Field name is [StockID] and   the second field name is [Status] And I have a nother table “Tbl2” with exatly the same two fields [StockID] and [Status]  , the [StockID] is the matching one, “CboStatus” Combobox is bound to [status] in Tbl2, What I need is after updating the CboStatus in Tbl2 Passing the same value to  [TxStatus] in Tbl1, Thanks in advance
Avatar of Shaun Vermaak
Shaun Vermaak
Flag of Australia image

Can you post file please?
Try:
Form is bound to tbl2.
DoCmd.RunSQL "Update tbl1 Set Status = " & Me.CboStatus & " Where StockID = " & Me.StockID

Open in new window

There is no built in way to make this happen and there never will be since it violates the rules of database normalization.  Each piece of data should exist once and only once in a properly structured database.  When you need data that is stored in another table, create a query that joins the two tables and select the value you want.

You have used dummy names and a dummy schema so I can't tell you precisely what to do but I can give you an example.  In your stock table, you have a product and its status is "on order".  When you look at unshipped orders, you join to the stock table and display the status from the stock table.  There is no reason to propagate it to all open orders (the field has no relevance on closed (shipped or canceled) orders.

PS - Do NOT define table fields as combos.  This is a crutch which is poorly implemented and will come back to bite you in oh so many ways.
Avatar of Ronald Malk

ASKER

Thanks for all replies, I tried the Code by HNasr It didn't work,  anyway for better explaining my self here is the file is in the  attachment
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
Thanks Pat for your comment and for the advice but for now I need to get it working with HNasr code, it needs some changes because the Status field is a string, you mentioned If Status is a string, then I need to enclose it in quotes but i'm not sure how, I tried this and  It doesn't except it
DoCmd.RunSQL "Update tbl1 Set "Status" = " & Me.CboStatus & " Where StockID = " & Me.StockID
ASKER CERTIFIED SOLUTION
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
Thanks, that was beautiful mate , it works very good, Thanks and Happy new year to you both and everyone
Welcome!
An early mentor of mine used to say -
"If you don't have time to do it right, what makes you think you have time to do it twice?"  The further you go on the known wrong path, the more things that ultimately need to b changed or you end up living forever with the bad design. But more likely, the system bets scrapped.  Many studies have been done over the years regarding the cost of change and earlier is ALWAYS cheaper, usually by a large factor.

Happy New Year.