Access 2003 Forms : update field via [Event Procedure]

[grinder332518]

My Form accesses a Query as follows :

SELECT tblA.A, tblB.B, tblB.C 
FROM   tblA LEFT JOIN tblB 
ON     tblA.A = tblB.A
UNION 
SELECT tblB.A, tblB.B, tblB.C
FROM   tblB LEFT JOIN TblA 
ON     tblB.A = TblA.A
WHERE  tblB.A not in (select TblA.A from  TblA)

This gives me all the rows from tblA, with tblB columns B & C, when present,
and also all the rows from tblB for those rows with no matches in tblA.

In my Form, however, I would now like the ability to update tblB.C,
via a dropdown in a Combi-Box.

I know that accessing a Query for the data does not really lend itself for such an update, but is it possible to build in an [Event Procedure] for tblB.C to perform my update ?

I tried this on “On Change”, but it didn’t work :

Private Sub C_Change(NewData As String)
    Dim strSQL As String
    If NewData = "" Then Exit Sub
    strSQL = "update tblB set tblB.C =  NewData;"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
End Sub

The amended value from the dropdown was not redisplayed,
nor was tblB.C updated.

Or am I attempting the impossible ?
Many thanks

[TheSmileyCoder]

Private Sub C_Change(NewData As String)
Dim strSQL As String
If NewData = "" Then Exit Sub
strSQL = "update tblB set tblB.C = NewData;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
End Sub

According to this, your trying to set tblB.C=NewData as a variable which the SQL engine has no chance of knowing. As you are passing a string literal, you will need to enclose it in ' such as:

strSQL = "update tblB set tblB.C = '" & NewData "& ';"

Note that this would set ALL rows in tblB.C equal to NewData.

[grinder332518]

sorry for the selay in getting back to you : all is well now : thanks a bundle to 'TheSmileyOne'

[TheSmileyCoder]

Better late then never :)