oopsNot recordset, rowsource !
Can you please tell us what's in the following properties for your Listbox? Thank you.Also, for reference, this is not a multiselect listbox (not sure whether this matters though as I'm not looking to clear the selected entries but clear the entire listbox of everything!!)
Can you please tell us what's in the following properties for your Listbox? Thank you.
Name - lstResults
Row Source - (blank)
Row Source Type - Table/Query
Bound Column - 1
Column Count - 3
Column Widths - 2cm;;3cm
Allow Value List Edits - yes
List Items Edit Form - (blank)
Inherit Value List - yes
Multi Select - none
Thanks. For starters, try changing the Inherit Value List to No.
With the Row Source being blank, can you also post a screenshot of the Listbox showing the items in it?
Private Sub cmdOk_Click()
Dim strSQL As String
If (Not IsNull(txtPartNumber.Value)) Then
strSQL = "SELECT PN as [Part No], DE as [Description], MC as [Manufacturers Code] FROM tblPT WHERE ((PN='" & txtPartNumber & "') OR (MC Like '*" & txtPartNumber & "*'));"
lstResults.RowSource = strSQL
End If
End Sub
Private Sub cmdClear_Click()
Me.lstResults.RowSource = ""
Set lstResults.Recordset = Nothing
End Sub
their views were set to no in format propertiesNot sure why I cannot see form view on that form?
Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
If (Not IsNull(txtPartNumber.Value)) Then
Set db = CurrentDb
strSQL = "SELECT PN as [Part No], DE as [Description], MC as [Manufacturers Code] FROM tblPT WHERE ((PN='" & txtPartNumber & "') OR (MC Like '*" & txtPartNumber & "*'));"
Set rs = db.OpenRecordset(strSQL)
If (Not rs.EOF = True) Then
rs.MoveLast 'NECESSARY WHEN THERE IS MORE THAN ONE RECORD RETURNED BUT NOT SURE OF IMPLEMENTATION...
If (rs.RecordCount = 1) Then
Debug.Print rs.RecordCount & " records found"
'move on - open form, process etc...
Set lstResults.Recordset = rs
ElseIf (rs.RecordCount > 1) Then
Set lstResults.Recordset = rs
Debug.Print rs.RecordCount & " records found"
txtPartNumber.SetFocus
End If
Else
MsgBox "No Records Found"
Set Me.lstResults.Recordset = Nothing
Me.lstResults.Requery
End If
End If
Exit_cmdOk_Click:
Exit Sub
Err_cmdOk_Click:
MsgBox err.Description
Resume Exit_cmdOk_Click
End Sub