Placing code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
Can someone guide me as to where to place the code to force a user to
populate a field before entering a record.I have tried before update event on
the control then on the form using event procedure and that gave me the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a code
between the before update (cancell as integer) statement and the end sub?
Please advise
 
Hello,
Can someone guide me as to where to place the code to force a user to
populate a field before entering a record.I have tried before update event on
the control then on the form using event procedure and that gave me the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a code
between the before update (cancell as integer) statement and the end sub?
Please advise
Click to expand...

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
 
The best place is the forms before update event.

Make sure you let the code builder make the Sub declaration for you.

If you been playing around now...you might want to delete any code you
placed.

Now, while the form is in design mode. (make sure the FORM is selected - do
this by going edit->select form)

Now, on the event tab, select the before update event, and then click on the
[...] button. Now, choose code, and you will get:

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

In the above you can thus enter:

Private Sub Form_BeforeUpdate(Cancel As Integer)

if isnull(me.InvNum) = true then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
num"
cancel = True
me.InvNum.SetFocus
exit sub
end if


End Sub

So, put the above code for each field you need to verify, and "force" the
user to enter. Note that the above is in the forms before update event..so
the user ONLY gets the message when they try and save the form. You can't
use each controls before update event, since the user might not even go into
those controls. Further, the before update event of a control ONLY fires if
the user changes something....so if they just tab through (not change), then
the before update event of the control does NOT fire. So, we kind of have to
use the forms before date.
 
Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided excellent
directions that should work, i continue to get the error of "before update,
ambigous name detected" when i try to enter the record.Below is exactly how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice "
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your patience on
this.....

fredg said:
Hello,
Can someone guide me as to where to place the code to force a user to
populate a field before entering a record.I have tried before update event on
the control then on the form using event procedure and that gave me the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a code
between the before update (cancell as integer) statement and the end sub?
Please advise
Click to expand...

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
 
You have the line:

Private Sub Form_BeforeUpdate(Cancel As Integer)

twice. Each sub name in a module must be unique.

jk said:
Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided excellent
directions that should work, i continue to get the error of "before
update,
ambigous name detected" when i try to enter the record.Below is exactly
how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice "
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your patience
on
this.....

fredg said:
Hello,
Can someone guide me as to where to place the code to force a user to
populate a field before entering a record.I have tried before update
event on
the control then on the form using event procedure and that gave me the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a
code
between the before update (cancell as integer) statement and the end
sub?
Please advise
Click to expand...

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
Click to expand...
 
Thanks Paul,

I have tried various ways with the code and removing this one line produces
the 'compile error...sub or function not defined.I think there must be a
small thing missing from letting me do this.....any other ideas?

Paul said:
You have the line:

Private Sub Form_BeforeUpdate(Cancel As Integer)

twice. Each sub name in a module must be unique.

jk said:
Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided excellent
directions that should work, i continue to get the error of "before
update,
ambigous name detected" when i try to enter the record.Below is exactly
how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice "
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your patience
on
this.....

fredg said:
On Tue, 12 Oct 2004 19:01:08 -0700, jk wrote:

Hello,
Can someone guide me as to where to place the code to force a user to
populate a field before entering a record.I have tried before update
event on
the control then on the form using event procedure and that gave me the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a
code
between the before update (cancell as integer) statement and the end
sub?
Please advise

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
Click to expand...
Click to expand...
 
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
See the way num "" has become separated from the preceding line? VBA is
looking for a procedure called num and attempting to call it with an
argument of ""

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


jk said:
Thanks Paul,

I have tried various ways with the code and removing this one line
produces
the 'compile error...sub or function not defined.I think there must be a
small thing missing from letting me do this.....any other ideas?

Paul said:
You have the line:

Private Sub Form_BeforeUpdate(Cancel As Integer)

twice. Each sub name in a module must be unique.

jk said:
Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided excellent
directions that should work, i continue to get the error of "before
update,
ambigous name detected" when i try to enter the record.Below is exactly
how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
"
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that
control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your
patience
on
this.....

:

On Tue, 12 Oct 2004 19:01:08 -0700, jk wrote:

Hello,
Can someone guide me as to where to place the code to force a user
to
populate a field before entering a record.I have tried before update
event on
the control then on the form using event procedure and that gave me
the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a
code
between the before update (cancell as integer) statement and the end
sub?
Please advise

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
Click to expand...
Click to expand...
 
Hello Brendan,
Below is exactly how i place it into the before event and i still get
"ambigous name detected"...
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
num"
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

The control name in SQL within the form is
[InvoiceDetails1].[InvNum]......does that have anything to do with it?


