Combo-Box & FindFirst Problem !

[Prakash Wadhwani]

I have a form (continuous) with a few fields :
First Name, Last Name, Policy Number, etc.

In the form footer I have a combo box for LastName which has 3 TEXT
fields: LastName, FirstName & Policy Number

When the user clicks a LastName on the Combo it should seek & position
me on that record on the continuous form. That's ok till here.

The problem is: It always position me on the 1st ocurence of the
LastName. Here's an example ...

L.Name F.Name Policy-Num
Astle Abbey 45
Bodey Art 56
Coelho Benny 87
Coelho Sebastian 33
Coelho Sebastian 34
Coelho Sebastian 35
Coelho Sebastian 36
Coelho Sebastian 37

Now, if I click on the 7th row Policy Number 36 (Coelho Sebastian), I
get positioned at Policy Number 87 (Coelho Benny).
Here's the SQL code for my combo box row source:
SELECT PMF.I_NAME_L, PMF.I_NAME_F, PMF.POLNO FROM PMF ORDER BY
PMF.I_NAME_L, PMF.I_NAME_F, PMF.POLNO;
Here's my combo-box Find Routine Code:

Private Sub Cmb_LName_Click()
Me.OrderBy = "I_NAME_L, I_NAME_F"
Me.OrderByOn = True ' Putting it for safety JUST TO BE SURE

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[I_NAME_L] = '" & Me.Cmb_LName.Column(0) & "' and "
& _
"[I_NAME_F]= '" & Me.Cmb_LName.Column(1) & "' and "
& _
"[POLNO]= '" & Me.Cmb_LName.Column(2) & "'"
Me.Bookmark = rs.Bookmark

Me.I_NAME_L.BackColor = 16777088
End Sub
Can someone please point out what I'm doing wrong ? I'm sure it's
something very minor that I'm overlooking.

Thx & Best Rgds,
Prakash.

[Arno R]

Prakash,

I have had a similar issue also. See
http://www.google.nl/gr*************...e.nl&lr=&hl=nl
Apparantly the issue is fixed in A2k Sp3. At least I can't reproduce it anymore (just installed sp3)
In A97 I still can reproduce it (SR2)

It's 'something' with the scope of the column(x) property I think.

I am pretty sure the following works if you add this in your sub:
Dim txtColum0 as text
Dim txtColum1 as text
Dim txtColum2 as text
txtColumn0=Me.Cmb_LName.Column(0)
txtColumn1=Me.Cmb_LName.Column(1)
txtColumn2=Me.Cmb_LName.Column(2)

Now if you will use there vars instead of the Column(x) property's then I think your code will work.

--
Hope this helps
Arno R

[Prakash Wadhwani]

Hi Arno ... Thx !! I solved it a couple of hrs back but was waiting
for my post to show up on the NG. As you suggested, I did store the
value to variables & shifted some statements back & forth & got it to
work. BTW, I'm using AXP over WinXP. (I don't think it's a bug with
Access ... it seems to be a slight logic problem with my code).
Because I change the ORDER of the records, the bookmark also changes.
Here's the code ...
===== Code Snippet Starts Here ==================================

Private Sub Cmb_LName_Click()
' DO NOT CHANGE THE ORDER of the statements in this routine.
' i.e. the variable names need to be stored 1st; then the ORDER BY
& then the FINDFIRST
'MsgBox (Me.Cmb_LName.Column(0))
'MsgBox (Me.Cmb_LName.Column(1))
'MsgBox (Me.Cmb_LName.Column(2))

Dim FName, LName, Pol_No As String
LName = Me.Cmb_LName.Column(0)
FName = Me.Cmb_LName.Column(1)
Pol_No = Me.Cmb_LName.Column(2)

Me.OrderBy = "I_NAME_L, I_NAME_F, POLNO"
Me.OrderByOn = True ' Putting it for safety JUST TO BE SURE

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[I_NAME_L] = '" & LName & "' and " & _
"[I_NAME_F]= '" & FName & "' and " & _
"[POLNO]= '" & Pol_No & "'"
Me.Bookmark = rs.Bookmark

Me.I_NAME_L.BackColor = 16777088
Me.I_NAME_F.BackColor = -2147483643 'White
End Sub

===== End of Code Snippet =================================

Thx for your help & time ...
Best Rgds,
Prakash.


"Arno R" <ar****************@tiscali.nl> wrote in message news:<3f**********************@dreader2.news.tisca li.nl>...

Prakash,

I have had a similar issue also. See
http://www.google.nl/gr*************...e.nl&lr=&hl=nl
Apparantly the issue is fixed in A2k Sp3. At least I can't reproduce it anymore (just installed sp3)
In A97 I still can reproduce it (SR2)

It's 'something' with the scope of the column(x) property I think.

I am pretty sure the following works if you add this in your sub:
Dim txtColum0 as text
Dim txtColum1 as text
Dim txtColum2 as text
txtColumn0=Me.Cmb_LName.Column(0)
txtColumn1=Me.Cmb_LName.Column(1)
txtColumn2=Me.Cmb_LName.Column(2)

Now if you will use there vars instead of the Column(x) property's then I think your code will work.