By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,391 Members | 1,926 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,391 IT Pros & Developers. It's quick & easy.

Dumb question - took my stupid pill too early

P: n/a
Good morning, and thank you in advance.

Using Access 2000 with all updates applied through last weekend. OS is XP
Home.

tblPlayers has only one field - it holds names of actors and actresses in a
field called "fldPlayerName".
cboPlayer1 is a combo box in which the user is to select the name of the
actor. The list is based on tblPlayers, ordered by the alphabetical order of
the names.

Problem - obviously, during the data entry, a previously unused name of an
actor will eventually turn up. When this happens, I simply want to add the
new name to the table tblPlayers. So, I wrote the following -
Private Sub cboPlayer1_NotInList(NewData As String, Response As Integer)

DoCmd.Echo (-1)
DoCmd.OpenTable "tblPlayers", acViewNormal
DoCmd.GoToRecord acDataTable, "tblPlayers", acNewRec: MsgBox "Third Line
Reached" 'Comment - to tell me that the routine has worked to this point
tblPlayers , fldPlayerName = cboPlayer1.Value: MsgBox "Fourth Line reached"
'Comment - to tell me that the routine has worked to this point

End Sub
The routine works up to the message box "Third Line Reached". After that, I
have gotten a variety of errors, depending on how I punctuated the last line
in the routine. I've seen "424 Object required". I've seen "Sub or
functioned not defined". I've seen "There's not enough memory to update the
display". And there have been a few others, but all of them are repeatable,
depending on the use of parentheses, quotation marks, periods, commas and
the like in the part of the last line that is to the left of the equal
sign..

The most frustrating thing about this is that I have done this same, or a
very similar, procedure before and I worked it out so that it
functioned...but I can't find the code that I wrote then. Figures.

Can I ask any of you for a bit of help on this, please? Thank you...
Steve E.


Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On Sat, 21 Feb 2004 15:44:31 GMT, Serious_Practitioner wrote:
Good morning, and thank you in advance.

Using Access 2000 with all updates applied through last weekend. OS is XP
Home.

tblPlayers has only one field - it holds names of actors and actresses in a
field called "fldPlayerName".
cboPlayer1 is a combo box in which the user is to select the name of the
actor. The list is based on tblPlayers, ordered by the alphabetical order of
the names.

Problem - obviously, during the data entry, a previously unused name of an
actor will eventually turn up. When this happens, I simply want to add the
new name to the table tblPlayers. So, I wrote the following -
Private Sub cboPlayer1_NotInList(NewData As String, Response As Integer)

DoCmd.Echo (-1)
DoCmd.OpenTable "tblPlayers", acViewNormal
DoCmd.GoToRecord acDataTable, "tblPlayers", acNewRec: MsgBox "Third Line
Reached" 'Comment - to tell me that the routine has worked to this point
tblPlayers , fldPlayerName = cboPlayer1.Value: MsgBox "Fourth Line reached"
'Comment - to tell me that the routine has worked to this point

End Sub
The routine works up to the message box "Third Line Reached". After that, I
have gotten a variety of errors, depending on how I punctuated the last line
in the routine. I've seen "424 Object required". I've seen "Sub or
functioned not defined". I've seen "There's not enough memory to update the
display". And there have been a few others, but all of them are repeatable,
depending on the use of parentheses, quotation marks, periods, commas and
the like in the part of the last line that is to the left of the equal
sign..

The most frustrating thing about this is that I have done this same, or a
very similar, procedure before and I worked it out so that it
functioned...but I can't find the code that I wrote then. Figures.

Can I ask any of you for a bit of help on this, please? Thank you...
Steve E.


Steve,

Note: tblPlayers should have at least 2 fields, not 1.
An ActorID field and the ActorName field.
Preferable would be 3 fields, ID, FirstName, LastName.
Sort the above by LastName, FirstName.

If all you wish to do is add a on field name to the list, as you are
now using it, (without need of also adding data in additional fields)
simply Append the name to the table.

In the Combo's NotInList event:

Private Sub cboPlayer1_NotInList(NewData As String, Response As
Integer)
' Prompt user to verify they wish to add new value.
If MsgBox("Actor is not in the list. Add it?", vbOKCancel) = vbOK Then
' Set Response argument to indicate that data is being added.
Response = acDataErrAdded
CurrentDb.Execute " INSERT INTO tblPlayers(fldPlayerName) SELECT
'" & NewData & "';", dbFailOnError
Else
' If user chooses Cancel, suppress error message and undo changes.
Response = acDataErrContinue
' Clear the name from the combo box.
Me!cboPlayer1 = Null
End If
End Sub

You can use a similar Append statement, adding 2 or more fields
(separated by a comma) at the same time, if needed if you decide to
properly use separate fields for first and last names. An easy method
of getting that second name is to use an input box.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 12 '05 #2

P: n/a
"Serious_Practitioner" <Se************************@att.net> wrote in message
news:z1********************@bgtnsc04-news.ops.worldnet.att.net...
Good morning, and thank you in advance.