Brendan Reynolds said:
See the way num "" has become separated from the preceding line? VBA is
looking for a procedure called num and attempting to call it with an
argument of ""

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


jk said:
Thanks Paul,

I have tried various ways with the code and removing this one line
produces
the 'compile error...sub or function not defined.I think there must be a
small thing missing from letting me do this.....any other ideas?

Paul said:
You have the line:

Private Sub Form_BeforeUpdate(Cancel As Integer)

twice. Each sub name in a module must be unique.

Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided excellent
directions that should work, i continue to get the error of "before
update,
ambigous name detected" when i try to enter the record.Below is exactly
how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
"
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that
control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your
patience
on
this.....

:

On Tue, 12 Oct 2004 19:01:08 -0700, jk wrote:

Hello,
Can someone guide me as to where to place the code to force a user
to
populate a field before entering a record.I have tried before update
event on
the control then on the form using event procedure and that gave me
the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i place a
code
between the before update (cancell as integer) statement and the end
sub?
Please advise

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
Click to expand...
Click to expand...
 
Yup, you have "Private Sub Form_BeforeUpdate (Cancel As Integer) in twice at
the top, and End Sub twice at the bottom. You need to delete one of each.

Probably what is happening is something like this. Someone posted some
example code which you copied and then pasted, yes? But when you create a
new procedure in VBA, VBA helpfully creates the first ("Private Sub
Form_BeforeUpdate (Cancel As Integer)") and last ("End Sub") lines for you.
If the code you pasted already included those lines, and you paste it in
between the two lines that the VBA editor created for you, you'll end up
with those two lines twice each, instead of once.

To solve it, first, open up Notepad, copy all of your code from the first
"Private Sub Form_BeforeUpdate (Cancel As Integer)" line down to the second
"End Sub" line, and paste it into Notepad. Save it as a text file. Now,
whatever happens, if things go wrong you can always get back to where you
were by copying and pasting from the text file.

Now back in the VBA editor, delete the first "Private Sub Form_BeforeUpdate
(Cancel As Integer)" line, and also delete the second "End Sub" line. Now
try to compile the code. If you get another error message, don't put the
deleted lines back in, post back here telling us what the new error message
is, and which line of code is highlighted, if any.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


jk said:
Hello Brendan,
Below is exactly how i place it into the before event and i still get
"ambigous name detected"...
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no invoice
num"
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

The control name in SQL within the form is
[InvoiceDetails1].[InvNum]......does that have anything to do with it?


Brendan Reynolds said:
MsgBox "you must enter the invoice number", vbExclamation, "no
invoice
"
num ""
Click to expand...

See the way num "" has become separated from the preceding line? VBA is
looking for a procedure called num and attempting to call it with an
argument of ""

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


jk said:
Thanks Paul,

I have tried various ways with the code and removing this one line
produces
the 'compile error...sub or function not defined.I think there must be
a
small thing missing from letting me do this.....any other ideas?

:

You have the line:

Private Sub Form_BeforeUpdate(Cancel As Integer)

twice. Each sub name in a module must be unique.

Thanks Fredg and Albert,

This is fraustrating since both you and Albert have provided
excellent
directions that should work, i continue to get the error of "before
update,
ambigous name detected" when i try to enter the record.Below is
exactly
how i
placed the code in the forms before update event procedure:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.InvNum) = True Then
MsgBox "you must enter the invoice number", vbExclamation, "no
invoice
"
num ""
Cancel = True
Me.InvNum.SetFocus
Exit Sub
End If


End Sub

End Sub

Does the SQL name on form have anything to do with this since that
control
under SQL view is [InvoiceDetails1].[InvNum]??? Thanks for your
patience
on
this.....

:

On Tue, 12 Oct 2004 19:01:08 -0700, jk wrote:

Hello,
Can someone guide me as to where to place the code to force a
user
to
populate a field before entering a record.I have tried before
update
event on
the control then on the form using event procedure and that gave
me
the
results of an error of ambigous name on form.
Private Sub InvNum_BeforeUpdate(Cancel As Integer)

End Sub
Above two statements is what i see in building the code.Do i
place a
code
between the before update (cancell as integer) statement and the
end
sub?
Please advise

Yes. Code goes between the 2 existing lines.

Without knowing fully what you are doing, perhaps this code should
go
in the Form's BeforeUpdate event, not InvNum BeforeUpdate.

You'll probably want something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull([CompanyName]) Or [CompanyName] = "" Then
MsgBox "You must enter a name in the 'CompanyName' Field."
Cancel = True
[CompanyName].SetFocus
End If
End Sub

If [CompanyName] is empty, then you'll get a message and the focus
will return to [CompanyName].
Click to expand...
Click to expand...
Click to expand...
 
Back
Top