Moving to a Control

  • Thread starter Thread starter scott
  • Start date Start date
S

scott

I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
 
Scott,

Why are you using EVAL( )?
BTW, your post doesn't have a closing ')' to the eval function.

what controls afterupdate event is this code in?

Are you trying to delete a value in control "ProjEndDate"? If so, just set
its value:

Me.ProjEndDate.Value = NULL
or
Me.ProjEndDate.Value = ""

To move the cursor to a particular control,
Change: Docmd.gotoControl "controlName"
To: Me.ControlName.SetFocus

HTH
Dale
 
What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If
 
Between the two answers I was able to get it to work. Thank you very much.

I was not able to use the isnull function and the eval function worked the
way it was supposed to. In other words, when I used the isnull function, it
didn't return the answer True for combo49 being Null, even though the value
was removed (and therefore didn't execute the code), whereas it did return
the answer True when I used the eval function.

I am curious how the isnull funtion is supposed to to work and what the
nuances are because I have used the eval function a lot in my code. It works
well, but I would prefer to use the isnull if it is better and it does what
it is supposed to do. I got that code when I moved from Access 2 macros to
using VBA code in Access 97. Access put that into the code when it converted
the Access 2 macros. I just continued to use it because it worked.

I suppose I should have told you that I was starting in the combo49 control.
What I needed it to do was execute the code you helped me with in the event
the value in the combo49 is removed. In other words, if the value is
removed, it is supposed to delete what is in ProjEndDate and go back to
combo49 (the key to getting it to work was setting the control Projenddate
value to Null, which I didn't know how to do - I still don't really
understand why it would not allow it to go back to combo49, but that is a
mute point for now).

If the user just replaces the value in combo49 with a different value (i.e.,
updates it), it is supposed to go on to execute other code, which is why I
have it exit the sub. The rest of the code is working fine.

Klatuu said:
What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If

--
Dave Hargis, Microsoft Access MVP


scott said:
I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
Click to expand...
Click to expand...
 
If the IsNull function is not returning true, the value you are checking is
not null.
That means something else is in the control. It could be a null string ""
or vbNullstring. The Eval function is useful for some things, but this is
not one of them. The code I posted would work if the controls are actually
Null. The Me.Undo will remove all values in all controls. In your code you
were trying to move to a control and delete the contents, then you delete the
record. You don't need to remove a value if you are going to delete the
record.

I don't think you can set focus to a different control in the After Update
event. Using the Before Update, as you should be, you don't have to move the
control at all. It stays in the control you were in.

If it is possible the control is not null, but a null string, then you can
test it like this:

If "[Forms]![RequestForm]![combo49] & vbNullString = vbNullString Or
[Forms]![RequestForm]![periodend] & vbNullString Then

IsNull returns True if the tested value is Null.

Believe me, this is the correct way to do what you are trying to do. Your
code contains a lot of useless code that only adds overhead to what you are
trying to do.
--
Dave Hargis, Microsoft Access MVP


scott said:
Between the two answers I was able to get it to work. Thank you very much.

I was not able to use the isnull function and the eval function worked the
way it was supposed to. In other words, when I used the isnull function, it
didn't return the answer True for combo49 being Null, even though the value
was removed (and therefore didn't execute the code), whereas it did return
the answer True when I used the eval function.

I am curious how the isnull funtion is supposed to to work and what the
nuances are because I have used the eval function a lot in my code. It works
well, but I would prefer to use the isnull if it is better and it does what
it is supposed to do. I got that code when I moved from Access 2 macros to
using VBA code in Access 97. Access put that into the code when it converted
the Access 2 macros. I just continued to use it because it worked.

I suppose I should have told you that I was starting in the combo49 control.
What I needed it to do was execute the code you helped me with in the event
the value in the combo49 is removed. In other words, if the value is
removed, it is supposed to delete what is in ProjEndDate and go back to
combo49 (the key to getting it to work was setting the control Projenddate
value to Null, which I didn't know how to do - I still don't really
understand why it would not allow it to go back to combo49, but that is a
mute point for now).

If the user just replaces the value in combo49 with a different value (i.e.,
updates it), it is supposed to go on to execute other code, which is why I
have it exit the sub. The rest of the code is working fine.

Klatuu said:
What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If

--
Dave Hargis, Microsoft Access MVP


scott said:
I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
Dave,

I was a little confused about what the OP was trying to accomplish as well.
I've used EVAL to evaluate text I entered into a textbox, and I guess that
could return a NULL if I entered the right function into the textbox:

strTest = "IIF(True, NULL, FALSE)"
ISNULL(eval(strTest))

Personally when checking for Null or NullString, I prefer to use:

If LEN([Forms]![RequestForm]![combo49] & vbNullString) = 0

Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Klatuu said:
If the IsNull function is not returning true, the value you are checking is
not null.
That means something else is in the control. It could be a null string ""
or vbNullstring. The Eval function is useful for some things, but this is
not one of them. The code I posted would work if the controls are actually
Null. The Me.Undo will remove all values in all controls. In your code you
were trying to move to a control and delete the contents, then you delete the
record. You don't need to remove a value if you are going to delete the
record.

I don't think you can set focus to a different control in the After Update
event. Using the Before Update, as you should be, you don't have to move the
control at all. It stays in the control you were in.

If it is possible the control is not null, but a null string, then you can
test it like this:

If "[Forms]![RequestForm]![combo49] & vbNullString = vbNullString Or
[Forms]![RequestForm]![periodend] & vbNullString Then

IsNull returns True if the tested value is Null.

Believe me, this is the correct way to do what you are trying to do. Your
code contains a lot of useless code that only adds overhead to what you are
trying to do.
--
Dave Hargis, Microsoft Access MVP


scott said:
Between the two answers I was able to get it to work. Thank you very much.

I was not able to use the isnull function and the eval function worked the
way it was supposed to. In other words, when I used the isnull function, it
didn't return the answer True for combo49 being Null, even though the value
was removed (and therefore didn't execute the code), whereas it did return
the answer True when I used the eval function.

I am curious how the isnull funtion is supposed to to work and what the
nuances are because I have used the eval function a lot in my code. It works
well, but I would prefer to use the isnull if it is better and it does what
it is supposed to do. I got that code when I moved from Access 2 macros to
using VBA code in Access 97. Access put that into the code when it converted
the Access 2 macros. I just continued to use it because it worked.

I suppose I should have told you that I was starting in the combo49 control.
What I needed it to do was execute the code you helped me with in the event
the value in the combo49 is removed. In other words, if the value is
removed, it is supposed to delete what is in ProjEndDate and go back to
combo49 (the key to getting it to work was setting the control Projenddate
value to Null, which I didn't know how to do - I still don't really
understand why it would not allow it to go back to combo49, but that is a
mute point for now).

If the user just replaces the value in combo49 with a different value (i.e.,
updates it), it is supposed to go on to execute other code, which is why I
have it exit the sub. The rest of the code is working fine.

Klatuu said:
What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If

--
Dave Hargis, Microsoft Access MVP


:

I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
I agree. Your test is fine, we all have different styles.

I don't use Eval that much, but on one project, I found a great use for it.
I was used to enforce business rules for some complex scenerios. What I did
was put the calcuations and constraint check in a table field. Then once the
record was selected based on other basic rules and constraints, I did an Eval
on the field. Worked like a charm.
--
Dave Hargis, Microsoft Access MVP


Dale Fye said:
Dave,

I was a little confused about what the OP was trying to accomplish as well.
I've used EVAL to evaluate text I entered into a textbox, and I guess that
could return a NULL if I entered the right function into the textbox:

strTest = "IIF(True, NULL, FALSE)"
ISNULL(eval(strTest))

Personally when checking for Null or NullString, I prefer to use:

If LEN([Forms]![RequestForm]![combo49] & vbNullString) = 0

Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Klatuu said:
If the IsNull function is not returning true, the value you are checking is
not null.
That means something else is in the control. It could be a null string ""
or vbNullstring. The Eval function is useful for some things, but this is
not one of them. The code I posted would work if the controls are actually
Null. The Me.Undo will remove all values in all controls. In your code you
were trying to move to a control and delete the contents, then you delete the
record. You don't need to remove a value if you are going to delete the
record.

I don't think you can set focus to a different control in the After Update
event. Using the Before Update, as you should be, you don't have to move the
control at all. It stays in the control you were in.

If it is possible the control is not null, but a null string, then you can
test it like this:

If "[Forms]![RequestForm]![combo49] & vbNullString = vbNullString Or
[Forms]![RequestForm]![periodend] & vbNullString Then

IsNull returns True if the tested value is Null.

Believe me, this is the correct way to do what you are trying to do. Your
code contains a lot of useless code that only adds overhead to what you are
trying to do.
--
Dave Hargis, Microsoft Access MVP


scott said:
Between the two answers I was able to get it to work. Thank you very much.

I was not able to use the isnull function and the eval function worked the
way it was supposed to. In other words, when I used the isnull function, it
didn't return the answer True for combo49 being Null, even though the value
was removed (and therefore didn't execute the code), whereas it did return
the answer True when I used the eval function.

I am curious how the isnull funtion is supposed to to work and what the
nuances are because I have used the eval function a lot in my code. It works
well, but I would prefer to use the isnull if it is better and it does what
it is supposed to do. I got that code when I moved from Access 2 macros to
using VBA code in Access 97. Access put that into the code when it converted
the Access 2 macros. I just continued to use it because it worked.

I suppose I should have told you that I was starting in the combo49 control.
What I needed it to do was execute the code you helped me with in the event
the value in the combo49 is removed. In other words, if the value is
removed, it is supposed to delete what is in ProjEndDate and go back to
combo49 (the key to getting it to work was setting the control Projenddate
value to Null, which I didn't know how to do - I still don't really
understand why it would not allow it to go back to combo49, but that is a
mute point for now).

If the user just replaces the value in combo49 with a different value (i.e.,
updates it), it is supposed to go on to execute other code, which is why I
have it exit the sub. The rest of the code is working fine.

:

What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If

--
Dave Hargis, Microsoft Access MVP


:

I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
I've only used eval a couple of times as well, but it can be very useful in
circumstances like you describe.


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Klatuu said:
I agree. Your test is fine, we all have different styles.

I don't use Eval that much, but on one project, I found a great use for it.
I was used to enforce business rules for some complex scenerios. What I did
was put the calcuations and constraint check in a table field. Then once the
record was selected based on other basic rules and constraints, I did an Eval
on the field. Worked like a charm.
--
Dave Hargis, Microsoft Access MVP


Dale Fye said:
Dave,

I was a little confused about what the OP was trying to accomplish as well.
I've used EVAL to evaluate text I entered into a textbox, and I guess that
could return a NULL if I entered the right function into the textbox:

strTest = "IIF(True, NULL, FALSE)"
ISNULL(eval(strTest))

Personally when checking for Null or NullString, I prefer to use:

If LEN([Forms]![RequestForm]![combo49] & vbNullString) = 0

Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Klatuu said:
If the IsNull function is not returning true, the value you are checking is
not null.
That means something else is in the control. It could be a null string ""
or vbNullstring. The Eval function is useful for some things, but this is
not one of them. The code I posted would work if the controls are actually
Null. The Me.Undo will remove all values in all controls. In your code you
were trying to move to a control and delete the contents, then you delete the
record. You don't need to remove a value if you are going to delete the
record.

I don't think you can set focus to a different control in the After Update
event. Using the Before Update, as you should be, you don't have to move the
control at all. It stays in the control you were in.

If it is possible the control is not null, but a null string, then you can
test it like this:

If "[Forms]![RequestForm]![combo49] & vbNullString = vbNullString Or
[Forms]![RequestForm]![periodend] & vbNullString Then

IsNull returns True if the tested value is Null.

Believe me, this is the correct way to do what you are trying to do. Your
code contains a lot of useless code that only adds overhead to what you are
trying to do.
--
Dave Hargis, Microsoft Access MVP


:

Between the two answers I was able to get it to work. Thank you very much.

I was not able to use the isnull function and the eval function worked the
way it was supposed to. In other words, when I used the isnull function, it
didn't return the answer True for combo49 being Null, even though the value
was removed (and therefore didn't execute the code), whereas it did return
the answer True when I used the eval function.

I am curious how the isnull funtion is supposed to to work and what the
nuances are because I have used the eval function a lot in my code. It works
well, but I would prefer to use the isnull if it is better and it does what
it is supposed to do. I got that code when I moved from Access 2 macros to
using VBA code in Access 97. Access put that into the code when it converted
the Access 2 macros. I just continued to use it because it worked.

I suppose I should have told you that I was starting in the combo49 control.
What I needed it to do was execute the code you helped me with in the event
the value in the combo49 is removed. In other words, if the value is
removed, it is supposed to delete what is in ProjEndDate and go back to
combo49 (the key to getting it to work was setting the control Projenddate
value to Null, which I didn't know how to do - I still don't really
understand why it would not allow it to go back to combo49, but that is a
mute point for now).

If the user just replaces the value in combo49 with a different value (i.e.,
updates it), it is supposed to go on to execute other code, which is why I
have it exit the sub. The rest of the code is working fine.

:

What you are trying to do should be in the Before update event. Also, you
don't need the Eval function. Unless there is code following the Exit Sub,
you don't need it. Is Null is for SQL, in VBA you use the IsNull Function.
Try this in the Before Update event:

If IsNull("[Forms]![RequestForm]![combo49]) Or
IsNull([Forms]![RequestForm]![periodend]) Then
With Me
.Label67.Visible = False
.ProjEndDate.Visible = True
.Undo
End With
Cancel = True
End If

--
Dave Hargis, Microsoft Access MVP


:

I'm doing a relatively simple after update event procedure that is not
letting me go back to the control from which I start. Can you tell me what I
need to do?

If (Eval("[Forms]![RequestForm]![combo49] Is Null or
[Forms]![RequestForm]![periodend] Is Null")) Then
Me!Label67.Visible = False
Me!ProjEndDate.Visible = True
DoCmd.GoToControl "ProjEndDate"
DoCmd.RunCommand acCmdDelete
DoCmd.GoToControl "combo49"
End If

Exit Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top