Using Access 2000 with all updates applied through last weekend. OS is XP
Home.

tblPlayers has only one field - it holds names of actors and actresses in a field called "fldPlayerName".
cboPlayer1 is a combo box in which the user is to select the name of the
actor. The list is based on tblPlayers, ordered by the alphabetical order of the names.

Problem - obviously, during the data entry, a previously unused name of an
actor will eventually turn up. When this happens, I simply want to add the
new name to the table tblPlayers. So, I wrote the following -
Private Sub cboPlayer1_NotInList(NewData As String, Response As Integer)

DoCmd.Echo (-1)
DoCmd.OpenTable "tblPlayers", acViewNormal
DoCmd.GoToRecord acDataTable, "tblPlayers", acNewRec: MsgBox "Third Line
Reached" 'Comment - to tell me that the routine has worked to this point
tblPlayers , fldPlayerName = cboPlayer1.Value: MsgBox "Fourth Line reached" 'Comment - to tell me that the routine has worked to this point

End Sub
The routine works up to the message box "Third Line Reached". After that, I have gotten a variety of errors, depending on how I punctuated the last line in the routine. I've seen "424 Object required". I've seen "Sub or
functioned not defined". I've seen "There's not enough memory to update the display". And there have been a few others, but all of them are repeatable, depending on the use of parentheses, quotation marks, periods, commas and
the like in the part of the last line that is to the left of the equal
sign..

The most frustrating thing about this is that I have done this same, or a
very similar, procedure before and I worked it out so that it
functioned...but I can't find the code that I wrote then. Figures.

Can I ask any of you for a bit of help on this, please? Thank you...
Steve E.



Steve,
Perhaps someone will post some possible suggestions to amend this code, but
if this was my database, I would simply delete it and start from scratch. I
don't believe you will find many text books advocating DoCmd.OpenTable
"tblPlayers", acViewNormal as the first step to adding a new record, and
although it may look like many more lines, I might write it more like this:

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

On Error GoTo Err_Handler

Dim strPlayerName As String

strPlayerName = StrConv(Trim(NewData), vbProperCase)

strPlayerName = InputBox(strPlayerName & " is not in the player list." &
_
vbCrLf & "Do you wish to add this player?", _
"New Player", strPlayerName)

If Len(strPlayerName) = 0 Then
Response = acDataErrContinue
cboPlayer1.Undo
Exit Sub
End If

If InsertPlayer(strPlayerName) Then
cboPlayer1 = strPlayerName
Response = acDataErrAdded
End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Public Function InsertPlayer(strPlayerName) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim strSQL As String

strSQL = "INSERT INTO tblPlayers ( fldPlayerName ) " & _
"VALUES ( """ & strPlayerName & """ )"

Set dbs = CurrentDb

dbs.Execute strSQL, dbFailOnError

If dbs.RecordsAffected = 1 Then
InsertPlayer = True
End If

Exit_Handler:

On Error Resume Next

If Not dbs Is Nothing Then
Set dbs = Nothing
End If

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function

Fletcher
Nov 12 '05 #3

P: n/a
first, I'd add a question to confirm that a new record is required,
ie. it wasn't a typo

second, why not just open the form that manages players, passing the
'player name' as an argument ?

"Serious_Practitioner" <Se************************@att.net> wrote in message news:<z1********************@bgtnsc04-news.ops.worldnet.att.net>...
Good morning, and thank you in advance.

Using Access 2000 with all updates applied through last weekend. OS is XP
Home.

tblPlayers has only one field - it holds names of actors and actresses in a
field called "fldPlayerName".
cboPlayer1 is a combo box in which the user is to select the name of the
actor. The list is based on tblPlayers, ordered by the alphabetical order of
the names.

Problem - obviously, during the data entry, a previously unused name of an
actor will eventually turn up. When this happens, I simply want to add the
new name to the table tblPlayers. So, I wrote the following -
Private Sub cboPlayer1_NotInList(NewData As String, Response As Integer)

DoCmd.Echo (-1)
DoCmd.OpenTable "tblPlayers", acViewNormal
DoCmd.GoToRecord acDataTable, "tblPlayers", acNewRec: MsgBox "Third Line
Reached" 'Comment - to tell me that the routine has worked to this point
tblPlayers , fldPlayerName = cboPlayer1.Value: MsgBox "Fourth Line reached"
'Comment - to tell me that the routine has worked to this point

End Sub
The routine works up to the message box "Third Line Reached". After that, I
have gotten a variety of errors, depending on how I punctuated the last line
in the routine. I've seen "424 Object required". I've seen "Sub or
functioned not defined". I've seen "There's not enough memory to update the
display". And there have been a few others, but all of them are repeatable,
depending on the use of parentheses, quotation marks, periods, commas and
the like in the part of the last line that is to the left of the equal
sign..

The most frustrating thing about this is that I have done this same, or a
very similar, procedure before and I worked it out so that it
functioned...but I can't find the code that I wrote then. Figures.

Can I ask any of you for a bit of help on this, please? Thank you...
Steve E.

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.