Combo "Not in List Event", MS generic message still appears

[basiltabethacat]

Hi,

I'm writing a database to help teachers write Unit Plans, containing several
tabs of hierarchical information, both within the tabs and between the tabs.
I'm trying to use a combo box to display current choices, but allow the user
to start a brand new Unit Plan (a relational table upon which many other
choices and related information and tables will hinge and be added to.

My form fields/Associated table fields

cboPlanName/PlanName
txtYearOfTeaching/TermYear
txtTermNumber/Term/Number

The code I have placed into the "Not in List" event is:

Private Sub cboPlanName_NotInList(NewData As String, Response As Integer)
Dim msgstr As String

msgstr = "You have entered a new Unit Plan name. Do you really want to
begin a new Unit Plan?"

If MsgBox(msgstr, vbYesNo, "Create new Unit Plan?") = vbYes Then
Response = acDataErrAdded
CurrentDb.Execute ("INSERT INTO [Term Plans](PlanName) SELECT '" &
NewData & "' AS Expr1;")

Else
repsonse = acDataErrContinue
Me.cboPlanName.Undo
End If
End Sub

Although I have placed the acDataErrAdded and acDataErrContinue commands, the
MS generic error messages still persist. I want my cbo box to save the new
entry, and then if vbYes, enable and null the text boxes, ready for the user
to enter the associated information to complete the record, if vbNo return
the user to the cbo box to make another choice with no info saved.

I'm an absolute beginner. I sort of understand what all the code does except
for the actual execution code, which is, of course, copied and adapted (any
suggested tutorials regarding this aspect would be appreciated).

Why do I still get the MS generic error messages?

 

[MikeJohnB]

I am not sure what you are trying to do from your code acdataerradded?

Take alook at

DoCmd.SetWarnings False

But always follow this with

DoCmd.SetWarnings True

Before you exit the procedure, normally after the line which throws up the
generic Access Response.

Regards

Mike B

 

[BruceM]

You could try putting the Response = ... line at the end of the section of
code (after inserting the new record, or after the undo). I'm not familiar
with using the NotInList exactly in the way you have done, but I have had
good success with code based on the following:
http://www.mvps.org/access/forms/frm0015.htm

Another approach is here:
http://www.datastrat.com/Code/NotInListCode.txt

I don't think SetWarnings will do anything to suppress the error messages.

 

[basiltabethacat via AccessMonster.com]

Thank you both for your time.

I tried "The Access Web" option (the first link) code:

Private Sub cboPlanName_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Term Plans] = 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

...only altering message dialogue and the database to "Plan Terms" (my db).

**Choosing Yes throws me straight to "An error occurred. Please try again."
without adding my new option to the list. Why does it ignore the first part
of the Else statement?

**Choosing No throws up run-time error 91, "Object variable or With block
variable not set" , the yellow debug highlight bar sits on "rs.Close" when
the code is opened via the bug message. Evidently this message has something
to do with incorrect setting of the variables?? Or Object libraries - I have
DAO 3.6 and ADO 2.1 selected - is this right?

Thanks.

PS Once again, I'm a complete beginner and don't really know what I'm talking
about ;-) - just trying to find solutions via the net.

You could try putting the Response = ... line at the end of the section of
code (after inserting the new record, or after the undo). I'm not familiar
with using the NotInList exactly in the way you have done, but I have had
good success with code based on the following:
http://www.mvps.org/access/forms/frm0015.htm

Another approach is here:
http://www.datastrat.com/Code/NotInListCode.txt

I don't think SetWarnings will do anything to suppress the error messages.

Hi,

[quoted text clipped - 47 lines]

Why do I still get the MS generic error messages?

 

[MikeJohnB]

I have looked carefully at your code and for the moment, I cannot see why
your dropping out of the Else line code into the error code.

I use the following amended code which works

Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub
Else

Dim Message1, Title1, Default1, MyValue1
Message1 = "Enter a Part Description" ' Set prompt.
Title1 = "Enter Part Name" ' Set title.
Default1 = "BONDED SEAL" ' Set default.
MyValue1 = InputBox(Message1, Title1, Default1)

Message1 = "HINT" & Chr(13) & "Enter a Customer's Description
where Known" & Chr(13) & "This is Normally a Customer's Part Number Link" &
Chr(13) & "To Newtown Part Number, Enter Newtown Part No" & Chr(13) & "If it
is known"
'(the above is one line)

