Auto Populate fields from subform to form.

[Rotor]

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.

[ADezii]

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:

DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"

To plug them into the appropriate Fields:

Private Sub Form_Activate()
  Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
End Sub

PS - You could also reference the 2 Fields in the Sub-Form directly

[Rotor]

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:

DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"

To plug them into the appropriate Fields:

Private Sub Form_Activate()
  Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
End Sub

PS - You could also reference the 2 Fields in the Sub-Form directly

[Rotor]

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.

[ADezii]

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.

[Rotor]

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?

[NeoPa]

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 :)

[Rotor]

Thxs NeoPa.

[NeoPa]

No problem - I hope it helped :)

[Rotor]

Okay here is the code i used:

Private Sub WO_WRI___s_Click()
    Me![WO or WRI No] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
    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?

[Rotor]

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?

[NeoPa]

Okay here is the code i used:

Private Sub WO_WRI___s_Click()
    Me![WO or WRI No] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
    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.

[Rotor]

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.

[Rotor]

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?

DoCmd.OpenForm "FrmJobAccomplished", acNormal, , , acFormEdit, acWindowNormal, "<hull#>,<WO#>"

I figured this one out:

Private Sub WO_WRI___s_Click()
Me![txthull#] = Left(Me.OpenArgs, InStr(Me.OpenArgs, ",") - 1)
  Me![txtWO#] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
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 :).

[Rotor]

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:

DoCmd.OpenForm "FormAcftJobsComp", acNormal,,, acFormAdd, acWindowNormal, "<WO/WRI#'s>"

End Sub

Now does the this code:

Private Sub Form_Activate()
    Me![txtWO/WRI#'s] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
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?

[Rotor]

Okay, so I tried this:

Private Sub WO_WRI___s_Click()
    DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, "<WorkOrder/WRI>"
 
End Sub
 
Private Sub Form_Activate()
  Me![txtWO/WRI#'s] = Right(Me.OpenArgs, Len(Me.OpenArgs) - InStr(Me.OpenArgs, ","))
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.

[NeoPa]

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.

[Rotor]

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.

[NeoPa]

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.

[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.

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.

[Rotor]

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?

[NeoPa]

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 :

Private Sub WO_WRI___s_Click()
    DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, Me.[WO/WRI#s]
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 :

Private intWOWRIs As Integer
 
Private Sub Form_Open()
  intWOWRIs = Me.OpenArgs
End Sub

Start with this. If this works, we can move forwards.

[Rotor]

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.

[NeoPa]

Sounds like a plan :)

[Rotor]

Okay, I renamed all pertinent fields to "WOorWRI"

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

Private Sub WOorWRI_Click()
    DoCmd.OpenForm "FormAcftJobsComp", acNormal, , ,
    acFormAdd, acWindowNormal, Me.[WOorWRI]
End Sub

Here is the code I placed in the form "FormAcftJobsComp":

Private intWOorWRI As Integer
Private Sub Form_Open()
    intWOorWRI = Me.OpenArgs
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.

[NeoPa]

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 (_).

[Rotor]

Private Sub WOorWRI_Click()
    DoCmd.OpenForm "FormAcftJobsComp", acNormal, , ,
    acFormAdd, acWindowNormal, Me.[WOorWRI]
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:

DoCmd.OpenForm "FormAcftJobsComp", acNormal, , , acFormAdd, acWindowNormal, Me.[WOorWRI]

In the debugger, the whole line as in the above window is highlighted.

[NeoPa]

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.

[NeoPa]

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 :

DoCmd.OpenForm "FormAcftJobsComp", , , , , , Me.[WOorWRI]

(It's narrower too :D)
Let me know if this has problems too.

[Rotor]

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 :

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?

[Rotor]

BTW, one question I did not answer is that WOorWRI field are number fields.

[Rotor]

BTW, one question I did not answer is that WOorWRI field are number fields.

Disregard this statement, I was mistaken.

[Rotor]

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]?

[NeoPa]

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.

[NeoPa]

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?

[NeoPa]

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.

[Rotor]

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.

[NeoPa]

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 :

Debug.Print Me.WOorWRI
DoCmd.OpenForm "FormAcftJobsComp", , , , , , Me.[WOorWRI]

Now see if the results are what you would expect.

[Rotor]

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.

[Rotor]

[quote=Rotor]

Here is the code I placed in the form "FormAcftJobsComp":

Private intWOorWRI As Integer
Private Sub Form_Open()
    intWOorWRI = Me.OpenArgs
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

Private Sub Form Open (Cancel As Integer)

To match the given code:

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.

[NeoPa]

The proper form is :

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.