Passing control to a form from another

Posted on 2012-08-18
Last Modified: 2012-08-23
I have a form that I attached that I would like to be able to select one of these rows and then display that information on the main form that will call this form..
Question by:seamus9909
    LVL 30

    Expert Comment

    Try this:
    Double click ProviderID field, a form opens with that record.
    Improve the form design!

    Author Comment

    Thanks for that.  I need to explain the process a little better.

    The main form is "Case Details".  On the page marked Reviewer I would like the user to click on the button to "open Reviewers Table:, this will open the form  "frmProviders".  THe user should then be able to select one of the records, and when closing that form the Provider information should be populated on the Reviewers subpage.  

    The Key that ties this all together would be:

    tblclaim   -          ClaimID          Primary Key

    So I guess what I want to do is select the provider from the providers form and update the tblclaim field ProviderID for the CLaimID that is open on the main form.

    Does this make sense?
    LVL 30

    Expert Comment

    Sorry seamus9909!
    In the sample database, we have only 2 tables Provider, and tblStates, and 1 form frmProvider.
    I created a form frmMain to open when you double click  ProviderID in a record.

    "The main form is 'Case Details'"
    This form is missing from the database.
    Update the database with any object required to explain the problem.

    Tell me step by step what to do from opening a form, specifying the form name, untill you come to the place where you need a different result from what you expect.

    Author Comment

    I have enclosed both the FE and BE of the DB. You will have to link them.  

    On the Cases form (Reviewer subpage), I want to click on the button to "add Provider from table" and open the frmprovider.  Then I want to allow the user to select one of the rows from the frmprovider form, return control back to the Cases Form with the Reviewer subpage filled out with the details for the record selected on the frmproviders form.
    LVL 26

    Accepted Solution

    Hi seamus9909,

    This may do the trick.

    In the footer of your Provider form [frmProvider], add a button, name it btnSelectProvider, make it fairly wide, we will provide some feedback using the button.caption property.

    In the properties dialog for the new button, in the Event tab, set "On Click" to Event Procedure, then click the adjacent build button [...] to bring up the VBA editor.

    In the event procedure for the btnSelectProvider
    Private Sub btnSelectProvider_Click()
      Dim strSql As String
      Dim intProviderID As Integer
      intProviderID = Me.ProviderID
      ' Get the InvoiceID from [Form_Case Details]
      Dim intInvoiceID As Integer 'Pkey from tblClaims
      intInvoiceID = [Form_Case Details].InvoiceID
      ' Concatenate the update SQL string
      strSql = "Update tblClaims Set Reviewer = " & intProviderID & " WHERE InvoiceID = " & intInvoiceID
      ' Execute the update
      DoCmd.RunSQL (strSql)
      ' Recalc the data on the case details form
      [Form_Case Details].Recalc
      ' Close this form
      DoCmd.Close acForm, Me.Name
    End Sub

    Open in new window

    In the Event Procedure for the Form Form_Current() event:
    Private Sub Form_Current()
    Me.btnSelectProvider.Caption = "Choose ProviderID " & Me.ProviderID.Text & " as the provider for this case."
    End Sub

    Open in new window

    Alan ";0)

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now