Default1 = "Bonded Seal"
Myvalue2 = InputBox(Message1, Title1, Default1)


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)

rs.AddNew
rs![Customer_Part_Number] = NewData
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

End If
End Sub

In the above, which is the same code base as yours, I have added more fields
to add to the underlying table.

I post this in case you can either modify it or use it to determin where
your error is?

Kindest Regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Thank you both for your time.

I tried "The Access Web" option (the first link) code:

Private Sub cboPlanName_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Term Plans] = 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

...only altering message dialogue and the database to "Plan Terms" (my db).

**Choosing Yes throws me straight to "An error occurred. Please try again."
without adding my new option to the list. Why does it ignore the first part
of the Else statement?

**Choosing No throws up run-time error 91, "Object variable or With block
variable not set" , the yellow debug highlight bar sits on "rs.Close" when
the code is opened via the bug message. Evidently this message has something
to do with incorrect setting of the variables?? Or Object libraries - I have
DAO 3.6 and ADO 2.1 selected - is this right?

Thanks.

PS Once again, I'm a complete beginner and don't really know what I'm talking
about ;-) - just trying to find solutions via the net.

You could try putting the Response = ... line at the end of the section of
code (after inserting the new record, or after the undo). I'm not familiar
with using the NotInList exactly in the way you have done, but I have had
good success with code based on the following:
http://www.mvps.org/access/forms/frm0015.htm

Another approach is here:
http://www.datastrat.com/Code/NotInListCode.txt

I don't think SetWarnings will do anything to suppress the error messages.

Hi,

[quoted text clipped - 47 lines]

Why do I still get the MS generic error messages?

 

[MikeJohnB]

One consideration, in your code

Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)

Your Table "Term Plans" has a space in the name, something I always try to
avoid these days. You may have to enclose "Term Plan" in Brackets

Set rs = db.OpenRecordset("[Term Plans]", dbOpenDynaset)

I would try that as a first option


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

I have looked carefully at your code and for the moment, I cannot see why
your dropping out of the Else line code into the error code.

I use the following amended code which works

Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub
Else

Dim Message1, Title1, Default1, MyValue1
Message1 = "Enter a Part Description" ' Set prompt.
Title1 = "Enter Part Name" ' Set title.
Default1 = "BONDED SEAL" ' Set default.
MyValue1 = InputBox(Message1, Title1, Default1)

Message1 = "HINT" & Chr(13) & "Enter a Customer's Description
where Known" & Chr(13) & "This is Normally a Customer's Part Number Link" &
Chr(13) & "To Newtown Part Number, Enter Newtown Part No" & Chr(13) & "If it
is known"
'(the above is one line)

Default1 = "Bonded Seal"
Myvalue2 = InputBox(Message1, Title1, Default1)


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)

rs.AddNew
rs![Customer_Part_Number] = NewData
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

End If
End Sub

In the above, which is the same code base as yours, I have added more fields
to add to the underlying table.

I post this in case you can either modify it or use it to determin where
your error is?

Kindest Regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Thank you both for your time.

I tried "The Access Web" option (the first link) code:

Private Sub cboPlanName_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Term Plans] = 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

...only altering message dialogue and the database to "Plan Terms" (my db).

**Choosing Yes throws me straight to "An error occurred. Please try again."
without adding my new option to the list. Why does it ignore the first part
of the Else statement?

**Choosing No throws up run-time error 91, "Object variable or With block
variable not set" , the yellow debug highlight bar sits on "rs.Close" when
the code is opened via the bug message. Evidently this message has something
to do with incorrect setting of the variables?? Or Object libraries - I have
DAO 3.6 and ADO 2.1 selected - is this right?

Thanks.

PS Once again, I'm a complete beginner and don't really know what I'm talking
about ;-) - just trying to find solutions via the net.

You could try putting the Response = ... line at the end of the section of
code (after inserting the new record, or after the undo). I'm not familiar
with using the NotInList exactly in the way you have done, but I have had
good success with code based on the following:
http://www.mvps.org/access/forms/frm0015.htm

Another approach is here:
http://www.datastrat.com/Code/NotInListCode.txt

I don't think SetWarnings will do anything to suppress the error messages.

Hi,

[quoted text clipped - 47 lines]

Why do I still get the MS generic error messages?

 

[MikeJohnB]

Another Trick

Place a stop in the first line of code
Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Stop
Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub

Your code should stop at the line stop. Use the F8 Key to single step
through your code to find the exact line that is throwing an error. Again I
would hazzard a guess at the line

Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)

