Update sql help

[Tamang Aman]

Dear Experts,

I am here again seeking for help as usual

I have some issues regarding updating in table
How can achieve update sql that does my need

I have table called tblLOTO and i want to update field where my cateria meets.

I want to update all the filed where my LOTO # matches and set status of LOTO as "On Hold" when i select the On Hold item from the drop down list from the form

I have no idea how i can achieve this. Would be much appreciated if some one can help me on this

Here is the example of my layout
I have same LOTO# as below attached with multiple WA# in the table called tblLOTO

LOTO # 12456
LOTO # 12456
LOTO # 12456
LOTO # 12456
LOTO # 12456
LOTO # 12456

So when i select On Hold item from the dropdown list from form i want all of the above LOTO/s # status to set change to "On Hold" in the table tblLOTO
How can i achive this
I am sure this most be easy to do it but i have no idea so far

Thanks in advance!
Tamang

 

[CJ_London]

something like this code on a button on the form

 currentdb.execute ("UPDATE tblLOTO set Status='" & Me.[COLOR=red]Dropdown[/COLOR]
  & "' WHERE Loto=" & me.[COLOR=red]LotoNum)[/COLOR]

you will need to change the bits in red to match your control names

 

[Tamang Aman]

Dear CJ_London,

Appreciated a lot for help and got working with your code. But i got Write conflict message on my screen. Is there anything we can write to above that message.

I did some modification docmd.requery but still the message keep showing up on my screen

Thanks & regards,
Tamang

 

[Tamang Aman]

Dear CJ_London,

The above issue is fixed now with Me.Dirty = false after playing while

Thanks for your help
Regards,
Tamang

 

[Tamang Aman]

Dear CJ_London/ All,
Would you mind for helping me once again since i have one bit of issue. I am trying to update another table and have know idea with Inner Join

As above i want to update the WA Status As "On Hold" with those LOTO/s are associated with the WA. Since WA numbers are stored in tblWA table and and LOTO/s are stored in tblLOTO saperately, have no idea how to achieve this.

Example:

tblWA tblLOTO
WA# LOTO#
27781 123456
27782 123456
27783 123456

Example above when user select those LOTO and puts in Hold in tblLOTO table so same time i want those associated WAs status sets to on Hold as well
This below code does all what i want for LOTOs in order to put them on hold
CurrentDb.Execute ("UPDATE qryLOTO set LOTOStatusList='" & Me.LOTOStatusList & "' WHERE LOTONumber=" & Me.LOTONumber)


I have been playing around with the below code to achieve for WA status setting to " On Hold "but no luck

CurrentDb.Execute ("UPDATE tblWA.WAPrefix, tblLOTO.LOTONumber FROM tblLOTO INNER JOIN set PermitStatus= 'On Hold' WHERE LOTONumber=" & Me.LOTONumber)

Looking forward to hear soon
Thanks in advance!
Tamang

 

[CJ_London]

"UPDATE tblWA.WAPrefix, tblLOTO.LOTONumber FROM tblLOTO INNER JOIN set PermitStatus= 'On Hold' WHERE LOTONumber=" & Me.LOTONumber

this won't work because a) you are trying to update two separate tables, b) you haven't completed the join.

Suggest you write your update query as a query first in the query builder to get the structure right, then copy and paste the SQL into your VBA code amending as required for the variables you are getting from the form.

I can't suggest the correct code because it is not clear why you need a join - on the face of it you don't need tblLOTO

 

[Tamang Aman]

Dear CJ_London,

Thanks a lot for right direction, i have tried with your seggestion and end up with the below statement and fixed my issues. Got working like charms

CurrentDb.Execute ("UPDATE tblWA INNER JOIN tblLOTO ON tblWA.WAPrefix = tblLOTO.WAAndPrefix SET tblWA.PermitStatus = tblLOTO.LOTOStatusList")

Once again thanks for great advise!
Thanks & regards,
Tamang

 

[CJ_London]

glad to help!