Field validation against field in the table

[Majic]

I created a database to scan item number and place it in a queue
FIFO. Now, I have another form that shows the pending item number in
the order it was scanned. I have a table with Item number,
description and quantity.
I want to be able to scan an item number and validate that number.
The reason for doing that, when I retrieve the item number that needs
to be pulled off the shelf, I want to be able able to automate
verification that the number that I pulled is the same number that I
need to pull to reduce errors.

If someone knows how to help that will be great.

Thank you

 

[Allen Browne]

Presumably this is a Number field (not a Text field), and you need to verify
that the number just entered is the minimum value in Table1. (Since it's
FIFO queue, I am assuming that the other processed numbers have been
removed.)

In the AfterUpdate event procedure of the text box where the entry is made,
use DMin() to get the minimum number from the FIFO table. This kind of
thing:

Private Sub Text0_AfterUpdate()
Dim varResult As Variant

With Me.Text0
If Not IsNull(.Value) Then
varResult = DMin("MyScanField", "Table1")
If Not IsNull(varResult) Then
If .Value <> varResult Then
MsgBox "Min was " & varResult, vbExclamation,
"Warning"
End If
End If
End If
End With
End Sub

 

[Majic]

Thank you so much Allen
I will try it

Presumably this is a Number field (not a Text field), and you need to verify
that the number just entered is the minimum value in Table1. (Since it's
FIFO queue, I am assuming that the other processed numbers have been
removed.)

In the AfterUpdate event procedure of the text box where the entry is made,
use DMin() to get the minimum number from the FIFO table. This kind of
thing:

Private Sub Text0_AfterUpdate()
Dim varResult As Variant

With Me.Text0
If Not IsNull(.Value) Then
varResult = DMin("MyScanField", "Table1")
If Not IsNull(varResult) Then
If .Value <> varResult Then
MsgBox "Min was " & varResult, vbExclamation,
"Warning"
End If
End If
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

 

[Majic]

Presumably this is a Number field (not a Text field), and you need to verify
that the number just entered is the minimum value in Table1. (Since it's
FIFO queue, I am assuming that the other processed numbers have been
removed.)

In the AfterUpdate event procedure of the text box where the entry is made,
use DMin() to get the minimum number from the FIFO table. This kind of
thing:

Private Sub Text0_AfterUpdate()
Dim varResult As Variant

With Me.Text0
If Not IsNull(.Value) Then
varResult = DMin("MyScanField", "Table1")
If Not IsNull(varResult) Then
If .Value <> varResult Then
MsgBox "Min was " & varResult, vbExclamation,
"Warning"
End If
End If
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

I tried this and did not work. If it is a text field and set for 6
characters, should I handle it differently. Here is what it is:
Name of the field: Sales Order Scan
Text (6 char)
Table name is Part Number

Could you please direct me of what I need to do? I do apologize for
my lack of information?

Your assistance is highly appreciated.
I also having a problem finding my responses. I used to have a log
under my profile that shows the messages and responses and I do not
now. I have search all group message to find it. Any suggestion.

Thank you

 

[Majic]

Presumably this is a Number field (not a Text field), and you need to verify
that the number just entered is the minimum value in Table1. (Since it's
FIFO queue, I am assuming that the other processed numbers have been
removed.)

In the AfterUpdate event procedure of the text box where the entry is made,
use DMin() to get the minimum number from the FIFO table. This kind of
thing:

Private Sub Text0_AfterUpdate()
Dim varResult As Variant

With Me.Text0
If Not IsNull(.Value) Then
varResult = DMin("MyScanField", "Table1")
If Not IsNull(varResult) Then
If .Value <> varResult Then
MsgBox "Min was " & varResult, vbExclamation,
"Warning"
End If
End If
End If
End With
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

Allen,
It does work, however, if you have more than 1 item number on the
list, it override the first item. In other words, when you validate
the number and try to go to next record it copies the first item
number.
Here is what I did:
I have a query with the existing data and 2 fields to scan to validate
two of the fields in the same query. There is a subform that feeds to
the query.
Item Number, Quantity, (field to scan Item and validate it from Item
Number), (field to enter qty and validate it with the quantity)
So, when i enter Item and it does work as far as the validation.
However, if I have two items on the list once I verify the first item
versus Item number and i go to the next item then it changes it to the
previous item.

Please let me know what I am doing wrong.

Thank you

 

[Allen Browne]

Sorry: I'm down with the 'flu at present, not able to think straight.

Perhaps someone else can make a suggestion.

 

[Majic]

Sorry: I'm down with the 'flu at present, not able to think straight.

Perhaps someone else can make a suggestion.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html








- Show quoted text -

I hope you feel better. Thank you

 

[Majic]

Allen,
It does work, however, if you have more than 1 item number on the
list, it override the first item. In other words, when youvalidate
the number and try to go to next record it copies the first item
number.
Here is what I did:
I have aquerywith the existing data and 2 fields to scan tovalidate
two of the fields in the samequery. There is a subform that feeds to
thequery.
Item Number, Quantity, (fieldto scan Item andvalidateit from Item
Number), (fieldto enter qty andvalidateit with the quantity)
So, when i enter Item and it does work as far as the validation.
However, if I have two items on the list once I verify the first item
versus Item number and i go to the next item then it changes it to the
previous item.

Please let me know what I am doing wrong.

Thank you- Hide quoted text -

- Show quoted text -

Could anyone answer me for this validation problem? I have this issue
for sometime now.
If you could help please let me know

Thank you
majic

 

[Jeff Boyce]

Majic

If you post a new thread, instead of continuing the one already started, you
may lose the benefit of having the same person better understanding what you
are trying to do. Anyone starting here will probably need to start over and
ask some of the same questions.

If these public, volunteer newsgroups are not providing an answer quickly
enough for your situation, consider hiring help...

I'm not sure I'm following from your description, but it sounds like you may
have a form that has a control which is NOT bound to an underlying field in
a table.

Regards

Jeff Boyce
Microsoft Office/Access MVP