AfterUpdate code problem

  • Thread starter Thread starter mark r
  • Start date Start date
M

mark r

Please help me turn this pseudo code into functional
actual code:

AfterUpdate Textbox_of_table1field1
for each record in table2
if table2.id = table1.id then
if me.field1 = "lawn" or "sandlot"
then
set table2.field2 = me.field1
' the user may have made the Textbox NULL using the
delete key
else if ISNULL(me.field1) then
set table2.filed2 to be null also
else set table2.filed2 = "concrete”
exit for
end if
next

end sub
 
Mark,

I didn't test this code since I don't have your tables, but this should be
pretty close to functional assuming:

1) This code is placed in the AfterUpdate event procedure for a text box
control named, "Textbox_of_table1field1". If you rename the control, then
you'll need to update the 5 name references in the below code.

2) ID, Field1 and Field2 are referencing field names in tables, not control
names on forms.

3) You have set a reference to the appropriate "Microsoft Active X Data
Objects 2.n Library".

Private Sub Textbox_of_table1field1_AfterUpdate()
On Error GoTo Err_Textbox_of_table1field1_AfterUpdate

Dim strSQL As String
Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strField1 As String

strSQL = "SELECT [ID], [field2] FROM table2 WHERE [ID] = " & Me.ID

Set cnn1 = CurrentProject.Connection

Set rst = New ADODB.Recordset

With rst
.Open strSQL, cnn1, adOpenForwardOnly, adLockOptimistic, adCmdText
If .RecordCount > 0 Then
.MoveFirst

strField1 = Me.Field1

Do While Not .EOF
If strField1 = "lawn" Or strField1 = "sandlot" Then
Set .Field2 = strField1
'The user may have made the Textbox NULL using the Delete
Key
ElseIf strField1 = "" Then
.Field2 = Null
Else
.Field2 = "concrete"
End If
.MoveNext
Loop
End If
End With

Exit_Textbox_of_table1field1_AfterUpdate:
On Error Resume Next
rst.Close
Set rst = Nothing
cnn1.Close
Set cnn1 = Nothing
strSQL = vbNullString
Exit Sub

Err_Textbox_of_table1field1_AfterUpdate:
If Err.Number <> 2501 Then
MsgBox "Error #: " & vbTab & vbTab & Err.Number & vbCrLf _
& "Description: " & vbTab & Err.Description
End If
Resume Exit_Textbox_of_table1field1_AfterUpdate

End Sub
 
Back
Top