automatic update of textbox

[Linda Brown]

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda

 

[Klatuu]

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.

 

[Linda Brown]

No, I am sorry but the focus is not left in the control. Can I add some code
to state that after selecting vbOkOnly, the focus goes back to SuppCode?
That was my intention. I added this:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If
End With
End Sub

After selecting vbOkOnly, can I add something to include the DoCmd and
SetFocus statements to accomplish going back to the SuppCode so that someone
who's entering code does not skip it, as the Scac is crucial to the output?
(After I enter data into a form, I output all the entries to a text delimited
spreadsheet. From there, all I have to do is select an entire line and copy,
then paste, into our mainframe. That Scac field is very important.
Currently there has not been anything set up to make the operator aware that
there is a problem if the Scac cannot be found in the lookup.)

Thank you! --Linda

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.
--
Dave Hargis, Microsoft Access MVP

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda

 

[Klatuu]

I need some more explicit explaination, Linda. You have me confused.

You have a control named scac on your form with a Dlookup as the control
source.
It references the control SuppCode. It appears that if a value in SuppCode
doesn't produce any results in the control scac, then you want to alert the
user and make SuppCode the active control. If that is not correct, please
tell me what you really want.

The code I posted should do that, but in retrospect. It probably should go
in the Form before update event. Also, once again, these lines are
redundant. They do the same thing. You don't need both of them. The
Setfocus method is preferred. Don't put both lines in your code.

DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus

So do it like this - without both lines in your code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With

You don't need to add any additional lines to the code.
End Sub

--
Dave Hargis, Microsoft Access MVP

No, I am sorry but the focus is not left in the control. Can I add some code
to state that after selecting vbOkOnly, the focus goes back to SuppCode?
That was my intention. I added this:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If
End With
End Sub

After selecting vbOkOnly, can I add something to include the DoCmd and
SetFocus statements to accomplish going back to the SuppCode so that someone
who's entering code does not skip it, as the Scac is crucial to the output?
(After I enter data into a form, I output all the entries to a text delimited
spreadsheet. From there, all I have to do is select an entire line and copy,
then paste, into our mainframe. That Scac field is very important.
Currently there has not been anything set up to make the operator aware that
there is a problem if the Scac cannot be found in the lookup.)

Thank you! --Linda

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.
--
Dave Hargis, Microsoft Access MVP

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda

 

[Linda Brown]

Sorry to confuse you, but I, too, am confused.

When I paste your code as this:
Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With
End Sub

I get the following error message:
Run-time error '2108':
You must save the field before you execute the GoToControl action,
the GoToControl method, or the SetFocus method.

I put the code in the SuppCode event because the Scac is automatically
looked up.

Can I supply you with more information to help you assist me? (I do
appreciate your taking your busy time with this matter.) --lb

I need some more explicit explaination, Linda. You have me confused.

You have a control named scac on your form with a Dlookup as the control
source.
It references the control SuppCode. It appears that if a value in SuppCode
doesn't produce any results in the control scac, then you want to alert the
user and make SuppCode the active control. If that is not correct, please
tell me what you really want.

The code I posted should do that, but in retrospect. It probably should go
in the Form before update event. Also, once again, these lines are
redundant. They do the same thing. You don't need both of them. The
Setfocus method is preferred. Don't put both lines in your code.

DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus

So do it like this - without both lines in your code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With

You don't need to add any additional lines to the code.
End Sub

--
Dave Hargis, Microsoft Access MVP

No, I am sorry but the focus is not left in the control. Can I add some code
to state that after selecting vbOkOnly, the focus goes back to SuppCode?
That was my intention. I added this:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If
End With
End Sub

After selecting vbOkOnly, can I add something to include the DoCmd and
SetFocus statements to accomplish going back to the SuppCode so that someone
who's entering code does not skip it, as the Scac is crucial to the output?
(After I enter data into a form, I output all the entries to a text delimited
spreadsheet. From there, all I have to do is select an entire line and copy,
then paste, into our mainframe. That Scac field is very important.
Currently there has not been anything set up to make the operator aware that
there is a problem if the Scac cannot be found in the lookup.)

Thank you! --Linda

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.
--
Dave Hargis, Microsoft Access MVP


:

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda

 

[Klatuu]

Sorry, Linda. I left a line out. I think this will do it.

As I said in the last post, move it to the Form's before update event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.Undo <-------------- Add this line to your code
.SuppCode.SetFocus
End If
End With


--
Dave Hargis, Microsoft Access MVP

Sorry to confuse you, but I, too, am confused.

When I paste your code as this:
Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With
End Sub

I get the following error message:
Run-time error '2108':
You must save the field before you execute the GoToControl action,
the GoToControl method, or the SetFocus method.

I put the code in the SuppCode event because the Scac is automatically
looked up.

Can I supply you with more information to help you assist me? (I do
appreciate your taking your busy time with this matter.) --lb

I need some more explicit explaination, Linda. You have me confused.

You have a control named scac on your form with a Dlookup as the control
source.
It references the control SuppCode. It appears that if a value in SuppCode
doesn't produce any results in the control scac, then you want to alert the
user and make SuppCode the active control. If that is not correct, please
tell me what you really want.

