setting focus to a field in a subform

[Guest]

hi

Can anyone help me out there?

I have a subform, and when entering data into the field and when that field
has lost focus I ask it to check the value in that field and if it is 0 then
a msgbox gives advises user that they must enter a number in that field.

when user clicks ok in msgbox the user is taken back to the subform but the
cursor has focus on the next row to input next rows data.... I want it to go
back to the field that prompted the message. I have tried setting focus to
that field after the msgbox code but it still goes to the next row!

PLEASE can some one help me asap!!!

Thanks in advance

 

[Allen Browne]

Use the BeforeUpdate event for validation.

Since there is no guarantee that the user will ever visit the field, you
need to use the BeforeUpdate event of the *form* -- not the control -- to
test if it is null.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField]) Then
Cancel = True
MsgBox "SomeField is required."
End If
End Sub

An even easier solution is to open your table in design view, select this
field, and set its Required property to Yes. You do not need any code then.

 

[Guest]

Thanks... that worked a treat.. did the easier method...

Just another question.. when user doesnt type anything in and the sys error
msg comes up.. how can I override that to have my own msg for the error, this
is a txtbox so no property for onError or error?

Can you help me this?

Thanks in advance

Use the BeforeUpdate event for validation.

Since there is no guarantee that the user will ever visit the field, you
need to use the BeforeUpdate event of the *form* -- not the control -- to
test if it is null.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField]) Then
Cancel = True
MsgBox "SomeField is required."
End If
End Sub

An even easier solution is to open your table in design view, select this
field, and set its Required property to Yes. You do not need any code then.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Can anyone help me out there?

I have a subform, and when entering data into the field and when that
field
has lost focus I ask it to check the value in that field and if it is 0
then
a msgbox gives advises user that they must enter a number in that field.

when user clicks ok in msgbox the user is taken back to the subform but
the
cursor has focus on the next row to input next rows data.... I want it to
go
back to the field that prompted the message. I have tried setting focus
to
that field after the msgbox code but it still goes to the next row!

PLEASE can some one help me asap!!!

Thanks in advance

 

[Allen Browne]

Go back into table design, and select the field.
Change the Required property back to No.
Instead set the fields Validation Rule to:
Is Not Null
Then put your custom message in the Validation Text property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks... that worked a treat.. did the easier method...

Just another question.. when user doesnt type anything in and the sys
error
msg comes up.. how can I override that to have my own msg for the error,
this
is a txtbox so no property for onError or error?

Can you help me this?

Thanks in advance

Use the BeforeUpdate event for validation.

Since there is no guarantee that the user will ever visit the field, you
need to use the BeforeUpdate event of the *form* -- not the control -- to
test if it is null.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField]) Then
Cancel = True
MsgBox "SomeField is required."
End If
End Sub

An even easier solution is to open your table in design view, select this
field, and set its Required property to Yes. You do not need any code
then.

Can anyone help me out there?

I have a subform, and when entering data into the field and when that
field
has lost focus I ask it to check the value in that field and if it is 0
then
a msgbox gives advises user that they must enter a number in that
field.

when user clicks ok in msgbox the user is taken back to the subform but
the
cursor has focus on the next row to input next rows data.... I want it
to
go
back to the field that prompted the message. I have tried setting
focus
to
that field after the msgbox code but it still goes to the next row!

 

[Guest]

Hi again

I decided to use the beforeupdate in the end see below:

Private Sub form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.quantity) Then
Cancel = True
MsgBox "Quantity Not Entered"
Else
If quantity.Value = 0 Then
Cancel = True
MsgBox "Quantity Cannot be 0"
End If
End If
End Sub

This way it traps both the null and 0 values and I can have my own error msg
;o)

Thanks you were a great help, may have been a easy task but it was a pain in
the wotsit for me, think I have been working to hard.

thanks again

Go back into table design, and select the field.
Change the Required property back to No.
Instead set the fields Validation Rule to:
Is Not Null
Then put your custom message in the Validation Text property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks... that worked a treat.. did the easier method...

