Using VBA and SQL to update a table based on textbox values

[BennyLinton]

Thanks for any help sorting out this Access VBA puzzle...

I have a form that has two textboxes: "billYear" and "billMonth" typical entry is 2015 and 08

I have two tables that are SQL Server ODBC linked called: dbo_certs and dbo_billing (both keyed only to "peopleID")

dbo_Billing has two fields "peopleID" and "recertifying"
dbo_certs has two fields "peopleID" and "recertificationDate" in this typical format: '2015-08-31 00:00:00.000'

I need in VBA (using SQL) to write code that will update a field in dbo_Billing called "recertifying" to -1 if in the dbo_Certs table I find "recertificationDate" having a date like '2015-08-31 00:00:00.000' that can be compared to the 2015 and 08 found on my form.

i may need to alter may tables and can if adding fields would help.

 

[BennyLinton]

I was able to create a standalone query which successfully marks the needed records:

UPDATE dbo_Billing AS a INNER JOIN dbo_certs AS b ON a.peopleid = b.peopleid SET a.recertifying = -1
WHERE year(b.certificationExpireDate) = Forms!billing!billyear and month(b.certificationExpireDate) = Forms!billing!billmonth;