UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update, Access 2016    
 
   
mike60smart
post Jul 16 2019, 12:24 PM
Post#1


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I currently have a table named tblOrders with a field named "Emailed" (Data Type Yes/No)

I also have a table named tblOrderDetails also with a field named "Emailed" (Data Type Yes/No)

I have a report that is attached to an email showing all Current Order Details which currently have the EMailed Control set as 0

What I am trying to do is set both of these fields to Yes so that when the next Report is generated it would only show those records with EMailed set to 0

I am trying to use this code but with no luck so far.

Any help highlighting me errors appreciated.

CODE
Dim strEMailed As String
Dim strEMailed2 As String

strEMailed = -1
strEMailed2 = 0


strSQL = "UPDATE [tblOrders] SET EMailed = " & strEMailed & " WHERE EMailed = 0;"
    
strSQL = "UPDATE [tblOrderDetails] SET EMailed = " & strEMailed2 & " WHERE EMailed = 0;"

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
theDBguy
post Jul 16 2019, 12:25 PM
Post#2


Access Wiki and Forums Moderator
Posts: 76,047
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. Are you getting any errors (type mismatch)?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
mike60smart
post Jul 16 2019, 12:27 PM
Post#3


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi theDBGuy

No nothing at all


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MadPiet
post Jul 16 2019, 12:41 PM
Post#4



Posts: 3,206
Joined: 27-February 09



I just added a Yes/No column to a table and used the wizard to build the update...

UPDATE DeliveryDocuments SET DeliveryDocuments.EMailed = True
WHERE (((DeliveryDocuments.EMailed)=False));

Works a champ. What if you replace the 1/0 with True/False?
Go to the top of the page
 
mike60smart
post Jul 16 2019, 01:56 PM
Post#5


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi MadPiet
Note*** Edited because I noticed my spelling mistake

It now works perfectly

Many thanks everyone yet again

cheers.gif
I moved the OnClick event to a New Command Button as follows:-

CODE
Private Sub cmdUpdate_Click()

    On Error GoTo cmdUpdate_Click_Error
    Dim strSQL As String
strSQL = "UPDATE [tblOrders] SET tblOrders.EMailed = True WHERE (((tblOrders.EMailed)=False));"
Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError



    
strSQL = "UPDATE [tblOrderDetails] SET tblOrderDetails.EMailed = True WHERE (((tblOrderDetailss.EMailed)=False));"
Debug.Print strSQL
    CurrentDb.Execute strSQL, dbFailOnError
    
    On Error GoTo 0
    Exit Sub

cmdUpdate_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."

End Sub


The Immediate Window shows this:-

CODE
UPDATE [tblOrders] SET tblOrders.EMailed = True WHERE (((tblOrders.EMailed)=False));
UPDATE [tblOrderDetails] SET tblOrderDetails.EMailed = True WHERE (((tblOrderDetailss.EMailed)=False));

Buy when it runs I get the following Error:

Attached File  error.JPG ( 14.41K )Number of downloads: 0

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MadPiet
post Jul 16 2019, 02:53 PM
Post#6



Posts: 3,206
Joined: 27-February 09



What does it do it you use 0 and -1?
Go to the top of the page
 
mike60smart
post Jul 16 2019, 03:10 PM
Post#7


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi MadPiet

That works as well


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
MadPiet
post Jul 16 2019, 03:14 PM
Post#8



Posts: 3,206
Joined: 27-February 09



So you're sorted?
Go to the top of the page
 
mike60smart
post Jul 16 2019, 03:16 PM
Post#9


UtterAccess VIP
Posts: 13,320
Joined: 6-June 05
From: Dunbar,Scotland


Hi MadPiet

Yes all sorted thanks's to you

Many thanks again
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 03:27 PM