else condition in form text box dblclick event

[hangbill]

Hi

Using Access 2003.
Have a subform with a text control called LockRecord. On double click am trying to populate the control with "yes", if the field is empty, and convserely delete the "yes" if the field has a yes value. Have placed the following code in the control's DblClick event, but it does not run.

Private Sub LockRecord_DblClick(Cancel As Integer)
If Me.LockRecord.Value = "Yes" Then Me.LockRecord.Value = "" Else If Me.LockRecord.Value = "" Then Me.LockRecord.Value = "Yes"
DoCmd.Requery
End Sub

It runs fine if I just have Me.LockRecord.Value = "Yes", but the conditional statement does not run. What am I doing wrong?
Thanks

 

[lcook1974]

Off the cuff...

[I]Private Sub LockRecord_DblClick(Cancel As Integer) 
If Me.LockRecord.Value = "Yes" Then [/I]
[I]Me.LockRecord.Value = "" [/I]
[I]END IF[/I]
[I]IF Me.LockRecord.Value = "" Then [/I]
[I]Me.LockRecord.Value = "Yes" [/I]
[I]ENd IF

DoCmd.Requery 
End Sub [/I]

Try that. I didnt' see and "END IF" after your statement

LWC

 

[Scooterbug]

Also, the syntax of your requery is wrong. Should be Me.Requery.

From VBA Help:

The Requery method of the DoCmd object carries out the Requery action in Visual Basic.
expression.Requery(ControlName)
expression Required. An expression that returns one of the above objects.
ControlName Optional Variant. A string expression that's the name of a control on the active object.

 

[hangbill]

Thanks for this feedback; double click has no effect now, nothing happennig.
I will take another look tonight and try figure it out; will post again.
Thanks

 

[hangbill]

Hi Still have problems with this code, although it does work intermittently. The text control "LockRecord" is on a subform (datasheet). On my test db this form has some 30 records. Sometimes a double click will insert a "yes" into an empty cell, other times not. Also, sometimes a double click will remove the "yes" and leave the cell empty. But it does not work on every double ckick and I cannot tell where it will work, or not work. In the code below have commented out the second if statement (two versions of them). Now the code works consistently, but only for removing the "yes". I need the following to happen, on double click: if the field has no value, then insert a yes if the field has a yes, then remove the "yes" and leave it empty. Thanks very much Private Sub LockRecord_DblClick(Cancel As Integer) If Not IsNull(Me.LockRecord.Value) Then Me.LockRecord.Value = "" End If 'If Me.LockRecord.Value = "" Then 'Me.LockRecord.Value = "Yes" 'End If 'If IsNull(Me.LockRecord.Value) Then 'Me.LockRecord.Value = "Yes" 'End If Me.Refresh End Sub

 

[hangbill]

Hi
Still have problems with this code, although it does work intermittently.

The text control "LockRecord" is on a subform (datasheet). On my test db this form has some 30 records. Sometimes a double click will insert a "yes" into an empty cell, other times not. Also, sometimes a double click will remove the "yes" and leave the cell empty. But it does not work on every double ckick and I cannot tell where it will work, or not work.

In the code below have commented out the second if statement (two versions of them).
Now the code works consistently, but only for removing the "yes".

I need the following to happen, on double click:
if the field has no value, then insert a yes
if the field has a yes, then remove the "yes" and leave it empty.

Thanks very much


Private Sub LockRecord_DblClick(Cancel As Integer)

If Not IsNull(Me.LockRecord.Value) Then
Me.LockRecord.Value = ""
End If

'If Me.LockRecord.Value = "" Then
'Me.LockRecord.Value = "Yes"
'End If

'If IsNull(Me.LockRecord.Value) Then
'Me.LockRecord.Value = "Yes"
'End If

Me.Refresh
End Sub

 

[hangbill]

Now here's the strange thing. Take this code

If Me.LockRecord.Value = "Yes" Then
Me.LockRecord.Value = " "
End If

If Me.LockRecord.Value = " " Then
Me.LockRecord.Value = "Yes"
End If

If I change the spacing between the quotes ("" to " ") then the code runs one way, for example can replace all empty values with "yes". But then to delete the "yes" values I have to change the "" spacing in the code from whatever it is, to something else. So I change

If Me.LockRecord.Value = "Yes" Then
Me.LockRecord.Value = " "
End If

to

If Me.LockRecord.Value = "Yes" Then
Me.LockRecord.Value = ""
End If

and I can delete the "yes" values

Then to add "yes" values I have to change the spacing again for the "Yes" code, and so-on.

Any idea what's happening here?
Thanks

 

[hangbill]

ok think i've got it, syntax was wrong.
thanks for trying to help.

Private Sub LockRecord_DblClick(Cancel As Integer)

If Me.LockRecord.Value = "Yes" Then
Me.LockRecord.Value = " "

ElseIf Me.LockRecord.Value = " " Then
Me.LockRecord.Value = "Yes"

End If

Me.Dirty = False
End Sub

 

[lcook1974]

So you got it working now?

cool...

 

[DKO]

ok think i've got it, syntax was wrong.
thanks for trying to help.

Private Sub LockRecord_DblClick(Cancel As Integer)

If Me.LockRecord.Value = "Yes" Then
Me.LockRecord.Value = " "

ElseIf Me.LockRecord.Value = " " Then
Me.LockRecord.Value = "Yes"

End If

Me.Dirty = False
End Sub

If IsNull (Me.LockRecord) Then
     Me.LockRecord = "Yes"
Else
     Me.LockRecord = Null

Try that. The way you have it is actually storing a "space" in the field and not setting the value to null. I'm not sure if that would create any problems for you or not...depending on what all you use the field for.

 

[hangbill]

Took me for ages to figure it out but seems my if/else statement was not correct.
For this situation think I will leave it "". The LockRecord field is visible on the form but is locked so user cannot enter anything, can only double-click. The yes and "" values are code driven and are not used for anything other than to lock the record for a yes value and to unlock it for a "" value. Seems to work fine.
Cheers