Pls. help me. I have a list box that connect to a dao table. The list box is populated by a pnr_no field from that table whenever a command button is clicked to make it visible. From the list box, I can click whatever PNR No. that was displayed in the text box and transfer that value to a text box and when ar command button is clicked, I extract then value of Field(1) and Field(2) and display it in a corresponding text box. This works fine the first time i run it but the next time around, the last PNR No. that i clicked is added in the list box but the table remains intact. I have tried the Listbox.Clear, Listbox.Refresh but didn't work out. I have also tried the DBList which has different effect with Listbox. I simply cannot figure out. I hope someone can help. Below is the source code.
Option Explicit
Private Sub cmdDot_Click()
On Error Resume Next
lstPNR.Visible = True
Do While Not Data1.Recordset.EOF
lstPNR.AddItem (Data1.Recordset.Fields("pnr_no"))
Data1.Recordset.MoveNext
Loop
End Sub
Private Sub lstPNR_Click()
txtPNRNO.Locked = False
txtPNRNO.Text = lstPNR.Text
lstPNR.Visible = False
End Sub
Private Sub cmdClear_Click()
txtPNRNO.Text = ""
txtPName.Text = ""
txtStat.Text = ""
lstPNR.Visible = False
End Sub
Private Sub cmdStatus_Click()
Dim c As Integer
If txtPNRNO.Text = "" Then
MsgBox "Error! Please select PNR No.", vbExclamation, "Error"
txtPNRNO.Text = ""
txtPName.Text = ""
txtStat.Text = ""
txtPNRNO.SetFocus
Exit Sub
End If
If Not IsNumeric(txtPNRNO.Text) Then
MsgBox "Invalid PNR No! Check again", vbExclamation, "Invalid"
txtPNRNO.Text = ""
txtPNRNO.SetFocus
Exit Sub
End If
Data1.RecordSource = "SELECT * FROM pnr WHERE pnr_no = " + txtPNRNO.Text + ""
Data1.Refresh
Do While Not Data1.Recordset.EOF
c = c + 1
Data1.Recordset.MoveNext
Loop
If c <> 0 Then
Data1.Recordset.MoveFirst
txtPName.Text = Data1.Recordset.Fields(1)
txtStat.Text = Data1.Recordset.Fields(2)
End If
End Sub
Private Sub UserDocument_Terminate()
Data1.Recordset.Close
End Sub
Thank you for your help.
Nonie