Subform how to

[scrawny]

Hi!

I am trying to create a form with an embedded subform and have hit a
bit of a snag...

Essentially the form is a quote form with a quote header section and
the subform as the quote lines.

My only difficulty is that when a line is keyed in to the subform, the
price needs to calculate based on a discount figure in the header
section combined with product weight & qty in the lines. This price
needs to end up in the quote lines table.

The discount can also switch between 2 different types of discount
structures (essentially: set rate OR % discount)

Can a subform see the controls in it's parent form?
If so, how do I code so that if/when the radio button is clicked to
switch between a set rate and a % discount, it automatically updates
the prices of all lines?

 

[Allen Browne]

A subform can see the value of a control on the parent form.
For example, if the main form is Form1, and the text box is Text99, you
could use:
=[Form].[Parent]![Text99]
or:
=Forms!Form1!Text99

A better solution might be to create a query that includes both the main
form's table and the subform's table, and use it as the RecordSource of the
subform. This way you can directly refer to the field from the main form's
table in the subform.

 

[scrawny]

A subform can see the value of a control on the parent form.
For example, if the main form is Form1, and the text box is Text99, you
could use:
    =[Form].[Parent]![Text99]
or:
    =Forms!Form1!Text99

A better solution might be to create a query that includes both the main
form's table and the subform's table, and use it as the RecordSource of the
subform. This way you can directly refer to the field from the main form's
table in the subform.

Hey! You're from Perth too! Lovely place that is. I miss it (moved to
banana bending land a few years ago).
You just gave me an idea, I could have a module run on a certain event
that checks for the existence of line records and update the pricing
accordingly (because the pricing conditions can be set before or after
the line records are entered)...
Then, as lines are keyed in I could update the pricing again on
(perhaps) the BeforeUpdate event.
I guess the module might look something like this:

Dim dbQuote As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set dbQuote = CurrentDb
If Me.CBO_Section.Value > 0 Then
sSQL = "SELECT QuoteID from Quote_Lines WHERE QuoteID = '" &
Forms!Form1!TXT_QuoteID.Value & "';"
Set rs = dbQuote.OpenRecordset(sSQL)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
'I'm typing this off the top of my head... correct me if
I'm wrong
sSQL = "UPDATE Quote_Lines SET price = [Weight * Quantity
* " & TXT_Discount & ]" WHERE QuoteID = '" & Forms!Form1!
TXT_QuoteID.Value & "';"
dbQuote.Docmd sSQL '??? Now my mind is going blank and I
can't remember how to execute SQL statements
End If
End If
rs.Close
dbQuote.Close

Any other suggestions would be greatly appreciated.

 

[Allen Browne]

Here's an example of Execute:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

If at all possible, I would try to avoid storing this dependent data.
Instead, calculate on the fly, so you don't have the maintenance nightmare
you try trying to solve here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

A subform can see the value of a control on the parent form.
For example, if the main form is Form1, and the text box is Text99, you
could use:
=[Form].[Parent]![Text99]
or:
=Forms!Form1!Text99

A better solution might be to create a query that includes both the main
form's table and the subform's table, and use it as the RecordSource of
the
subform. This way you can directly refer to the field from the main
form's
table in the subform.

Hey! You're from Perth too! Lovely place that is. I miss it (moved to
banana bending land a few years ago).
You just gave me an idea, I could have a module run on a certain event
that checks for the existence of line records and update the pricing
accordingly (because the pricing conditions can be set before or after
the line records are entered)...
Then, as lines are keyed in I could update the pricing again on
(perhaps) the BeforeUpdate event.
I guess the module might look something like this:

Dim dbQuote As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String

Set dbQuote = CurrentDb
If Me.CBO_Section.Value > 0 Then
sSQL = "SELECT QuoteID from Quote_Lines WHERE QuoteID = '" &
Forms!Form1!TXT_QuoteID.Value & "';"
Set rs = dbQuote.OpenRecordset(sSQL)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
'I'm typing this off the top of my head... correct me if
I'm wrong
sSQL = "UPDATE Quote_Lines SET price = [Weight * Quantity
* " & TXT_Discount & ]" WHERE QuoteID = '" & Forms!Form1!
TXT_QuoteID.Value & "';"
dbQuote.Docmd sSQL '??? Now my mind is going blank and I
can't remember how to execute SQL statements
End If
End If
rs.Close
dbQuote.Close

Any other suggestions would be greatly appreciated.