Full Version: Code error only when subform embedded
UtterAccess Forums > Microsoft® Access > Access Forms
scromwell
I am using the following code to bring up a form to create a new job record, but also bring over one piece of data from the previous form. It works fine when used directly from the Console_props form when it is open alone, but gives an error that the form doesn't exist when the Console_props form is embedded as a subform in my Owner_Console form.
reviously, I was able to code the button pressed (from "Console_props" while embedded in "Owner_Console") to bring up matching APN records when opening the "Worksheet from Console" form that the below code is in now. However, it was bringing up the previous job, and I'm trying to allow them to create a new job, but bring over the APN from the console.
This is my first post here, hopefully I've been clear enough and thanks in advance for any help offered!
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
APN = [Forms]![Console_props]![APN]
End Sub
RuralGuy
Start by using the OnLoad event rather than the Open event. Your method requires the Console_props form to be open.
scromwell
I changed it to OnLoad, but it is still having that issue. Console_props is open as a subform. It is a button on that subform that is opening the "Worksheet from Console" form.
When I have Console_props open on it's own, not within Owner_Console, it works fine and brings up a new job record with the APN that was listed in the Console_props form.
However, when I have Owner_Console open and Console_props is displayed within that as a subform, then clicking that same button that worked a moment ago, comes back with an error that it can't find the Console_props form, and then brings up the Worksheet form as a blank in all fields (instead of bringing over the APN).
I guess what I am doing wrong is that the referencing of the Console_props needs to be coded differently when it is embedded as a subform?
Jack Cowley
Welcome to Utter Access Forums!!!
et me see if I understand what you want to do... You want to open your form "Worksheet from Console" to a new record and you want the APN number carried forward and used in a control on form "Worksheet from Console". If that is correct you can try this code in your command button on the subform:
DoCmd.OpenForm "Worksheet from Console", , acNewRec, , , Me.APN
In the On Open event of "Worksheet from Console":
If Not IsNull(OpenArgs) Then
Me.NameOfControlToGetAPN = OpenArgs
End If
I have assumed that on the subform you have a control called APN and it has the number, or whatever, that you want to carry over. If APN is ONLY on the main form then:
DoCmd.OpenForm "Worksheet from Console", , acNewRec, , , [Forms]![NameOfMainForm]![APN]
Let me know if I understood what you want to do and if this works for you.
Jack
scromwell
Yes, you understand my objective.
I have an Owner_Console, which has a subform called Console_props, which has a button to pop-up a Worksheets from Console form. I want that Worksheets form to open a new record, but carry over the APN from the Console_props subform.
My original code works exactly as I want it to when I have the Console_props form open by itself. It stops working when I am pressing the button on the Console_props form as a subform within Owner_Console. That is why I thought it was a subform referencing problem.
Otried both methods of coding the command button that you suggested above, and changed the On Open event to the above (changing the control name), and that produced Type Mismatch errors whether the button was pressed from the form directly or as a subform.
Just to be clear. APN is the control name in both the Console_props subform and the Worksheets from Console form. My original code only DOES NOT work when I have Console_props as a subform within Owner_Console. The Owner_Console does not have an APN control. My original code works perfectly when I test it directly from the Console_props form, it only has the problem when it is a subform within Owner_Console, and I do need to access it that way, because they are looking at one owner with several properties, and then entering a worksheet for the specific property they want.
scromwell
Original code from the button on the Console_props form
Private Sub WSfromConsole_Click()
On Error GoTo Err_WSfromConsole_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Worksheets from Console"

stLinkCriteria = "[APN]=" & "'" & Me![APN] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_WSfromConsole_Click:
Exit Sub
Err_WSfromConsole_Click:
MsgBox Err.Description
Resume Exit_WSfromConsole_Click

