473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Dumb question - took my stupid pill too early

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
3 3182
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

16
by: squash | last post by:
a dumb question i had on my mind: Say you have a dynamically created web page . Isn't it more secure to write it in php since a visitor will not be able to tell it is dynamically created? But...
4
by: Aubrey Hutchison | last post by:
I have exhausted places to look for where "PYTHONPATH" is located.. In windows (using the DOS command) no such thing exist but path does. I find no file with that name. I have yet to find a...
6
by: George R. Gonzalez | last post by:
Ok, I've wasted two days trying to figure this out. I'm stuck using Symantec C++ 7.5 on this one project. I'd port it over to something more recent, but time is pressing... I need to move some...
6
by: Bonge Boo! | last post by:
This has got to be obvious, but I can't make it work. I have a form called with 3 pull down menus. They are linked to a database which generates the values for the <SELECT? Pull-downs. Lets...
8
by: Fred Hebert | last post by:
I have a function that requires a LPCTSTR parameter. I have the value I want to pass to it in a TextBox->Text field. Is there any way to do it in a single assignment. e.g. DWORD...
4
by: Stelrad Doulton | last post by:
Hi, Apologies if this isn't the correct forum. I am writing a communication solution (actually on the Compact Framework) based on HttpWebRequests hooking up with custom handlers on the...
5
by: Wayne Dixon | last post by:
Ok, I know this is going to sound like a really stupid quesiton, but does anybody know how to load a form from a click event? I've tried my standard VB6 code tricks, of course to no avail. ...
24
by: cameltoer | last post by:
Need a good tutorial on Classes, OOP, Template systems, etc, because absolutely nothing, and I mean nothing looks familiar to my procedural based programming that has served me for years. Should I...
4
by: theapeman | last post by:
Sorry to bore everyone with this question, which I'm sure is the equivalent of "Please help! What letter comes between C and E in the alphabet?" but seriously, if you really didn't know that, I don't...
331
by: Xah Lee | last post by:
http://xahlee.org/emacs/modernization.html ] The Modernization of Emacs ---------------------------------------- THE PROBLEM Emacs is a great editor. It is perhaps the most powerful and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.