Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: Multiple column list box - to find records

    this has been edited at 8.54pm Australian Time 19/March/09

    Hi all
    I am not sure if I am approaching this correctly and need your help.


    I want to click on a record being displayed in the Unbound select query called [LMRESAList] and have
    -the record populate the [aLMRESAF] bound fields and allow me to add/edit and delete
    or
    -finds and displays the record equal to the value being kept in the [FindRcdClick] field and populate the [aLMRESAF] bound fields thus allowing me to add/edit and delete.

    *********

    I am creating a MS Access 2000 Database (yes I know this is old but it is all I have to work with).

    I have a table called LMRESA
    This table has the following 4 Fields
    ID (primary key)
    ESA
    ESACode
    LMR

    I also have created a form called [aLMRESAF] which has the following fields and allows me to add/edit and delete.
    ID
    ESA
    ESACode
    LMR

    The problem is that I have to use the record scroll bar to move forward or back to records that I want to edit.


    The form also has two additional unbound text boxes.

    One [LMRESAList] allows me to perform a select query as follows:

    SELECT [LMRESA].[ID], [LMRESA].[ESA], [LMRESA].[ESACode], [LMRESA].[LMR] FROM LMRESA ORDER BY [LMRESA].[ESA];

    It lists all ESAs (over 100) and allows me to scroll up and down the list.

    The other is called [FindRcdClick] has been created to store the result of clicking on one of the records in the [LMEESAList].

    This is done by having an ON CLICK - EVENT PROCEDURE in the [LMRESAList] field with the following visual basic

    *****

    Private Sub LMRESAList_Click()
    On Error GoTo Err_LMRESAList_Click

    Me!FindRcdClick.Value = Me!LMRESAList.Column(0)

    Exit_LMRESAList_Click:
    Exit Sub

    Err_LMRESAList_Click:
    MsgBox Error$
    Resume Exit_LMRESAList_Click

    End Sub

    ********

    My problem is that I want to click on a record being displayed in the Unbound select query called [LMRESAList] and have
    -the record populate the following [aLMRESAF] fields and allow me to add/edit and delete
    or
    -finds and displays the record equal to the value being kept in the [FindRcdClick] field and populate the following [aLMRESAF] fields thus allowing me to add/edit and delete.
    ID
    ESA
    ESACode
    LMR


    I have spent hours trying different things to no avail.
    I did find that if I added the following to the visual basic command

    DoCmd.GoToRecord acForm, "aLMRESAF", acGoTo, 9

    It will show me record 9 and allow me to edit it, but as I don’t always want to look at record 9 this is of no use. Plus the ID record does not always match the actual record number at the bottom of the form. I could have 100 records but because of deletion of records my ID number may be higher than 100. So in this case 9 is not necessarily the record with the ID of 9 and I am wanting to add edit delete records which I have selected from the [LMRESAList] .

    I hope I have made myself clear, if not please do not hesitate to ask for more info.
    Regards
    Karen
    Last edited by Karen Day; 03-21-09 at 01:26. Reason: make title easier for searching

  2. #2
    Join Date
    May 2002
    Posts
    157

    I found the answer on the internet

    Hi everyone
    I found the answer at Microsoft Access tips: Using a Combo Box to Find Records

    Thank you so much to Allen Browne who put this up on the internet.

    So what did I do?

    I have a table called LMRESA
    This table has the following 4 Fields
    ID (primary key)
    ESA
    ESACode
    LMR

    I also have created a form called [aLMRESAF] which has the following fields and allows me to add/edit and delete.
    ID
    ESA
    ESACode
    LMR


    The form also has one additional unbound text box called [LMRESAList] which allows me to perform a select query as follows:

    SELECT [LMRESA].[ID], [LMRESA].[ESA], [LMRESA].[ESACode], [LMRESA].[LMR] FROM LMRESA ORDER BY [LMRESA].[ESA];

    It lists all ESAs (over 100) and allows me to scroll up and down the list.

    The ON CLICK - EVENT PROCEDURE in the [LMRESAList] field has the following code entered (and it works perfectly):

    Private Sub LMRESAList_Click()
    On Error GoTo Err_LMRESAList_Click

    Dim rs As DAO.Recordset

    If Not IsNull(Me.LMRESAList) Then
    'save before move.
    If Me.Dirty Then
    Me.Dirty = False
    End If
    'search in the clone set
    Set rs = Me.RecordsetClone
    rs.FindFirst "[ID] = " & Me.LMRESAList
    If rs.NoMatch Then
    MsgBox "No Record Found"
    Else
    'display the found record in the form.
    Me.Bookmark = rs.Bookmark
    End If
    Set rs = Nothing
    End If

    Exit_LMRESAList_Click:
    Exit Sub

    Err_LMRESAList_Click:
    MsgBox Error$
    Resume Exit_LMRESAList_Click

    End Sub

Posting Permissions

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