acNewRec works inconsistently

  • Thread starter Thread starter Silvio
  • Start date Start date
S

Silvio

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record it
overwrite an existing record, what I am doing wrong here?

.... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number in
the project table

end of sub
 
Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform) somehow
has the focus when this code is executed, so the GoToRecord is happening in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work by
default on the current form) you explicitly specify the optional arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
 
Graham, I am using Access 2003. The code runs from the subform. Below is the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with the
estimated completion date, and the cycle estimated completion date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

Graham Mandeno said:
Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform) somehow
has the focus when this code is executed, so the GoToRecord is happening in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work by
default on the current form) you explicitly specify the optional arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number in
the project table

end of sub
Click to expand...
Click to expand...
 
OK, so you do not have On Error Resume Next, but have you tried explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, I am using Access 2003. The code runs from the subform. Below is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with the
estimated completion date, and the cycle estimated completion date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

Graham Mandeno said:
Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform) somehow
has the focus when this code is executed, so the GoToRecord is happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name, acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my subform from
wich I am running the code. I am not sure what I am missing out since your
methong is new to me :-(

Graham Mandeno said:
OK, so you do not have On Error Resume Next, but have you tried explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, I am using Access 2003. The code runs from the subform. Below is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with the
estimated completion date, and the cycle estimated completion date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

Graham Mandeno said:
Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform) somehow
has the focus when this code is executed, so the GoToRecord is happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Silvio said:
Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name, acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my subform
from
wich I am running the code. I am not sure what I am missing out since your
methong is new to me :-(

Graham Mandeno said:
OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, I am using Access 2003. The code runs from the subform. Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with the
estimated completion date, and the cycle estimated completion date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record
it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Graham, the new record is created on the subform.

Graham Mandeno said:
Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Silvio said:
Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name, acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my subform
from
wich I am running the code. I am not sure what I am missing out since your
methong is new to me :-(

Graham Mandeno said:
OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the subform. Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with the
estimated completion date, and the cycle estimated completion date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly + vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new record
it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
So, did you try:
Me.Recordset.AddNew

And did it work?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, the new record is created on the subform.

Graham Mandeno said:
Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not
seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Silvio said:
Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name, acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my
subform
from
wich I am running the code. I am not sure what I am missing out since
your
methong is new to me :-(

:

OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the subform.
Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with
the
estimated completion date, and the cycle estimated completion
date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or
Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or
Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you
can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that
work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new
record
it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Well... the record is added, however when I look in the table the autonumber
skips in incremental of one number, meaning that I have record 1, 3, 5, 7 and
so on. It appeas like one record is created and one is deleted. FYI the reson
I am using unbonded control to enter data is beacuse it gives me better
control in validate data before gets saved. What a nightmare...

Graham Mandeno said:
So, did you try:
Me.Recordset.AddNew

And did it work?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, the new record is created on the subform.

Graham Mandeno said:
Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not
seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name, acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my
subform
from
wich I am running the code. I am not sure what I am missing out since
your
methong is new to me :-(

:

OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the subform.
Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time with
the
estimated completion date, and the cycle estimated completion
date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or
Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or
Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before you
can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete = Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that
work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle) via
forms/subform, however, some time instead of creating a new
record
it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Hi Silvio

It sounds like you are already on a new record when you execute the AddNew.
You can check for this first:

If Not Me.NewRecord then Me.Recordset.AddNew

But Linq is quite right. I can see no reason for using unbound controls to
enter your data. Just use bound controls and use the Form_BeforeUpdate
event procedure to validate the data. You can easily give the user the
option of either fixing the problem or cancelling the new record.

Also, you should never rely on autonumber fields generating sequences
without gaps. In fact, I would never make an autonumber value visible to
the user, because their values should be absolutely meaningless except as
the primary key in a relationship. If you require a gapless sequence, then
you should maintain your own sequential key values.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Well... the record is added, however when I look in the table the
autonumber
skips in incremental of one number, meaning that I have record 1, 3, 5, 7
and
so on. It appeas like one record is created and one is deleted. FYI the
reson
I am using unbonded control to enter data is beacuse it gives me better
control in validate data before gets saved. What a nightmare...

Graham Mandeno said:
So, did you try:
Me.Recordset.AddNew

And did it work?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Graham, the new record is created on the subform.

:

Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not
seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name,
acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my
subform
from
wich I am running the code. I am not sure what I am missing out
since
your
methong is new to me :-(

:

OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the subform.
Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time
with
the
estimated completion date, and the cycle estimated completion
date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or
Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or
Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before
you
can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete =
Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the
focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your
subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that
work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the code below to create a new record (new cycle)
via
forms/subform, however, some time instead of creating a new
record
it
overwrite an existing record, what I am doing wrong here?

... (more code up here)...

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the
new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

end of sub
Click to expand...
Click to expand...
Click to expand...
 
Folk thank you very much for the constructive criticism. Whatever refines my
skills is appreciated. What I meant with better control is that when you use
bonded control as soon you fill in any control the system creates a record,
and if the used change his/her mind, then gets trapped in sequence of error
message because some of the fields are required therefore Access will not let
them delete the record until all the field are filled. In my specific
database, I make all the fields not required and I force the form to make
validations before adding the record. For the same table, all the fields are
required when using a specific form to enter data but are not all required if
using a different form to enter data in the same table (For example, when
setting up cycle 1 all the fields are required, however when setting up cycle
2 forward then only once component is required as minimum). I am sure if I
had more experience I could have used different/better approach,
unfortunately I have no one to rely on other than the support I receive from
this fantastic forum. I try very hard to prevent garbage to be saved into the
database, but then I come across other problem like this one.

Graham Mandeno said:
Hi Silvio

It sounds like you are already on a new record when you execute the AddNew.
You can check for this first:

If Not Me.NewRecord then Me.Recordset.AddNew

But Linq is quite right. I can see no reason for using unbound controls to
enter your data. Just use bound controls and use the Form_BeforeUpdate
event procedure to validate the data. You can easily give the user the
option of either fixing the problem or cancelling the new record.

Also, you should never rely on autonumber fields generating sequences
without gaps. In fact, I would never make an autonumber value visible to
the user, because their values should be absolutely meaningless except as
the primary key in a relationship. If you require a gapless sequence, then
you should maintain your own sequential key values.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Well... the record is added, however when I look in the table the
autonumber
skips in incremental of one number, meaning that I have record 1, 3, 5, 7
and
so on. It appeas like one record is created and one is deleted. FYI the
reson
I am using unbonded control to enter data is beacuse it gives me better
control in validate data before gets saved. What a nightmare...

Graham Mandeno said:
So, did you try:
Me.Recordset.AddNew

And did it work?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, the new record is created on the subform.

:

Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are not
seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name,
acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my
subform
from
wich I am running the code. I am not sure what I am missing out
since
your
methong is new to me :-(

:

OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the subform.
Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time
with
the
estimated completion date, and the cycle estimated completion
date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate) Or
Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate) Or
Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete) Then
MyResponse = MsgBox("You must close the currect cycle before
you
can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete =
Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate = Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the
focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your
subform)
somehow
has the focus when this code is executed, so the GoToRecord is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods that
work
by
default on the current form) you explicitly specify the optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Silvio" <[email protected]> wrote in message
Click to expand...
Click to expand...
Click to expand...
 
Hi Silvio

If you are using bound controls, you can do all your data validation in the
Form_BeforeUpdate event procedure. If there is a problem with the data
entry, you can construct a helpful message and display it with a
Retry/Cancel MsgBox. If the user chooses Retry, you set the Cancel argument
to True, which returns control to the user without attempting to save the
record. If the user chooses Cancel then set Cancel=True and perform Me.Undo
(which returns the record to the "new" state). Of course, if you have an
autonumber primary key they the number will be skipped, but as I said in a
previous post, contiguous autonumber should not be relied on.

To me, it seems so much simpler to do it this way. If you really want to
persist with bound controls, I suggest you add the record using the form's
RecordsetClone, rather than navigating to a new record.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Folk thank you very much for the constructive criticism. Whatever refines
my
skills is appreciated. What I meant with better control is that when you
use
bonded control as soon you fill in any control the system creates a
record,
and if the used change his/her mind, then gets trapped in sequence of
error
message because some of the fields are required therefore Access will not
let
them delete the record until all the field are filled. In my specific
database, I make all the fields not required and I force the form to make
validations before adding the record. For the same table, all the fields
are
required when using a specific form to enter data but are not all required
if
using a different form to enter data in the same table (For example, when
setting up cycle 1 all the fields are required, however when setting up
cycle
2 forward then only once component is required as minimum). I am sure if I
had more experience I could have used different/better approach,
unfortunately I have no one to rely on other than the support I receive
from
this fantastic forum. I try very hard to prevent garbage to be saved into
the
database, but then I come across other problem like this one.

Graham Mandeno said:
Hi Silvio

It sounds like you are already on a new record when you execute the
AddNew.
You can check for this first:

If Not Me.NewRecord then Me.Recordset.AddNew

But Linq is quite right. I can see no reason for using unbound controls
to
enter your data. Just use bound controls and use the Form_BeforeUpdate
event procedure to validate the data. You can easily give the user the
option of either fixing the problem or cancelling the new record.

Also, you should never rely on autonumber fields generating sequences
without gaps. In fact, I would never make an autonumber value visible to
the user, because their values should be absolutely meaningless except as
the primary key in a relationship. If you require a gapless sequence,
then
you should maintain your own sequential key values.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Silvio said:
Well... the record is added, however when I look in the table the
autonumber
skips in incremental of one number, meaning that I have record 1, 3, 5,
7
and
so on. It appeas like one record is created and one is deleted. FYI the
reson
I am using unbonded control to enter data is beacuse it gives me better
control in validate data before gets saved. What a nightmare...

:

So, did you try:
Me.Recordset.AddNew

And did it work?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, the new record is created on the subform.

:

Hi Silvio

I'm a little confused about what you are actually trying to do,

The code is in your subform, right?

Are you creating a new record in your main form or the subform?

If it's the subform, try this:
Me.Recordset.AddNew

If it's the main form:
Me.Parent.Recordset.AddNew
or
DoCmd.GoToRecord acForm, Me.Parent.Name, acNewRec

The reason Me.Name will not work in a subform is that subforms are
not
seen
as open forms which are part of the Forms collection.
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Graham I have tried to add: DoCmd.GoToRecord acForm, Me.Name,
acNewRec
however I am getting an error message "Error: 2489 the object
"frmProjAddCycle" isn't open". frmProjAddCycle is the name of my
subform
from
wich I am running the code. I am not sure what I am missing out
since
your
methong is new to me :-(

:

OK, so you do not have On Error Resume Next, but have you tried
explicitly
naming the form in GoToRecord as I suggested?
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham, I am using Access 2003. The code runs from the
subform.
Below
is
the
full code (A little lenghty)

Private Sub AddCyc_Click()
On Error GoTo HandleErr

Dim MyResponse As Integer

If IsNull(Me.TxAdmin) And _
IsNull(Me.TxAdminDate) And _
IsNull(Me.TxBuilding) And _
IsNull(Me.TxBuildingDate) And _
IsNull(Me.TxComplDate) And _
IsNull(Me.TxElectrical) And _
IsNull(Me.TxElectricalDate) And _
IsNull(Me.TxTradeCombo) And _
IsNull(Me.TxTradeComboDate) And _
IsNull(Me.TxTradeComboDate) Then
MsgBox "As minimum, you must enter at least one trade time
with
the
estimated completion date, and the cycle estimated completion
date.",
vbOKOnly + vbCritical, "Stop!"
Me.TxAdmin.SetFocus
Exit Sub
End If

If Not IsNull(Me.TxAdmin) And IsNull(Me.TxAdminDate) Or Not
IsNull(Me.TxAdminDate) And IsNull(Me.TxAdmin) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxAdmin) Then
Me.TxAdmin.SetFocus
Else
Me.TxAdminDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxBuilding) And IsNull(Me.TxBuildingDate) Or
Not
IsNull(Me.TxBuildingDate) And IsNull(Me.TxBuilding) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxBuilding) Then
Me.TxBuilding.SetFocus
Else
Me.TxBuildingDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxElectrical) And IsNull(Me.TxElectricalDate)
Or
Not
IsNull(Me.TxElectricalDate) And IsNull(Me.TxElectrical) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxElectrical) Then
Me.TxElectrical.SetFocus
Else
Me.TxElectricalDate.SetFocus
End If
Exit Sub
End If

