|
|
Hello,
I am posting data from a Form and Subform to a Table and Subtable. In the
code below line 1000 and 2000 show the main table being updated and that is
ok, not problem. Line 3000 and 4000 show the subtable being updated but the
updates aren't holding. When I walk through the code and check the field
values being assigned before the update they are correct but the changes
just don't take affect after .Update. This is probably something simple,
but I can't work it out.
I apologize for posting the whole procedure, but there might be something
outside of the Edit/Update that is the problem. I added the line numbers to
point you to the appropriate lines.
Thanks for any help and God Bless,
Mark A. Sam
Private Sub SendToTripReport_Click()
On Error GoTo errSec
DoCmd.RunCommand acCmdSaveRecord
If IsNull([TruckNumber]) Then
MsgBox "You need to enter a Truck Numbeer!"
[TruckNumber].SetFocus
Exit Sub
ElseIf IsNull([TrailerNumber]) Then
MsgBox "You need to enter a Trailer Number!"
[TrailerNumber].SetFocus
Exit Sub
ElseIf IsNull([DriverName]) Then
MsgBox "You need to enter a Driver Name!"
[DriverName].SetFocus
Exit Sub
ElseIf IsNull([WeekStarting]) Then
MsgBox "You need to enter a Week Starting Date!"
[btnPickWeek].SetFocus
Exit Sub
ElseIf IsNull([WeekEnding]) Then
MsgBox "You need to enter a Week Ending Date!"
[btnPickWeek].SetFocus
Exit Sub
End If
Dim rstTripRptMain As Recordset
Dim rstTripRptEntry As Recordset
Dim lngMainID As Long
Set rstTripRptMain = CurrentDb.OpenRecordset("qryLookUpTripRptMain",
dbOpenDynaset)
Set rstTripRptEntry = CurrentDb.OpenRecordset("TripRptEntry", dbOpenDynaset)
rstTripRptMain.FindFirst "[Period] = '" & [Period] & "' and [Truck] = '" &
[TruckNumber] & "'"
If rstTripRptMain.NoMatch Then
'MsgBox "No matching record found"
rstTripRptMain.AddNew
'rstTripRptMain![loadID] = [loadID] 'Don't need this field populated
rstTripRptMain![Truck] = [TruckNumber]
rstTripRptMain![Trailer] = [TrailerNumber]
rstTripRptMain![Driver] = [DriverName]
rstTripRptMain![WeekStarting] = [WeekStarting]
rstTripRptMain![WeekEnding] = [WeekEnding]
rstTripRptMain![Miles] = [PeriodHubMiles] 'Calculated control based on a
query, qryTotHubMilesPerPeriod, to total Hub Miles by period
'rstTripRptMain![Gallons] Total Gallons for period. Use a query based
on LoadID
lngMainID = rstTripRptMain![trMainID]
rstTripRptMain.Update
rstTripRptEntry.AddNew
rstTripRptEntry![trMainID] = lngMainID
rstTripRptEntry![loadID] = loadID
rstTripRptEntry![LoadNumber] = [LoadNumber]
rstTripRptEntry![ShipperNumber] = [ShipperNumber]
rstTripRptEntry![Truck] = [TruckNumber]
rstTripRptEntry![Trailer] = [TrailerNumber]
rstTripRptEntry![Driver] = [DriverName]
rstTripRptEntry![Revenue] = [Revenue]
rstTripRptEntry![HubMiles] = [HubMilesTotal]
rstTripRptEntry![PCMiler] = [PTtoPTMiles]
With [LoadPickDrop].Form.RecordsetClone
.FindFirst "[order]=1"
If Not .NoMatch Then
rstTripRptEntry![TripStartDate] = ![PDDate]
rstTripRptEntry![TripStartDay] = Format(![PDDate], "dddd")
rstTripRptEntry![TripStartLoc] = ![TripLoc]
End If
.FindFirst "[order]=2"
If Not .NoMatch Then
rstTripRptEntry![TripEndDate] = ![PDDate]
rstTripRptEntry![TripEndDay] = Format(![PDDate], "dddd")
rstTripRptEntry![TripEndLoc] = ![TripLoc]
End If
End With
rstTripRptEntry.Update
[txtMessage1] = "Record Added"
'DoCmd.OpenForm "TripRptMain", , , "[Period] = '" & [Period] & "' and
[Truck] = '" & [TruckNumber] & "'", , acDialog, "Modal"
Else 'Find existing records and update.
1000 rstTripRptMain.Edit
'rstTripRptMain![loadID] = [loadID] 'Don't need this field populated
rstTripRptMain![Truck] = [TruckNumber]
rstTripRptMain![Trailer] = [TrailerNumber]
rstTripRptMain![Driver] = [DriverName]
rstTripRptMain![WeekStarting] = [WeekStarting]
rstTripRptMain![WeekEnding] = [WeekEnding]
rstTripRptMain![Miles] = [PeriodHubMiles] 'Based on a query,
qryTotHubMilesPerPeriod, to total Hub Miles by period
'rstTripRptMain![Gallons] Total Gallons for period. Use a query based
on LoadID
lngMainID = rstTripRptMain![trMainID]
2000 rstTripRptMain.Update
rstTripRptEntry.FindFirst [loadID] = [loadID] '[loadID] identifies the
line item in TripRptEntry corresponding to the load
If Not rstTripRptEntry.NoMatch Then
rstTripRptEntry.Edit
' rstTripRptEntry![trMainID] = lngMainID
rstTripRptEntry![loadID] = loadID
rstTripRptEntry![LoadNumber] = [LoadNumber]
rstTripRptEntry![ShipperNumber] = [ShipperNumber]
rstTripRptEntry![Truck] = [TruckNumber]
rstTripRptEntry![Trailer] = [TrailerNumber]
rstTripRptEntry![Driver] = [DriverName]
rstTripRptEntry![Revenue] = [Revenue]
rstTripRptEntry![HubMiles] = [HubMilesTotal]
rstTripRptEntry![PCMiler] = [PTtoPTMiles]
With [LoadPickDrop].Form.RecordsetClone
.FindFirst "[order]=1"
If Not .NoMatch Then
rstTripRptEntry![TripStartDate] = ![PDDate]
rstTripRptEntry![TripStartDay] = Format(![PDDate], "dddd")
rstTripRptEntry![TripStartLoc] = ![TripLoc]
End If
.FindFirst "[order]=2"
If Not .NoMatch Then
rstTripRptEntry![TripEndDate] = ![PDDate]
rstTripRptEntry![TripEndDay] = Format(![PDDate], "dddd")
rstTripRptEntry![TripEndLoc] = ![TripLoc]
End If
End With
4000 rstTripRptEntry.Update
End If
[txtMessage1] = "Record Edited"
End If
DoCmd.OpenForm "TripRptMain", , , "[Period] = '" & [Period] & "' and [Truck]
= '" & [TruckNumber] & "'", , acDialog, "Modal"
exitSec:
On Error Resume Next
rstTripRptMain.Close
Set rstTripRptMain = Nothing
rstTripRptEntry.Clone
Set rstTripRptEntry = Nothing
Exit Sub
errSec:
MsgBox "Error " & Err & ": " & Err.Description
Resume Next
End Sub
|
|
I can't understand why, but I unremmed this line,
rstTripRptEntry![trMainID] = lngMainID
and it fixed it. [trManID] is the LinkChildField for the subtable, so
editing it shouldn't have been necessary.
|
|
"Mark A. Sam" <MarkASam[ at ]EmEssEn.Com> wrote in message
news:%236%23bmFcSJHA.4764[ at ]TK2MSFTNGP05.phx.gbl...
[Quoted Text] > Hello,
>
> I am posting data from a Form and Subform to a Table and Subtable. In
> the code below line 1000 and 2000 show the main table being updated and
> that is ok, not problem. Line 3000 and 4000 show the subtable being
> updated but the updates aren't holding. When I walk through the code and
> check the field values being assigned before the update they are correct
> but the changes just don't take affect after .Update. This is probably
> something simple, but I can't work it out.
>
> I apologize for posting the whole procedure, but there might be something
> outside of the Edit/Update that is the problem. I added the line numbers
> to point you to the appropriate lines.
>
> Thanks for any help and God Bless,
>
> Mark A. Sam
>
> Private Sub SendToTripReport_Click()
> On Error GoTo errSec
>
> DoCmd.RunCommand acCmdSaveRecord
>
> If IsNull([TruckNumber]) Then
> MsgBox "You need to enter a Truck Numbeer!"
> [TruckNumber].SetFocus
> Exit Sub
> ElseIf IsNull([TrailerNumber]) Then
> MsgBox "You need to enter a Trailer Number!"
> [TrailerNumber].SetFocus
> Exit Sub
> ElseIf IsNull([DriverName]) Then
> MsgBox "You need to enter a Driver Name!"
> [DriverName].SetFocus
> Exit Sub
> ElseIf IsNull([WeekStarting]) Then
> MsgBox "You need to enter a Week Starting Date!"
> [btnPickWeek].SetFocus
> Exit Sub
> ElseIf IsNull([WeekEnding]) Then
> MsgBox "You need to enter a Week Ending Date!"
> [btnPickWeek].SetFocus
> Exit Sub
> End If
>
> Dim rstTripRptMain As Recordset
> Dim rstTripRptEntry As Recordset
> Dim lngMainID As Long
> Set rstTripRptMain = CurrentDb.OpenRecordset("qryLookUpTripRptMain",
> dbOpenDynaset)
> Set rstTripRptEntry = CurrentDb.OpenRecordset("TripRptEntry",
> dbOpenDynaset)
>
> rstTripRptMain.FindFirst "[Period] = '" & [Period] & "' and [Truck] = '" &
> [TruckNumber] & "'"
> If rstTripRptMain.NoMatch Then
> 'MsgBox "No matching record found"
> rstTripRptMain.AddNew
> 'rstTripRptMain![loadID] = [loadID] 'Don't need this field populated
> rstTripRptMain![Truck] = [TruckNumber]
> rstTripRptMain![Trailer] = [TrailerNumber]
> rstTripRptMain![Driver] = [DriverName]
> rstTripRptMain![WeekStarting] = [WeekStarting]
> rstTripRptMain![WeekEnding] = [WeekEnding]
> rstTripRptMain![Miles] = [PeriodHubMiles] 'Calculated control based on a
> query, qryTotHubMilesPerPeriod, to total Hub Miles by period
> 'rstTripRptMain![Gallons] Total Gallons for period. Use a query based
> on LoadID
> lngMainID = rstTripRptMain![trMainID]
> rstTripRptMain.Update
>
> rstTripRptEntry.AddNew
> rstTripRptEntry![trMainID] = lngMainID
> rstTripRptEntry![loadID] = loadID
> rstTripRptEntry![LoadNumber] = [LoadNumber]
> rstTripRptEntry![ShipperNumber] = [ShipperNumber]
> rstTripRptEntry![Truck] = [TruckNumber]
> rstTripRptEntry![Trailer] = [TrailerNumber]
> rstTripRptEntry![Driver] = [DriverName]
> rstTripRptEntry![Revenue] = [Revenue]
> rstTripRptEntry![HubMiles] = [HubMilesTotal]
> rstTripRptEntry![PCMiler] = [PTtoPTMiles]
> With [LoadPickDrop].Form.RecordsetClone
> .FindFirst "[order]=1"
> If Not .NoMatch Then
> rstTripRptEntry![TripStartDate] = ![PDDate]
> rstTripRptEntry![TripStartDay] = Format(![PDDate], "dddd")
> rstTripRptEntry![TripStartLoc] = ![TripLoc]
> End If
> .FindFirst "[order]=2"
> If Not .NoMatch Then
> rstTripRptEntry![TripEndDate] = ![PDDate]
> rstTripRptEntry![TripEndDay] = Format(![PDDate], "dddd")
> rstTripRptEntry![TripEndLoc] = ![TripLoc]
> End If
> End With
> rstTripRptEntry.Update
>
> [txtMessage1] = "Record Added"
> 'DoCmd.OpenForm "TripRptMain", , , "[Period] = '" & [Period] & "' and
> [Truck] = '" & [TruckNumber] & "'", , acDialog, "Modal"
>
> Else 'Find existing records and update.
> 1000 rstTripRptMain.Edit
> 'rstTripRptMain![loadID] = [loadID] 'Don't need this field populated
> rstTripRptMain![Truck] = [TruckNumber]
> rstTripRptMain![Trailer] = [TrailerNumber]
> rstTripRptMain![Driver] = [DriverName]
> rstTripRptMain![WeekStarting] = [WeekStarting]
> rstTripRptMain![WeekEnding] = [WeekEnding]
> rstTripRptMain![Miles] = [PeriodHubMiles] 'Based on a query,
> qryTotHubMilesPerPeriod, to total Hub Miles by period
> 'rstTripRptMain![Gallons] Total Gallons for period. Use a query based
> on LoadID
> lngMainID = rstTripRptMain![trMainID]
> 2000 rstTripRptMain.Update
>
> rstTripRptEntry.FindFirst [loadID] = [loadID] '[loadID] identifies the
> line item in TripRptEntry corresponding to the load
> If Not rstTripRptEntry.NoMatch Then
> rstTripRptEntry.Edit
> ' rstTripRptEntry![trMainID] = lngMainID
> rstTripRptEntry![loadID] = loadID
> rstTripRptEntry![LoadNumber] = [LoadNumber]
> rstTripRptEntry![ShipperNumber] = [ShipperNumber]
> rstTripRptEntry![Truck] = [TruckNumber]
> rstTripRptEntry![Trailer] = [TrailerNumber]
> rstTripRptEntry![Driver] = [DriverName]
> rstTripRptEntry![Revenue] = [Revenue]
> rstTripRptEntry![HubMiles] = [HubMilesTotal]
> rstTripRptEntry![PCMiler] = [PTtoPTMiles]
> With [LoadPickDrop].Form.RecordsetClone
> .FindFirst "[order]=1"
> If Not .NoMatch Then
> rstTripRptEntry![TripStartDate] = ![PDDate]
> rstTripRptEntry![TripStartDay] = Format(![PDDate], "dddd")
> rstTripRptEntry![TripStartLoc] = ![TripLoc]
> End If
> .FindFirst "[order]=2"
> If Not .NoMatch Then
> rstTripRptEntry![TripEndDate] = ![PDDate]
> rstTripRptEntry![TripEndDay] = Format(![PDDate], "dddd")
> rstTripRptEntry![TripEndLoc] = ![TripLoc]
> End If
> End With
> 4000 rstTripRptEntry.Update
> End If
>
> [txtMessage1] = "Record Edited"
> End If
>
> DoCmd.OpenForm "TripRptMain", , , "[Period] = '" & [Period] & "' and
> [Truck] = '" & [TruckNumber] & "'", , acDialog, "Modal"
>
>
> exitSec:
> On Error Resume Next
> rstTripRptMain.Close
> Set rstTripRptMain = Nothing
> rstTripRptEntry.Clone
> Set rstTripRptEntry = Nothing
> Exit Sub
>
> errSec:
> MsgBox "Error " & Err & ": " & Err.Description
> Resume Next
>
>
> End Sub
Mark -
This may be unrelated to your problem, but I don't see how this line can be
right:
> rstTripRptEntry.FindFirst [loadID] = [loadID]
Shouldn't the expression "[loadID] = [loadID]" have had some quotes
involved, maybe as
> rstTripRptEntry.FindFirst "[loadID] = " & [loadID]
? It seems to me that, as written, the criterion will either always
evaluate to True (if there's a field on your form named "loadID", and that
field is not Null), or to Null (if the field on your form is Null), or else
will raise an error (if there's no field named "loadID" on your form).
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Dirk,
Your right. I forgot the quotes, but it works. I was having trouble
focusing and would normally write it "[loadID] = " & [loadID]. [loadID] is
a long int. I guess I didn't notice becuse it compiled.
Maybe the syntax is illegal is former versions, but ok in Access 2007?
[Quoted Text] > Mark -
>
> This may be unrelated to your problem, but I don't see how this line can
> be right:
>
>> rstTripRptEntry.FindFirst [loadID] = [loadID]
>
> Shouldn't the expression "[loadID] = [loadID]" have had some quotes
> involved, maybe as
>
>> rstTripRptEntry.FindFirst "[loadID] = " & [loadID]
>
> ? It seems to me that, as written, the criterion will either always
> evaluate to True (if there's a field on your form named "loadID", and that
> field is not Null), or to Null (if the field on your form is Null), or
> else will raise an error (if there's no field named "loadID" on your
> form).
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
|
|
"Mark A. Sam" <MarkASam[ at ]EmEssEn.Com> wrote in message
news:OMsq8efSJHA.4680[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > Dirk,
>
> Your right. I forgot the quotes, but it works. I was having trouble
> focusing and would normally write it "[loadID] = " & [loadID]. [loadID]
> is a long int. I guess I didn't notice becuse it compiled.
>
> Maybe the syntax is illegal is former versions, but ok in Access 2007?
No, the syntax is perfectly legal and always has been, but it won't be doing
what you think. may not be working the way you think. The statement
rstTripRptEntry.FindFirst [loadID] = [loadID]
will first evaluate the expression
[loadID] = [loadID]
and always get the logical value True. Then that will be converted to a
string to pass to the FindFirst method, so that the statement resolves to
rstTripRptEntry.FindFirst "True"
Every record in the recordset will meet that condition, so the result of the
FindFirst will be that the recordset will be positioned at the first record
of the recordset, no matter what the value of [loadID] is.
This is presumably not what you want.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
I see what you mean. I appreciate you discovering this. I likely wouldn't
have noticed it.
"Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com.invalid> wrote in message
news:esjcJwfSJHA.4452[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > "Mark A. Sam" <MarkASam[ at ]EmEssEn.Com> wrote in message
> news:OMsq8efSJHA.4680[ at ]TK2MSFTNGP06.phx.gbl...
>> Dirk,
>>
>> Your right. I forgot the quotes, but it works. I was having trouble
>> focusing and would normally write it "[loadID] = " & [loadID]. [loadID]
>> is a long int. I guess I didn't notice becuse it compiled.
>>
>> Maybe the syntax is illegal is former versions, but ok in Access 2007?
>
>
> No, the syntax is perfectly legal and always has been, but it won't be
> doing what you think. may not be working the way you think. The
> statement
>
> rstTripRptEntry.FindFirst [loadID] = [loadID]
>
> will first evaluate the expression
>
> [loadID] = [loadID]
>
> and always get the logical value True. Then that will be converted to a
> string to pass to the FindFirst method, so that the statement resolves to
>
> rstTripRptEntry.FindFirst "True"
>
> Every record in the recordset will meet that condition, so the result of
> the FindFirst will be that the recordset will be positioned at the first
> record of the recordset, no matter what the value of [loadID] is.
>
> This is presumably not what you want.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
|
|
|