End Sub
This is the original code in the "Worksheets from Console" form
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
APN = [Forms]![Console_props]![APN]
End Sub
When Console_props is open by itself, I click the button and the Worksheet pops up with a new record and has carried over the APN perfectly. Once the Console_props is inserted as a subform within Owner_Console, pressing the button brings back the error that it can't find Console_props referred to in the macro, and then brings up a completely blank copy of the Worksheet.
Hopefully, I've given the info that you need. I really appreciate the help on this. My first major Access project was/is building an entire system for our small business, so I'm constantly learning on the fly frown.gif
Jack Cowley
I'm sorry but I gave you a bad piece of code... I should not alway rely on my memory:
oCmd.OpenForm "NameOfFormToOpen", , , , acFormAdd, , Me.APN
The APN IS a control in the subform so this code, in the button on the suborm should pass the APN to the On Open event of the WorkSheet form:
If Not IsNull(OpenArgs) Then
Me.NameOfControlToGetAPN = OpenArgs
End if
Try that new code above, with my apologies, and see if your WorkSheet form opens to a new record and the APN is in the correct control on the form.
If the APN is NOT where it should be you can put the word Stop before the code in the On Open event and when the form open so will the code editor and the Stop will be highlighted. If you hover the cursor over OpenArgs it will tell you what value is in that variable. If it is not what you expect then the Me.APN at the end of the DoCmd code may not be getting the data from the correct record....
Let me know if this solves the problem.
Jack
scromwell
Tried that and now the error is simply, "OpenForm action was canceled".
It's driving me insane that the original works as long as the Console_props isn't a subform.
Jack Cowley
Is there a possibility that you can post your db, without sensitive data? The max size, zipped, that you can post is 500k. If you can compact and repair the db then zip it and let us know what forms to look at we will take a look and see if we can sort it out. NO guarantees, of course, but we can give it a try....
ack
scromwell
Hmm.. the cleanest copy is running about 11MB right now. I'd have to create a dummy copy with bogus data for testing and see what size it was. Definitely won't be able to get to it today.
So, it's not some simple error I'm making with referencing the subform?
Jack Cowley
It could be something simple.
If you are getting the error about OpenForm cancelled then I wonder if you have some other code that is running or that you run when the worksheet is open. If you put this code in the subform:
DoCmd.OpenForm "NameOfFormToOpen"
Does your form open without any problems? If you use:
DoCmd.OpenForm "NameOfFormToOpen", , , , acFormAdd
Does the form open in data entry mode without any problems?
Just trying the old trial and error trick...
Jack
scromwell
That works fine to bring up a blank new record, how do we get this + the APN from the Console_props subform?
scromwell
Tried using that, plus added the following
Private Sub WSfromConsole_Click()
On Error GoTo Err_WSfromConsole_Click
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , "[APN]=" & "'" & Me![APN] & "'"
Exit_WSfromConsole_Click:
Exit Sub
Err_WSfromConsole_Click:
MsgBox Err.Description
Resume Exit_WSfromConsole_Click

End Sub
With this in the Worksheets from Console form
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(OpenArgs) Then
Me.APN = OpenArgs
End If
End Sub
And it gave me an error that I could not assign a value to this object.
Jack Cowley
Some progress! Now if you do this:

DoCmd.OpenForm "NameOfFormToOpen", , , , acFormAdd, , Me.APN

Now put this on in the On Open event. The code in red will open the Code Editor when the form executes the On Open event. The word Stop will be black in your code:

<font color="red">Stop <!--color--></font>
If Not IsNull(OpenArgs) Then
Me.NameOfControlToGetAPN = OpenArgs
End if

When the code editor click on the Step Into icon on the icon bar and when the second line is highlighted put the cursor on OpenArgs and wait a moment. You should see a little window by the cursor and it should say OpenArgs = xxx. Where see the xxx you should see the value of APN from your subform. What do you see?

