Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2018, 01:17 PM   #1
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
combobox to find record based in value

i have a combo box that finds a record on my form based on order number i select in the drop down , is there a way i can get it to auto populate if i search part of the order number instead of the whole thing.

EXAMPLE:
instead of typing 000018052913 in the combo box i would like to type in 2913

Cubsm22p is offline   Reply With Quote
Old 06-14-2018, 02:01 PM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: combobox to find record based in value

Perhaps with LIKE operator and wildcard.
June7 is offline   Reply With Quote
Old 06-14-2018, 02:13 PM   #3
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: combobox to find record based in value

do you think that would be under property sheet > validation rule
'Like '@'

because i dont want to limit it to just that order number

would it be in an event

Cubsm22p is offline   Reply With Quote
Old 06-14-2018, 02:14 PM   #4
Cubsm22p
Newly Registered User
 
Join Date: Feb 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Cubsm22p is on a distinguished road
Re: combobox to find record based in value

i have this after update
Code:
Private Sub Combo1040_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Order Number] = '" & Me![Combo1040] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
i have tried this
Code:
Private Sub Combo1036_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
    Dim strCriteria As String
    strCriteria = "[Order Number] Like '%" & Combo1036 & "%'"
    Set rs = Me.Recordset.Clone
    rs.FindFirst strCriteria
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Cubsm22p is offline   Reply With Quote
Old 06-14-2018, 06:03 PM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 479
Thanks: 6
Thanked 115 Times in 113 Posts
MajP will become famous soon enough
Re: combobox to find record based in value

This i how I do it

1. Make a CLASS module not a standard module. Call it exactly "FindAsYouTypeCombo"
2. Drop the following code in the class. Do not edit
Code:
Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use: To use the class, you need a reference to DAO and code
'similar to the following in a form's module.

'Parmaters:
'  TheComboBox: Your Combobox object passed as an object
'  FilterFieldName: The name of the field to Filter as
'  string
'  FilterFromStart: Determines if you filter a field that 
'  starts with the text or if the text appears anywhere in 
'  the record.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' Public faytProducts As New FindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False
' End Sub
'
'******* END: Form Code ******************


Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Private mFilterFromStart As Boolean

Public Property Get FilterComboBox() As Access.ComboBox
  Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
  Set mCombo = TheComboBox
End Property
Private Sub mCombo_Change()
  Call FilterList
End Sub
Private Sub mCombo_GotFocus()
   mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
  Call unFilterList
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub mForm_Close()
  ' Code provided by BenSacheri to keep Access from crashing
   Call Class_Terminate
End Sub 
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = mCombo.Text
  If mFilterFieldName = "" Then
    MsgBox "Must Supply A FieldName Property to filter list."
    Exit Sub
  End If
  If mFilterFromStart = True Then
    strFilter = mFilterFieldName & " like '" & strText & "*'"
  Else
    strFilter = mFilterFieldName & " like '*" & strText & "*'"
  End If
  Set rsTemp = mRsOriginalList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  If rsTemp.RecordCount > 0 Then
    Set mCombo.Recordset = rsTemp
  End If
  mCombo.Dropdown
  Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Private Sub unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub
Public Property Get FilterFieldName() As String
  FilterFieldName = mFilterFieldName
End Property
Public Property Let FilterFieldName(ByVal theFieldName As String)
  mFilterFieldName = theFieldName
End Property
Private Sub Class_Initialize()
    
End Sub
Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    Set mRsOriginalList = Nothing
End Sub
Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, Optional FilterFromStart = True)
   On Error GoTo errLabel
   If Not TheComboBox.RowSourceType = "Table/Query" Then
      MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
      Exit Sub
   End If
   Set mCombo = TheComboBox
   Set mForm = TheComboBox.Parent
   mFilterFieldName = FilterFieldName
   mFilterFromStart = FilterFromStart
   mForm.OnCurrent = "[Event Procedure]"
   mCombo.OnGotFocus = "[Event Procedure]"
   mCombo.OnChange = "[Event Procedure]"
   mCombo.AfterUpdate = "[Event Procedure]"
   mForm.OnClose = "[Event Procedure]"
   With mCombo
     .SetFocus
     .AutoExpand = False
   End With
   Set mRsOriginalList = mCombo.Recordset.Clone
   Exit Sub
errLabel:
    MsgBox Err.Number & " " & Err.Description
End Sub
3. In your form add the following code. Read instructions in class
Code:
Option Compare Database
 Option Explicit
 Public faytProducts As New FindAsYouTypeCombo
 Form_Open(Cancel As Integer)
   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False
 End Sub
Where me.cmbProucts would be the name of you combobox and ProductName is the name of the field you are filtering.

If done correctly that should filter your combo based on whatever you type
You type 2 any number with 2 shows, then 29 and filter to records with 29, .... Make sure to remove the events from the combo. The class module creates all the events.

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combobox Find A Record Based on Value Selected Zaxxon Forms 2 08-26-2008 02:00 PM
Find a record combobox issue moebek Forms 0 06-15-2008 09:22 PM
find a record... combobox settings Milothicus Forms 7 10-06-2004 01:01 AM
Find Record From Combobox moose Forms 8 07-09-2004 02:52 AM
How to find record using combobox in subform anilthomas Forms 0 12-27-2000 12:50 AM




All times are GMT -8. The time now is 04:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World