Requery 1 subform from another.

G

Guest

I have searched the discussion group and all the solutions will not work.

I have a main form with 2 subforms. Both of the subforms are "Continuous
Forms". When a record is added to the 1st subform, I have code to add to the
table behind the 2nd subform. I cannot get the 2nd subform to refresh when I
add the record.

Now I'm trying to delete the record and I tried using both Refresh and
Requery method in the after update and on delete event of 1st subform. All
that is showing up is "#delete" in all the fields. Any suggestions will help.
 
P

Paul Overway

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery
 
G

Guest

Paul

This did work on the adding of the record, thanks. I had to change rows for
the requery to work. I'm still have the problem with the delete. I put the
same code where I delete the record and the requery did not work.

John
 
G

Guest

Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub
 
P

Paul Overway

I suspect it actually is being executed, but the record hasn't really been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

Paul Overway said:
Post your code.
Click to expand...
Click to expand...
 
G

Guest

Paul

You were correct. When I put it in the "On delete Confirm", that worked just
fine.

Thanks for all the time and help.

Paul Overway said:
I suspect it actually is being executed, but the record hasn't really been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

Paul Overway said:
Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to change rows
for
the requery to work. I'm still have the problem with the delete. I put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have code to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Paul

Why would my error message appear twice? I tried removing a record with the
hours not equal to zero.

John

Paul Overway said:
I suspect it actually is being executed, but the record hasn't really been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

Paul Overway said:
Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to change rows
for
the requery to work. I'm still have the problem with the delete. I put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have code to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Overway

Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Why would my error message appear twice? I tried removing a record with
the
hours not equal to zero.

John

Paul Overway said:
I suspect it actually is being executed, but the record hasn't really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate "
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to change
rows
for
the requery to work. I'm still have the problem with the delete. I
put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will
not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Paul

I made the else look like the following and I still get two messages:

If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Else
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
Cancel = True
End If

John

Paul Overway said:
Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Why would my error message appear twice? I tried removing a record with
the
hours not equal to zero.

John

Paul Overway said:
I suspect it actually is being executed, but the record hasn't really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals " &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject &
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate "
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to change
rows
for
the requery to work. I'm still have the problem with the delete. I
put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will
not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Overway

Step through your code and determine why the delete is occuring more than
once.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

I made the else look like the following and I still get two messages:

If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Else
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
Cancel = True
End If

John

Paul Overway said:
Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

Why would my error message appear twice? I tried removing a record with
the
hours not equal to zero.

John

:

I suspect it actually is being executed, but the record hasn't really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals
" &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate
"
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to
change
rows
for
the requery to work. I'm still have the problem with the delete.
I
put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will
not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have
code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Paul

I put a break point on the first executable statement. Open the form and it
delete. It stopped there. I walked the code. When the message appeared, I
replied yes. After I said ok and hit it hit the cancel = True statement, end
if and stopped at the break point again. I still don't know why it is
repeating this routine twice. Any other suggestions?

John

Paul Overway said:
Step through your code and determine why the delete is occuring more than
once.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

I made the else look like the following and I still get two messages:

If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Else
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
Cancel = True
End If

John

Paul Overway said:
Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Why would my error message appear twice? I tried removing a record with
the
hours not equal to zero.

John

:

I suspect it actually is being executed, but the record hasn't really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals
" &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate
"
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to
change
rows
for
the requery to work. I'm still have the problem with the delete.
I
put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will
not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have
code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Paul

When I commented out the message and hit delete 4 times, I received the
following message: "Could not start transaction; too many transactions
already nested". Could this have anything to do with the problem?

John

Paul Overway said:
Step through your code and determine why the delete is occuring more than
once.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

I made the else look like the following and I still get two messages:

If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Else
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
Cancel = True
End If

John

Paul Overway said:
Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Why would my error message appear twice? I tried removing a record with
the
hours not equal to zero.

John

:

I suspect it actually is being executed, but the record hasn't really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from QryTabActuals
" &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) & MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from QryTabEstimate
"
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to
change
rows
for
the requery to work. I'm still have the problem with the delete.
I
put
the
same code where I delete the record and the requery did not work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions will
not
work.

I have a main form with 2 subforms. Both of the subforms are
"Continuous
Forms". When a record is added to the 1st subform, I have
code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of 1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 
P