Just another question.. when user doesnt type anything in and the sys
error
msg comes up.. how can I override that to have my own msg for the error,
this
is a txtbox so no property for onError or error?

Can you help me this?

Thanks in advance

Use the BeforeUpdate event for validation.

Since there is no guarantee that the user will ever visit the field, you
need to use the BeforeUpdate event of the *form* -- not the control -- to
test if it is null.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField]) Then
Cancel = True
MsgBox "SomeField is required."
End If
End Sub

An even easier solution is to open your table in design view, select this
field, and set its Required property to Yes. You do not need any code
then.


Can anyone help me out there?

I have a subform, and when entering data into the field and when that
field
has lost focus I ask it to check the value in that field and if it is 0
then
a msgbox gives advises user that they must enter a number in that
field.

when user clicks ok in msgbox the user is taken back to the subform but
the
cursor has focus on the next row to input next rows data.... I want it
to
go
back to the field that prompted the message. I have tried setting
focus
to
that field after the msgbox code but it still goes to the next row!

 

[Guest]

Since you seem to know what you're doing, wondered if you knew how to do the
following:

I have a subform where I enter items which are orderlines, the row contains
a field called subtotal which adds up the total of that row, a simple sum
where it looks at no. of items ordered and multiplys that by the price to
give a subtotal for the row.
Outside of this subform I have a field which gives the total value of the
subtotals so giving at the end a total order value.

Problem is, if user deletes a row off the subform, the total value does not
change and I would like it to update the value but not sure how to do this.

The code I have to calculate the total order value is:

Private Sub quantity_LostFocus()
Forms!order!txtTotal = Forms!order!txtTotal + txtSubTotal
End Sub

Can you help?

Thanks again

Go back into table design, and select the field.
Change the Required property back to No.
Instead set the fields Validation Rule to:
Is Not Null
Then put your custom message in the Validation Text property.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks... that worked a treat.. did the easier method...

Just another question.. when user doesnt type anything in and the sys
error
msg comes up.. how can I override that to have my own msg for the error,
this
is a txtbox so no property for onError or error?

Can you help me this?

Thanks in advance

Use the BeforeUpdate event for validation.

Since there is no guarantee that the user will ever visit the field, you
need to use the BeforeUpdate event of the *form* -- not the control -- to
test if it is null.

Example:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.SomeField]) Then
Cancel = True
MsgBox "SomeField is required."
End If
End Sub

An even easier solution is to open your table in design view, select this
field, and set its Required property to Yes. You do not need any code
then.


Can anyone help me out there?

I have a subform, and when entering data into the field and when that
field
has lost focus I ask it to check the value in that field and if it is 0
then
a msgbox gives advises user that they must enter a number in that
field.

when user clicks ok in msgbox the user is taken back to the subform but
the
cursor has focus on the next row to input next rows data.... I want it
to
go
back to the field that prompted the message. I have tried setting
focus
to
that field after the msgbox code but it still goes to the next row!

 

[Allen Browne]

I think you asked this as a separate thread?

This article might help explain when and how to store calculated results:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

 

[Guest]

Hi again

Visited the link you gave and was useful and tried it, but didnt work.

Think it may be because the quantity is for items ordered which is in table
orderline, and price is in stock table.

How can I get it to do this using two tables?

thanks

 

[Allen Browne]

The key idea is that you do not store the quantity in any table.
It is calculated as needed.

There is then no question about how to keep the stored value up to date, as
the stored value does not exist.

 

[Guest]

The thing is I need to store how many items the person has ordered per
orderline so that I can produce other reports etc and it is needed for this.
If I dont store how much they are ordering of an item how do I know how much
to send out?
Surely a quantity would be stored somewhere... if I need to print reports or
print a list of somesort showing me details of orders placed, I would want to
know how many of the items were ordered by a customer...

So can it be done?

Thanks again

 

[Allen Browne]

Yes, the quantity of an item is stored, but not the total quantity of all
items.

For an example of the kind of structure for this kind of thing, open the
Northwind sample database, the Order Details subform. The quantity of each
item is a field stored in each row of the order detail.