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. . . . . . - Private Sub Form_Load()
-
Me!frmProviderEnrollmentProcessing.Form!ProviderMainID.Value = Forms!frmProviderMain.Form!ProviderMainID
-
End Sub
I appreciate any help, or suggestions for a better way to do this.
8 20622
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.
Two ways to do this. I don't particularly like to reference controls on other forms, but you would use: - If CurrentProject.AllForms("frmProviderMain").IsLoaded Then
-
ProviderMainID = Forms!frmProviderMain!ProviderMainID
-
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: - 'Somewhere in frmProviderMain code
-
DoCmd.OpenForm "frmEnrollmentProcessing", , , , , , ProviderMainID
-
-
'In frmEnrollmentProcessing
-
Private Sub Form_Open(Cancel As Integer)
-
If IsNull(OpenArgs) Then
-
MsgBox "No ID Number!"
-
DoCmd.Close acForm, "frmEnrollmentProcessing"
-
Else
-
ProviderMainID = OpenArgs
-
End If
-
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. - DoCmd.OpenForm "Sample", , , , , , value1 & "+" & value2
-
-
'Sample Form_Open
-
Dim strArgs() as String
-
strArgs = Split(OpenArgs, "+")
-
value1 = strArgs(0)
-
value2 = strArgs(1)
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.
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: - DoCmd.OpenForm "frmEnrollmentProcessing", , , "ProviderMainID = " & ProviderMainID
See OpenForm Method for more information.
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).
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.
I appreciate all your feedback. I'm going to give the OpenArgs with the invisible text box a whirl tomorrow. Thanks, again!
NeoPa 32,554
Expert Mod 16PB
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Irwinsp |
last post by:
Hi All,
I have a form with an auto number field displayed. The field looks great
except when the user is entering a new record. The field then has the text
"auto number" in it. Is there a...
|
by: Lyn |
last post by:
Hi,
I hope someone can help.
I have a main form which mostly fills the Access window. In the bottom half
of this form I have a tab control to display various types of data related
to the main...
|
by: Geoff Portbury |
last post by:
I'm a swimming club coach.
I have a data base for recording times at various meets.
I have a FORM for entering times called SWIM TIMES.
When I enter the swimmers name ID, eg FOR01 for Doug Ford I...
|
by: Rolan |
last post by:
I need assistance regarding code needed to prevent someone from opening
a form if a table field name(s) has been changed. For example, there is
existing code to check for certain data based on...
|
by: Steven C |
last post by:
Hello:
I am a Forxpro developer trying to come up to speed in C#. I'm trying
to do in C# what I do effortlessly in Foxpro, and that is to call a
grid refresh method in the parent form from the...
|
by: Jason Galvin |
last post by:
I would like to disable the auto-populating feature (remembers form
element text between post-backs) when creating a .NET form.
I have succeeded in disabling auto-populate by creating my controls...
|
by: rdemyan via AccessMonster.com |
last post by:
My application is split into a front end and back end. Each user has their
own copy of the front end.
There are a few forms I only want to be open for one user at a time. So I've
implemented the...
|
by: ZaphodBBB |
last post by:
Hi
I have a table that has as its Primary Key the Auto-Increment Field:
Equipment_Number.
In M.S. Access is there any way to define a starting number for the field?
OR another way in which...
|
by: David Wright |
last post by:
Hello Everyone
I would be grateful if someone could help me with the automatic increment of a field on my subform called ‘Test_SrNo’. I am Using Microsoft Office 2000. The auto entry of the...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| |