Not in List Problem

[Guest]

I cannot figure out what is wrong with my code. I am trying to add a not in
the list entry to the original table which is the source of the combo box on
my form. The source table is "Nametech" which has the field "NameTech that
needs to be updated from the current form ""Internal Chart Audit" the combo
box name is "Technician_Name" Spot anything wrong with the coding?

Private Sub Technician_Name_NotInList(NewData As String, Response As Integer)
On Error Resume Next
Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@Do you want to add them?"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

Set Db = CurrentDb
Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Technician_Name = 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
End Sub

 

[Terry]

Hi,
Have a look at my code below and see if it is anything you can alter and
use.
Regards


Private Sub PlacesOfWork_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
On Error GoTo Err_Event

' Prompt user to verify they wish to add new value.
If MsgBox("Your entry is not in the list, do you wish to add it??", _
vbYesNo) = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("HP_TimsWorkList", dbOpenDynaset)
rs.AddNew
rs!WorkPlaces = Left$(NewData, 50) 'Data is returned by NewData
here
rs!SortOrder = 0
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
'Response of acDataErrAdded tells the comobox to requery its row
source
Response = acDataErrAdded
Else
'Show default message
Response = acDataErrContinue
Me.Undo
'Response = acDataErrDisplay
End If

Exit_Event:
Exit Sub

Err_Event:
MsgBox "I have encountered an unexpected error." & vbCrLf & _
"Please advise the System Administrator." & vbCrLf & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbOKOnly + vbCritical, _
"Error Encountered"
Resume Exit_Event

End Sub

 

[Guest]

So "HP-TimSWorkList" is the source table that you are adding the new item?
and rs!WorkPlaces is the name of the field in that table?

Hi,
Have a look at my code below and see if it is anything you can alter and
use.
Regards


Private Sub PlacesOfWork_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
On Error GoTo Err_Event

' Prompt user to verify they wish to add new value.
If MsgBox("Your entry is not in the list, do you wish to add it??", _
vbYesNo) = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("HP_TimsWorkList", dbOpenDynaset)
rs.AddNew
rs!WorkPlaces = Left$(NewData, 50) 'Data is returned by NewData
here
rs!SortOrder = 0
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
'Response of acDataErrAdded tells the comobox to requery its row
source
Response = acDataErrAdded
Else
'Show default message
Response = acDataErrContinue
Me.Undo
'Response = acDataErrDisplay
End If

Exit_Event:
Exit Sub

Err_Event:
MsgBox "I have encountered an unexpected error." & vbCrLf & _
"Please advise the System Administrator." & vbCrLf & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbOKOnly + vbCritical, _
"Error Encountered"
Resume Exit_Event

End Sub

I cannot figure out what is wrong with my code. I am trying to add a not in
the list entry to the original table which is the source of the combo box
on
my form. The source table is "Nametech" which has the field "NameTech that
needs to be updated from the current form ""Internal Chart Audit" the
combo
box name is "Technician_Name" Spot anything wrong with the coding?

Private Sub Technician_Name_NotInList(NewData As String, Response As
Integer)
On Error Resume Next
Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@Do you want to add them?"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

Set Db = CurrentDb
Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Technician_Name = 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
End Sub

 

[Guest]

Hi.

I cannot figure out what is wrong with my code.

Your table name, Nametech, is in brackets in the code below. You should
remove these brackets. You also mentioned that the field name is "NameTech,"
but your code uses "Technician_Name," instead. Your code must use the actual
names of the table and the field. (I suspect that when you mentioned that
"NameTech" was the name of both your table and field, that was only a typo.)

Your use of "On Error Resume Next" makes you blind to the actual error.
It's akin to stepping into the crosswalk with your eyes closed, because you
don't want to see the look of horror on the face of the driver as he hits and
kills a pedestrian. Proper error handling lets you at least see that car
coming so that you can step back onto the sidewalk.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I cannot figure out what is wrong with my code. I am trying to add a not in
the list entry to the original table which is the source of the combo box on
my form. The source table is "Nametech" which has the field "NameTech that
needs to be updated from the current form ""Internal Chart Audit" the combo
box name is "Technician_Name" Spot anything wrong with the coding?

Private Sub Technician_Name_NotInList(NewData As String, Response As Integer)
On Error Resume Next
Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@Do you want to add them?"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

Set Db = CurrentDb
Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Technician_Name = 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
End Sub

 

[Dirk Goldgar]

"'69 Camaro" <[email protected]_SPAM>
wrote in message

Your use of "On Error Resume Next" makes you blind to the actual
error.
It's akin to stepping into the crosswalk with your eyes closed,
because you don't want to see the look of horror on the face of the
driver as he hits and kills a pedestrian. Proper error handling lets
you at least see that car coming so that you can step back onto the
sidewalk.

Wow, Gunny, that's pretty graphic! But I don't know ... isn't proper
error-handling more like letting you see the license plate of the car
that just hit you, as it speeds away?

 

[Guest]

Updated coding... I receive error #3265.

Private Sub Technician_Name_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database, rs As DAO.Recordset
On Error GoTo Err_Event

' Prompt user to verify they wish to add new value.
If MsgBox("Your entry is not in the list, do you wish to add it??", _
vbYesNo) = vbYes Then
Set db = CurrentDb
Set rs = db.OpenRecordset("tbleNametech", dbOpenDynaset)
rs.AddNew
rs!Nametech = NewData
rs.Update
rs.Close
Set rs = Nothing
Set db = Nothing
'Response of acDataErrAdded tells the combobox to requery its row
Source
Response = acDataErrAdded
Else
'Show default message
Response = acDataErrContinue
Me.Undo
'Response = acDataErrDisplay
End If
Exit_Event:
Exit Sub