If Not IsNull(Me.TxTradeCombo) And IsNull(Me.TxTradeComboDate)
Or
Not
IsNull(Me.TxTradeComboDate) And IsNull(Me.TxTradeCombo) Then
MsgBox "You must enter both, time and date.", vbOKOnly +
vbCritical,
"Stop!"
If IsNull(Me.TxTradeCombo) Then
Me.TxTradeCombo.SetFocus
Else
Me.TxTradeComboDate.SetFocus
End If
Exit Sub
End If

If IsNull(Me.TxComplDate) Then
MsgBox "You must enter the estimated completion date for
this
cycle.",
vbOKOnly + vbCritical, "Stop!"
Me.TxComplDate.SetFocus
Exit Sub
End If

If IsNull(Forms!frmProjEdit!frmProjCycle.Form!DateComplete)
Then
MyResponse = MsgBox("You must close the currect cycle
before
you
can
create a new one. Would you like to close it now?", vbYesNo +
vbDefaultButton2 + vbQuestion, "Attention")
If MyResponse = vbYes Then
Forms!frmProjEdit!frmProjCycle.Form!DateComplete =
Date
Forms!frmProjEdit!frmProjCycle.Form!UpdateBy =
DLookup("UserName", "qryCurrentUser")
Forms!frmProjEdit!frmProjCycle.Form!DateUpdate =
Now()
Else
Exit Sub
End If
End If