Jack
Edited by: Jack Cowley on Thu Apr 27 19:26:25 EDT 2006.
Jack Cowley
Disregard my last reply -
If you cannot assign a value then that is the problem. What kind of field is APN in the Worksheet table?
Also, as APN text?
Jack
scromwell
Hovering on Me.APN, it shows Me.APN = Null
Hovering on OpenArgs, it shows OpenArgs = 678-071-16 (which is record we want)
This is with the following in the subform, without the ,,Me.APN at the end, the form just comes up with a blank record.
Private Sub WSfromConsole_Click()
On Error GoTo Err_WSfromConsole_Click
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , Me.APN
Exit_WSfromConsole_Click:
Exit Sub
Err_WSfromConsole_Click:
MsgBox Err.Description
Resume Exit_WSfromConsole_Click

End Sub
When I take the Stop out of the code, it just goes right back to that section with the debugger, highlighting that line Me.APN = OpenArgs
scromwell
APN is text in both the Jobs table (where the worksheet data is stored) and the Properties table (where the Console_props subform is pulling data from)
Jack Cowley
Ah, some progress! If OpenArgs in the On Open event of your Worksheet form is showing 678-071-16 then that means everything is working EXCEPT being able to assign that value to the APN control on your WorkSheet form. You stated that you got the error that you could not assign a value to APN in the On Open event of the WorkSheet form. Now we need to figure out why it can't be assigned.... Is your WorkSheet form based on a query? If so, is the query updateable? Is APN in the table that the WorkSheet form is based on a Text field?
After you remove the stop from the On Open event you might want to do a compact and repair and/or a Compile of your code, to make sure all is as it should be.
Jack
scromwell
No query, the Worksheet form is based on the Jobs table, where APN is a text field.
Jack Cowley
Hmmm. So for some reason Access does not want to let you assign the APN in OpenArgs. Is APN the foreign key for the Worksheet forms table? Is there anything unique about the APN control on the form?
How that we know that OpenArgs is carrying the data over to the WorkSheet form have you done a compact and repair and tried the command button again? Do you still get the Can't Assign error?
Jack
scromwell
It is the Primary Key in the Jobs table (where the Worksheet form stores data), indexed Duplicates OK.
Yes, I have compact and repaired.
Yes, still getting Run-time error -2147352567 (80020009) You can't assign a value to this object.
and then when I click on the Debug button, it takes me to the Me.APN = OpenArgs line of code. Hovering on Me.APN shows Me.APN = Null and hovering on OpenArgs shows the actual APN number that we want passed. Is the problem because we are using Null in the code, when the field is a text field?
Jack Cowley
The APN is null because of the error you are getting.
If APN is the Primary Key in the table that the WorkSheet form is based on then you cannot assign a value to that field. You can assign a value if APN is the Foreign Key but it sounds like the APN is the primary key based on this statement, "It is the Primary Key in the Jobs table (where the Worksheet form stores data), indexed Duplicates OK."
In your database do you assign the Primary Key (possibly a number or text that someone enters) or are your Primary Keys Autonumber, which are assigned by Access?
Jack
RuralGuy
Jack,
I think the Open event is too early in the process. Try the OnLoad event.
ace
Seems to me it's just a matter of properly referencing the Owner_Console
form now that it is embeded in the subform control. Assuming the subform
control was also named Owner_Console try:
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acNewRec
APN = Forms!Console_props!Owner_Console.Form!APN
End Sub
scromwell
Ace - that is what I figured the problem was, because it works fine when the subform is not embeded. I'm still fairly convinced that this is where my problem lies.
However, I tried your line above and it brough back an error that it couldn't find Console_props. I tried putting the sections in brackets, reversing the order of the Console_props and Owner_Console, also with and without brackets.
It's interesting to note that when I click Debug after the error, and hover over the APN = part of the code, it shows APN = 678-071-16, which is the correct data... so, it's making it over, but erroring out nonetheless.
scromwell
There are three keys in that table, JobID (which is an autonumber), CustID (which is a Number), and APN (which is text).
Why would it not allow me to assign a value coded this way, but it will allow me to do exactly that with the original code I was using, when it was run from the Console_props form (not embeded in Owner_Console)?
scromwell
Ruralguy - I tried that when you first suggested it, but it was having the same issue as OnOpen - working fine when Console_props was on it's own, but giving an error (Console_props not found) when the Console_props form was embeded as a subform in Owner_Console.
RuralGuy
Jack's code for using the OpenArgs argument will work but you can't reference the form objects in the open event. You need to wait until the Load event to move the OpenArgs to the control on the next form. If you want to reference a SubForm on a different open form then you have to go through the MainForm as Ace suggested. The syntax is Forms!MainFormName!SubFormControlName.Form!ControlNameOnTheSubForm
The SubFormControlName defaults to the name of the SubForm it contains but it is not a requirement. The name of the SubFormControl can be different than the SubForm.
ace
I don't like replying when I'm not going to be able to follow up
for hours at a time, but I think If you attach an .mdb file with the
participating forms and tables one of the other two guys could clear this
up for you in about five minutes.
Jack Cowley
RG -
You are dead on! Using the On Open event using OpenArgs works for some things but NOT setting a value. You have to use the OpenArgs code in the On Load event... I should have tested that before continuing with the OP. I will make another post as OpenArgs should do the trick...
Thank you for catching my blunder!!!
Jack
Jack Cowley
scromwell -
This code, AT LONG LAST, should do the trick. Here is the code in your command button on the subform:
CODE
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , "[APN]= '" & Me![APN] & "'"

