By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,062 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,062 IT Pros & Developers. It's quick & easy.

Auto populating a field on form from another open form

sickenhoofer
P: 19
I have a form, 'frmProviderMain' with a macro which opens another form, frmEnrollmentProcessing.The macro is located on a tab on 'frmProviderMain.' I would like one of the controls/fields on the secondary form, specifically [frmEnrollmentProcessing].[ProviderMainID], to fill in based on the selected provider on 'frmProviderMain'.

I attempted to set an event procedure which would set the value when the form loads as follows. However it does not work. As I am new to VBA, I am not sure if I have the language wrong, or if I am attempting it in the wrong way (and Access 2007 HELP is no help at all).

Here is the code I was using. . . . . .
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Me!frmProviderEnrollmentProcessing.Form!ProviderMainID.Value = Forms!frmProviderMain.Form!ProviderMainID
  3. End Sub
I appreciate any help, or suggestions for a better way to do this.
Jun 16 '09 #1
Share this Question
Share on Google+
8 Replies


sickenhoofer
P: 19
I noticed that the posting appears to have a space in the middle of 'ProviderMainID.' However, there is no space in the code from which it was copied.
Jun 16 '09 #2

Expert 100+
P: 1,287
Two ways to do this. I don't particularly like to reference controls on other forms, but you would use:
Expand|Select|Wrap|Line Numbers
  1. If CurrentProject.AllForms("frmProviderMain").IsLoaded Then
  2.   ProviderMainID = Forms!frmProviderMain!ProviderMainID
  3. End If
You would need the .Form if you were trying to reference a control on a subform. All that is explained in this chart (start at the top with "If you are on").

The other way is to pass the value as the OpenArgs argument of the DoCmd.OpenForm, like:
Expand|Select|Wrap|Line Numbers
  1. 'Somewhere in frmProviderMain code
  2.   DoCmd.OpenForm "frmEnrollmentProcessing", , , , , , ProviderMainID
  3.  
  4. 'In frmEnrollmentProcessing
  5. Private Sub Form_Open(Cancel As Integer)
  6.   If IsNull(OpenArgs) Then
  7.     MsgBox "No ID Number!"
  8.     DoCmd.Close acForm, "frmEnrollmentProcessing"
  9.   Else
  10.     ProviderMainID = OpenArgs
  11.   End If
  12. End Sub
If you need to pass more than one value, you can combine them in one string with a delimiter, then use Split to separate them in the other form.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Sample", , , , , , value1 & "+" & value2
  2.  
  3. 'Sample Form_Open
  4.   Dim strArgs() as String
  5.   strArgs = Split(OpenArgs, "+")
  6.   value1 = strArgs(0)
  7.   value2 = strArgs(1)
Jun 16 '09 #3

sickenhoofer
P: 19
Thanks for your response. I understand the first solution. However, I need to clarify something on the second one.

Am I correct in understanding that in the subprocedure you listed above (lines 4-12 in 2nd example), that the form (frmEnrollmentProcessing) would close if the providermainID field is null? Closing the form would not be the desired effect.

frmEnrollmentProcessing is a form into which data is entered, but I do not want it to be set to DataEntry = yes. I want the form to display all processing events (records) related to the provider on main form. A provider may not have begun enrollment processing yet . . .in which case it should open to a blank record. So, I wouldn't want it to open and then close if the control ProviderMainID on frmProviderEnrollmentProcessing is blank.

Sorry, if I am being dense. Did I say I was new to vba?:):)

In the event that I wasn't clear above, here is some clarification on set up:
Provider Main Info
*tblProviderMain (PK =ProviderMainID - Autonum)
*qryProviderMain (feeds frmProviderMain)
frmProviderMain (view only form)

Enrollment Processing
*tblEnrollmentProcessing (PK = EnrollmentProcessingID - Autonum) . . Also has ProviderMainID (joined globally)
*qryEnrollmentProcessing
*frmEnrollmentProcessing (opens via macro on a tab on frmProviderMain)

Thanks, for your help.
Jun 16 '09 #4

Expert 100+
P: 1,287
Yes, in the example, the form would be closed. You can leave that out completely, or take other action as your application requires.

It sounds like you may want to simply filter the records in the form that you open using the WhereCondition argument, like:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmEnrollmentProcessing", , , "ProviderMainID = " & ProviderMainID
See OpenForm Method for more information.
Jun 16 '09 #5

sickenhoofer
P: 19
I do want to filter as well, I just didn't list that, because I think I can get that part. My main goal was to eliminate the end user from typing the ProviderID in the field that joins it to the specific provider. In fact, that control is locked.

Although, now that you have me thinking . . . . once the filters are applied, will it automatically fill in the provider ID as they begin typing in the other fields? I know it does it for the Enrollmentprocessing Id (PK for that table).
Jun 16 '09 #6

Expert 100+
P: 1,287
I see what you mean now, and I suppose a new record would not fill in the Provider ID automatically. Nothing better than using the OpenArgs comes to mind. In fact, you might have to save that ID in an invisible text box, and set the Provider ID equal to that when moving to a new record. Setting it just the one time in Form_Open will not be sufficient if more records will be added.
Jun 16 '09 #7

sickenhoofer
P: 19
I appreciate all your feedback. I'm going to give the OpenArgs with the invisible text box a whirl tomorrow. Thanks, again!
Jun 16 '09 #8

NeoPa
Expert Mod 15k+
P: 31,709
You've been well lead here (Filtering; OpenArgs; etc), but I suggest setting the .Default of the appropriate control as soon as the form is opened, rather than trying to set the value over and again.
Jun 17 '09 #9

Post your reply

Sign in to post your reply or Sign up for a free account.