troubleshooting Question

How can I make thhis code update the form?

Avatar of rcleon
rcleonFlag for United States of America asked on
Microsoft Access
15 Comments1 Solution396 ViewsLast Modified:
Hi,

I found this module and I implemented i my project and it's doing just what I needed with one exception. Once the user click on the company name I need the form to refresh with all of the company information.

The text box and list box are located in the form header the form name is COMPANYINFO.

Please help

Rafael

Option Compare Database
Option Explicit

Const acbcErrNoError = 0
Const acbcQuote = """"

Public Function acbDoSearchDynaset(ctlText As Control, _
 ctlList As Control, strBoundField As String) As Variant

    ' Search through a bound listbox, given text to find from a text box.
    ' Move the list box to the appropriate row.
    ' The listbox can have either a table or a dynaset
    ' (a query or an SQL statement) as its row source.
    ' In:
    '     ctlText: a reference to the text box you're typing into
    '     ctlList: a reference to the list box you're looking up in
    '     strBoundField: the name of the field in the underlying
    '       table in which you're looking for values.
    ' Out:
    '     Return value: either 0 (no error) or an error variant
    '      containing the error number.
   
    Dim rst As DAO.Recordset
    Dim varRetval As Variant
    Dim db As DAO.Database
   
    On Error GoTo HandleErr
   
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(ctlList.RowSource, dbOpenDynaset)
    ' Use the .Text property, because you've not yet left the
    ' control.  Its value (or its .Value property) aren't
    ' set until you leave the control.
    rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & ctlText.Text & acbcQuote
    If Not rst.NoMatch Then
        ctlList = rst(strBoundField)
    End If
    varRetval = acbcErrNoError

ExitHere:
    acbDoSearchDynaset = varRetval
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function

HandleErr:
    varRetval = CVErr(Err)
    Resume ExitHere
End Function

Public Function acbDoSearchTable(ctlText As Control, ctlList As Control, _
 strBoundField As String, strIndex As String) As Variant
    ' Search through a bound listbox, given text to find from a text box.
    ' Move the list box to the appropriate row.
    ' The listbox can have only a table as its
    ' row source.

    ' In:
    '     ctlText: a reference to the text box you're typing into
    '     ctlList: a reference to the list box you're looking up in
    '     strBoundField: the name of the field in the underlying
    '       table in which you're looking for values.
    '     strIndex: the name of the index to look through.
    ' Out:
    '     Return value: either 0 (no error) or an error variant
    '      containing the error number.
   
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varRetval As Integer

    On Error GoTo HandleErr
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(ctlList.RowSource, dbOpenTable)
    rst.Index = strIndex
    ' Use the .Text property, because you've not yet let the
    ' control.  Its value (or its .Value property) aren't
    ' set until you leave the control.
    rst.Seek ">=", ctlText.Text
    If Not rst.NoMatch Then
        ctlList = rst(strBoundField)
    End If
    varRetval = acbcErrNoError

ExitHere:
    On Error Resume Next
    rst.Close
    On Error GoTo 0
    acbDoSearchTable = varRetval
    Exit Function

HandleErr:
    varRetval = CVErr(Err)
    Resume ExitHere
End Function

Sub acbUpdateSearch(ctlText As Control, ctlList As Control)
    ctlText = ctlList
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros