Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form access (2000)

    I have a tabular form built using a query. I would like to be able to double click on an entry on that tabular form, and go to another form that contains all the information in the chosen record. Is this possible, and how? Thanks in advance for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    Say that the records in the form are identified by a number field RecordID. The form contains a text box txtSomething; if the user double clicks it, you want to show frmDetailedInfo with all information about the selected record.

    Open the first form in design view.
    Select txtSomething.
    Activate the Event tab of the Properties window.
    Click in the On Dbl Click event, and select Event Procedure from the dropdown list.
    Click the Builder button (the three dots ... to the right of the dropdown arrow.)
    You will be taken to the Visual Basic Editor.
    Complete the code to look like this:

    Private Sub txtSomething_DblClick(Cancel As Integer)
    ' Get out if no RecordID
    If IsNull(Me.RecordID) Then
    Exit Sub
    End If

    ' Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Open form
    DoCmd.OpenForm FormName:="frmDetailedInfo", WhereCondition:="RecordID = " & Me.RecordID
    End Sub

    Substitute the appropriate names.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    I seem to be a little slow in learning VBE stuff. I've placed the code, changed the name (s) But am still having trouble making it work. When I double click on the field name (Serial_number) the correct form shows up, but the record is wrong. What is the function of "Me.RecordID" and "Me.Dirty"? Should I be changing these? following is the changed code. What have I done wrong?

    Private Sub Engine_SN_DblClick(Cancel As Integer)
    ' Get out if no RecordID
    If IsNull(Me.RecordID) Then
    Exit Sub
    End If

    ' Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Open form
    DoCmd.OpenForm FormName:="FRM sales dept information", WhereCondition:="Engine_SN "& Me.RecordID
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    If you look closely at Hans' example and your own adaptation of the Where condition in the last line of code, you will see a missing equals sign. I suspect that is why you aren't going to the correct record.

    Welcome to my little world! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    PeterN

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    You didn't adapt the code completely. RecordID is meant to be the name of a field that uniquely identifies the record, for example the primary key of the table behind the form. I assume that Engine_SN is the name of the identifier field. If that is correct, the other occurrences of RecordID should be replaced with Engine_SN too.

    PeterN already pointed out that you forgot an = sign in the WhereCondition.

    Dirty is a property of the form that is only available in VBA code, not in the Properties window. It is True if the current record in the form has been changed since it was last saved, and False if there are no changes. The code tests if the record has been changed, and if so, saves it, so that FRM sales dept information will display up-to-date information.

    The Me that occurs throughout the code refers to the form that runs the code; it is an efficient way to refer to the form without explicitly specifying its name, and if you type <big>Me.</big>, IntelliSense will show a list of available properties and methods.

    Modified code:

    Private Sub Engine_SN_DblClick(Cancel As Integer)
    ' Get out if no Engine_SN
    If IsNull(Me.Engine_SN) Then
    Exit Sub
    End If

    ' Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If

    ' Open form
    DoCmd.OpenForm FormName:="FRM sales dept information", WhereCondition:="Engine_SN = " & Me.Engine_SN
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    I'm still having trouble with this code, (see below)The form i'm in when I double click on the Serial Number field is called "FRM_brown book customer search". It is the result of a qry from the form I'm trying to open that contains the information in the chosen record (Getting complicated?) that searches for a customer name. When I have the VB window open, the last line is highlited in yellow (i suppose it's the problem line). When I put my cursor over the words Formname, I'm told that the name of the form I'm trying to open is "FRM_brown book customer search" rather than "FRM_Sales_information". When I run the code by double clicking the "serial number" in the form, the query request opens, and asks for the serial number. HELP!!
    Private Sub SERIAL_NUMBER_DblClick(Cancel As Integer)
    'Get out if no engine serial number
    If IsNull(Me.INVENTORY_NUMBER) Then
    Exit Sub
    End If
    'Save record if necessary
    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    'Open Form
    DoCmd.OpenForm FormName:="FRM_Sales_information", WhereCondition:="INVENTORY_NUMBER=" & Me.INVENTORY_NUMBER

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    What kind of field is INVENTORY_NUMBER? Number or text? And are you absolutely sure that both the control on the form and the field in the table are named INVENTORY_NUMBER (with an underscore)?

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    INVENTORY NUMBER IS A TEXT FIELD. THERE IS NO UNDERSCORE IN THE NAME,..BUT WHEN I REMOVE THE UNDERSCORE AN ERROR MESSAGE POPS UP.... SAYS SOMETHING IS EXPECTED.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    No need to shout! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    If you have field names with spaces in them, you must enclose them in square brackets [ ]. For controls on a form or report, you can replace the spaces with underscores (Access does this automatically when creating an event procedure), but that doesn't work for field names. Try this:<pre>DoCmd.OpenForm FormName:="FRM_Sales_information", _
    WhereCondition:="[INVENTORY NUMBER]=" & Chr(34) & Me.INVENTORY_NUMBER & Chr(34)</pre>

    I modified the field name, I left the control name alone. I also added quotes around the value, in the form Chr(34). If you wish, you can use Me.[INVENTORY NUMBER] instead of Me.INVENTORY_NUMBER too.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    OK your fix works fine much thanks. I appreciate your help and patience. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> One problem left, when i go to the chosen form, then request another search, i go back to the same results from the previous search the form has not cleared and i have to hit shift f9 to initiate a new search. Any ideas? BTW I didn't intend to shout, the caps ke was locked on, just to lazy to unlock. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    I'm sorry, I don't understand. What is this "search"?

  12. #12
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    When I am in the first form, I have a command button tied to a query that finds customer names and displays fields on another form. I can then double click on a serial number that is displayed and bring up the record for that serial number. When i then click on the same command button in the first form to do another search, I am taken back to the form displaying the results of the previous search. I then have to "Shift F9" to enter a name for a new search. I should be able to enter a new name in the first form without "Shift F9" (There are only 2 forms involved in all this)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    I'm still confused <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    You state that only two forms are involved. Let me try to get this straight.

    1. "in the first form, I have a command button". I will call this Form A.
    2. "... and displays fields on another form". I will call this other one Form B (in reality, FRM_brown book customer search).
    3. "double click ... and bring up the record for that serial number" - this is the code we've been looking at in this thread, I assume; if I understand correctly this opens a third form Form C (in reality, FRM_Sales_information).

    I count three forms! Where do I go wrong? And which of these forms is the one you have to press Shift+F9 in? Form B?

  14. #14
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Monroeville, Pennsylvania, USA
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form access (2000)

    I think I mentioned this was becoming complicated..
    OK here we go.....Form A is first. It has a command button that is connected to a query.
    After using the command button and requesting the information, Form B opens with the results of the query.
    Double clicking the Serial Number field in form B returns you to the requested record on form A. If I then click on the same command button, to request a new search, I am immediatly taken to form B, which contains the same info from the first query. It is then that I must "Shift F9" to request another search... I hope this is clearer????

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form access (2000)

    Try this: in the On Click event of the command button, requery Form B (FRM_brown book customer search) after opening it:

    DoCmd.OpenForm FormName:="FRM_brown book customer search"
    Forms![FRM_brown book customer search].Requery

Page 1 of 2 12 LastLast

Posting Permissions

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