Paul Overway

It might, but it is difficult to say without seeing the entire app. I think
you're beyond what can be accomplished via NG.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

When I commented out the message and hit delete 4 times, I received the
following message: "Could not start transaction; too many transactions
already nested". Could this have anything to do with the problem?

John

Paul Overway said:
Step through your code and determine why the delete is occuring more than
once.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


jbruen said:
Paul

I made the else look like the following and I still get two messages:

If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Else
MsgBox "Cannot delete record if there are hours in Actual or
Estimate", vbOKOnly, "Delete Error"
Cancel = True
End If

John

:

Ah...you haven't canceled the Delete...put the following in the Else

Cancel = True

And get rid of the Docmd.CancelEvent....that should only be used under
certain conditions in Macros.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Why would my error message appear twice? I tried removing a record
with
the
hours not equal to zero.

John

:

I suspect it actually is being executed, but the record hasn't
really
been
deleted yet. Try moving the requery to the AfterDelConfirm event.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

Here is the code.

Private Sub Form_AfterInsert()
Dim MyDB As DAO.Database
Dim STRCRITERIA As String
Dim ActHrs As DAO.Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set ActHrs = MyDB.OpenRecordset("TabActuals", DB_OPEN_DYNASET)
With ActHrs
.AddNew
!UserName = Me.UserName
!ProjectName = Me.ProjectName
!ActYear = Me.EstYear
.Update
End With
Me.Parent.FrmHoursActuals.Requery <==== Working.
End Sub

Private Sub Form_Delete(Cancel As Integer)
Dim MyDB As DAO.Database
Dim MySet As DAO.Recordset
Dim TotalAct As Long
Dim TotalEst As Long
Dim MyName As String
Dim MyYear As Double
Dim MyWhere As String
Dim MyProject As String
Dim MySql As String
MyName = Me.UserName
MyYear = Me.EstYear
MyProject = Me.ProjectName
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) &
MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [ActYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotAct from
QryTabActuals
" &
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalAct = !TotAct
End With
MyWhere = "Where [UserName] = " & Chr(34) & MyName & Chr(34)
MyWhere = MyWhere + " AND [ProjectName] = " & Chr(34) &
MyProject
&
Chr(34)
MyWhere = MyWhere + " AND [EstYEAR] = " & MyYear
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("Select TotEst from
QryTabEstimate
"
&
MyWhere, DB_OPEN_DYNASET)
With MySet
.MoveFirst
TotalEst = !TotEst
End With
Set MyDB = Nothing
Set MySet = Nothing
If TotalEst = 0 And TotalAct = 0 Then
DoCmd.SetWarnings False
MySql = "Delete from TabActuals " & MyWhere
DoCmd.RunSQL MySql
Me.Parent.FrmHoursActuals.Requery <=== not working
Else
DoCmd.CancelEvent
MsgBox "Cannot delete record if there are hours in Actual
or
Estimate", vbOKOnly, "Delete Error"
End If

End Sub

:

Post your code.

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


Paul

This did work on the adding of the record, thanks. I had to
change
rows
for
the requery to work. I'm still have the problem with the
delete.
I
put
the
same code where I delete the record and the requery did not
work.

John

:

In the subform routine where you're adding the record, put

Me.Parent.NameOfOtherSubform.Requery

--
Paul Overway
Logico Solutions
http://www.logico-solutions.com


I have searched the discussion group and all the solutions
will
not
work.

I have a main form with 2 subforms. Both of the subforms
are
"Continuous
Forms". When a record is added to the 1st subform, I have
code
to
add
to
the
table behind the 2nd subform. I cannot get the 2nd subform
to
refresh
when
I
add the record.

Now I'm trying to delete the record and I tried using both
Refresh
and
Requery method in the after update and on delete event of
1st
subform.
All
that is showing up is "#delete" in all the fields. Any
suggestions
will
help.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Requery subform 1
Pls help requery subform 2
Referencing a subform 4
Update (refresh) Subform#1 when a check box is selected on Subform 8
Subform of subform not being linked 3
How to always get the top level form reference, when calling from any subform/subform... 0
REQUERY SUBFORM 4
requery of data viewed on a subform 2

Top