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.Cl one
rs.FindFirst "[I_NAME_L] = '" & Me.Cmb_LName.Co lumn(0) & "' and "
& _
"[I_NAME_F]= '" & Me.Cmb_LName.Co lumn(1) & "' and "
& _
"[POLNO]= '" & Me.Cmb_LName.Co lumn(2) & "'"
Me.Bookmark = rs.Bookmark

Me.I_NAME_L.Bac kColor = 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]

Re: Combo-Box & FindFirst Problem !

Prakash,

I have had a similar issue also. See

http://www.google.nl/groups?ie=UTF-8&oe=UTF-8&as_umsgid=b4k6p4%24r7n%241@news.telebyte.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.C mb_LName.Column (0)
txtColumn1=Me.C mb_LName.Column (1)
txtColumn2=Me.C mb_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]

Re: Combo-Box & FindFirst Problem !

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.C olumn(0))
'MsgBox (Me.Cmb_LName.C olumn(1))
'MsgBox (Me.Cmb_LName.C olumn(2))

Dim FName, LName, Pol_No As String
LName = Me.Cmb_LName.Co lumn(0)
FName = Me.Cmb_LName.Co lumn(1)
Pol_No = Me.Cmb_LName.Co lumn(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.Cl one
rs.FindFirst "[I_NAME_L] = '" & LName & "' and " & _
"[I_NAME_F]= '" & FName & "' and " & _
"[POLNO]= '" & Pol_No & "'"
Me.Bookmark = rs.Bookmark

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

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

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




"Arno R" <arracomn_o_s_p _a_m@tiscali.nl > wrote in message news:<3fae50b8$ 0$79342$5fc3050 @dreader2.news. tiscali.nl>...[color=blue]
> Prakash,
>
> I have had a similar issue also. See
> http://www.google.nl/groups?ie=UTF-8...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.C mb_LName.Column (0)
> txtColumn1=Me.C mb_LName.Column (1)
> txtColumn2=Me.C mb_LName.Column (2)
>
> Now if you will use there vars instead of the Column(x) property's then I think your code will work.[/color]