After Update code requires amendment

[Jack Sheet]

Code behind a combo box used for selecting records in a form reads as
follows:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo28 = Null
End Sub
Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Other Combo box properties that may be relevant are
"Limit to list" = yes
"Enabled" = yes
"Locked" = no

In the Form properties:
"Allow Filters" = yes
"Allow Edits" = yes
"Allow Deletions" = yes
"Allow Additions" = no
"Data Entry" = no

Problem:
The form seems to operate pretty much as desired with the following
exception.
If the user types in a reference in the combo box, then changes his mind, it
works fine if he hits escape key, but if instead he back-spaces over the
typed characters and then hits Enter or Tab I get
Run Time Error 3077
Sytax Error (missing operator) in expression
with the following expression highlighted on hitting Debug:
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]

If I then click "End" on the error message prompt everything proceeds
smoothly, but I would rather disable the error message in that instance.
Any takers? Thanks

 

[Douglas J Steele]

Try

Sub Combo28_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo28] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub

 

[John Spencer]

Test for a value in combo28.

Sub Combo28_AfterUpdate()
' Find the record that matches the control.
'Test for null value in Combo28
If IsNull(Me.Combo28) = False then vice the above line
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
'Test for noMatch found
If Me.RecordsetClone.NoMatch = False Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End if
End Sub

 

[Jack Sheet]

Thanks Doublas and John, both solutions work as of course expected.

Just one question to Douglas: What is the effect of the string
& ""
within the IF statement?

Try

Sub Combo28_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo28] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Code behind a combo box used for selecting records in a form reads as
follows:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo28 = Null
End Sub
Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Other Combo box properties that may be relevant are
"Limit to list" = yes
"Enabled" = yes
"Locked" = no

In the Form properties:
"Allow Filters" = yes
"Allow Edits" = yes
"Allow Deletions" = yes
"Allow Additions" = no
"Data Entry" = no

Problem:
The form seems to operate pretty much as desired with the following
exception.
If the user types in a reference in the combo box, then changes his mind, it
works fine if he hits escape key, but if instead he back-spaces over the
typed characters and then hits Enter or Tab I get
Run Time Error 3077
Sytax Error (missing operator) in expression
with the following expression highlighted on hitting Debug:
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]

If I then click "End" on the error message prompt everything proceeds
smoothly, but I would rather disable the error message in that instance.
Any takers? Thanks

 

[Douglas J Steele]

You can't use Len on a Null field. (Len(Null) = Null)

By appending & "" to a field, you can check for Null or zero-length string
with one comparison.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doublas and John, both solutions work as of course expected.

Just one question to Douglas: What is the effect of the string
& ""
within the IF statement?

Try

Sub Combo28_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo28] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Code behind a combo box used for selecting records in a form reads as
follows:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo28 = Null
End Sub
Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Other Combo box properties that may be relevant are
"Limit to list" = yes
"Enabled" = yes
"Locked" = no

In the Form properties:
"Allow Filters" = yes
"Allow Edits" = yes
"Allow Deletions" = yes
"Allow Additions" = no
"Data Entry" = no

Problem:
The form seems to operate pretty much as desired with the following
exception.
If the user types in a reference in the combo box, then changes his

mind,
it

works fine if he hits escape key, but if instead he back-spaces over the
typed characters and then hits Enter or Tab I get
Run Time Error 3077
Sytax Error (missing operator) in expression
with the following expression highlighted on hitting Debug:
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]

If I then click "End" on the error message prompt everything proceeds
smoothly, but I would rather disable the error message in that instance.
Any takers? Thanks

 

[Jack Sheet]

Thanks. Onward and upward

You can't use Len on a Null field. (Len(Null) = Null)

By appending & "" to a field, you can check for Null or zero-length string
with one comparison.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doublas and John, both solutions work as of course expected.

Just one question to Douglas: What is the effect of the string
& ""
within the IF statement?

Try

Sub Combo28_AfterUpdate()
' Find the record that matches the control.
If Len(Me![Combo28] & "") > 0 Then
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Code behind a combo box used for selecting records in a form reads as
follows:

Option Compare Database
Option Explicit
Private Sub Form_Current()
Combo28 = Null
End Sub
Sub Combo28_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Other Combo box properties that may be relevant are
"Limit to list" = yes
"Enabled" = yes
"Locked" = no

In the Form properties:
"Allow Filters" = yes
"Allow Edits" = yes
"Allow Deletions" = yes
"Allow Additions" = no
"Data Entry" = no

Problem:
The form seems to operate pretty much as desired with the following
exception.
If the user types in a reference in the combo box, then changes his mind,
it
works fine if he hits escape key, but if instead he back-spaces over the
typed characters and then hits Enter or Tab I get
Run Time Error 3077
Sytax Error (missing operator) in expression
with the following expression highlighted on hitting Debug:
Me.RecordsetClone.FindFirst "[ID_Tasks] = " & Me![Combo28]

If I then click "End" on the error message prompt everything proceeds
smoothly, but I would rather disable the error message in that instance.
Any takers? Thanks