If thats the case, it will then jump striaght to the err line

You can also use break points (Alternative to the stop) by double clicking
the edge border of the code window, it should highlight the line in red with
a big red dot in the left hand edge column. To remove, just click again
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

One consideration, in your code

Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)

Your Table "Term Plans" has a space in the name, something I always try to
avoid these days. You may have to enclose "Term Plan" in Brackets

Set rs = db.OpenRecordset("[Term Plans]", dbOpenDynaset)

I would try that as a first option


--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

I have looked carefully at your code and for the moment, I cannot see why
your dropping out of the Else line code into the error code.

I use the following amended code which works

Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub
Else

Dim Message1, Title1, Default1, MyValue1
Message1 = "Enter a Part Description" ' Set prompt.
Title1 = "Enter Part Name" ' Set title.
Default1 = "BONDED SEAL" ' Set default.
MyValue1 = InputBox(Message1, Title1, Default1)

Message1 = "HINT" & Chr(13) & "Enter a Customer's Description
where Known" & Chr(13) & "This is Normally a Customer's Part Number Link" &
Chr(13) & "To Newtown Part Number, Enter Newtown Part No" & Chr(13) & "If it
is known"
'(the above is one line)

Default1 = "Bonded Seal"
Myvalue2 = InputBox(Message1, Title1, Default1)


Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)

rs.AddNew
rs![Customer_Part_Number] = NewData
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

End If
End Sub

In the above, which is the same code base as yours, I have added more fields
to add to the underlying table.

I post this in case you can either modify it or use it to determin where
your error is?

Kindest Regards

Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Thank you both for your time.

I tried "The Access Web" option (the first link) code:

Private Sub cboPlanName_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Term Plans] = 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

...only altering message dialogue and the database to "Plan Terms" (my db).

**Choosing Yes throws me straight to "An error occurred. Please try again."
without adding my new option to the list. Why does it ignore the first part
of the Else statement?

**Choosing No throws up run-time error 91, "Object variable or With block
variable not set" , the yellow debug highlight bar sits on "rs.Close" when
the code is opened via the bug message. Evidently this message has something
to do with incorrect setting of the variables?? Or Object libraries - I have
DAO 3.6 and ADO 2.1 selected - is this right?

Thanks.

PS Once again, I'm a complete beginner and don't really know what I'm talking
about ;-) - just trying to find solutions via the net.

BruceM wrote:
You could try putting the Response = ... line at the end of the section of
code (after inserting the new record, or after the undo). I'm not familiar
with using the NotInList exactly in the way you have done, but I have had
good success with code based on the following:
http://www.mvps.org/access/forms/frm0015.htm

Another approach is here:
http://www.datastrat.com/Code/NotInListCode.txt

I don't think SetWarnings will do anything to suppress the error messages.

Hi,

[quoted text clipped - 47 lines]

Why do I still get the MS generic error messages?

 

[basiltabethacat via AccessMonster.com]

Hey Mike!

Thanks for you replies - they are really appreciated . Sorry about the
tardy reply - I got frustrated and took a day's break!

Thanks for the tip about Stopping (I thought there was a way to do that but
couldn't work out how to 'step' (F8)) ) - I've done this for all the
problem scenario possibilities as follows:


I tried adding the [ ] to the OpenRecordset line, but it seems not to work -
it throws an error:

"Run-time error '3078':
The Microsoft Access database engine cannot find the input table or query '
[Term Plans]'. Make sure it exists and that its name is spelled correctly." -
this suggests to me that Access is happy with the " " and sees the brackets
as part of the name.

Proceeding without the brackets, I stepped through the choice of Yes. Access
does indeed step through the following Else statement dealing with adding
NewData to the table (picking up the NewData properly, according to mouse-
over tool-tip), through the line "rs.update", but then, alas, continues to,
and through, the next "If Err" statement, throwing that error. Is the term
"Err" an inbuilt term - it's not set up anywhere? Also note that NewData is
not added to either the table or combo box choices. And because it goes
through this "If Err" bit, it skips the associated Else statement for
acDataErrAdded (which never gets processed).

Also without the brackets, I stepped through the choice of No. Access
appropriately skips the Else statement, straight down to the "End If"
statement and, on the "rs.close" line throws up the same error as previously
mentioned:

"Run-time error '91':
Object variable or With block variable not set."

Prior to stepping through the code (still at stop), I notice that:

Response = 1 (I assume this is the default?)
acDataErrContinue = 0 (This means false?)
acDataErrAdded = 2 (Why?)

Thanks again for your help . Any other ideas?

Another Trick

Place a stop in the first line of code
Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Stop
Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub

Your code should stop at the line stop. Use the F8 Key to single step
through your code to find the exact line that is throwing an error. Again I
would hazzard a guess at the line

Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)

