Solved: open main form by sending info to the subform!

[ailyn]

me again!

Thanks to Geekgirlau I have discovered the marvels of using the wizard for making simple code. It solved so many problems that the macro couldn't solve!

But now I have a complicated query that the wizard can't solve. I have a form with a subform that I open whenever I make an order from products, but it doesn't show the product because it is in the subform!

Here is a better explanation (all my fields in the form have the original name of the table values):
FormNewOrder:
orderId
SellerId
CustomerId
VAT
Discount
Charges
Total (unbound Sum)

Subform:
orderdetailsId
orderId(bound it to the FormNewOrders)
productId(see FormProducts)
location (seeFormProducts)
ref
motorId
price
extras
...
-------------- [VBA][/VBA] [VBA][/VBA]
FormProducts:
ProductId
location
ref
...

This is the code from the FormProducts (made thanks to the wizard):
[VBA]
Private Sub OpnFormNewOrder_Click()
On Error GoTo Err_OpnFormNewOrder_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FormNewOrder"

stLinkCriteria = "[ProductId]=" & Me![ProductId]
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd
DoCmd.Close acForm, "FormProducts"

Exit_OpnFormNewOrder_Click:
Exit Sub

Err_OpnFormNewOrder_Click:
MsgBox Err.Description
Resume Exit_OpnFormNewOrder_Click

End Sub
[/VBA]

I understand why it doesn't work, but how can I make it work?

[chocobochick]

You've given us the code and the field names, but you were a little unclear as to what you wanted the opened form to do.

Judging by the form's name, I'm assuming you want to open to a blank record for the user to enter new data, possibly while setting the form's ProductId field to match FormProduct's value by default. To fix your current code, you need to remove the criteria from the OpenForm command, like this:
[VBA]
' stLinkCriteria = "ProductId]=" & Me![ProductId] - You can delete this line.
DoCmd.OpenForm stDocName, , , , acFormAdd ' Remove reference to stLinkCriteria
Forms(stDocName).ProductId = Me.ProductId ' Add this line to initialize value of ProductId
DoCmd.Close acForm, "FormProducts"
[/VBA]

[ailyn]

sorry, but it doesn't work. And yes, you got it right. I just wanted the form to load the data from the selected products.

I also have the problem that whenever I try to make an order from the form itself (FormNewOrders) I cannot load the data inside the subform. It shows an error of automatisation or something like that. What can I do?

[chocobochick]

Sorry, I forgot about the subform. You can set the values like this:
[VBA]
Forms!FormNewOrder.NameOfSubformControl!ProductId = Me.ProductId
[/VBA]

Note that NameOfSubformControl is the name that appears when you open frmNewOrder in design view, right-click on one of the outside borders of the subform, select Properties (Properties dialog should be titled Subform/Subreport), and open the Other tab. The default naming scheme for subforms will look something like "Child1."

[ailyn]

pfff, sorry but it doesn't work either. It says thta it cannot find the value because it doesn't exist. The thing is the subform has more than one productId:

Subform:
orderdetailsId
orderId(bound it to the FormNewOrders)
orderdetails.productId(see FormProducts)
motorId
price
extras
products.productId (to show other items from the Products table, here under)
location (seeFormProducts)
ref(see FormProducts)
...

I put this:
[VBA]
DoCmd.OpenForm stDocName, , , , acFormAdd ' Remove reference to stLinkCriteria
Forms!NewOrders.Subform!Products.ProductId = Me.ProductId
DoCmd.Close acForm, "Products"
[/VBA]

[chocobochick]

First of all, I believe you misidentified your main form in the code you posted, as you've stated your form's name is FormNewOrders, not NewOrders.

Secondly, what are "orderdetails" and "products" in relation to productId? An option group? Another nested subform? In your original post, you specified that all your form controls carried the same name as the original table values. If that's the case, how could you have more than one ProductId in the table your subform is based upon?

[ailyn]

Sorry, but I just wanted to make things easier. The name of the forms is NordersMaken for the main form and NordersDetails for the subform.

To better explain the working of my forms check the relationships that I attached to this post. The forms are pretty simple:

