CODEWITHSUNDEEP
sqlms-accesssql-updateany
Undas Ound
Undas Ound

Reputation: 11

ACCESS - How to update field in specific record based upon SQL SELECT ANY statement being false

I am attempting to use SQL to update the field [tblServiceSession].[First Prebooked] to TRUE for the current record on an open form [Forms]![frmNewServiceSessionPopUp]![Service Access ID] if a complex series of logic is met.

Here is the SQL that I have produced thus far:

SELECT tblServiceSession.REQUESTID, tblServiceSession.[FIRST PREBOOKED]
FROM tblServiceSession
WHERE (((tblServiceSession.[FIRST PREBOOKED])=True) AND (((([tblServiceSession].[RequestID])=[Forms]! 
[frmNewServiceSessionPopup]![RequestID]))=
Any (SELECT tblServiceSession.[FIRST PREBOOKED] 
FROM tblServiceSession 
WHERE tblServiceSession.[FIRST 
PREBOOKED] = true)));

So the above SQL uses "ANY" and queries a series of records that all share the same [requestID] and then filters for only those where [First Preebooked] = TRUE.

What I want to be able to do is to have the above SQL query produce a true/false value and then use the result to determine whether or not to run the update query below. Basically, if there are any records returned in the above query (TRUE) then do nothing ELSE (FALSE), run the following SQL:

UPDATE tblServiceSession SET tblServiceSession.[FIRST PREBOOKED] = True
WHERE (((tblServiceSession.[SERVICE ACCESS ID])=Eval("Forms!frmNewServiceSessionPopUp!cboServiceAccessID")) 
AND ((tblServiceSession.ATTENDED)<>"counsellor rescheduled" 
Or (tblServiceSession.ATTENDED)<>"counsellor cancellation") 
AND ((tblServiceSession.[SERVICE TYPE])="scheduled") AND ((tblServiceSession.[SERVICE PROVIDED])="individual" 
Or (tblServiceSession.[SERVICE PROVIDED])="Family" 
Or (tblServiceSession.[SERVICE PROVIDED])="Couple"));

The above SQL is a simple update query with some qualifying criteria that updates the [First Prebooked] field for the current record on the same open form [Forms]![frmNewServiceSessionPopUp]![Service Access ID]

Any help is very much appreciated.

Upvotes: 0

Views: 438

Answers (1)

Undas Ound
Undas Ound

Reputation: 11

The following comment helped me to implement the DCount() function in the second query and reference the first query: "Are you using VBA? What is your code? Do a record count of the first query and proceed accordingly. Either use DCount() domain aggregate function or open a recordset."

I was able to set the criteria of the update query to be DCount() and only update if less than 1!

Thanks everyone.

Upvotes: 0

Related Questions