Event procedure to update form field

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

I have VBA code (below) to find the maximum date in a field, ([Date]), based
on the criteria that the field (WELL_ID) is equal to a combobox (Combo34),
triggered by the "On Click" event in a textbox, but currently it does
nothing. Can anyone help me QA what could be wrong with my code (below)?

Dim DateX As Date
Dim Msg

Private Sub Text54_Click()
On Error GoTo ErrorHandler
DateX = DMax("[Date]", "tblWellMaintenanceLogbook", "[WELL_ID]=" & Chr(34) &
Forms!frmWellMaintenance.Combo34 & Chr(34))


ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
 
It does return the correct value in a message box, by adding the line MsgBox
DateX below the function, but what can I do to have it display the value in
the textbox?
 
I have VBA code (below) to find the maximum date in a field, ([Date]), based
on the criteria that the field (WELL_ID) is equal to a combobox (Combo34),
triggered by the "On Click" event in a textbox, but currently it does
nothing. Can anyone help me QA what could be wrong with my code (below)?

Dim DateX As Date
Dim Msg

Private Sub Text54_Click()
On Error GoTo ErrorHandler
DateX = DMax("[Date]", "tblWellMaintenanceLogbook", "[WELL_ID]=" & Chr(34) &
Forms!frmWellMaintenance.Combo34 & Chr(34))


ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub
Click to expand...

What are you expecting it to do? I'm sure it's setting the value of the local
variable Datex to the maximum value of Date for that well, but you don't then
DO anything with the variable DateX.

What do you want to happen with the date after you find it?
 
It does return the correct value in a message box, by adding the line MsgBox
DateX below the function, but what can I do to have it display the value in
the textbox?
Click to expand...

Just set the textbox's value to the variable. I'd suggest a couple of
revisions - you really don't need to make the variables global, and I'd
specify the datatypes differently:

Private Sub Text54_Click()
Dim DateX As Variant ' to handle the chance that it's null
Dim Msg As String
On Error GoTo ErrorHandler
DateX = DMax("[Date]", "tblWellMaintenanceLogbook", "[WELL_ID]=" & _
Chr(34) & Forms!frmWellMaintenance.Combo34 & Chr(34))
Me!Text54 = DateX

ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

I'd actually use the DblClick event instead - the Click event can be too
hairtrigger, in that it fires when you just try to select the textbox. That's
up to you though.
 
Back
Top