Run code if record does or does not exist for value entered in for

  • Thread starter Thread starter bymarce
  • Start date Start date
B

bymarce

I have a form in which my user enters experimental data. The property combo
box is limited to list and opens a subform for entering additional
information if a property is entered that is not in list. The method combo
box source is a query that limits methods to values associated with the
property selected in the combo box. I have a many to many relationship set
up between methods and properties tables. If the user enters a method that
is not in the list I want them to be able to add it with the same form on
which the property info is added. How do I add a record to the junction
table if the method is already in the methods table but not listed in the
property methods junction table for that property? How do I use the
existance of a record in the methods table to determine whether the method
table gets added to or just the junction table? Curently I have the
following code. Thanks for the help.
Marcie

Private Sub cboTestMethod_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("This method is not listed for the property you have entered.
Would you like to add it?", vbYesNo, Methods)
If x = vbYes Then
'I want to add another If statement dependant on the existance of a
record
'in the methods table.
strsql = "Insert Into Methods ([Method]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
 
Got my own problem worked out. The code is as follows if anyone is
interested. I used If Not Is Null Dlookup() with the necessary arguments.
Marcie

Private Sub cboTestMethod_NotInList(NewData As String, Response As Integer)

Dim strsql As String, x As Integer, strsqlM As String, NewProperty As
String
x = MsgBox("This method is not listed for the property you have entered.
Would you like to add it?", vbYesNo, "Methods")
NewProperty = [Forms]![DataEntry]![Property]
If x = vbYes Then
If Not IsNull(DLookup("Method", "TestMethods", "[Method] = '" &
NewData & "'")) Then
strsql = "Insert Into PropertiesMethodsJunction
([JMethod],[JProperty]) values ('" & NewData & "','" & NewProperty & "')"
CurrentDb.Execute strsql, dbFailOnError
Else
strsql = "Insert Into TestMethods ([Method]) values ('" &
NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
strsqlM = "Insert Into PropertiesMethodsJunction
([JMethod],[JProperty]) values ('" & NewData & "','" & NewProperty & "')"
CurrentDb.Execute strsqlM, dbFailOnError
End If
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" &
NewProperty & "'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub


bymarce said:
I have a form in which my user enters experimental data. The property combo
box is limited to list and opens a subform for entering additional
information if a property is entered that is not in list. The method combo
box source is a query that limits methods to values associated with the
property selected in the combo box. I have a many to many relationship set
up between methods and properties tables. If the user enters a method that
is not in the list I want them to be able to add it with the same form on
which the property info is added. How do I add a record to the junction
table if the method is already in the methods table but not listed in the
property methods junction table for that property? How do I use the
existance of a record in the methods table to determine whether the method
table gets added to or just the junction table? Curently I have the
following code. Thanks for the help.
Marcie

Private Sub cboTestMethod_NotInList(NewData As String, Response As Integer)
Dim strsql As String, x As Integer
x = MsgBox("This method is not listed for the property you have entered.
Would you like to add it?", vbYesNo, Methods)
If x = vbYes Then
'I want to add another If statement dependant on the existance of a
record
'in the methods table.
strsql = "Insert Into Methods ([Method]) values ('" & NewData & "')"
CurrentDb.Execute strsql, dbFailOnError
DoCmd.OpenForm "Properties", acNormal, , "[Property]='" & NewData &
"'", acFormEdit, acDialog
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
 
Back
Top