If thats the case, it will then jump striaght to the err line

You can also use break points (Alternative to the stop) by double clicking
the edge border of the code window, it should highlight the line in red with
a big red dot in the left hand edge column. To remove, just click again

One consideration, in your code

[quoted text clipped - 136 lines]

 

[MikeJohnB]

Hi Basil, I hope you haven't given up on this yet and are still monitoring
the thread?

I am about to set up a table and combobox according to your layout but
before I do that, I have noticed an error in your code that I hadn't seen
before.

(Your Code)
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)'<<<<<note
"Term Plans" Above refers to a table

On Error Resume Next
rs.AddNew
rs![Term Plans] = NewData '<<<<<This should be the field name
not the table name

rs.Update

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

End If

Can you provide me with the field name so I can replicate your situation???,
also, replace your field name with the correct field where Term Plans data is
held? I don't think you would have given the same name to both the table and
the field?????

(My Code)

Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)'*

rs.AddNew
rs![Customer_Part_Number] = NewData'**
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

'*You will see rs=Db.OpenRecordset("Table_Name",dbOpenDynaset)
'** rs![Customer_PAr_Number]=NewData (is a field name within the Table)

Please advise Correct Field Name.

Thanks and kindest Regards
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hey Mike!

Thanks for you replies - they are really appreciated . Sorry about the
tardy reply - I got frustrated and took a day's break!

Thanks for the tip about Stopping (I thought there was a way to do that but
couldn't work out how to 'step' (F8)) ) - I've done this for all the
problem scenario possibilities as follows:


I tried adding the [ ] to the OpenRecordset line, but it seems not to work -
it throws an error:

"Run-time error '3078':
The Microsoft Access database engine cannot find the input table or query '
[Term Plans]'. Make sure it exists and that its name is spelled correctly." -
this suggests to me that Access is happy with the " " and sees the brackets
as part of the name.

Proceeding without the brackets, I stepped through the choice of Yes. Access
does indeed step through the following Else statement dealing with adding
NewData to the table (picking up the NewData properly, according to mouse-
over tool-tip), through the line "rs.update", but then, alas, continues to,
and through, the next "If Err" statement, throwing that error. Is the term
"Err" an inbuilt term - it's not set up anywhere? Also note that NewData is
not added to either the table or combo box choices. And because it goes
through this "If Err" bit, it skips the associated Else statement for
acDataErrAdded (which never gets processed).

Also without the brackets, I stepped through the choice of No. Access
appropriately skips the Else statement, straight down to the "End If"
statement and, on the "rs.close" line throws up the same error as previously
mentioned:

"Run-time error '91':
Object variable or With block variable not set."

Prior to stepping through the code (still at stop), I notice that:

Response = 1 (I assume this is the default?)
acDataErrContinue = 0 (This means false?)
acDataErrAdded = 2 (Why?)

Thanks again for your help . Any other ideas?

Another Trick

Place a stop in the first line of code
Private Sub ComboSearchCusPtNo_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg1 As String

Stop
Msg1 = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg1 = Msg & "Do you want to add it?"
If MsgBox(Msg1, vbQuestion + vbYesNo) = vbNo Then
Response1 = acDataErrContinue
MsgBox "Try again."
Exit Sub

Your code should stop at the line stop. Use the F8 Key to single step
through your code to find the exact line that is throwing an error. Again I
would hazzard a guess at the line

Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)

If thats the case, it will then jump striaght to the err line

You can also use break points (Alternative to the stop) by double clicking
the edge border of the code window, it should highlight the line in red with
a big red dot in the left hand edge column. To remove, just click again

One consideration, in your code

[quoted text clipped - 136 lines]

Why do I still get the MS generic error messages?

 

[basiltabethacat via AccessMonster.com]

Hey Mike!

Yep, my word I am! )

I changed the line in my code to:

"rs![PlanName] = NewData"
(Although, sadly and puzzlingly, this didn't change the errors)

"PlanName" is the name of the table field I need the new record to go to.

Thank you for that . Of course, it should have been the table column name
(obvious when someone points it out! )).

My table is called "Term Plans".

