Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Form with cmd Button to open related form

    Hello

    What I am trying to do is to have a from with information about a project.
    On that form I have 6 cmdButtons each to open a form that relates to the Sales Order number on the page with the buttons.

    My tables are

    Project Table
    =Sales Order Number (Key)
    =Project Number

    Service Engineer Table
    =SE ID (Key)
    =First Name
    =Last Name
    =Sales Order Number ID

    The relationship is a one (Project) to many (Service Engineer).
    Between Sales Order Number (Project Table Key Field) and Sales Order Number (Service Engineer Field).

    I can get the button to open the form but everything I type into that form makes a new record without the relationship to the Sales Order number.


    I appreciate any help.
    Attached Thumbnails Attached Thumbnails Project Form.JPG   Service Engineer Popup.JPG   Relationship.JPG  
    Last edited by ratte; 09-10-09 at 03:30.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by ratte
    Hello

    What I am trying to do is to have a from with information about a project.
    On that form I have 6 cmdButtons each to open a form that relates to the Sales Order number on the page with the buttons.

    My tables are

    Project Table
    =Sales Order Number (Key)
    =Project Number

    Service Engineer Table
    =SE ID (Key)
    =First Name
    =Last Name
    =Sales Order Number ID

    The relationship is a one (Project) to many (Service Engineer).
    Between Sales Order Number (Project Table Key Field) and Sales Order Number (Service Engineer Field).

    I can get the button to open the form but everything I type into that form makes a new record without the relationship to the Sales Order number.


    I appreciate any help.
    Hi

    There are a number of ways of fixing this, but perhaps the simplest to explain is to use the Open Args facility of the DoCmd.OpenForm command something like this

    In the Button Clck to open the Service Engineer form use this

    DoCmd.OpenForm frmServiceEngineer, , , , , , [Sales Order Number]

    And in the LoadEvent of the frmServiceEngineer form enter this

    [Sales Order Number]=Me.OpenArgs

    Assumptions in above

    1. Service engineer form is named frmServiceEngineer
    2. The field ‘Sales Order Number’ is returned in the Record Source of both the Project form and the Service Engineer form.

    HTH


    MTB
    Last edited by MikeTheBike; 09-10-09 at 09:25.

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    First of all thanks for helping me out with this.

    I took some pictures of what I did.
    It is still not working, I think I put the code in the wrong spot.

    Runtime error 2494
    The action or method requires a form name argument.
    Attached Thumbnails Attached Thumbnails Button Code.JPG   Service Engineer code.JPG   error 001.JPG  
    Last edited by ratte; 09-10-09 at 20:45.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Try this

    DoCmd.OpenForm "frmServiceEngineer", , , , , , [Sales Order Number]

    The form name is a string argument (my fault)

    On reflection, if, as you indicate, you are adding a new (Service Engineer) record, then I suggest this

    DoCmd.OpenForm "frmServiceEngineer", , , ,acFormAdd , , [Sales Order Number]

    This sets the form to a new record (and its Load event sets the foreign key value).



    MTB
    Last edited by MikeTheBike; 09-11-09 at 04:12.

  5. #5
    Join Date
    Sep 2009
    Posts
    16
    Hey

    I still getting a error see pictures.

    Question to your first post

    Assumptions in above

    1. Service engineer form is named frmServiceEngineer
    2. The field ‘Sales Order Number’ is returned in the Record Source of both the Project form and the Service Engineer form.

    Number 2 what doo you mean by that.

    I have The relation between the sales order number key in te project table
    and the sales order number ID in the Service engineer table

    When i make a form with the project table and make the Service engineer table a subform it take the sales order number from the project table over to the service engineer table.

    But I dont won't the subform i want the button with a popup.

    Let me know if I understand something wrong.
    Attached Thumbnails Attached Thumbnails Error.JPG   Error 2.JPG  

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the Calling form:
    Code:
    Private Sub Button_Open_Service_Engineer_Click()
    
        DoCmd.OpenForm "frmServiceEngineer", , , , , , CStr(Me![Sales Order Number])
        
    End Sub
    In the form to open (frmServiceEngineer):
    Code:
    Function GotoRecord(RecordID As Long)
    
        Dim rst As DAO.Recordset
    
        Set rst = Me.RecordsetClone
        rst.FindFirst "[Sales Order Number] = " & RecordID
        If rst.NoMatch = False Then
            Me.BookMark = rst.BookMark
        End If
        rst.Close
    
    Exit_GotoRecord:
        Set rst = Nothing
        Exit Function
        
    End Function
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim Argument as String
        
        Argument = Nz(OpenArgs, "")
        If Len(Argument) Then
            GotoRecord(Clng(Argument))
        End If
    
    End Sub
    Have a nice day!

  7. #7
    Join Date
    Sep 2009
    Posts
    16
    Hi

    Sorry to annoy you but all it dose is opening the servvice engineer form but it dosn't relate what i type into it to the project.

    I attached my db if you coul'd have a look please.

    Thanks
    Attached Files Attached Files

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the sample database you joined to your last post, the table Project contains only one row with [Sales Order] = 98 while the table SE contains only one row with [Sales Order] = Null. Obviously those records are not related and clicking on the button in the Project form cannot open the SE form on the SE record related to the record it displays.

    If what you want to do is open the SE form on a matching record when one exists OR open it on a new record if such a match cannot be found, then change the code I supplied, like this:
    Code:
    Function GotoRecord(RecordID As Long)
    
        Dim rst As DAO.Recordset
    
        Set rst = Me.RecordsetClone
        rst.FindFirst "[Sales Order Number] = " & RecordID
        If rst.NoMatch = False Then
            Me.Bookmark = rst.Bookmark
        Else
            DoCmd.GotoRecord acDataForm, Me.Name, acNewRec
            Me.Sales_Order_Number.Value = RecordID
        End If
        rst.Close
    
    Exit_GotoRecord:
        Set rst = Nothing
        Exit Function
    End Function
    Otherwise I do not understand what you try to do.
    Have a nice day!

  9. #9
    Join Date
    Sep 2009
    Posts
    16
    Thanks for the code.

    About the matching records, what I am trying to do with those two forms is to make those relationships.

    Both tables are empty.
    I open the project form and start a new project by typing the sales order number and project number. After that I click on the Service engineer button to open the SE form
    to type the name of the service engineer for this project and close the form.
    So later this project will relate to many more tables such as Daily Reports, Equipment ect.

Posting Permissions

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