This code should be in the On Load event of "Worksheets From Console"
CODE
Private Sub Form_Load()
If Not IsNull(OpenArgs) Then
Me.APN = OpenArgs
End If
End Sub

My sincere apologies for not catching my On Load vs On Open evern sooner. Thank you for sticking with us and if this does NOT work please let me know.
Jack
RuralGuy
Hi Jack,
Oseem to remember you helping me a few times. We're just here to resolve the OP's issue and I believe we have it now. I'm also a big fan of OpenArgs which is why I know what event to use. Been there, done that.
Edited by: RuralGuy on Fri Apr 28 12:04:08 EDT 2006.
Jack Cowley
RG -
It never seems to fail that I use the wrong event to use OpenArgs in! I normally try to test even the simplest code, but this time I did not..OBVIOUSLY...or I would have found my error.
Be that as it may, I do appreciate you jumping in and getting me, and the OP, on the right path...at long last. I just hope that I did not scare the OP away with my wrong event....
Thanks again!
Jack
RuralGuy
Jack,
If you keep that up you will get white hair!
Jack Cowley
RG-
OL! You have a point! I'm off to the drug store to buy some 'Hair Color For Old Guys'....
Jack
scromwell
Jack & RG,
Thanks so much for helping me (the OP?) with this!! I wasn't scared off, just had to sleep for a few hours wink.gif
I just tried the newest code and hurrah, no errors!! However, the form was coming up with the APN showing "[APN]= '656-058-06'", so I took bit by bit out of it, until it worked.
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , "" & Me![APN] & ""
Hopefully, as I get better at all this, I'll be able to contribute back to the community here.
Jack Cowley
scromwell -
guess I was destined to drive you mad as I just noticed that my last code is STILL wrong! I used:
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , "[APN]= '" & Me![APN] & "'"
and it should have been:
DoCmd.OpenForm "Worksheets from Console", , , , acFormAdd, , Me![APN]
If your code is working with the surround quotes then you can leave it, but they shouldn't be necessary... I wonder if those are famous last words???
Thank you for sticking with us (me in particular!) and we are very please to have as a memember of the Utter Access community!!!
Jack
ace
is just not true at all.
The op has some other problem going on.
Jack Cowley
Can you please eloborate on the problem and let us know why the suggestions are not true at all? A working solution to the OP's problem would be most appreciated.
ack
ace
/>My comment was about the statement that you can not set a value using the
openargs property in a Form's OnOpen event. I probably mis-interpreted what
you were saying. You can set the value of a variable, but you can not set the
value of a bound control.
Edited by: ace on Fri Apr 28 16:56:38 EDT 2006.
ace
OP = the person who originated the thread (original poster).
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.