Table fields bound to a form called "Unit Plan Creation", tab "Unit Plan
Name":

PlanName bound to cboPlanName
TermYear bound to txtYearOfTeaching
TermNumber bound to txtTermNumber
TermID(unique key)

I really appreciate your efforts ))

Kind Regards,

Hi Basil, I hope you haven't given up on this yet and are still monitoring
the thread?

I am about to set up a table and combobox according to your layout but
before I do that, I have noticed an error in your code that I hadn't seen
before.

(Your Code)
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)'<<<<<note
"Term Plans" Above refers to a table

On Error Resume Next
rs.AddNew
rs![Term Plans] = NewData '<<<<<This should be the field name
not the table name

rs.Update

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

End If

Can you provide me with the field name so I can replicate your situation???,
also, replace your field name with the correct field where Term Plans data is
held? I don't think you would have given the same name to both the table and
the field?????

(My Code)

Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)'*

rs.AddNew
rs![Customer_Part_Number] = NewData'**
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

'*You will see rs=Db.OpenRecordset("Table_Name",dbOpenDynaset)
'** rs![Customer_PAr_Number]=NewData (is a field name within the Table)

Please advise Correct Field Name.

Thanks and kindest Regards

Hey Mike!

[quoted text clipped - 73 lines]

 

[MikeJohnB]

Although you still get the errors, does it now append the table? And
therefore refresh the combobox?

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hey Mike!

Yep, my word I am! )

I changed the line in my code to:

"rs![PlanName] = NewData"
(Although, sadly and puzzlingly, this didn't change the errors)

"PlanName" is the name of the table field I need the new record to go to.

Thank you for that . Of course, it should have been the table column name
(obvious when someone points it out! )).

My table is called "Term Plans".

Table fields bound to a form called "Unit Plan Creation", tab "Unit Plan
Name":

PlanName bound to cboPlanName
TermYear bound to txtYearOfTeaching
TermNumber bound to txtTermNumber
TermID(unique key)

I really appreciate your efforts ))

Kind Regards,

Hi Basil, I hope you haven't given up on this yet and are still monitoring
the thread?

I am about to set up a table and combobox according to your layout but
before I do that, I have noticed an error in your code that I hadn't seen
before.

(Your Code)
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)'<<<<<note
"Term Plans" Above refers to a table

On Error Resume Next
rs.AddNew
rs![Term Plans] = NewData '<<<<<This should be the field name
not the table name

rs.Update

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

End If

Can you provide me with the field name so I can replicate your situation???,
also, replace your field name with the correct field where Term Plans data is
held? I don't think you would have given the same name to both the table and
the field?????

(My Code)

Set Db = CurrentDb
Set rs = Db.OpenRecordset("Tbl_Cus_Part_Numbers", dbOpenDynaset)'*

rs.AddNew
rs![Customer_Part_Number] = NewData'**
rs![Description] = MyValue1
rs![CustomerDescription] = Myvalue2
rs.Update
Response = acDataErrAdded

'*You will see rs=Db.OpenRecordset("Table_Name",dbOpenDynaset)
'** rs![Customer_PAr_Number]=NewData (is a field name within the Table)

Please advise Correct Field Name.

Thanks and kindest Regards

Hey Mike!

[quoted text clipped - 73 lines]

Why do I still get the MS generic error messages?

 

[basiltabethacat via AccessMonster.com]

Hey Mike!

Thanks for the quick reply.

Alas, no it doesn't... :-(

Although you still get the errors, does it now append the table? And
therefore refresh the combobox?

Hey Mike!

[quoted text clipped - 81 lines]

 

[MikeJohnB]

Without going deeply into your setup, I have set the following

Table "Term Plans"
Field [PlanName]

I have a combo box on a straight form called Combo4

Here is the code

Private Sub Combo4_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

'note error corrected above on use of " around the end of the message

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![PlanName] = NewData
rs.Update
Debug.Print Err 'Print any error to the imediate window
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


This works without throwing an error and no error is displayed in the
immediate window.

Is it possible to zip you efforts and e mail to (name on here) at Hotmail
Dot Com?
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hey Mike!

Thanks for the quick reply.

Alas, no it doesn't... :-(

Although you still get the errors, does it now append the table? And
therefore refresh the combobox?

Hey Mike!

[quoted text clipped - 81 lines]

Why do I still get the MS generic error messages?

 

[MikeJohnB]

Sorry, there was no error on the Message Text Quotes, it was VB adding an
extra " in a line as I copied it from your Thread Message.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Without going deeply into your setup, I have set the following

