Update 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

 

[vbaInet]

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

Not sure why you included that line in bold since you don't have an argument that's set as Response.

I wouldn't advise you do this on the Change event unless it's absolutely necessary. You could use a command button instead.

The syntax of your UPDATE statement is correct but your update value is not the value of NewData but the string "NewData". Also, what you've written would affect ALL records so you need a WHERE clause unless this is what you want. If the data type of tblB.C is of type String, the syntax is this:

[FONT=Times New Roman][SIZE=3]strSQL = "UPDATE tblB SET [C] = [COLOR=Red]'[/COLOR]" & NewData & "[COLOR=Red]' [/COLOR]WHERE [ID] = " & Me.ID & ";"[/SIZE][/FONT]

Notice the single quotes in red wrapping the string input (as you would when you are setting criteria in design view of a query.

If type of tblB.C is Number:

[FONT=Times New Roman][SIZE=3]strSQL = "UPDATE tblB SET [C] = " & NewData & " WHERE [ID] = " & Me.ID & ";"[/SIZE][/FONT]

Single quotes not needed here.

You would need to adjust the WHERE clause to include or not include the single quotes from what has been explained.

You could also create an UPDATE query and just simply calling it when needed.

I hope this helps.