Checking two form fields against a table if found deny save

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

Guest

Hi, I have a form with two drop down fields that I need to compare to a table before saving and deny saving the record if the combination is found. Basically the user fills out the screen then clicks a save button. Before the save takes place I need it to check the part field and the machine field and see if the two in combination exists in the Xref table. If so give error message to change machines. Can anyone tell me what I am doing wrong in this code. Thanks, Annett

Private Sub Form_BeforeUpdate(Cancel As Integer

Dim db As Database, rst As Recordset, strSQL As Strin

strSQL = "SELECT *
strSQL = strSQL & "FROM PART_MACHINE_XREF
strSQL = strSQL & "WHERE db!PART_MACHINE_XREF!Part_num = Me![Part_Num] and db!PART_MACHINE_XREF!Machine_ID = Me![Machine_ID];
Set db = CurrentD
Set rst = db.OpenRecordset(strSQL
If Not rst.EOF The
MsgBox "This Part cannot run on this machine. Please select a valid Machine ID.
Els
End I

End Sub
 
This will work if your part# and machineID values are
text, but take out the ' surrounding the value if it's a
number.

strSQL = "SELECT PART_MACHINE_XREF.* " _
& "FROM PART_MACHINE_XREF " _
& "WHERE [Part_num]='" & Me!Part_Num & "'" _
& "And [Machine_ID]='" & Me!Machine_ID & "'"

A little easier would be to use DCount() instead

strSQL = "[Part_num]='" & Me!Part_Num & "'" _
& "And [Machine_ID]='" & Me!Machine_ID & "'"
If DCount("Part_num","PART_MACHINE_XREF",strSQL)>0 Then
MsgBox "This Part cannot run on this machine."
End If


-----Original Message-----
Hi, I have a form with two drop down fields that I need
Click to expand...
to compare to a table before saving and deny saving the
record if the combination is found. Basically the user
fills out the screen then clicks a save button. Before
the save takes place I need it to check the part field and
the machine field and see if the two in combination exists
in the Xref table. If so give error message to change
machines. Can anyone tell me what I am doing wrong in
this code. Thanks, Annette
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database, rst As Recordset, strSQL As String

strSQL = "SELECT * "
strSQL = strSQL & "FROM PART_MACHINE_XREF "
strSQL = strSQL & "WHERE db!PART_MACHINE_XREF!
Click to expand...
Part_num = Me![Part_Num] and db!PART_MACHINE_XREF!
Machine_ID = Me![Machine_ID];"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If Not rst.EOF Then
MsgBox "This Part cannot run on this
Click to expand...
machine. Please select a valid Machine ID."
 
You are very close:

If Not rst.EOF Then
MsgBox "This Part cannot run on this machine. Please
select a valid Machine ID."
Cancel = True
Else


Chris Nebinger

-----Original Message-----
Hi, I have a form with two drop down fields that I need
Click to expand...
to compare to a table before saving and deny saving the
record if the combination is found. Basically the user
fills out the screen then clicks a save button. Before
the save takes place I need it to check the part field and
the machine field and see if the two in combination exists
in the Xref table. If so give error message to change
machines. Can anyone tell me what I am doing wrong in
this code. Thanks, Annette
Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database, rst As Recordset, strSQL As String

strSQL = "SELECT * "
strSQL = strSQL & "FROM PART_MACHINE_XREF "
strSQL = strSQL & "WHERE db!PART_MACHINE_XREF!
Click to expand...
Part_num = Me![Part_Num] and db!PART_MACHINE_XREF!
Machine_ID = Me![Machine_ID];"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
If Not rst.EOF Then
MsgBox "This Part cannot run on this
Click to expand...
machine. Please select a valid Machine ID."
 
Back
Top