Updating SubForms

[Guest]

I am trying to get the correct syntax for allowing a SubForm to update in
response to a Combo box control update.

I am using the ComboBox_AfterUpdate Event, and I am building a Query String
which references the ComboBox value (i.e. SELECT Field1, Field2 FROM
TableName WHERE Field3.TableName = Forms!SubFormName!NameOfComboBoxControl).

I then set the query string to the SourceObject of the SubForm and Call a
Requery Event for the SubForm.

My Code looks something like this.....

Private Sub Combo0_AfterUpdate()
Dim SQLCommand As String
SQLCommand = "SELECT PurchaseOrderDetails.ProductID,
PurchaseOrderDetails.ProductQuantity FROM PurchaseOrderInvoice INNER JOIN
PurchaseOrderDetails ON PurchaseOrderInvoice.PurchaseOrderNumber =
PurchaseOrderDetails.PurchaseOrderNumber WHERE
(((PurchaseOrderInvoice.PurchaseOrderNumber)=Forms!.Form3!.Combo0));"
Me.SubForm.SourceObject = SQLCommand
Me.SubForm.Requery
End Sub

I get a range of errors when I try and run this code, and I am wondering if
there is a specific syntax/ methodology for updating sub forms on a requery,
and if there is a limit to the length of the query string which can be passed
to the SubForm.SourceObject Property.

Can anyone elaborate....

 

[Steve Schapel]

Potatoman,

There are a number of problems with your code.

First, there are !s and .s in the reference to the Combo0. The .s
should not be there.

In any case, this can be referenced as a value from the form that the
code is being called from, by use of the Me keyword.

But the main problem is that the SourceObject is not the appropriate
property to your purpose. The SourceObject would be the name of a form.
I think you need the RecordSource property here.

By the way, the Requery method will not achieve anything.

So, try like this...

Private Sub Combo0_AfterUpdate()
Dim SQLCommand As String
SQLCommand = "SELECT PurchaseOrderDetails.ProductID,
PurchaseOrderDetails.ProductQuantity" & _
" FROM PurchaseOrderInvoice INNER JOIN
PurchaseOrderDetails ON PurchaseOrderInvoice.PurchaseOrderNumber =
PurchaseOrderDetails.PurchaseOrderNumber" & _
" WHERE
PurchaseOrderInvoice.PurchaseOrderNumber=" & Me.Combo0
Me.SubForm.Form.RecordSource = SQLCommand
End Sub

 

[Guest]

Thanks for that, it was most helpful. The code works with and allows the
subform to update based on the combo box value. Is there any way to set a
schema for the subform table to accept the result from my SQLCommand.

 

[Steve Schapel]

Potatoman,

Sorry, I can't quite grasp your meaning here. Can you give me an
example of what you want to happen?

 

[Guest]

thanks very much for your help. I rejigged the query which gave me the
'structure' of the output table I required when adding the SubForm control,
used your code and bam it works perfectly.

I must say these technical chat groups are a fantastic resource..