The code I posted should do that, but in retrospect. It probably should go
in the Form before update event. Also, once again, these lines are
redundant. They do the same thing. You don't need both of them. The
Setfocus method is preferred. Don't put both lines in your code.

DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus

So do it like this - without both lines in your code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With

You don't need to add any additional lines to the code.
End Sub

--
Dave Hargis, Microsoft Access MVP

No, I am sorry but the focus is not left in the control. Can I add some code
to state that after selecting vbOkOnly, the focus goes back to SuppCode?
That was my intention. I added this:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If
End With
End Sub

After selecting vbOkOnly, can I add something to include the DoCmd and
SetFocus statements to accomplish going back to the SuppCode so that someone
who's entering code does not skip it, as the Scac is crucial to the output?
(After I enter data into a form, I output all the entries to a text delimited
spreadsheet. From there, all I have to do is select an entire line and copy,
then paste, into our mainframe. That Scac field is very important.
Currently there has not been anything set up to make the operator aware that
there is a problem if the Scac cannot be found in the lookup.)

Thank you! --Linda

:

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.
--
Dave Hargis, Microsoft Access MVP


:

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda

 

[Linda Brown]

Sorry about my delay in responding, but I could not get back into the system
until now.

Would it make a difference that the form is a subform, because the code
still does not work exactly right? I have a parent form with about 3 or 4
subforms on it. The particular form I need to work correctly is on the first
subform in the parent. (I am sure you don't want me to send any database
because I also have two other databases that are linked to this particular
one.)

I do not get an error message and the cursor jumps on to the next field....

Thank you. --Linda

Sorry, Linda. I left a line out. I think this will do it.

As I said in the last post, move it to the Form's before update event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.Undo <-------------- Add this line to your code
.SuppCode.SetFocus
End If
End With


--
Dave Hargis, Microsoft Access MVP

Sorry to confuse you, but I, too, am confused.

When I paste your code as this:
Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With
End Sub

I get the following error message:
Run-time error '2108':
You must save the field before you execute the GoToControl action,
the GoToControl method, or the SetFocus method.

I put the code in the SuppCode event because the Scac is automatically
looked up.

Can I supply you with more information to help you assist me? (I do
appreciate your taking your busy time with this matter.) --lb

I need some more explicit explaination, Linda. You have me confused.

You have a control named scac on your form with a Dlookup as the control
source.
It references the control SuppCode. It appears that if a value in SuppCode
doesn't produce any results in the control scac, then you want to alert the
user and make SuppCode the active control. If that is not correct, please
tell me what you really want.

The code I posted should do that, but in retrospect. It probably should go
in the Form before update event. Also, once again, these lines are
redundant. They do the same thing. You don't need both of them. The
Setfocus method is preferred. Don't put both lines in your code.

DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus

So do it like this - without both lines in your code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
Cancel = True
.SuppCode.SetFocus
End If
End With

You don't need to add any additional lines to the code.
End Sub

--
Dave Hargis, Microsoft Access MVP


:

No, I am sorry but the focus is not left in the control. Can I add some code
to state that after selecting vbOkOnly, the focus goes back to SuppCode?
That was my intention. I added this:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)
With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If
End With
End Sub

After selecting vbOkOnly, can I add something to include the DoCmd and
SetFocus statements to accomplish going back to the SuppCode so that someone
who's entering code does not skip it, as the Scac is crucial to the output?
(After I enter data into a form, I output all the entries to a text delimited
spreadsheet. From there, all I have to do is select an entire line and copy,
then paste, into our mainframe. That Scac field is very important.
Currently there has not been anything set up to make the operator aware that
there is a problem if the Scac cannot be found in the lookup.)

Thank you! --Linda

:

Put it in the Before Update event of the control. Note modifications:

Private Sub SuppCode_BeforeUpdate(Cancel As Integer)

With Me
If IsNull(.Scac) Then
Beep
MsgBox "Check Supp Code!!!"
End If
End With

End Sub

These two lines do exactly the same thing:
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
In this case, neiter are needed. Canceling the Before Update event leaves
the focus in the control.

This line:
Me.Scac.Value

The Value property is not necessary. It is the default property for a form
control.
--
Dave Hargis, Microsoft Access MVP


:

I am using Access 2003, SP 2. I have the following as the control source in
unbound textbox titled "Scac" in a form with the following value.
=DLookUp("[ScacCode]","tblScacCodes","[SupCode]=[Forms]![frmContainerAndProNumbers]![tblProNumbers subform].[Form]![SuppCode]")

The following code is supposed to notify me if bound textbox "SuppCode" is
missing in the lookup table and then go back to the SuppCode field. But
something is not working exactly right. Which textbox do I need to add this
to and after which event?

If IsNull(Me.Scac.Value) Then
Beep
MsgBox "Check Supp Code!!!"
DoCmd.GoToControl "SuppCode"
Me.SuppCode.SetFocus
End If

Thank you for any assistance and best wishes! --Linda