Results 1 to 1 of 1
  1. #1
    Join Date
    Oct 2002

    Post Unanswered: NEED HELP! Thousands of Records in Combo Box

    Okay, my records are the companies who will remit to us. and our records is beyond the limits. so what i did in solving the combo box is that when you type the first two letters, the combo box is blank but when its on the 4th letter, the combo will only supply the matching companies. the loading time is much faster and less companies will display in the combo box. The code works perfectly but the problem is that. When i'm in a new record, then i want to go back to the previous record in my transaction form, theirs no display in my combo box. but the record is still there..its just that theirs no display in the combo box. When i create the report, the company is there. its just in the form, theirs no display in the combo box. Please tell me what i did wrong in my code. i really need help. Thanks. =)

    Combo Box
    Name Emp_ID
    BoundColumn 1
    ColumnCount 2

    Forms Module
    Option Compare Database
    Option Explicit

    Dim sEmployerName As String
    Const conEmployerMin = 3

    Function ReloadEmployerN(sEmployer As String)
    Dim sNewEmployer As String ' First chars of EmployerName Text

    sNewEmployer = Nz(Left(sEmployer, conEmployerMin), "")
    ' If first n chars are the same as previously, do nothing.
    If sNewEmployer <> sEmployerName Then
    If Len(sNewEmployer) < conEmployerMin Then
    'Remove the RowSource
    Me.Emp_ID.RowSource = "SELECT Emp_ID, Employer_Name FROM CompanyInfo WHERE (FALSE);"
    sEmployerName = ""
    'New RowSource
    Me.Emp_ID.RowSource = "SELECT Emp_ID, Employer_Name FROM CompanyInfo WHERE (Employer_Name Like """ & _
    sNewEmployer & "*"") ORDER by Emp_ID, Employer_Name;"
    sEmployerName = sNewEmployer
    End If
    End If
    End Function
    Private Sub Form_Current()
    Call ReloadEmployerN(Nz(Me.Emp_ID, ""))
    End Sub
    Private Sub Emp_ID_Change()
    Dim cbo As ComboBox
    Dim sText As String

    Set cbo = Me.Emp_ID
    sText = cbo.Text
    Select Case sText
    Case " "
    cbo = Null 'Remove Initial Space
    Case "MT "
    cbo = "Mount"
    cbo.SelStart = 6
    Call ReloadEmployerN(sText)
    Case Else
    Call ReloadEmployerN(sText)
    End Select
    Set cbo = Nothing
    End Sub
    Private Sub Emp_ID_AfterUpdate()
    Dim cbo As ComboBox
    Set cbo = Me.Emp_ID
    If Not IsNull(cbo.Value) Then
    If cbo.Value = cbo.Column(0) Then
    If Len(cbo.Column(1)) > 0 Then
    Me.Employer_Name = cbo.Column(1)
    End If
    'Me.Employer_Name = Null
    End If
    End If
    Set cbo = Nothing
    End Sub
    Last edited by digtaz; 10-09-02 at 23:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts