469,290 Members | 1,887 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,290 developers. It's quick & easy.

Auto Populate fields from subform to form.

72
Hello,

I have a subform that displays hull# and WO#. I would like for those fields to populate when the user opens up a form that requires them, after clicking on the hull#.

So, I setup the hull# field to open FrmJobAccomplished on click.

Then when FrmJobAccomplished opens, I would like for Hull# and WO# to be populated from subform.

Make sense?

Now, I went looking around and saw some code for similar actions, but unfortunatly I did not understand. Now if there is anyway to get this done through the UI rather than SQL, it would be easier on me. If not, I will need baby steps directions :).

Using 2003.

Thxs.
Mar 25 '07 #1
40 3742
ADezii
8,800 Expert 8TB
Hello,

I have a subform that displays hull# and WO#. I would like for those fields to populate when the user opens up a form that requires them, after clicking on the hull#.

So, I setup the hull# field to open FrmJobAccomplished on click.

Then when FrmJobAccomplished opens, I would like for Hull# and WO# to be populated from subform.

Make sense?

Now, I went looking around and saw some code for similar actions, but unfortunatly I did not understand. Now if there is anyway to get this done through the UI rather than SQL, it would be easier on me. If not, I will need baby steps directions :).

Using 2003.

Thxs.
There are several Methods for accomplished this. One of them is to pass the values of these 2 Fields in OpenArgs as in:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"
To plug them into the appropriate Fields:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Activate()
  2.   Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  3.   Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  4. End Sub
PS - You could also reference the 2 Fields in the Sub-Form directly
Mar 25 '07 #2
Rotor
72
Thxs for the reply ADezii,

Questions :).

This code I would make it as a macro through the UI in the Macro Design? Also, the first code I sorta get. However, the second code "Me!" and "OpenArgs" are not familiar to me. If you can help me understand that, then I can figure out what the code is doing.

I am a novice Access, but I want to learn :).

There are several Methods for accomplished this. One of them is to pass the values of these 2 Fields in OpenArgs as in:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"
To plug them into the appropriate Fields:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Activate()
  2.   Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  3.   Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  4. End Sub
PS - You could also reference the 2 Fields in the Sub-Form directly
Mar 25 '07 #3
Rotor
72
Thxs for the reply ADezii,

Questions :).

This code I would make it as a macro through the UI in the Macro Design? Also, the first code I sorta get. However, the second code "Me!" and "OpenArgs" are not familiar to me. If you can help me understand that, then I can figure out what the code is doing.

I am a novice Access, but I want to learn :).
One more thing, the subform displays the 10 most recent records. So, if I list the fields directly, I have to make sure that the one that is populates is the one the user clicked.
Mar 25 '07 #4
ADezii
8,800 Expert 8TB
Thxs for the reply ADezii,

Questions :).

This code I would make it as a macro through the UI in the Macro Design? Also, the first code I sorta get. However, the second code "Me!" and "OpenArgs" are not familiar to me. If you can help me understand that, then I can figure out what the code is doing.

I am a novice Access, but I want to learn :).
This code is not in a 'Macro' but it is VBA code contained within the Click() Event of a Command Button. Me refers to the current instance of code ececution, which, in your particular case is the active Form. OpenArgs is simply an Argument to the OpenForm() Method which allows data/information to be passed to the Form that will be opened.
Mar 25 '07 #5
Rotor
72
This code is not in a 'Macro' but it is VBA code contained within the Click() Event of a Command Button. Me refers to the current instance of code ececution, which, in your particular case is the active Form. OpenArgs is simply an Argument to the OpenForm() Method which allows data/information to be passed to the Form that will be opened.
Okay, I type that code in the little field On Click?
Mar 25 '07 #6
NeoPa
32,173 Expert Mod 16PB
  1. Open the form in Design View.
  2. Select the Command Button to set up.
  3. View Properties (Alt-Enter).
  4. Go to the On Click property.
  5. Select [Event Procedure] from Drop-Down.
  6. Click on Elipsis (...) to open VBA window.
You are now in the very routine within the VBA code viewer window :)
Mar 27 '07 #7
Rotor
72
Thxs NeoPa.
Mar 27 '07 #8
NeoPa
32,173 Expert Mod 16PB
No problem - I hope it helped :)
Mar 27 '07 #9
Rotor
72
Okay here is the code i used:
Expand|Select|Wrap|Line Numbers
  1. Private Sub WO_WRI___s_Click()
  2.     Me![WO or WRI No] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  3.     End Sub
I received an error "Invalid use of Null" also I dont see how that code opens the form FormAcftJobsComp.

So, where did I miss the boat?
Mar 28 '07 #10
Rotor
72
PS - You could also reference the 2 Fields in the Sub-Form directly
This seems like it might be an easier solutiont, but I am not quite catching the meaning.

Go to form
select field
properties
then go to data?
Mar 28 '07 #11
NeoPa
32,173 Expert Mod 16PB
Okay here is the code i used:
Expand|Select|Wrap|Line Numbers
  1. Private Sub WO_WRI___s_Click()
  2.     Me![WO or WRI No] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  3.     End Sub
I received an error "Invalid use of Null" also I dont see how that code opens the form FormAcftJobsComp.

So, where did I miss the boat?
Me.OpenArgs can only be referenced from within the Form_Open() procedure.
Usual practice is to save the value(s) passed in Private variable(s) at this point then reference them from there later in your code.
Multiple items within OpenArgs can be handled by using the Split() function within your Form_Open() procedure and assigning each separate element to a different variable or simply assigning the whole Variant array to a Variant variable for reference later.
Mar 28 '07 #12
Rotor
72
Hello NeoPa,

Hope you had a good weekend :). Anyways, I've read what you wrote, but I am having a hard time wrapping my head around it :|.

Anyways, I went ahead and made a macro to where the by clicking wo# field in subform of FormEntryPage it opens FormAcftJobsComp. That part works like a champ. Now, to autoenter the wo# that the user clicked from subform in FormEntryPage to FormAcftJobsComp, I have to write code where? Do I go to the Data tab wo# in FormAcftJobsComp, and under default value enter code so the wo# is entered from the click? Or do I go to Event tab? If event tab, which event? Either way is the code the same as above?

Sorry for baby steps here.
Apr 2 '07 #13
Rotor
72
Okay, i think I know what I am doing wrong, or not doing for that matter.

How do I get to this spot in the VBA editor?

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"
I figured this one out:

Expand|Select|Wrap|Line Numbers
  1. Private Sub WO_WRI___s_Click()
  2. Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  3.   Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  4. End Sub
I think if I can figure out where the first code goes, and knowing where the second goes, I can then make them talk to each other :).
Apr 2 '07 #14
Rotor
72
  1. Open the form in Design View.
  2. Select the Command Button to set up.
  3. View Properties (Alt-Enter).
  4. Go to the On Click property.
  5. Select [Event Procedure] from Drop-Down.
  6. Click on Elipsis (...) to open VBA window.
You are now in the very routine within the VBA code viewer window :)
Okay, back to these instructions. I know realize what that I was in the right spot all along, just got confuse by the terminology in VBA editor.

Okay, so I am in

Private Sub WO_WRI__s_Click() where I Put the following:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormAcftJobsComp", acNormal,,, acFormAdd, acWindowNormal, "<WO/WRI#'s>"
End Sub

Now does the this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Activate()
  2.     Me![txtWO/WRI#'s] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  3. End Sub
Go inside the area same as the first code bofore End Sub up there, or does it go in its own area below it?
Apr 2 '07 #15
Rotor
72
Okay, so I tried this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub WO_WRI___s_Click()
  2.     DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, "<WorkOrder/WRI>"
  3.  
  4. End Sub
  5.  
  6. Private Sub Form_Activate()
  7.   Me![txtWO/WRI#'s] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
  8. End Sub
It opens form, but the wo# is not autofilled, all I get is default value set by "FormsAcftJobsComp".

I believe I have all the code in the right spot, it seems it is now a matter of sorting out the code itself? The "WorkOrder/WRI" is the field in the form being opened, FormAcftJobsComp", that I need populated with the information from field "WO/WRI#'s" the user clicked in the subform.
Apr 2 '07 #16
NeoPa
32,173 Expert Mod 16PB
Rotor,

It seems like you've made a whole heap of progress without any support. Congratulations and sorry for my absence. It's to be extended I'm afraid, at least until tomorrow as I've got to give this some careful thought and it's after 02:00 at the moment.
If you hear nothing tomorrow then bump it. I don't want to leave you hanging.

Cheers -NeoPa.
Apr 3 '07 #17
Rotor
72
Rotor,

It seems like you've made a whole heap of progress without any support. Congratulations and sorry for my absence. It's to be extended I'm afraid, at least until tomorrow as I've got to give this some careful thought and it's after 02:00 at the moment.
If you hear nothing tomorrow then bump it. I don't want to leave you hanging.

Cheers -NeoPa.
NeoPa, I appreciate all the help you all have given me for the past few weeks. I in no way expect immediate help, nor for you stay up into the wee hours of the morning on my account. Now, sleep well, and then work the next 48hrs straight until problem is solved ;). Just kidding. Again, thxs for your help.
Apr 3 '07 #18
NeoPa
32,173 Expert Mod 16PB
Right, lets start off by naming the from and to forms so we know where we are.
Can you let me have the names of the form you're starting from (caller) and the form you're calling (callee).
Some of the code goes in one and the other goes in the other so we need to be clear as to what we're saying here.
Apr 3 '07 #19
NeoPa
32,173 Expert Mod 16PB
NeoPa, I appreciate all the help you all have given me for the past few weeks. I in no way expect immediate help, nor for you stay up into the wee hours of the morning on my account. Now, sleep well, and then work the next 48hrs straight until problem is solved ;). Just kidding. Again, thxs for your help.
Lol.
Missed this the first time around :D
I can't say I was up just sorting you out Rotor, but I did remember that I'd made a mental note not to leave you blowing in the wind for too long.
Apr 3 '07 #20
Rotor
72
Right, lets start off by naming the from and to forms so we know where we are.
Can you let me have the names of the form you're starting from (caller) and the form you're calling (callee).
Some of the code goes in one and the other goes in the other so we need to be clear as to what we're saying here.
Okay, subform "SubFormAcftWorked" resides within "FormEntryPage" as a quick preview for my users of hull# and associated WO# recently worked. It displays the last 10 records from its correspoding table.

By clicking field "WO/WRI#'s" form "FormAcftJobsComp" is opened where the user can enter a new job done. I would like for the field "WorkOrder/WRI" in form "FormAcftJobsComp" to be populated from the respective field "WO/WRI#'s" that the user clicked in subform "SubFormAcftWorked".

Clear as mud?
Apr 3 '07 #21
NeoPa
32,173 Expert Mod 16PB
Okay, subform "SubFormAcftWorked" resides within "FormEntryPage" as a quick preview for my users of hull# and associated WO# recently worked. It displays the last 10 records from its correspoding table.

By clicking field "WO/WRI#'s" form "FormAcftJobsComp" is opened where the user can enter a new job done. I would like for the field "WorkOrder/WRI" in form "FormAcftJobsComp" to be populated from the respective field "WO/WRI#'s" that the user clicked in subform "SubFormAcftWorked".

Clear as mud?
Firstly, your naming is causing you (us) problems as you're using invalid characters which need to be replaced when creating procedures. This doesn't make life easier.

Secondly, what type of field is [WO/WRI#s]? I've assumed an integer will hold the value but let me know if it's string or other type of field.

Try, in your [SubFormAcftWorked] module :
Expand|Select|Wrap|Line Numbers
  1. Private Sub WO_WRI___s_Click()
  2.     DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, Me.[WO/WRI#s]
  3. End Sub
I suspect you're not copy/pasting as your field name seems to change between references, but it's whatever you have that is the correct spelling for it in code (Type Me.WO & see what appears).

Now, in your [FormAcftJobsComp] module :
Expand|Select|Wrap|Line Numbers
  1. Private intWOWRIs As Integer
  2.  
  3. Private Sub Form_Open()
  4.   intWOWRIs = Me.OpenArgs
  5. End Sub
Start with this. If this works, we can move forwards.
Apr 3 '07 #22
Rotor
72
Firstly, your naming is causing you (us) problems as you're using invalid characters which need to be replaced when creating procedures. This doesn't make life easier.
Okay, I am going to eliminate this variable by changing the field names, then I'll get back to ya with the rest. That way we know for sure that is not the problem.
Apr 3 '07 #23
NeoPa
32,173 Expert Mod 16PB
Sounds like a plan :)
Apr 3 '07 #24
Rotor
72
Okay, I renamed all pertinent fields to "WOorWRI"

Here is the code in the subform to open "FormAcftJobsComp":

Expand|Select|Wrap|Line Numbers
  1. Private Sub WOorWRI_Click()
  2.     DoCmd.OpenForm "FormAcftJobsComp", acNormal, , ,
  3.     acFormAdd, acWindowNormal, Me.[WOorWRI]
  4. End Sub
Here is the code I placed in the form "FormAcftJobsComp":
Expand|Select|Wrap|Line Numbers
  1. Private intWOorWRI As Integer
  2. Private Sub Form_Open()
  3.     intWOorWRI = Me.OpenArgs
  4. End Sub
Okay, when the field is clicked i get a Run-time error '2501' The Open Form Action was cancelled. When I open the debugger the whole first code is highlighted.
Apr 3 '07 #25
NeoPa
32,173 Expert Mod 16PB
What's highlighted exactly?
The code you have in WOorWRI_Click() cannot work as displayed, as there is no '_' at the end of the first line. Lines in VBA cannot be spanned without the continuation character (_).
Apr 4 '07 #26
Rotor
72
Expand|Select|Wrap|Line Numbers
  1. Private Sub WOorWRI_Click()
  2.     DoCmd.OpenForm "FormAcftJobsComp", acNormal, , ,
  3.     acFormAdd, acWindowNormal, Me.[WOorWRI]
  4. End Sub
What's highlighted exactly?
The code you have in WOorWRI_Click() cannot work as displayed, as there is no '_' at the end of the first line. Lines in VBA cannot be spanned without the continuation character (_).
I am lost. I copy pasted the code you posted, I only changed the field name. The split in my post was just for posting purposes, in the editor the line is one continuous line. As such:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, Me.[WOorWRI]
In the debugger, the whole line as in the above window is highlighted.
Apr 4 '07 #27
NeoPa
32,173 Expert Mod 16PB
That's fine Rotor. I had to check, even though I had considered it likely that what you said was the case.
I didn't know what you meant about the line that was highlighted though, so that's clarified nicely. I'll try to look into why when I get a break, as I'm at work atm.
Apr 4 '07 #28
NeoPa
32,173 Expert Mod 16PB
I've checked it over and can't see why it wouldn't work.
The code I posted was simply a doctored version of what you had before. Instead, try using :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormAcftJobsComp", , , , , , Me.[WOorWRI]
(It's narrower too :D)
Let me know if this has problems too.
Apr 4 '07 #29
Rotor
72
I've checked it over and can't see why it wouldn't work.
The code I posted was simply a doctored version of what you had before. Instead, try using :
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "FormAcftJobsComp", , , , , , Me.[WOorWRI]
(It's narrower too :D)
Let me know if this has problems too.
Sorry, no dice NeoPa....the only thing I noticed between your code and the initial iteration I tried is the brackets and quotations around the field name?
Apr 4 '07 #30
Rotor
72
BTW, one question I did not answer is that WOorWRI field are number fields.
Apr 4 '07 #31
Rotor
72
BTW, one question I did not answer is that WOorWRI field are number fields.
Disregard this statement, I was mistaken.
Apr 4 '07 #32
Rotor
72
Thiking outloud here, but while working on parameters queries based on a form, I learned that [Forms]![FormName]![FieldName] instructs access to obtain teh value of the field control in the form from the Forms collection. This becomes the criteria for running the query.

Can this not be done in like the OpenEvent of the form that I going into? LIke on open set focus to WOorWri, and then in the OnFocus event for WOorWRI put in the [Forms]![FormName]![WOorWRI]?
Apr 4 '07 #33
NeoPa
32,173 Expert Mod 16PB
Sorry Rotor.
My whole phone system was dead last night when I got home. No phones; No internet; No fun :(
Sorry, no dice NeoPa....the only thing I noticed between your code and the initial iteration I tried is the brackets and quotations around the field name?
I took out some explicit parameters from the version you were using. That was in case any were incompatible with each other. First rule of debugging is to Simplify, Simplify, Simplify (Emphasis - lol).
It's important to know that failed too. It's not related to the syntax of the OpenForm statement at least.
Apr 5 '07 #34
NeoPa
32,173 Expert Mod 16PB
BTW, one question I did not answer is that WOorWRI field are number fields.
Disregard this statement, I was mistaken.
Silly question, but what is the field type?
Apr 5 '07 #35
NeoPa
32,173 Expert Mod 16PB
Thiking outloud here, but while working on parameters queries based on a form, I learned that [Forms]![FormName]![FieldName] instructs access to obtain teh value of the field control in the form from the Forms collection. This becomes the criteria for running the query.

Can this not be done in like the OpenEvent of the form that I going into? LIke on open set focus to WOorWri, and then in the OnFocus event for WOorWRI put in the [Forms]![FormName]![WOorWRI]?
That would certainly work as long as you leave the calling form open when you open the called form. The Forms collection only contains the currently open forms.

As far as determining the current error you're getting, I'm afraid I don't see any obvious problem. This is something that's hard to see without the database to hand I'm afraid. Sometimes there are obvious issues but this time I see nothing.
I expect this means there is something in your database setup which is causing this but as I don't have all the details available I can't help there.
Apr 5 '07 #36
Rotor
72
That would certainly work as long as you leave the calling form open when you open the called form. The Forms collection only contains the currently open forms.

As far as determining the current error you're getting, I'm afraid I don't see any obvious problem. This is something that's hard to see without the database to hand I'm afraid. Sometimes there are obvious issues but this time I see nothing.
I expect this means there is something in your database setup which is causing this but as I don't have all the details available I can't help there.
On Monday I'll try going back with the original suggestion (Now that the field name has changed) and see if that helps. Being an Access noob, is very possible I may have borked something in the DB, but dont know what to even look for hehehe.

Also, the calling form is always open, so that wont be a problem if I try the other workaround. The only problem I see, is that with this code will it remember which record the user clicked? The calling form displays the last 10 records.

As far as the field, I checked and they are all text fields. The only unique thing is that "WOorWRI" field is the primary key for tblAcftWorked which is the table that the subform is using to obtain the data.

Is there anything I should look for in the field settings that might be throwing us off? One thing is that WOorWRI in the called form is a combo box which gets its data from tblAcftWorked also. I dont know if that would prevent the code execution. Also, will field layout within the called form have anything to do with it? WOorWRI is not the first field in the called form that gets focus or entry on a normal basis.

You have been more than patient in helping me, and I appreciate it.
Apr 5 '07 #37
NeoPa
32,173 Expert Mod 16PB
No problem Rotor. I only get impatient when it's clear an OP is expecting us (me) to do all the work for them. Your problems are simply from ignorance and not attitude.
Back to the question.
The value referred to will be the same whether accessed from the form and passed as an OpenArgs value or from your new form using the Forms! code.
ComboBoxes can make life complicated as they are 'Clever'.
They can show one thing for the operator and, at the same time, return a separate value to the program.
Try :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Me.WOorWRI
  2. DoCmd.OpenForm "FormAcftJobsComp", , , , , , Me.[WOorWRI]
Now see if the results are what you would expect.
Apr 5 '07 #38
Rotor
72
Hello,

Sorry to comeback to this so late, but had a really busy week. Anyways, I tried the Debug code and nothing happens, still same error. Is this code supposed to print to a txt file?

Thxs.
Apr 11 '07 #39
Rotor
72
[quote=Rotor]

Here is the code I placed in the form "FormAcftJobsComp":
Expand|Select|Wrap|Line Numbers
  1. Private intWOorWRI As Integer
  2. Private Sub Form_Open()
  3.     intWOorWRI = Me.OpenArgs
  4. End Sub
Okay after troubleshooting, the above code is the problem. If I eliminate the code, the form opens fine. Ofcourse the field data is not copied over. I tried a couple of things. But it seems every time I remove the code within the parenthesis in the builder's default statement
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form Open (Cancel As Integer)
To match the given code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form Open()
I get the 2501 Run-Time error. However, with (Cancel As Integer) in there, the form opens, but the data is not copied over.
Apr 11 '07 #40
NeoPa
32,173 Expert Mod 16PB
The proper form is :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
I'm sorry if my earlier post missed out that detail. Don't forget the '_' in Form_Open.

To see the results of the Debug.Print commands you need to type Ctrl-G from within the VBA window and that will show the Immediate Pane where the results are shown. I need to know the results when the code from post #38 is run.
Apr 11 '07 #41

Post your reply

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

Similar topics

By using this site, you agree to our Privacy Policy and Terms of Use.