Table "Term Plans"
Field [PlanName]

I have a combo box on a straight form called Combo4

Here is the code

Private Sub Combo4_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 Unit Plan. " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to create a new Unit Plan?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to create a New Unit Plan
or No to choose and existing one."

'note error corrected above on use of " around the end of the message

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new Unit Plan?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("Term Plans", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![PlanName] = NewData
rs.Update
Debug.Print Err 'Print any error to the imediate window
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


This works without throwing an error and no error is displayed in the
immediate window.

Is it possible to zip you efforts and e mail to (name on here) at Hotmail
Dot Com?
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

Hey Mike!

Thanks for the quick reply.

Alas, no it doesn't... :-(

Although you still get the errors, does it now append the table? And
therefore refresh the combobox?

Hey Mike!

[quoted text clipped - 81 lines]

Why do I still get the MS generic error messages?

 

[basiltabethacat via AccessMonster.com]

LOL! It did the same to me when I copied and pasted it back into my event ;-).
I've sent it to you (both 2003 and 2007 versions) in separate e-mails. In the
first e-mail, I've tried to describe to you what I am trying to do - not sure
how successful my descriptions were. I'm a night-owl, but 4am (in Australia)
is probably getting a little late ;-).

Looking forward to hearing from you.

Kindest Regards,

Sorry, there was no error on the Message Text Quotes, it was VB adding an
extra " in a line as I copied it from your Thread Message.

Without going deeply into your setup, I have set the following

[quoted text clipped - 63 lines]

 

[MikeJohnB]

Further to our communications, your problem is with two other fields that
have required set to Yes in the table where you are adding a record. When you
add the record, these fields remain null and therefore the error 3314 is
thrown. There are ways around this, (1) to include the other two fields and
add data to those at the same time. I will modify your code tomorrow in line
as a suggestion.

(2) The other way is to change the two respective fields Required Yes/No
setting from Yes to No

(Already communicated, modifications to follow, this note for clarity and
completness of the thread only)

Regards

Mike b
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

LOL! It did the same to me when I copied and pasted it back into my event ;-).
I've sent it to you (both 2003 and 2007 versions) in separate e-mails. In the
first e-mail, I've tried to describe to you what I am trying to do - not sure
how successful my descriptions were. I'm a night-owl, but 4am (in Australia)
is probably getting a little late ;-).

Looking forward to hearing from you.

Kindest Regards,

Sorry, there was no error on the Message Text Quotes, it was VB adding an
extra " in a line as I copied it from your Thread Message.

Without going deeply into your setup, I have set the following

[quoted text clipped - 63 lines]

Why do I still get the MS generic error messages?

 

[basiltabethacat via AccessMonster.com]

Ah! As usual, I think, "Why didn't I think of that??" . Access is trying
to add a new record where the other two fields in the record are also
required, but not yet entered (this combo box being the first field of the
three to be entered on my form).

Both options you mention are probably feasible to what I want to achieve. I
also have the Validation Rule property set on those two other fields a) Like
"2###" (a year), and b) ">0 and <5" (the number of the school term - up to 4
each year). Although I now have visions of users entering and adding the
combo box info without the other two fields, and leaving an incomplete orphan
record...

Perhaps the first option (add the fields at the same time - through the
button on_click?). Or...

...Or change the order of the fields - place the txtYearOfTeaching and
txtTermNumber before the cboPlanName in tab order, then when the question is
asked regarding the user adding a new record all three can be added together?
This is probably easier. I simply had the PlanName field first through an
inner sense of propriety that says "Name it first!" - the entering order
doesn't really matter .

Once again, a huge thanks for your help! The combo box works properly now -
it even creates the new record instead of overwriting the old one! ))

Kindest Regards

Further to our communications, your problem is with two other fields that
have required set to Yes in the table where you are adding a record. When you
add the record, these fields remain null and therefore the error 3314 is
thrown. There are ways around this, (1) to include the other two fields and
add data to those at the same time. I will modify your code tomorrow in line
as a suggestion.

(2) The other way is to change the two respective fields Required Yes/No
setting from Yes to No

(Already communicated, modifications to follow, this note for clarity and
completness of the thread only)

Regards

Mike b

LOL! It did the same to me when I copied and pasted it back into my event ;-).
I've sent it to you (both 2003 and 2007 versions) in separate e-mails. In the

[quoted text clipped - 13 lines]