From Form "Products" (with all fields from tblProducts) I click to make a new order and open "NordersMaken" that has all fields of tblOrders.
The subform of NordersMaken is NordersDetails that has tblOrderDetails as the main table that links it to the main form by OrderDetails.OrderId. It also contains all the fields from tblProducts and all the fields from tblBlowers.

I hope that was specific enough.I'm sorry I'm such a mess explaining this things.Sorry!

[chocobochick]

Is NordersDetails the name of the control that houses the subform, or just the name of the form as it appears in the Forms section of the Database window? Use the steps I mentioned above about NameOfFormControl to verify it. If the control is named NordersDetails, and that subform has a textbox similarly named ProductId, then you would use:
[VBA]
Forms!NordersMaken.NordersDetails!ProductId = Me.ProductId
[/VBA]

If that still doesn't work, let me know. I'll have something experimental to try.

[ailyn]

No luck. It doesn't work. & 'NordersDetails' is the name of the control as well as the name of the subform on the forms section of the db. I always try to make them identical to avoid problems.
I also tried this (although I knew it would not work either):
[VBA]
Forms!NordersMaken.NordersDetails!Order_Details.ProductId = Me.ProductId
[/VBA]

when the form opens it says that it could not find the product.Id (or the Order_Details.ProductId).

I do not know what is you other idea, but I've read somewhere that it could be useful to use OpenArgs, something like parent.OpenArgs maybe? I don't know how to develop a code for this. What do you think?

[chocobochick]

Hmm... to do anything else, I'd really need to double-check information about the controls on your form. Ideally, it'd be best to have an example of the database attached to the thread, but as I recall you ran into some difficulties attempting that earlier. So instead, I created a script you can run that will put the necessary information into a text file. Just follow these steps:

1. In your Access database, open the VB Editor.
2. Select Insert / Module from the menu.
3. Copy and Paste the following code into your new module.
[VBA]
Option Compare Database
Option Explicit

Const frmName As String = "NordersMaken"
Const outputPath As String = "C:\controls.txt"

Public Sub getControlData()
Dim fs As Object
Dim ts As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set ts = fs.CreateTextFile(outputPath)
ts.WriteLine "Name, Type, Source, Parent"
DoCmd.OpenForm frmName
extractData Forms(frmName), ts
ts.Close
Set ts = Nothing
Set fs = Nothing
End Sub

Private Sub extractData(o As Object, ts As Object, Optional p As String = "")
Dim c As Control
For Each c In o.Controls
ts.WriteLine getProperty(c, "Name") & ", " & getProperty(c, "ControlType") & ", " & _
getProperty(c, "ControlSource") & ", " & p
If StrComp(getProperty(c, "ControlType"), "112") = 0 Then
extractData c, ts, c.Name
End If
Next
End Sub

Private Function getProperty(c As Control, s As String) As String
On Error GoTo PropertyError
If s = "Name" Then
getProperty = c.Name
ElseIf s = "ControlType" Then
getProperty = c.ControlType
ElseIf s = "ControlSource" Then
getProperty = c.ControlSource
End If
Exit Function
PropertyError:
getProperty = ""
End Function
[/VBA]
4. Optional step: If you want the text file to be created someplace other than the C: drive, change the value of outputPath (located at the top of the code) to the path and filename you wish to use.
5. Select Run / Run Sub/UserForm from the menu. If prompted, select "getControlData" and click the Run button.
6. If the program runs without errors, it should create a new text file (located in the main directory of your C: drive unless you changed it above). Open that file and copy/paste its contents to here.

If the program does create an error, let me know what the error message says.

[ailyn]

Wow, thanks. Here is the result:

Name, Type, Source, Parent
OrderId, 109, Orders.OrderId,
OrderId_Bijschrift, 100, ,
Afbeelding29, 103, ,
Title, 100, ,
OrderDate, 109, OrderDate,
OrderDate_Bijschrift, 100, ,
SellerId, 111, SellerId,
SellerId_Bijschrift, 100, ,
TransportZizo, 106, TransportZizo,
TransportZizo_Bijschrift, 100, ,
TransportFirma, 109, TransportFirma,
TransportFirma_Bijschrift, 100, ,
Expected, 109, Expected,
Expected_Bijschrift, 100, ,
Coments, 109, Coments,
Coments_Bijschrift, 100, ,
FreightCharges, 109, FreightCharges,
FreightCharges_Bijschrift, 100, ,
Discount, 109, Discount,
Discount_Bijschrift, 100, ,
VAT, 109, VAT,
VAT_Bijschrift, 100, ,
Paid, 106, Paid,
Paid_Bijschrift, 100, ,
NordersDetails, 112, ,
OrderId, 111, OrderId, NordersDetails
OrderId_Bijschrift, 100, , NordersDetails
txtImageName, 109, txtImageName, NordersDetails
Bijschrift18, 100, , NordersDetails
ImageFrame, 103, , NordersDetails
BlowerNr, 109, Order Details.BlowerId, NordersDetails
txtImageNote, 109, , NordersDetails
Available, 106, Available, NordersDetails
Bijschrift25, 100, , NordersDetails
Avail, 106, Avail, NordersDetails
Bijschrift26, 100, , NordersDetails
TypeId, 111, TypeId, NordersDetails
Bijschrift27, 100, , NordersDetails
BlowerType, 111, BlowerType, NordersDetails
Bijschrift28, 100, , NordersDetails
Reference, 109, Reference, NordersDetails
Bijschrift29, 100, , NordersDetails
Serial, 109, Order Details.ProductId, NordersDetails
Bijschrift30, 100, , NordersDetails
LocationId, 111, LocationId, NordersDetails
Bijschrift31, 100, , NordersDetails
Products.ProductId, 109, Products.ProductId, NordersDetails
Price, 109, Price, NordersDetails
Price_Bijschrift, 100, , NordersDetails
Extras, 109, Extras, NordersDetails
Extras_Bijschrift, 100, , NordersDetails
ExtrasDescrip, 109, ExtrasDescrip, NordersDetails
ExtrasDescrip_Bijschrift, 100, , NordersDetails
Subtotal, 109, =Sum(Nz([Price])+Nz([Extras])), NordersDetails
Bijschrift21, 100, , NordersDetails
volgende, 104, , NordersDetails
vorige, 104, , NordersDetails
Subtotal, 109, =NordersDetails.Form!Subtotal,
Bijschrift25, 100, ,
Total, 109, =(([xrttl])+Nz([FreightCharges])),
Bijschrift27, 100, ,
xrttl, 109, =((([Subtotal])-(([Subtotal])*Nz([Discount])))+((([Subtotal])-(([Subtotal])*Nz([Discount])))*Nz([VAT]))),
Vak42, 101, ,
Bijschrift43, 100, ,
Lijn44, 102, ,
Bijschrift45, 100, ,
View All Products, 104, ,
Bijschrift1, 100, ,
View Sold Products, 104, ,
Bijschrift3, 100, ,
View NOT Sold Products, 104, ,
Bijschrift5, 100, ,
View Blowers Available, 104, ,
Bijschrift7, 100, ,
View Sellers, 104, ,
Bijschrift9, 100, ,
View All Reports, 104, ,
Bijschrift11, 100, ,
Bijschrift17, 100, ,
Help, 104, ,
View Orders, 104, ,
Bijschrift51, 100, ,
EditCustomers, 100, ,
EditSellers, 100, ,
CustomerIdctrl, 111, CustomerId,
Bijschrift34, 100, ,
chkBox, 106, ,
Bijschrift42, 100, ,
SvOrder, 104, ,
NwOrder, 104, ,
DltOrder, 104, ,
Bijschrift40, 100, ,
Bijschrift41, 100, ,
Logo, 103, ,
Bijschrift72, 100, ,

[chocobochick]

That helps a lot! Thanks!

