If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Before_update validation
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. |
#3
|
|||
|
|||
Before_update validation
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. . |
#4
|
|||
|
|||
Before_update validation
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. . |
#5
|
|||
|
|||
Before_update validation
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. . . |
#6
|
|||
|
|||
Before_update validation
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. . . |
#7
|
|||
|
|||
Before_update validation
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. . . . |
Thread Tools | |
Display Modes | |
|
|