Full Version: Should "Called" form refer to "Main" or "SubForm"?
UtterAccess Forums > Microsoft® Access > Access Forms
Dennis222
During execution of the order function, I am calling the add new product form if the product is new.
I have patterned my order function using Northwind with a form/subform combination. During execution, I get error using return button (to Orders).
The name of my ProductID control (in the subform "OrdersSubform") is ProductID.
However, I get message, 'RunTime '2465': Microsoft access can't find the field "ProductID" referred to in your expression. On debug, the highlighted statement is
Forms![Orders]!ProductID.Value = NewProductID
I have another form (different function) that also calls the same form (to add a new product) which works fine, but in that case, the calling form does not have a subform integrated.
I tried referencing the subform name, but I had other problems trying that.
Would appreciate any advice on how to correct my code, shown below:
Private Sub btnReturntoOrders_Click()
'Do these steps only if the Orders form is open
If CurrentProject.AllForms("Orders").IsLoaded Then
'Copy the new product's ProductID, Product Year and Size to variables.
Dim NewProductID As Integer
Dim NewProductYear As Integer
Dim NewProductSize As String
NewProductID = Me.ProductID.Value
NewProductYear = Me.ProductYear.Value
NewProductSize = Me.Size.Value
'Close the frmAddProduct, saving new record.
DoCmd.Close acForm, "frmAddProduct", acSaveYes
'Make sure we're at a new, blank record in Orders Subform
DoCmd.GoToRecord acDataForm, "Orders", acNewRec
'Copy new ProductID into the Orders form's cmbProductID control
Forms![Orders]!ProductID.Value = NewProductID HIGHLIGHTED at break!!!!!
Forms![Orders]!ProductYear.Value = NewProductYear
Forms![Orders]!Size.Value = NewProductSize
'Set cursor at CsePrice upon return to frmAddDscntCorr
Forms![Orders]![CsePrice].SetFocus
End If

Exit_btnReturntoDscntCorr_Click:
Exit Sub
Err_btnReturntoDscntCorr_Click:
MsgBox Err.Description
Resume Exit_btnReturntoDscntCorr_Click

End Sub
Thanks to all for reading this.
Howard
freakazeud
If you call a subform from somewhere different then the mainform the syntax needs to be:
orms!Mainform!Subform1.Form!ControlName
If you call this from the mainform of the subform then it needs to be:
Me!Subform1.Form!ControlName.Enabled
HTH
Good luck
Dennis222
Thank you for your response.
o test my understanding of your post, I changed the 1st of the three control references from:
Forms![Orders]!ProductID.Value = NewProductID to
Forms![Orders]![OrdersSubform]!ProductID.Value = NewProductID
Got same error (couldn't find, etc.) Highlighted same VBA statement (one I changed). This is only change I made.
I did I code it correctly?
Howard
freakazeud
No...it should be:
orms![Orders]![OrdersSubform].Form!ProductID = Me.NewProductID
This would mean that you set the productID in the subform located in your orders main form to the value of the NewProductID on the current form.
HTH
Good luck
Dennis222
Changed all 3 lines of code to reference controls as you suggested...also the 4th control (set.focus).
This goround, I got a compile error:
Method or data member not found. Microsoft had selected NewProductID following the Me. of the changed code.
Also noticed that as I was coding, suggested popup values didn't include NewProductID, but I hand coded it anyway.
Also the Private Sub statement was yellow highlighted.
What do you think?
I have to run to work now and will have to look at my mail later this AM.
THANK YOU THANK YOU for this dialogue!
Howard
freakazeud
As I mentioned...Me.NewProductID would refer to the NewProductID control on the current form. If you are not executing this from the form which holds that control then you need to reference the control accordingly.
HTH
Good luck
sredworb
Hi Howard,
heck this out, I found this last week.
Jerry
freakazeud
This information can be found here!
jmcwk
Jerry,Howard you may find the attached useful as well Created by Roger Carlson
Dennis222
Bullseye!!!!!!!!
kind of...
1) Returned to subform part of form properly!
2) Populated all 3 controls properly!
3) Focus set at 4th control properly!
However, upon return,
1) all of the earlier info keyed into the Main form is gone (blank fields);
2) an order record has been added to the Orders table with initially keyed in data including invoice/order number;
3) the product I just entered was added in the Products table;
4) the 4th control (at which I requested focus) is highlighted and I think the cursor is sitting there;
5) the control will not accept information from the keyboard;
6) if I simply hit enter, I return to the Add Product form from which I just came (with blank controls)
7) if instead, I attempt to mouse the cursor to a different field, I get a compile error "Variable Not Defined" and
find myself in the following VBA code with acCurViewDesign selected.
Option Compare Database
Option Explicit
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If

End Function
How could such a miserable set of circumstances accompany such outstanding results from the earlier change in the RETURN BUTTON?
So, I may not be quite there yet and I have no idea what happened. Any ideas from (I hope will still talk to me)
kind people who have guided me so far......will be much, much appreciated.
Howard
Dennis222
Addendum!
Is I try to figure this thing out, I discovered that I'm getting same error if I simply execute the Orders form without going through the ADD Product path. I.E., when I tab through to the 1st field in the SubForm (detail items re: products), I get the error as soon as I select a product from the combo list.
So, looks like there may not be any relationship to the linking process????...rather just the next step in debugging the form design.
Would appreciate any diagnostic direction.
Howard
sredworb
Hi Dennis,
anta strip your db down so everyone can work on a solution that way..
If you're not sure how to attach then click here.
Dennis222
sredworb...(et al)
Have pared my file down to 494k zipped, but getting cannot proceed message when I attach it. Am I missing something else?
Thank you,
Howard (Dennis is middle name)
sredworb
Hi Howard,
Try trimming it down a little more and then attempt to attach it.
See what happens..
From Jerry , (et al) at UA
Dennis222
Here's another attempt...
.
Dennis222
Jerry et Al, I really chopped...Hope the important forms, queries and tables still OK.
H.
Dennis222
Think a lightbulb just turned on.
My "Add Product" button is on the Main Form (whereas the products are on the subform...my order/suborder forms are a clone of Northwind).
I have patched up my code such that now, my only symptoms seem to be that, upon return to the order/subform form from the add product form (after successfully adding a new product AND populating the correct fields in the subform prior to return AND even placing the cursor at the desired control on return), my errors are:
The controls in the main form are now empty (blank);
The control I set focus on will not accept keyed data;
When I mouseclick another control, I get a request to select a customer (the control is now empty).
I tried to place an "add product" button on the subform, but it doesn't appear in form view when the order form is opened; so I couldn't try it. Is that because the subform is shown in datasheet view?
If my problem has to do with the location of the "add Product" button, could that error in itself be causing ACCESS to literally STOP on the control I set focus on AND blank out the main form controls or would those problems be from something else? I think my return code is OK, because I do return to intended control in the subform.
I am really in new territory here with this "add product" function. I notice that NorthWind doesn't include that function in the sample. Is there an example someplace illustrating VBA associated with linking from a subform.
Please help....
Howard
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.