According to this data, there are several controls pointing to fields with "." within the control source (Data tab in control's Properties), including "Products.ProductId" in the NorderDetails form. Was there a reason for this? If the form OrdersDetails uses the table [Order Details] as its record source, that table has its own field simply named "ProductId". Therefore, the control source should typically just read "ProductId".

If your intention was to grab the ProductId field from the Products table instead, I don't think you can actually use it this way, as the Products table is not part of the record source you're using. In fact, I'm not sure why you'd need the version of ProductId from the Products table if ProductId from the [Order Details] table shares a relationship with that value. If you have some records entered in the [Order Details] table, try opening the form NordersDetails just by itself, with no filters, and browse through the records. If the ProductId control comes up with "#Name?", the control source is configured incorrectly. The same might be true of OrderId in NordersMaken and BlowerId in NordersDetails, so make sure the controls are actually storing data where you want them to.

Regardless of where the data above is pointed, putting a period in the name of a control unnecessarily complicates things in VB Code. If you have a textbox named "Products.ProductId" and try to reference it in code such as "Products.ProductId = 5", Visual Basic is looking for a control named Products with a property named ProductId. But since a control named Products doesn't exist, you get an error. I would rename every control containing a period to something simpler. Oddly enough, the control you've apparantly been trying to access, "Order_Details.ProductId", is actually named "Serial" right now, so the proper code for what we tried earlier should have been:
[VBA]
Forms!NordersMaken.NordersDetails!Serial = Me.ProductId
[/VBA]

[ailyn]

Thaanks!
It works!
The problem from the beginning was that I was trying to load the ProductId form tblProducts in the form Products into the ProductId from tblProducts in the form NorderDetails. I thought it could be done that way but it can't. Also I never saw the conflict with the names an the values, those dots are tricky ^.^
The form NordersMaken and its subform work perfectly. They load all the correct info from previous orders. I needed the info from tblProducts into the subform to be able to show the location and other stuff in there too. That is why there were more than one ProductId in there. Now I see that for new orders I have to select from the controls that have the values of tblorderdetails. Because the same happens with blowersId.
Still is there not a way to allow me to select values from tblProducts in the subform and make the rest of the info for the product load in the other fields?

[chocobochick]

It would be possible by using the DLookup command somewhat like this:
[VBA]
Forms!NordersMaken.NordersDetails!LocationId = DLookup("[LocationId]","Products","ProductId = " & Me.ProductId)
[/VBA]

This is assuming your ProductId field holds a numerical value; you will have to use extra quotes somehow if it's a text value. Also, you would need to duplicate this line of code in ProductId's Exit event if you want the LocationId field to be updated if the user manually changes the ProductId. As you can see, the DLookup command can be awkward and complicated, not to mention slower to run on the machine.

As a personal design preference, I would probably avoid including those extra fields altogether, at least for your data entry form. The NordersMaken form and its NordersDetails subform are there to add new records to the Orders and Order Details tables respectively. But only a single field (ProductId) on the Order Details table has anything to do with the Products table, and so ProductId should be the only thing the user needs to enter. If you wanted to load extra information from the Products table simply for confirmation purposes (assuring the user he has selected the proper ID), the simplest method of achieving this is by using a multicolumn combo box as the control for the ProductId field. You can design the combo box to display the fields you want from the Products table and use the ProductId field as the bound column.

For the purposes of viewing previous orders, I would create a different form entirely. You can base the form off a query (using the relationships between all three tables) and get all the Detail and Product information for each order perfectly with little fuss, just as if everything was part of a single record in one big mega-table.

[ailyn]

Thanks for the explanation.

Right now I managed to make a filter in the controls for orderdetails_productId and orderdetails_blowerId to avoid geting values that are not available. I also clicked in the Properties option of the controls proceding from tblProducts and tblBlowers to be non integrated (I hope the term is right my access is in Dutch) so that the user won't select those fields and won't get the Jet error.


But the idea is that when you open the NordersMaken in blank (with a new record) and you select type or reference (we could convert it to the multicolumncombobox you mentioned) the rest of the info will load and the selected ProductId will get into orderdetails_producId. Would that be very complicated?

[chocobochick]

If your NorderDetails subform uses [Order Details] as its control source, and a control uses "ProductId" (not "Products.ProductId" or "Order_Details.ProductId") as its control source, then anything entered or selected in the control will be saved to the [Order Details] table when the record is updated. If you set the value of the control like we did in the above code, it should work.

[ailyn]

Thanks but I can't make it work. It seems too complicated for me I tried the Dlookup with the type but the value just disappears. I think I'll leave it like this for now.
Thank you very much for your help!