SQL statement in a form

  • Thread starter Thread starter Ana
  • Start date Start date
A

Ana

Hi,

In a form I have a Combo1 field which provides Agent_Id and Agent_Name from
Table1.

Then I have Date_In and Date_Out fields which are chosen from a calendar.

I would like a command button to execute the following SQL statement:



UPDATE dbo.Table1
SET COMMISIONS_LIQ = '1'
WHERE (AGENT_ID = Combo1) AND (COMMISIONS_LIQ = 0) AND
(DATE_START BETWEEN Date_In AND Date_Out)



Howto?

TIA

Ana
 
Ana, are you asking how to execute SQL from within MS Access? If so try this:

Dim strSQL As String

strSQL = "UPDATE dbo.Table1" & _
" SET COMMISIONS_LIQ = '1' " & _
" WHERE (AGENT_ID = Combo1) AND (COMMISIONS_LIQ = 0) AND" & _
" (DATE_START BETWEEN Date_In AND Date_Out)"

DoCmd.RunSQL strSQL

HTH
 
This shouldn't be too difficult

The 'better' approach is to do it with code since it'll allow you t
do error handling and such - the relevant lines of code to run tha
query would be something like

strQry = "UPDATE dbo.Table1 SET COMMISIONS_LI
= '1' WHERE (AGENT_ID = Combo1) AND (COMMISIONS_LIQ
0) AND (DATE_START BETWEEN Date_In AND Date_Out)
DBEngine(0)(0).Execute strQry
dbFailOnErro

If you're not comfortable with coding then the alternative would be t
create a macro, use the 'RunSQL' action and then just copy and past
your SQL (as a single line) into the SQL statement box

Either way should do the job fine but it'd be better to do it in cod
if only to get into the habit :
 
Back
Top