Passing control to a form from another

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..
Who is Participating?
Alan WarrenConnect With a Mentor Applications DeveloperCommented:
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)
Try this:
Double click ProviderID field, a form opens with that record.
Improve the form design!
seamus9909Author Commented:
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?
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.
seamus9909Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.