NotInList

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

Guest

Good day! I copied the code and it works fine, on one of my form because
there is no other underlying field needed (or only one field in
FL_Organism2). But the other form I created for FL_Organism has 2 fields,
(1) Organism (2) Type. once i type-in the new field with its corresponding
type and close this form, the error message that its not in list would
appear. How can i edit this code? thanks very much!

Private Sub Isolate_NotInList(NewData As String, Response As Integer)
Dim strPrompt As String

On Error GoTo Err_NotInList

strPrompt = "'" & Me.Isolate.Text & "' is not in the list." & vbCr & vbCr &
"Do you want to add it?"
If MsgBox(strPrompt, vbExclamation + vbYesNo, "Not In List") = vbNo Then
Response = acDataErrContinue
Me.Isolate.Undo
MsgBox "Please select an item from the list"
Exit Sub
End If

DoCmd.OpenForm "FL_Organism", , , , acFormAdd, acDialog
Me.Isolate.Undo
Response = acDataErrAdded

Exit_NotInList:
Exit Sub
Err_NotInList:
MsgBox "Error" & Err.Number & ": " & Err.Description, ,
"Isolate_NotInList"
Resume Exit_NotInList
End Sub
 
G'day Ben

You might try this way, this will tell the user that it is not in the list,
if user elects to add it, it will update the underlying table before you
close the form.

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

Dim db As Database, rs As Recordset, strMsg As String

strMsg = NewData & " is not listed ! Do you want to add it to the list?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Organism ?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblYourTable", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Organism = NewData
rs.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
End Sub

HTH
Mark
 
Hi Mark! thanks for the reply! I tried to use the code you gave. Once i
entered a field in Organism that is not in list, it would gave me the message
"do you want to add". when i add the new field and close the form I would
get the message "The expression you entered refers to an object that is
closed or doesn't exist" but the new field would eventually be added... how
can i remove the error message?
Thanks!
 
Hi Ben,
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Click to expand...

The above code is for error trapping. You need the error number like...

If Err.Number = 3078 Then ' I think that's what Mark miss out, it might be
this number.
 
Thanks AccessVandal!
I tried error trapping but it would still show the error message "The
expression you entered refers to an object that is closed or doesn't exist"
and it shows no error number. When I click OK to this error message, the new
entry would actually be added but the form would change such that I could not
add another entry.

Thanks again!
 
Hi Ben,

You need to insert break points to debug your code.

It appears that this error comes from another event or somewhere else which
you have not give in your post.

It can be a mispelled control, variable, etc...

You need to find out which line gives that error.
 
Many thanks! I will try to debug it

AccessVandal via AccessMonster.com said:
Hi Ben,

You need to insert break points to debug your code.

It appears that this error comes from another event or somewhere else which
you have not give in your post.

It can be a mispelled control, variable, etc...

You need to find out which line gives that error.
Click to expand...
 
Hi again AccesVandal!

I found whats causing the error message and finally fixed it. But I still
have the problem of adding another entry after I added a new entry. No new
record line appears. I still have to close this form so that I could add
another entry. To give you the code again.

Private Sub Isolate_NotInList(NewData As String, Response As Integer)
Dim strPrompt As String

On Error GoTo Err_NotInList

strPrompt = "'" & Me.Isolate.Text & "' is not in the list." & vbCr & vbCr & _
"Do you want to add it?"

If MsgBox(strPrompt, vbExclamation + vbYesNo, "Not In List") = vbNo Then
Response = acDataErrContinue
Me.Isolate.Undo
MsgBox "Please select an item from the list"
Exit Sub
End If

DoCmd.OpenForm "FL_Organism", , , , acFormAdd, acDialog
Me.Isolate.Undo
Response = acDataErrAdded

Exit_NotInList:
Exit Sub
Err_NotInList:
MsgBox "Error" & Err.Number & ": " & Err.Description, ,
"Isolate_NotInList"
Resume Exit_NotInList

End Sub


Does the acFormAdd of FL_Organism affects my other form FM_bi_bc making it
also an acFormAdd? Thanks in advance!
 
Hi Ben,

This form "FL_Organism", does the form’s properties “Navigation Buttons†set
to “Yes�

Or you have your own customized buttons?

Or I’m mistaken that the Form View is “Single Form� Is it “Continuous Form�
benj wrote:
Hi again AccesVandal!

I found whats causing the error message and finally fixed it. But I still
have the problem of adding another entry after I added a new entry. No new
record line appears. I still have to close this form so that I could add
another entry. To give you the code again. snip....
Does the acFormAdd of FL_Organism affects my other form FM_bi_bc making it
also an acFormAdd? Thanks in advance!
Click to expand...

No, does not affect other forms.
 
Hi Ben,

Perhaps, I should have asked which form was the problem?

Maybe you need to requery the form after a new record is inserted?

In the form "FL_Organism" ( I hope that's the form where you insert a new
record), add this line the the event ... something like...

Forms!YourFormNameHere.Requery

Hope it works for you.
 
Back
Top