Member Avatar for Nonie

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

  • 2 Contributors
  • 2 Replies
  • 98 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Nonie

Dani AI

Generated

The behavior you described, , almost always comes from changing the Data control to a filtered result for the lookup and never restoring it. After you run the SELECT for a single PNR the Data control contains only that row, so the next refill loop only sees that one value. Also avoid top-level On Error Resume Next while debugging — it hides the real error. As suggested, having the project helped confirm this pattern.

Fix options (pick one):

  • Do not repoint the Data control for the lookup. Open a separate DAO recordset for lookups and for filling the list. That leaves the UI data control alone.
  • If you must change the Data control, save its original RecordSource when the form loads and restore it before repopulating the list.
  • Always clear the list (lstPNR.Clear) before adding items and check rs.BOF/rs.EOF or call MoveFirst on the recordset before looping.

Example patterns (VB6 + DAO):

Populate the list from a fresh DAO recordset:

Private Sub PopulateList()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset("SELECT pnr_no FROM pnr", dbOpenSnapshot)

    lstPNR.Clear
    Do Until rs.EOF
        lstPNR.AddItem rs!pnr_no
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Lookup without changing the main control:

Private Sub ShowPNRStatus(pnrValue As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String

    sql = "SELECT pname, status FROM pnr WHERE pnr_no = '" & Replace(pnrValue, "'", "''") & "'"
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

    If Not (rs.BOF And rs.EOF) Then
        txtPName.Text = rs!pname
        txtStat.Text  = rs!status
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Quick checks: confirm whether pnr_no is numeric or text (quote strings), ensure the ListBox is not bound via RowSource (bound lists ignore AddItem/Clear), remove any blanket On Error Resume Next, and use Debug.Print or MsgBox to show the SQL/RecordCount while testing.

Recommended Answers

All 2 Replies

Member Avatar for Marikanna

Hi

Can you post the program as a zip format attachment. It will be easy to check.


Marikanna

Member Avatar for Nonie

Sorry for the delay Marikanna. Attached is the zipped file of the program.

Thank you.

Nonie

TBStatus.zip (3.15 KB)
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.