changing records and running code

[Guest]

Hello, I have hit a block and was hoping someone might know the answer to
this. I have a form with a subform in it. Users need to enter their
initials in one or the other if they have changed anything on either of these
forms which works fine however the users have requested that the message only
occur when they change a record not when changing from the main form to the
subform. Sounds logical however I can not seem to make this function in that
manner. I have tried using the oncurrent event but that creates an error
based on trying to open the form. Currently my code reads as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If
End Sub

I am sure its something I am just missing but any help would be soooo
appreciated.

Thanks

 

[strive4peace]

I am not sure what you are asking... can you please clarify?
Are you wanting to update the mainform UpdatedBy AND the
subform UpdatedBy when you change the subform? Just the
subform UpdatedBy when you change the subform?

try

If Len(trim(nz(me.UpdatedBy))) = 0 then
'code
End If

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Hi, thanks for responding. I am looking to update the main form but only
after the user is ready to leave the record.

I am not sure what you are asking... can you please clarify?
Are you wanting to update the mainform UpdatedBy AND the
subform UpdatedBy when you change the subform? Just the
subform UpdatedBy when you change the subform?

try

If Len(trim(nz(me.UpdatedBy))) = 0 then
'code
End If

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello, I have hit a block and was hoping someone might know the answer to
this. I have a form with a subform in it. Users need to enter their
initials in one or the other if they have changed anything on either of these
forms which works fine however the users have requested that the message only
occur when they change a record not when changing from the main form to the
subform. Sounds logical however I can not seem to make this function in that
manner. I have tried using the oncurrent event but that creates an error
based on trying to open the form. Currently my code reads as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If
End Sub

I am sure its something I am just missing but any help would be soooo
appreciated.

Thanks

 

[strive4peace]

Hi dc,

There is no way to know when the user is ready to leave
unless there is a command button they click on (which I am
sure you don't want) ... but what you can do is update the
main form when you update the subform in the subform's
BeforeUpdate event.

In the original code you posted, I see what is missing ...
the statement to do the upodate if the data is there

also, this:
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then

is better than this:
If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then

Len = length
Trim = trim leading and trailing spaces
Nz = convert null to zero (or empty string)

'~~~~~~~~~~~~~~~~~

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

'changed your condition
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If

'This will update a control on the main form
me.parent.controlname = me.UpdatedBy

End Sub

'~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi, thanks for responding. I am looking to update the main form but only
after the user is ready to leave the record.

:

I am not sure what you are asking... can you please clarify?
Are you wanting to update the mainform UpdatedBy AND the
subform UpdatedBy when you change the subform? Just the
subform UpdatedBy when you change the subform?

try

If Len(trim(nz(me.UpdatedBy))) = 0 then
'code
End If

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello, I have hit a block and was hoping someone might know the answer to
this. I have a form with a subform in it. Users need to enter their
initials in one or the other if they have changed anything on either of these
forms which works fine however the users have requested that the message only
occur when they change a record not when changing from the main form to the
subform. Sounds logical however I can not seem to make this function in that
manner. I have tried using the oncurrent event but that creates an error
based on trying to open the form. Currently my code reads as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If
End Sub

I am sure its something I am just missing but any help would be soooo
appreciated.

Thanks

 

[Guest]

Hi Crystal, Thank you so much for your knowledge and willingness to share it!!
I will definitely try the code and see how the users respond.

Thank you again!

Dayna

Hi dc,

There is no way to know when the user is ready to leave
unless there is a command button they click on (which I am
sure you don't want) ... but what you can do is update the
main form when you update the subform in the subform's
BeforeUpdate event.

In the original code you posted, I see what is missing ...
the statement to do the upodate if the data is there

also, this:
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then

is better than this:
If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then

Len = length
Trim = trim leading and trailing spaces
Nz = convert null to zero (or empty string)

'~~~~~~~~~~~~~~~~~

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

'changed your condition
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If

'This will update a control on the main form
me.parent.controlname = me.UpdatedBy

End Sub

'~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi, thanks for responding. I am looking to update the main form but only
after the user is ready to leave the record.

:

I am not sure what you are asking... can you please clarify?
Are you wanting to update the mainform UpdatedBy AND the
subform UpdatedBy when you change the subform? Just the
subform UpdatedBy when you change the subform?

try

If Len(trim(nz(me.UpdatedBy))) = 0 then
'code
End If

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

dc wrote:

Hello, I have hit a block and was hoping someone might know the answer to
this. I have a form with a subform in it. Users need to enter their
initials in one or the other if they have changed anything on either of these
forms which works fine however the users have requested that the message only
occur when they change a record not when changing from the main form to the
subform. Sounds logical however I can not seem to make this function in that
manner. I have tried using the oncurrent event but that creates an error
based on trying to open the form. Currently my code reads as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If
End Sub

I am sure its something I am just missing but any help would be soooo
appreciated.

Thanks

 

[strive4peace]

you're welcome, Dayna happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi Crystal, Thank you so much for your knowledge and willingness to share it!!
I will definitely try the code and see how the users respond.

Thank you again!

Dayna

:

Hi dc,

There is no way to know when the user is ready to leave
unless there is a command button they click on (which I am
sure you don't want) ... but what you can do is update the
main form when you update the subform in the subform's
BeforeUpdate event.

In the original code you posted, I see what is missing ...
the statement to do the upodate if the data is there

also, this:
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then

is better than this:
If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then

Len = length
Trim = trim leading and trailing spaces
Nz = convert null to zero (or empty string)

'~~~~~~~~~~~~~~~~~

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

'changed your condition
If len(trim(nz(Me!UpdatedBy,""))) = 0 Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If

'This will update a control on the main form
me.parent.controlname = me.UpdatedBy

End Sub

'~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hi, thanks for responding. I am looking to update the main form but only
after the user is ready to leave the record.

:



I am not sure what you are asking... can you please clarify?
Are you wanting to update the mainform UpdatedBy AND the
subform UpdatedBy when you change the subform? Just the
subform UpdatedBy when you change the subform?

try

If Len(trim(nz(me.UpdatedBy))) = 0 then
'code
End If

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

dc wrote:


Hello, I have hit a block and was hoping someone might know the answer to
this. I have a form with a subform in it. Users need to enter their
initials in one or the other if they have changed anything on either of these
forms which works fine however the users have requested that the message only
occur when they change a record not when changing from the main form to the
subform. Sounds logical however I can not seem to make this function in that
manner. I have tried using the oncurrent event but that creates an error
based on trying to open the form. Currently my code reads as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Display message if UpdatedBy is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!UpdatedBy) Or Me!UpdatedBy = "" Then
strMsg = "You must put your initials in the Updated By box."
strTitle = "Initials Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Me![UpdatedBy].SetFocus
Cancel = True
End If
End Sub

I am sure its something I am just missing but any help would be soooo
appreciated.

Thanks