HillTJ
To train a dog, first know more than the dog..
- Local time
- Yesterday, 15:31
- Joined
- Apr 1, 2019
- Messages
- 731
I have a combobox that I wish to populate with lots of records. I'm trying to adapt Allen Browns code (demo for populating a combobox with postcodes), but haven't got it right. I have a table called 'TBL_Fittings_Products' with PKroductID(Autonumber) & PM_Part_Number (Short Text). I've copied Allen Browns Code & adapted it. I've left the variable names as per Allen for now. See below.
The combo box is populated correctly with the rowsource with the productIDs starting with the 3 digits entered into [ProductID] on the form. But upon moving to a different field the combobox does not retain the value (displays nothing).
If someone can assist with the code it would be appreciated.
The combo box is populated correctly with the rowsource with the productIDs starting with the 3 digits entered into [ProductID] on the form. But upon moving to a different field the combobox does not retain the value (displays nothing).
If someone can assist with the code it would be appreciated.
Code:
Option Compare Database
Option Explicit
Const conSuburbMin = 3
Function ReloadSuburb(sSuburb As String)
Dim sSuburbStub As String
Dim sNewStub As String ' First chars of Suburb.Text
sNewStub = Nz(Left(sSuburb, conSuburbMin), "")
' If first n chars are the same as previously, do nothing.
If sNewStub <> sSuburbStub Then
If Len(sNewStub) < conSuburbMin Then
'Remove the RowSource
Me.ProductID.RowSource = "SELECT ProductID,PM_Part_Number FROM tbl_Fittings_Products WHERE (False);"
sSuburbStub = ""
Else
'New RowSource
Me.ProductID.RowSource = "SELECT ProductID,PM_Part_Number FROM tbl_Fittings_Products WHERE (PM_Part_Number Like """ & sNewStub & "*"") ORDER BY ProductID;" ' This works Correctly
sSuburbStub = sNewStub
End If
End If
End Function
Private Sub Form_Current()
Call ReloadSuburb(Nz(Me.ProductID, ""))
End Sub
Private Sub ProductID_AfterUpdate()
Dim cbo As ComboBox
Set cbo = Me.ProductID
If Not IsNull(cbo.Value) Then
If cbo.Value = cbo.Column(0) Then
If Len(cbo.Column(1)) > 0 Then
'Me.ProductID = cbo.Column(1)
Me.ProductID = cbo.Column(0)
End If
'If Len(cbo.Column(2)) > 0 Then
' Me.ProductID = cbo.Column(1)
' End If
Else
Me.ProductID = Null
End If
End If
Set cbo = Nothing
End Sub
Private Sub ProductID_Change()
Dim cbo As ComboBox ' Suburb combo.
Dim sText As String ' Text property of combo.
Set cbo = Me.ProductID
sText = cbo.Text
Select Case sText
Case " " ' Remove initial space
cbo = Null
'Case "MT " ' Change "Mt " to "Mount ".
' cbo = "MOUNT "
' cbo.SelStart = 6
'Call ReloadSuburb(sText)
Case Else ' Reload RowSource data.
Call ReloadSuburb(sText)
End Select
Set cbo = Nothing
End Sub