Search Combo

[Jody]

Hi,

I have a combo box whose row source is determined via the
SQL Statement:Query Builder. The purpose of the combo is
to allow a search, and not store data in a field. When a
value is selected, the entire record of which the value is
a part, is shown in a form.

The Query taps into a field called Subsnumber (text data
type) in a table called tblSubscriber. Some of the
Subsnumber data is identical, but each is part of a
distinct record.

The problem is that when I select any of these identical
data values, the same record shows in the form, rather
than the correct record. Here is the code that likely
controls this (which was created by a Wizard):

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProspSubscrID] = " & Str(Nz(Me!
[Combo78], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Does anyone know how to correct the code, so that the
proper record is shown for each identical data value?

Cheers,
Jody

 

[Michel Walsh]

Hi,


Dim strCriteria As String

str = "ProspSubscrID=" & Nz(Me.Combo78, 0)

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext str
If .NoMatch Then .FindFirst str
If .NoMatch Then
Else
Me.Bookmark=.Bookmark
End If
End With


Note that I use a FindNext, rather than FindFirst, after having positioned the clone to the same
record than the one actually displayed. If no match is found, I try again, this time, starting from
the beginning with a FindFirst. Finally, if I ever got a match, the form is now moved to the right
record.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


You can try:


str = "SubsNumber=""" & Nz(Me.Combo83, 0) & """"
1 3 4


with the second line, with digits, are not part of any code, I just add them to indicate the number
of " . If Me.Combo83 may have " in it (as in 4'- 6" 7/8 ), then, with Access 2000 or later,
try

str = "SubsNumber=""" & Replace(Nz(Me.Combo83, 0), """", """""" ) & """"


where the second and third argument of Replace are 4 and 6 double-quotes.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Thanks very much for your input. I took the essence of
your code and applied it to a slightly modified combo box
with the issues as originally described. I'm getting an
error but I think I'm alot closer than I was. Here is the
code I've implemented:

Private Sub Combo83_AfterUpdate()
' Find the record that matches the control.

Dim str As String

str = "SubsNumber=" & Nz(Me.Combo83, 0)

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext str
If .NoMatch Then .FindFirst str
If .NoMatch Then
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

I'm dealing with a field called 'SubsNumber' instead of
'ProspSubscrID' and the combobox name has changed slightly.
I also took the liberty to change the locally declared
variable to 'str' in lieu of 'strCriteria' (as per your
code) in order to match with the rest of your code.

The error I'm getting is:

Run time error 3464 - Data type mismatch in criteria
expression.

SubsNumber is of datatype text.

Any idea what could be happening.

Your help is greatly appreciated.

Cheers,
Jody

-----Original Message-----
Hi,


Dim strCriteria As String

str = "ProspSubscrID=" & Nz(Me.Combo78, 0)

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext str
If .NoMatch Then .FindFirst str
If .NoMatch Then
Else
Me.Bookmark=.Bookmark
End If
End With


Note that I use a FindNext, rather than FindFirst, after

having positioned the clone to the same

record than the one actually displayed. If no match is

found, I try again, this time, starting from

the beginning with a FindFirst. Finally, if I ever got a

match, the form is now moved to the right

record.

Hoping it may help,
Vanderghast, Access MVP

Hi,

I have a combo box whose row source is determined via the
SQL Statement:Query Builder. The purpose of the combo is
to allow a search, and not store data in a field. When a
value is selected, the entire record of which the value is
a part, is shown in a form.

The Query taps into a field called Subsnumber (text data
type) in a table called tblSubscriber. Some of the
Subsnumber data is identical, but each is part of a
distinct record.

The problem is that when I select any of these identical
data values, the same record shows in the form, rather
than the correct record. Here is the code that likely
controls this (which was created by a Wizard):

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProspSubscrID] = " & Str(Nz(Me!
[Combo78], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Does anyone know how to correct the code, so that the
proper record is shown for each identical data value?

Cheers,
Jody

.

 

[Jody]

worked beautifully!! Thanks Michel.

Jody

-----Original Message-----
Hi,


You can try:


str = "SubsNumber=""" & Nz(Me.Combo83, 0) & """"
1 3 4


with the second line, with digits, are not part of any

code, I just add them to indicate the number

of " . If Me.Combo83 may have " in it (as in 4'- 6"

7/8 ), then, with Access 2000 or later,

try

str = "SubsNumber=""" & Replace(Nz(Me.Combo83, 0), """", """""" ) & """"


where the second and third argument of Replace are 4 and 6 double-quotes.



Hoping it may help,
Vanderghast, Access MVP

Hi Michel,

Thanks very much for your input. I took the essence of
your code and applied it to a slightly modified combo box
with the issues as originally described. I'm getting an
error but I think I'm alot closer than I was. Here is the
code I've implemented:

Private Sub Combo83_AfterUpdate()
' Find the record that matches the control.

Dim str As String

str = "SubsNumber=" & Nz(Me.Combo83, 0)

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext str
If .NoMatch Then .FindFirst str
If .NoMatch Then
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

I'm dealing with a field called 'SubsNumber' instead of
'ProspSubscrID' and the combobox name has changed slightly.
I also took the liberty to change the locally declared
variable to 'str' in lieu of 'strCriteria' (as per your
code) in order to match with the rest of your code.

The error I'm getting is:

Run time error 3464 - Data type mismatch in criteria
expression.

SubsNumber is of datatype text.

Any idea what could be happening.

Your help is greatly appreciated.

Cheers,
Jody

-----Original Message-----
Hi,


Dim strCriteria As String

str = "ProspSubscrID=" & Nz(Me.Combo78, 0)

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext str
If .NoMatch Then .FindFirst str
If .NoMatch Then
Else
Me.Bookmark=.Bookmark
End If
End With


Note that I use a FindNext, rather than FindFirst, after

having positioned the clone to the same

record than the one actually displayed. If no match is

found, I try again, this time, starting from

the beginning with a FindFirst. Finally, if I ever got a

match, the form is now moved to the right

record.

Hoping it may help,
Vanderghast, Access MVP


Hi,

I have a combo box whose row source is determined via the
SQL Statement:Query Builder. The purpose of the combo is
to allow a search, and not store data in a field. When a
value is selected, the entire record of which the value is
a part, is shown in a form.

The Query taps into a field called Subsnumber (text data
type) in a table called tblSubscriber. Some of the
Subsnumber data is identical, but each is part of a
distinct record.

The problem is that when I select any of these identical
data values, the same record shows in the form, rather
than the correct record. Here is the code that likely
controls this (which was created by a Wizard):

Private Sub Combo78_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProspSubscrID] = " & Str(Nz(Me!
[Combo78], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Does anyone know how to correct the code, so that the
proper record is shown for each identical data value?

Cheers,
Jody


.

.