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

    Unanswered: Multiple column List box - to find records -Compile Error: User-defined type not defi

    Hi everyone

    I need more help on this.

    I am getting the error Compile Error: User-defined type not defined.
    The code affected is:
    Dim rs As DAO.Recordset
    (where rs As DAO.Recordset is highlighted by the debugger)


    As previously advised I am using Microsoft Access 2000 service pack 3.

    I had thought I found the answer to my issue and advised the forum of what I had done (see original post and advise below) - at one point it was working!

    I have developed an error: Error accessing file. Network conection may have been lost. In trying to resolve this error I copied all the code to a text file, then changed all all my forms to show they did not have a module attached. I checked the modules and found they still had text in them so I deleted the text and closed and saved the database. I then created a brand new database and imported all the tables, queries, forms, reports and macros. I then ran a compile on the database and saved and closed out of the database. I am now going throught the process of manually adding my code from the text file back into the forms.

    As I had thought I had successfully found the answer (thanks to Allen Browne) to my multi column on click problem, I thought I would work on this form first. I simply opened the form, and opened the properties of my multiple column list box and using ON CLICK I selected the Event Procedure for the unbound multiple column list box.

    The form automatically came up with:

    Private Sub LMRESAList_Click()

    End Sub


    I copied the following code (from my text file) into the form code between these two pieces of code:

    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


    I then saved the code and save the form. I closed out of the form and the database and then reopend the database and form in Form View.

    I clicked on the a record which was listed in my multiple column list box and the following error came up:

    Compile Error: User-defined type not defined.

    I am self taught on how to write simple code (thanks to this wonderful forum and the internet).

    I have checked my code and the code written by Allen browne.

    Apart from Field name differences the only other difference is that his code starts with SUB and my code starts with Private Sub

    Is this the problem?

    I have read the help "User-defined type not defined" and it is all 'over my head'.

    Please please help me. I have been trying to resolve this for days now.

    Thanks Karen






    ____________________________

    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 http://allen borw.

    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


    ________________
    Original lodgement:

    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 02:47.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in the code window: Tools/References

    ?? do you have Microsoft DAO reference selected?
    ...if no, scroll on down and select it.
    ...if yes, is it flagged MISSING ?

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    May 2002
    Posts
    157
    Hi all,

    Once again it seems that I have been able to hunt down the answer from the internet. This time I have Tom Wickerath to thank (ADO and DAO Library References)

    I followed his very clear instructions and then changed my code as follows and it all seems to be working again.

    Private Sub LMRESAList_Click()

    On Error GoTo Err_LMRESAList_Click

    Dim db As DAO.Database
    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


    Thanks Tom

  4. #4
    Join Date
    May 2002
    Posts
    157
    Thanks IZY for your reply, it seems like we were both on the right track.
    Thanks for taking the time to reply.

    Regards

    Karen

Posting Permissions

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