[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Passing control to a form from another

Posted on 2012-08-18
Medium Priority
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
  • 2
  • 2
LVL 31

Expert Comment

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

Author Comment

ID: 38309478
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 31

Expert Comment

ID: 38310768
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

ID: 38311149
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

Alan Warren earned 2000 total points
ID: 38319230
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

834 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