Not In List Access Project (adp)

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

Guest

I am working with an access project with the DB sitting on MS SQL Server and
I have been looking up postings and help on adding data straight onto the
table if the value IS NOT IN LIST but the examples are seem to be for mdb and
nothing is working for me....
Can anyone help me out on this...
Thanks,
 
The NotInList event isn't an issue. Doing an insert into the database is the
same as anywhere else in your application. You could use a stored proc or a
sql string to do this.

Barry
 
Hi Barry thanks for the tip,
On access project, I have a Crops and its Varieties tables and got a unbound
combo with the following in not-in-list procedure...but I get Runtime error
'91' saying "Object variable or with block Variable not set"... What am I
doing wrong... if I have to write an insert statement in the procedure below,
where would it go...?

Private Sub cmbCrop_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Variety Name " & vbCrLf
& vbCrLf
strMsg = strMsg & "Do you want to associate the new Variety to the
current Record?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Crops", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!CropName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
 
I am back again...
I now got this with the insert statement but it gives me the same error
message as above. More help please...

Private Sub cmbCrop_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Crop...")
If i = vbYes Then
strSQL = "Insert Into Crops ([strCrop]) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
 
I have used ADO instead of DAO but I still get the error message.
And when I go into debug, it highlights this bit of the code:
CurrentDb.Execute strSQL, dbFailOnError
But I don't know what to do to sort this out...

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Crop...")
If i = vbYes Then
strSQL = "Insert Into Crops ([strCrop]) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Below is the whole code
 
I have finally gotten up this this as per code below and it does insert the
value into the combo but now I need to get it to synchronise with the Subform
but don't know how to do that... I have Requery procedure both on the Main
form and the combo box AfterUpdate Events but nothing seems to work until I
close and open the form again.... Any ideas on this? The code I got at the
moment is shown below:

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

On Error GoTo cmbCrop_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Crop " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "RCL Crops")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Crops([CropName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Your new Crop has been added to the list." _
, vbInformation, "RCL Crops"
Response = acDataErrAdded
Else
MsgBox "Please choose a Crop from the list." _
, vbInformation, "RCL Crops"
Response = acDataErrContinue
End If
cmbCrop_NotInList_Exit:
Exit Sub
cmbCrop_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cmbCrop_NotInList_Exit

End Sub
 
You could try requerying the form after you run your strSQL. You could
also try putting the requery on the subform ACTIVATE event.
 
Back
Top