DoCmd.GoToRecord , , acNewRec

EstAdmin = Me.TxAdmin
EstAdminDate = Me.TxAdminDate

EstBuilding = Me.TxBuilding
EstBuildingDate = Me.TxBuildingDate

EstElectrical = Me.TxElectrical
EstElectricalDate = Me.TxElectricalDate

EstTradeCombo = Me.TxTradeCombo
EstTradeComboDate = Me.TxTradeComboDate

EstComplDate = Me.TxComplDate
CreatedBy = DLookup("UserName", "qryCurrentUser")
Me.ProjCycle = NewCycle 'enter the new cycle number to the
new
cycle
set
Forms!frmProjEdit!CurrentCycle = NewCycle 'update the cycle
number
in
the project table

Me.TxAdmin = Null
Me.TxAdminDate = Null
Me.TxBuilding = Null
Me.TxBuildingDate = Null
Me.TxElectrical = Null
Me.TxElectricalDate = Null
Me.TxTradeCombo = Null
Me.TxTradeComboDate = Null
Me.TxComplDate = Null
Forms!frmProjEdit.Refresh
MsgBox "New cycle saved successfully!", vbOKOnly, "Great!"

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " &
Err.Description,
vbCritical, "Form_frmProjCycleAdd.AddCyc_Click"
End Select

End Sub

:

Hi Silvio

Do you have On Error Resume Next?

I suspect that GoToRecord is failing for some reason, so the
focus
remains
on the old record, but the error is being ignored.

The other possibility is that another form (perhaps your
subform)
somehow
has the focus when this code is executed, so the GoToRecord
is
happening
in
the wrong form.

I suggest for GoToRecord (and DoCmd.Close and other methods
that
work
by
default on the current form) you explicitly specify the
optional
arguments
so there is no ambiguity:

DoCmd.GoToRecord acForm, Me.Name, acNewRec

Depending on the version of Access, this might also work:

Me.Recordset.AddNew
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Silvio" <[email protected]> wrote in message
Click to expand...
Click to expand...
Click to expand...
 
Back
Top