Auto save and commit of an inverse record

G

Guest

I have a table with 3 Fields. OrgID, AssociatedID and AssociationID.
From a form I capture the data for OrgID and AssociatedID. I have some code
the worked under office 97 to save this data, then invert AssociatedID and
OrgID and save again. Since upgrading only the second half of the operation
is commited, it seems like the first part is not. What am I missing? I am
not a developer so please keep it simple. Here is the code.

Private Sub cmdSave_Click()
On Error GoTo errhandler

' Validate fields
If IsNull(AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If
If IsNull(StartDate) Then StartDate = Date

' Save current association
OrgID = [Forms]![frmMainMenu]![OrgID]

AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord



' Save associated association


Dim tmpId As Long


tmpId = OrgID
OrgID = AssociatedID
AssociatedID = tmpId
Me.Dirty = False

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
' MsgBox "The organizations are already associated to each
other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", Err,
Err.Description
Me.Undo
DoCmd.Close acForm, Me.Name
End Select

End Sub
 
T

tina

if the form is bound to a table, then "Me.Dirty=False" saves the record,
there is no need for the RunCommands. also, if the form is bound, the second
half of the code overwrites the current record - it does not save a *new*
record.

assuming that the form is bound to a table, try the following, as

Private Sub cmdSave_Click()

On Error GoTo errhandler

' Validate fields
If IsNull(Me!AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If

If IsNull(Me!StartDate) Then Me!StartDate = Date

' Save current association
Me!OrgID = Forms!frmMainMenu!OrgID

Me!AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then Me.Dirty = False

' Save associated association
Dim tmpOrg As Long, tmpAssocd As Long
Dim tmpAssocn As Long

tmpOrg = Me!AssociatedID
tmpAssocd = Me!OrgID
tmpAssocn = Me!AssociationID

DoCmd.RunCommand acCmdRecordsGoToNew

Me!OrgID = tmpOrg
Me!AssociatedID = tmpAssocd
Me!AssociationID = tmpAssocn

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close , , acSaveNo

exitsub:
Exit Sub

errhandler:
Select Case err
Case 3022
' MsgBox "The organizations are already " _
& "associated to each other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", _
err, err.Description
Me.Undo
DoCmd.Close , , acSaveNo
End Select

End Sub

note that i assumed all three fields in the underlying table are Number data
type, field size Long. if this is incorrect, you'll need to change the data
types of the variables to the appropriate types.

hth


Richard said:
I have a table with 3 Fields. OrgID, AssociatedID and AssociationID.
From a form I capture the data for OrgID and AssociatedID. I have some code
the worked under office 97 to save this data, then invert AssociatedID and
OrgID and save again. Since upgrading only the second half of the operation
is commited, it seems like the first part is not. What am I missing? I am
not a developer so please keep it simple. Here is the code.

Private Sub cmdSave_Click()
On Error GoTo errhandler

' Validate fields
If IsNull(AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If
If IsNull(StartDate) Then StartDate = Date

' Save current association
OrgID = [Forms]![frmMainMenu]![OrgID]

AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord



' Save associated association


Dim tmpId As Long


tmpId = OrgID
OrgID = AssociatedID
AssociatedID = tmpId
Me.Dirty = False

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
' MsgBox "The organizations are already associated to each
other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", Err,
Err.Description
Me.Undo
DoCmd.Close acForm, Me.Name
End Select

End Sub
Click to expand...
 
G

Guest

Tina,
Thank you,
The code you provide does make everything tighter; however, as you pointed
out (and the problem I was actually having) is that only the second half of
the code is written to the table and saved, it seems that the first half it
overwritten. How do I get both halves committed to the table?
Thank you.

tina said:
if the form is bound to a table, then "Me.Dirty=False" saves the record,
there is no need for the RunCommands. also, if the form is bound, the second
half of the code overwrites the current record - it does not save a *new*
record.

assuming that the form is bound to a table, try the following, as

Private Sub cmdSave_Click()

On Error GoTo errhandler

' Validate fields
If IsNull(Me!AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If

If IsNull(Me!StartDate) Then Me!StartDate = Date

' Save current association
Me!OrgID = Forms!frmMainMenu!OrgID

Me!AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then Me.Dirty = False

' Save associated association
Dim tmpOrg As Long, tmpAssocd As Long
Dim tmpAssocn As Long

tmpOrg = Me!AssociatedID
tmpAssocd = Me!OrgID
tmpAssocn = Me!AssociationID

DoCmd.RunCommand acCmdRecordsGoToNew

Me!OrgID = tmpOrg
Me!AssociatedID = tmpAssocd
Me!AssociationID = tmpAssocn

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close , , acSaveNo

exitsub:
Exit Sub

errhandler:
Select Case err
Case 3022
' MsgBox "The organizations are already " _
& "associated to each other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", _
err, err.Description
Me.Undo
DoCmd.Close , , acSaveNo
End Select

End Sub

note that i assumed all three fields in the underlying table are Number data
type, field size Long. if this is incorrect, you'll need to change the data
types of the variables to the appropriate types.

hth


Richard said:
I have a table with 3 Fields. OrgID, AssociatedID and AssociationID.
From a form I capture the data for OrgID and AssociatedID. I have some code
the worked under office 97 to save this data, then invert AssociatedID and
OrgID and save again. Since upgrading only the second half of the operation
is commited, it seems like the first part is not. What am I missing? I am
not a developer so please keep it simple. Here is the code.

Private Sub cmdSave_Click()
On Error GoTo errhandler

' Validate fields
If IsNull(AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If
If IsNull(StartDate) Then StartDate = Date

' Save current association
OrgID = [Forms]![frmMainMenu]![OrgID]

AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord



' Save associated association


Dim tmpId As Long


tmpId = OrgID
OrgID = AssociatedID
AssociatedID = tmpId
Me.Dirty = False

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
' MsgBox "The organizations are already associated to each
other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", Err,
Err.Description
Me.Undo
DoCmd.Close acForm, Me.Name
End Select

End Sub
Click to expand...
Click to expand...
 
T

tina

the "GoToNew" command should move to a new record, before setting the values
of the controls. if that's not happening, you need to find out where the
code is failing. suggest you put a break on the code, on the first line of
the first If statement, and step through the code to see if you can spot the
problem.

hth


Richard said:
Tina,
Thank you,
The code you provide does make everything tighter; however, as you pointed
out (and the problem I was actually having) is that only the second half of
the code is written to the table and saved, it seems that the first half it
overwritten. How do I get both halves committed to the table?
Thank you.

tina said:
if the form is bound to a table, then "Me.Dirty=False" saves the record,
there is no need for the RunCommands. also, if the form is bound, the second
half of the code overwrites the current record - it does not save a *new*
record.

assuming that the form is bound to a table, try the following, as

Private Sub cmdSave_Click()

On Error GoTo errhandler

' Validate fields
If IsNull(Me!AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If

If IsNull(Me!StartDate) Then Me!StartDate = Date

' Save current association
Me!OrgID = Forms!frmMainMenu!OrgID

Me!AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then Me.Dirty = False

' Save associated association
Dim tmpOrg As Long, tmpAssocd As Long
Dim tmpAssocn As Long

tmpOrg = Me!AssociatedID
tmpAssocd = Me!OrgID
tmpAssocn = Me!AssociationID

DoCmd.RunCommand acCmdRecordsGoToNew

Me!OrgID = tmpOrg
Me!AssociatedID = tmpAssocd
Me!AssociationID = tmpAssocn

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close , , acSaveNo

exitsub:
Exit Sub

errhandler:
Select Case err
Case 3022
' MsgBox "The organizations are already " _
& "associated to each other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", _
err, err.Description
Me.Undo
DoCmd.Close , , acSaveNo
End Select

End Sub

note that i assumed all three fields in the underlying table are Number data
type, field size Long. if this is incorrect, you'll need to change the data
types of the variables to the appropriate types.

hth


Richard said:
I have a table with 3 Fields. OrgID, AssociatedID and AssociationID.
From a form I capture the data for OrgID and AssociatedID. I have some code
the worked under office 97 to save this data, then invert AssociatedID and
OrgID and save again. Since upgrading only the second half of the operation
is commited, it seems like the first part is not. What am I missing?
Click to expand...
I
am
not a developer so please keep it simple. Here is the code.

Private Sub cmdSave_Click()
On Error GoTo errhandler

' Validate fields
If IsNull(AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If
If IsNull(StartDate) Then StartDate = Date

' Save current association
OrgID = [Forms]![frmMainMenu]![OrgID]

AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord



' Save associated association


Dim tmpId As Long


tmpId = OrgID
OrgID = AssociatedID
AssociatedID = tmpId
Me.Dirty = False

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
' MsgBox "The organizations are already associated to each
other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", Err,
Err.Description
Me.Undo
DoCmd.Close acForm, Me.Name
End Select

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

tina,
Thank you for the help. I finally had our summer student take a look at the
code and this is what he came up with that works.

Posting this so others can use it.

Private Sub cmdSave_Click()
Dim iAssociatedID As Integer, iorgID As Integer

iAssociatedID = AssociatedID

On Error GoTo errhandler

'Validate fields
If IsNull(Me!AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If

If IsNull(Me!StartDate) Then
Me!StartDate = Date
End If

' Save current association
Me!OrgID = Forms!frmMainMenu!OrgID
iorgID = Me!OrgID


Me!AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If
DoCmd.RunCommand acCmdRecordsGoToNew

' Save associated association
Dim tmpOrg As Long, tmpAssocd As Long
Dim tmpAssocn As Long


Me!AssociationID = NextID("T_Association", "AssociationID")
' MsgBox (Str(Me!AssociationID))


tmpOrg = iAssociatedID
tmpAssocd = iorgID
tmpAssocn = Me!AssociationID

Me!OrgID = tmpOrg
Me!AssociatedID = tmpAssocd
Me!AssociationID = tmpAssocn

If IsNull(Me!StartDate) Then
Me!StartDate = Date
End If

Me.Dirty = False

DoCmd.Close , , acSaveNo
End
exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
MsgBox "The organizations are already " _
& "associated to each other.", , "Info"
Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", _
Err, Err.Description
Me.Undo
DoCmd.Close , , acSaveNo
End Select

End Sub

Again Thanks!

tina said:
the "GoToNew" command should move to a new record, before setting the values
of the controls. if that's not happening, you need to find out where the
code is failing. suggest you put a break on the code, on the first line of
the first If statement, and step through the code to see if you can spot the
problem.

hth


Richard said:
Tina,
Thank you,
The code you provide does make everything tighter; however, as you pointed
out (and the problem I was actually having) is that only the second half of
the code is written to the table and saved, it seems that the first half it
overwritten. How do I get both halves committed to the table?
Thank you.

tina said:
if the form is bound to a table, then "Me.Dirty=False" saves the record,
there is no need for the RunCommands. also, if the form is bound, the second
half of the code overwrites the current record - it does not save a *new*
record.

assuming that the form is bound to a table, try the following, as

Private Sub cmdSave_Click()

On Error GoTo errhandler

' Validate fields
If IsNull(Me!AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If

If IsNull(Me!StartDate) Then Me!StartDate = Date

' Save current association
Me!OrgID = Forms!frmMainMenu!OrgID

Me!AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then Me.Dirty = False

' Save associated association
Dim tmpOrg As Long, tmpAssocd As Long
Dim tmpAssocn As Long

tmpOrg = Me!AssociatedID
tmpAssocd = Me!OrgID
tmpAssocn = Me!AssociationID

DoCmd.RunCommand acCmdRecordsGoToNew

Me!OrgID = tmpOrg
Me!AssociatedID = tmpAssocd
Me!AssociationID = tmpAssocn

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close , , acSaveNo

exitsub:
Exit Sub

errhandler:
Select Case err
Case 3022
' MsgBox "The organizations are already " _
& "associated to each other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", _
err, err.Description
Me.Undo
DoCmd.Close , , acSaveNo
End Select

End Sub

note that i assumed all three fields in the underlying table are Number data
type, field size Long. if this is incorrect, you'll need to change the data
types of the variables to the appropriate types.

hth


I have a table with 3 Fields. OrgID, AssociatedID and AssociationID.
From a form I capture the data for OrgID and AssociatedID. I have some
code
the worked under office 97 to save this data, then invert AssociatedID and
OrgID and save again. Since upgrading only the second half of the
operation
is commited, it seems like the first part is not. What am I missing? I
am
not a developer so please keep it simple. Here is the code.

Private Sub cmdSave_Click()
On Error GoTo errhandler

' Validate fields
If IsNull(AssociatedID) Then
MsgBox "[Associated Org]", , "Mandatory Field"
AssociatedID.SetFocus
Exit Sub
End If
If IsNull(StartDate) Then StartDate = Date

' Save current association
OrgID = [Forms]![frmMainMenu]![OrgID]

AssociationID = NextID("T_Association", "AssociationID")

If Me.Dirty = True Then
Me.Dirty = False
End If


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord



' Save associated association


Dim tmpId As Long


tmpId = OrgID
OrgID = AssociatedID
AssociatedID = tmpId
Me.Dirty = False

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

exitsub:
Exit Sub

errhandler:
Select Case Err
Case 3022
' MsgBox "The organizations are already associated to each
other.", , "Info"
' Me.Undo
SetDefaults
Case Else
ErrorMessage "frmAddAssociation - cmdSave_Click()", Err,
Err.Description
Me.Undo
DoCmd.Close acForm, Me.Name
End Select

End Sub
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

Dont Save Record Unless: 6
Dont Save OnClose if.. 2
Save Record and Close Window 3
Go To Specific Record after Requery 2
Closing and open Form whilst linking the correct record being open 1
Dirty Event Not Raised on Row Insert Where BeforeInsert Handler Ex 1
Requery Subform from a different form? 2
"Variable Not Defined" 6

Top