Before_update validation

One of the fields on my form corresponds to a field in
another table and I wuld like to be able to verify that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the problem
but not until the record is being saved. I can't make it a
combobox because the list is toooo long. Any suggestions?
Thanks for the help.

Would DLookup work for your scenario? DLookup can look up the value based on
a value in your present table or another table. For instance I use DLookup
to check the time and City of a driver's last delivery using the current
date.

"Small Fry" wrote in message
...
One of the fields on my form corresponds to a field in
another table and I wuld like to be able to verify that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the problem
but not until the record is being saved. I can't make it a
combobox because the list is toooo long. Any suggestions?
Thanks for the help.

I'm not sure, but I don't think so. I don't want to auto-
populate I just want to verify that the data exists in
another table.
-----Original Message-----
Would DLookup work for your scenario? DLookup can look up
the value based on
a value in your present table or another table. For
instance I use DLookup
to check the time and City of a driver's last delivery
using the current
date.

"Small Fry" wrote
in message
...
One of the fields on my form corresponds to a field in
another table and I wuld like to be able to verify that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the problem
but not until the record is being saved. I can't make
it a
combobox because the list is toooo long. Any
suggestions?
Thanks for the help.


.

SmallFry, What about something like this
(Air Code)

If IsNull(DLookup("[MyField]", "MyTable", "[MyTableID] = " & intMyCriteria)
then
....Do this
else
....Do this
End if

--
Reggie

www.smittysinet.com
----------
"SmallFry" wrote in message
...
I'm not sure, but I don't think so. I don't want to auto-
populate I just want to verify that the data exists in
another table.
-----Original Message-----
Would DLookup work for your scenario? DLookup can look up
the value based on
a value in your present table or another table. For
instance I use DLookup
to check the time and City of a driver's last delivery
using the current
date.

"Small Fry" wrote
in message
...
One of the fields on my form corresponds to a field in
another table and I wuld like to be able to verify that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the problem
but not until the record is being saved. I can't make
it a
combobox because the list is toooo long. Any
suggestions?
Thanks for the help.


.

Thanks for the informaton but my VBA skills are sorely
lacking so can you spell it out more using the following:
text box on form = FrameID
table = tblManuf
field name in table = PartID
If PartID does not exist I would just have a msgBox (and
then return focus to textbox or ?)
Will this code go in the before_update property of the
textbox?

-----Original Message-----
SmallFry, What about something like this
(Air Code)

If IsNull(DLookup("[MyField]", "MyTable", "[MyTableID]
= " & intMyCriteria)
then
....Do this
else
....Do this
End if

--
Reggie

www.smittysinet.com
----------
"SmallFry" wrote in
message
...
I'm not sure, but I don't think so. I don't want to
auto-
populate I just want to verify that the data exists in
another table.
-----Original Message-----
Would DLookup work for your scenario? DLookup can look
up
the value based on
a value in your present table or another table. For
instance I use DLookup
to check the time and City of a driver's last delivery
using the current
date.

"Small Fry" wrote
in message
...
One of the fields on my form corresponds to a field
in
another table and I wuld like to be able to verify
that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the
problem
but not until the record is being saved. I can't make
it a
combobox because the list is toooo long. Any
suggestions?
Thanks for the help.


.



.

SmallFry, Try one of these depending on the type of data you are checking
for(number, string, date). Watch for word wrapping below.

*****************************************
''''If it's a Number
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim intNum As Integer
intNum = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]= " & intNum)) Then
MsgBox "Number does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If

End Sub
****************************************
''''If it's a String
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim str As String
str = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]= '" & str & "'"))
Then
MsgBox "String does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub
*****************************************
''''If it's a Date
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim dte As Date

If IsNull(Me.FrameID) Then Exit Sub
dte = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]= #" & dte & "#"))
Then
MsgBox "Date does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub

Hope it helps!
--
Reggie

www.smittysinet.com
----------
"Small Fry" wrote in message
...
Thanks for the informaton but my VBA skills are sorely
lacking so can you spell it out more using the following:
text box on form = FrameID
table = tblManuf
field name in table = PartID
If PartID does not exist I would just have a msgBox (and
then return focus to textbox or ?)
Will this code go in the before_update property of the
textbox?

-----Original Message-----
SmallFry, What about something like this
(Air Code)

If IsNull(DLookup("[MyField]", "MyTable", "[MyTableID]
= " & intMyCriteria)
then
....Do this
else
....Do this
End if

--
Reggie

www.smittysinet.com
----------
"SmallFry" wrote in
message
...
I'm not sure, but I don't think so. I don't want to
auto-
populate I just want to verify that the data exists in
another table.
-----Original Message-----
Would DLookup work for your scenario? DLookup can look
up
the value based on
a value in your present table or another table. For
instance I use DLookup
to check the time and City of a driver's last delivery
using the current
date.

"Small Fry" wrote
in message
...
One of the fields on my form corresponds to a field
in
another table and I wuld like to be able to verify
that
the input in the form is ok before the entire form is
filled in. Referential Integrity will catch the
problem
but not until the record is being saved. I can't make
it a
combobox because the list is toooo long. Any
suggestions?
Thanks for the help.


.



.

Purrrfect. Reggie, thanks so much for the detailed help.

-----Original Message-----
SmallFry, Try one of these depending on the type of data
you are checking
for(number, string, date). Watch for word wrapping below.

*****************************************
''''If it's a Number
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim intNum As Integer
intNum = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]
= " & intNum)) Then
MsgBox "Number does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If

End Sub
****************************************
''''If it's a String
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim str As String
str = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]
= '" & str & "'"))
Then
MsgBox "String does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub
*****************************************
''''If it's a Date
Private Sub FrameID_BeforeUpdate(Cancel As Integer)
Dim dte As Date

If IsNull(Me.FrameID) Then Exit Sub
dte = Me.FrameID
If IsNull(DLookup("[PartID]", "tblManuf", "[PartID]=
#" & dte & "#"))
Then
MsgBox "Date does not exist"
DoCmd.RunCommand acCmdUndo
Cancel = True
End If
End Sub

Hope it helps!
--
Reggie

www.smittysinet.com
----------
"Small Fry" wrote
in message
...
Thanks for the informaton but my VBA skills are sorely
lacking so can you spell it out more using the
following:
text box on form = FrameID
table = tblManuf
field name in table = PartID
If PartID does not exist I would just have a msgBox (and
then return focus to textbox or ?)
Will this code go in the before_update property of the
textbox?

-----Original Message-----
SmallFry, What about something like this
(Air Code)

If IsNull(DLookup("[MyField]", "MyTable", "[MyTableID]
= " & intMyCriteria)
then
....Do this
else
....Do this
End if

--
Reggie

www.smittysinet.com
----------
"SmallFry" wrote
in
message
...
I'm not sure, but I don't think so. I don't want to
auto-
populate I just want to verify that the data exists
in
another table.
-----Original Message-----
Would DLookup work for your scenario? DLookup can
look
up
the value based on
a value in your present table or another table. For
instance I use DLookup
to check the time and City of a driver's last
delivery
using the current
date.

"Small Fry"
wrote
in message
...
One of the fields on my form corresponds to a
field
in
another table and I wuld like to be able to verify
that
the input in the form is ok before the entire
form is
filled in. Referential Integrity will catch the
problem
but not until the record is being saved. I can't
make
it a
combobox because the list is toooo long. Any
suggestions?
Thanks for the help.


.



.



.