Controlling the use of an "Other" field with VBA

[ResearchRN]

In my brain tumor database I have a surgery data form that is embedded in the demographics form. The surgery data form is based on a query called qrySurgeryData. There are two fields in the surgery data form to enter the type of surgical procedure, planProc and planProcOther. planProc is a combo box with a list of procedures which includes “other” as one of the choices. PlanProcOther is a textbox where the user can type the name of a procedure if “Other” was selected in the planProc field.

[FONT=Verdana][COLOR=black]I want to control the use of the planProcOther field in the Surgery Data Form. The PlanProcOther field is disabled until the user selects “Other” from planProc comboBox. When the user enters text in the planProcOther field it creates a new record in tblPlanProcOther.


Here’s the code minus the error handling parts.

Private Sub cboPlanProc_AfterUpdate()
 
'Enable the planProcOther field and move the cursor when OTHER is selected.
If Me.planProc = "other" Then
Me.planProcOther.Locked = False
Me.planProcOther.Enabled = True
Me.planProcOther.SetFocus

But now comes the hard part. Suppose the user changes his/her mind and changes the planProc field from “other” to something else? I need code that checks to see if a new the entry was made in tblPlanProcOther and if so to delete it. If not, just reset the enabled property in the planProcOther field to false.

I imagine the code would be assigned to the On Dirty event in planProc and would look something like this, again minus the error handling:

 
Private Sub cboPlanProc_Dirty (Cancel As Integer)
 
'Check to see if there is a record in tblPlanProcOther with the same 'caseNUM as the current case number
 
If ***there is a record in tblPlanProcOther with the same caseNum as the current case number*** Then
 
   ***Code to delete it goes here***
Me.planProcOther.Enabled = False
 
Else
Me.planProcOther.Enable = False
 
End IF
End Sub

I need help filling in the details. Can someone please help me out?

[John_G]

How and when is the new record entered into tblPlanProcOther? Is the text box PlanProcOther bound to a field in a table/query?

If the text box is unbound, then all you need to do is set it to blank if the user changes their mind.

[ResearchRN]

How and when is the new record entered into tblPlanProcOther? Is the text box PlanProcOther bound to a field in a table/query?

If the text box is unbound, then all you need to do is set it to blank if the user changes their mind.

A new record is entered into tblPlanProcOther when the user types text into the planProcOther textbox. The textbox is bound to a query called qrySurgeryData.

[pbaldy]

I would expect the code to be in the after update event of the combo, where the existing code is. You could use an Else clause and test for the existence of the record in the other table and delete it.

[ResearchRN]

I would expect the code to be in the after update event of the combo, where the existing code is. You could use an Else clause and test for the existence of the record in the other table and delete it.

Thanks for your reply. I tried this, but it didn't work:

Private Sub cboPlanProc_AfterUpdate()
On Error GoTo ErrHandler
'Moves the cursor from the PLANNED PROCEDURE field to the OTHER PROCEDURE field
' when OTHER is selected.
If Me.planProc = "other" Then
Me.planProcOther.Locked = False
Me.planProcOther.Enabled = True
Me.planProcOther.SetFocus
 
Else
 
If Me.caseNum = [Tables]![tblPlanProcOther]![caseNum] Then
DoCmd.RunSQL "Delete * From tblPlanProcOther Where CaseNum = Me.CaseNum"
End If
 
End If
Exit_cboPlanProc_AfterUpdate:
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume Exit_cboPlanProc_AfterUpdate
 
End Sub

I just don't know enough VBA to figure this out. Can you show me what I'm doing wrong?

[pbaldy]

Two problems, in these lines:

If Me.caseNum = [Tables]![tblPlanProcOther]![caseNum] Then
DoCmd.RunSQL "Delete * From tblPlanProcOther Where CaseNum = Me.CaseNum"

First, you can't refer to the table value that way. Second, you have to concatenate the value from the form into the SQL. I'd test with a DCount:

If DCount(...) > 0 Then

see here for the appropriate syntax:

DLookup Usage Samples


For the SQL, presuming that's a numeric value:

CurrentDb.Execute "Delete * From tblPlanProcOther Where CaseNum = " & Me.CaseNum, dbFailOnError

Note that the Execute method is more efficient and doesn't throw the warnings.

Edit: I'd expect you'd also be setting the locked and enabled properties in the Else clause.

[ResearchRN]

Two problems, in these lines:

If Me.caseNum = [Tables]![tblPlanProcOther]![caseNum] Then
DoCmd.RunSQL "Delete * From tblPlanProcOther Where CaseNum = Me.CaseNum"

First, you can't refer to the table value that way. Second, you have to concatenate the value from the form into the SQL. I'd test with a DCount:

If DCount(...) > 0 Then

see here for the appropriate syntax:

DLookup Usage Samples


For the SQL, presuming that's a numeric value:

CurrentDb.Execute "Delete * From tblPlanProcOther Where CaseNum = " & Me.CaseNum, dbFailOnError

Note that the Execute method is more efficient and doesn't throw the warnings.

Edit: I'd expect you'd also be setting the locked and enabled properties in the Else clause.

Thanks, pbaldy! That gives me something to work with. I'll give it a try.

[pbaldy]

No problem; post back if you get stuck.