Err_Event:
MsgBox "I have encountered an unexpected error." & vbCrLf & _
"Please advise the System Administrator." & vbCrLf & vbCrLf & _
"Error: " & Err.Number & " " & Err.Description, vbOKOnly + vbCritical, _
"Error Encountered"
Resume Exit_Event
End Sub

 

[Kevin K. Sullivan]

I think VBA's error-handling shows you the model of the car that just
hit you, but not the license plate, since you can tell the error number
but not the code that raised it.

Kevin

 

[Guest]

Hi.

Updated coding... I receive error #3265.

Error 3265 is "Item not found in this collection." Your Fields Collection
doesn't contain a field named "Nametech" so check the spelling in the table's
Design View. (Copy/paste if you have to to make sure the code and the field
name match.)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[David C. Holley]

I like the Error Handling metaphor - esp the 'look of horror on the face'

Hi.

I cannot figure out what is wrong with my code.

Your table name, Nametech, is in brackets in the code below. You should
remove these brackets. You also mentioned that the field name is "NameTech,"
but your code uses "Technician_Name," instead. Your code must use the actual
names of the table and the field. (I suspect that when you mentioned that
"NameTech" was the name of both your table and field, that was only a typo.)

Your use of "On Error Resume Next" makes you blind to the actual error.
It's akin to stepping into the crosswalk with your eyes closed, because you
don't want to see the look of horror on the face of the driver as he hits and
kills a pedestrian. Proper error handling lets you at least see that car
coming so that you can step back onto the sidewalk.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

I cannot figure out what is wrong with my code. I am trying to add a not in
the list entry to the original table which is the source of the combo box on
my form. The source table is "Nametech" which has the field "NameTech that
needs to be updated from the current form ""Internal Chart Audit" the combo
box name is "Technician_Name" Spot anything wrong with the coding?

Private Sub Technician_Name_NotInList(NewData As String, Response As Integer)
On Error Resume Next
Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@Do you want to add them?"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

Set Db = CurrentDb
Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Technician_Name = 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
End Sub

 

['69 Camaro]

Hi, Dirk.

Well, as pedestrians, all we are is points for the drivers anyway. 1 point
for a pedestrian with his eyes closed, 2 points for the ones who saw the car
coming and threw something at the driver, 3 points for the ones who can hang
onto the windshield wipers without breaking them, and 10 points for lawyers
with briefcases (whether or not they have their eyes closed).

Just kidding! (Except for the lawyers. They're only worth 1 point.) ;-)

Gunny

 

['69 Camaro]

You got me there. If I'd known you guys were paying attention, I'd have
written something witty. I'll have to think of something later.

Gunny

 

[David C. Holley]

Actually I think that Error handling is like putting up cams in the
house and leaving the kids alone for the weekend. When I walk back in on
Monday morning and the house is spotless, the CAMS let me see just what
exactly happened over the weekend including the prostitutes that they
had over and their dancing in their underwear and dress shirts to 'OL
Time Rock and Roll.

 

[David C. Holley]

Comment out the On Error GoTo Err_Event

This should cause the code to break on the offending line.

 

[David C. Holley]

Not to mention of course that the driver of the car doesn't speak your
native language and so you can't understand a word of the explanation

(I STILL hate the 'USER CANCELED THE PREVIOUS OPERATION' when a domain
function craps out!)

David H

 

[Guest]

Thanks for the help with it! I have it working thanks to you. I appreciate
the attention to the car metaphors the others made. It sure helped me so
much. I didn't think I was that bad for only programming access for around 3
weeks, especially since the code was a modified version of a "experienced"
programmer!
Thanks again Terry for the real help!

P

 

['69 Camaro]

<g> It gets the point across.

Gunny

I like the Error Handling metaphor - esp the 'look of horror on the face'

Hi.

I cannot figure out what is wrong with my code.

Your table name, Nametech, is in brackets in the code below. You should
remove these brackets. You also mentioned that the field name is
"NameTech," but your code uses "Technician_Name," instead. Your code
must use the actual names of the table and the field. (I suspect that
when you mentioned that "NameTech" was the name of both your table and
field, that was only a typo.)

Your use of "On Error Resume Next" makes you blind to the actual error.
It's akin to stepping into the crosswalk with your eyes closed, because
you don't want to see the look of horror on the face of the driver as he
hits and kills a pedestrian. Proper error handling lets you at least see
that car coming so that you can step back onto the sidewalk.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail
harvesters for spammers are (e-mail address removed) and
(e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's
Online Community, please sign in to the Community and mark these posts as
"Answers," so that all may benefit by filtering on "Answered questions"
and quickly finding the right answers to similar questions. Remember
that questions answered the quickest are often from those who have a
history of rewarding the contributors who have taken the time to answer
questions correctly.


:

I cannot figure out what is wrong with my code. I am trying to add a not
in the list entry to the original table which is the source of the combo
box on my form. The source table is "Nametech" which has the field
"NameTech that needs to be updated from the current form ""Internal Chart
Audit" the combo box name is "Technician_Name" Spot anything wrong
with the coding?

Private Sub Technician_Name_NotInList(NewData As String, Response As
Integer)
On Error Resume Next
Dim Db As Database, rs As Recordset
Dim strmsg As String
strmsg = "'" & NewData & "' is not in Current List"
strmsg = strmsg & "@Do you want to add them?"
strmsg = strmsg & "@Click Yes to link or No to Cancel."
If MsgBox(strmsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else

Set Db = CurrentDb
Set rs = Db.OpenRecordset("[Nametech]", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!Technician_Name = 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
End Sub

 

[Terry]

You're welcome, glad this thread threw up a few grins for us.
regards