Need help!

[elainenguyen]

I am trying to write a code so that when the user click on an account in the list box, it will search for a record in the table and dispay the record, if there is no record found, display the "No record on file " message. it works well with one account, but when I have 2 accounts in the list box, when a user click on the second account in the list box, the form display the data, but the error message still pop up saying "no record on file for the 1st account). Here is my code. Anybody has any idea?
Thanks a lot!

Dim db As Database
Dim rst As Recordset
Dim stLinkCriteria As String

'Open form and carry account number over first
stLinkCriteria = "[CVACCTNO]=" & Me![txtAcctNo]
DoCmd.OpenForm "frmIDX", , , stLinkCriteria
[Forms]![frmIDX]![txtPtName] = Me.txtPtName

'Find if record is exit. Find from beginning of recordset to ending of recordset
Set db = CurrentDb
Set rst = db.OpenRecordse t("tblIDX", dbOpenDynaset)
rst.FindFirst "[ACCTNOIDX] = " & Me![CVACCTNO] & ""

If rst.NoMatch Then
MsgBox "No IDX record was found under this account number: " & CVACCTNO & "", vbOKOnly, "No Record On File"
DoCmd.Close acForm, "frmIDX", acSaveNo
Else
stLinkCriteria = "[CVACCTNO]=" & Me![txtAcctNo]
DoCmd.OpenForm "frmIDX", , , stLinkCriteria
[Forms]![frmIDX]![txtPtName] = Me.txtPtName
End If

[PEB]

Hi,

I don't understand why do you use this line:
[Forms]![frmIDX]![txtPtName] = Me.txtPtName
Why do you eplace the value [Forms]![frmIDX]![txtPtName] with Me.txtPtName???

[MMcCarthy]

I am trying to write a code so that when the user click on an account in the list box, it will search for a record in the table and dispay the record, if there is no record found, display the "No record on file " message. it works well with one account, but when I have 2 accounts in the list box, when a user click on the second account in the list box, the form display the data, but the error message still pop up saying "no record on file for the 1st account). Here is my code. Anybody has any idea?
Thanks a lot!

Firstly, you haven't closed your recordset. This will always cause problems.

Secondly, you're opening a form. Then checking a recordset and then trying to open the form again.

I don't understand what you're doing.

Also

rst.FindFirst "[ACCTNOIDX] = " & Me![CVACCTNO] & ""

indicated CVACCTNO is a string

However

stLinkCriteria = "[CVACCTNO]=" & Me![txtAcctNo]

indicates it's a number.

Can you explain in detail.

What is the record source of frmIDX is it simply tblIDX or a query based on that table. If so what is the query.

Why are you comparing two fields that seem to be in the same table (based on your code) ACCTNOIDX and CVACCTNO.