Update SubForm

  • Thread starter Thread starter Ginger
  • Start date Start date
G

Ginger

I have a form and a subform. The form shows events, and the
subform shows event expenses with an inserted subtable for
expandable line items for each record in the subform. The
relationship between the form and subform is "EventID".

If there are no expenses for that event, the subform should
be blank.

I need to be able to update the subform, when the form
opens and when the textbox for the "EventID" changes.

I have set up the following code: but even when I go to a
record that I know has event expenses, the subform shows no
data.

Here is the code:

Public Sub UpdateSubForm()

Dim strSQL As String

strSQL = "Select DISTINCTROW
tblExpenses.ExpenseSummaryID, tblExpenses.EventID,
Sum(tblExpenses.Quantity) AS [Sum Of Quantity],
Sum(tblExpenses.Cost) AS [Sum Of Cost]"
FROM tblExpenses
WHERE [tblExpenses.EventID] =
[Forms]![frmEventSummary]![txtEventID].[AfterUpdate]
GROUP BY(tblExpenses.ExpenseSummaryID),
(tblExpenses.EventID)

'set the subform to strSQL
subfrmEventExp.Form.RecordSource = strSQL
subfrmEventExp.Requery

End Sub

Thank you in advance for your assistance.

Ginger
 
Ginger,

There are a few points that arise from your post.

First of all, since you are not trying to use varying recordsources
for different circumstances, there is no reason at all to use code to
assign the subform's recordsource. Just hardwire it in.

Even if it was a good idea to set the recordsource in code, there
would be no need for the Requery line, which would achieve nothing.

Thirdly, your WHERE clause is not correct.
WHERE [tblExpenses.EventID] =
[Forms]![frmEventSummary]![txtEventID].[AfterUpdate]
will not work, because AfterUpdate is an event property of the
textbox, and does not have a value. Plus there is an error with the
placement of the []s. I think what you mean is
.... WHERE [EventID]=" & Me![txtEventID]

Possibly, you were thinking that the code should run on the
AfterUpdate event of one of the controls on the main form?

Fourthly, I am not sure without knowing more about the data and the
forms, but I feel doubtful that Grouping By the ExpenseSummaryID field
is correct?

But in any case, my first comment was my main one. Scrap the code
stuff, make a query based on your tblExpenses table, and enter and
save the name of this query in the design view as the recordsource of
the subform. As long as the subform's LinkChildFields and
LinkMasterFields properties are set to EventID, which you implied was
already taken care of, all should be sweet.

- Steve Schapel, Microsoft Access MVP
 
Thank you very much, Steve

Ginger

-----Original Message-----
Ginger,

There are a few points that arise from your post.

First of all, since you are not trying to use varying recordsources
for different circumstances, there is no reason at all to use code to
assign the subform's recordsource. Just hardwire it in.

Even if it was a good idea to set the recordsource in code, there
would be no need for the Requery line, which would achieve nothing.

Thirdly, your WHERE clause is not correct.
WHERE [tblExpenses.EventID] =
[Forms]![frmEventSummary]![txtEventID].[AfterUpdate]
will not work, because AfterUpdate is an event property of the
textbox, and does not have a value. Plus there is an error with the
placement of the []s. I think what you mean is
.... WHERE [EventID]=" & Me![txtEventID]

Possibly, you were thinking that the code should run on the
AfterUpdate event of one of the controls on the main form?

Fourthly, I am not sure without knowing more about the data and the
forms, but I feel doubtful that Grouping By the ExpenseSummaryID field
is correct?

But in any case, my first comment was my main one. Scrap the code
stuff, make a query based on your tblExpenses table, and enter and
save the name of this query in the design view as the recordsource of
the subform. As long as the subform's LinkChildFields and
LinkMasterFields properties are set to EventID, which you implied was
already taken care of, all should be sweet.

- Steve Schapel, Microsoft Access MVP


I have a form and a subform. The form shows events, and the
subform shows event expenses with an inserted subtable for
expandable line items for each record in the subform. The
relationship between the form and subform is "EventID".

If there are no expenses for that event, the subform should
be blank.

I need to be able to update the subform, when the form
opens and when the textbox for the "EventID" changes.

I have set up the following code: but even when I go to a
record that I know has event expenses, the subform shows no
data.

Here is the code:

Public Sub UpdateSubForm()

Dim strSQL As String

strSQL = "Select DISTINCTROW
tblExpenses.ExpenseSummaryID, tblExpenses.EventID,
Sum(tblExpenses.Quantity) AS [Sum Of Quantity],
Sum(tblExpenses.Cost) AS [Sum Of Cost]"
FROM tblExpenses
WHERE [tblExpenses.EventID] =
[Forms]![frmEventSummary]![txtEventID].[AfterUpdate]
GROUP BY(tblExpenses.ExpenseSummaryID),
(tblExpenses.EventID)

'set the subform to strSQL
subfrmEventExp.Form.RecordSource = strSQL
subfrmEventExp.Requery

End Sub

Thank you in advance for your assistance.

Ginger
Click to expand...

.
Click to expand...
 
Back
Top