Replies

Thanks. I figured out a way.
Clark

Thanks. I figured out a way.
Clark

Thanks!

Your suggestion to show hidden characters turned out to be very helpful. It proved that even something this chaotic has some order to it, in this case I was dealing mostly with multiples of five spaces and lots of tabs. A couple of find/replaces and I had the document all cleaned up. Thanks for your help.
Clark

Thanks. I tried this and it worked pretty well. It left some words run together and it left some larger than usual spaces between words. I’m not sure where that comes from. There will be a single space between two words, which looks like two or more spaces. If I delete it and hit the space bar, then a normal space is inserted.
Clark

Ha! It worked! Thanks very much HansV. It’s so nice to have a place where dummies like me can come and get help.
Clark

The table is named “In-Print Inventory2”. I was assuming that “tbl” had to be included in the SQL. Leaving it out brings a third error. So here’s the code:

Private Sub Publisher_NotInList(NewData As String, Response As Integer)
If MsgBox(“The type ” & NewData & ” does not occur not in the list.” & vbCrLf & _
“Do you want to add it?”, vbYesNo + vbQuestion) = vbYes Then
Dim strSQL As String
strSQL = “INSERT INTO [In-Print Inventory2] (Publisher) VALUES (” & _
Chr$(34) & NewData & Chr$(34) & “)”
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Publisher.Undo
Response = acDataErrContinue
End If
End Sub

Here’s the new error message:”Run-time error ‘3464’: Data type mismatch in criteria expression.” And the de###### references the same line of code (CurrentDb.Execute strSQL, dbFailOnError)

Adding brackets (like [tblIn-Print Inventory2]) just brings up another error: “runtime error ‘3192’ could not find output table ‘tblIn-Print Inventory2’.”

Modifying some of the code examples I was referred to I have come up with the following:

Private Sub Publisher_NotInList(NewData As String, Response As Integer)
If MsgBox(“The type ” & NewData & ” does not occur not in the list.” & vbCrLf & _
“Do you want to add it?”, vbYesNo + vbQuestion) = vbYes Then
Dim strSQL As String
strSQL = “INSERT INTO tblIn-Print Inventory2 (Publisher) VALUES (” & _
Chr$(34) & NewData & Chr$(34) & “)”
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Publisher.Undo
Response = acDataErrContinue
End If
End Sub

What I am trying to do is this: in the form “In-Print Inventory2”, based on the the table “In-Print Inventory2”, there is a field called “Publisher” which is a combo box based on the field “Short Name” in the table “Publishers”. I want to be able to add a new publisher to the combo box on the fly. I’ve placed the code above as an event procedure in the Not In List event property of the field “Publisher” of the form “In-Print Inventory2”. When I add a new publisher in the form I’m told the name is not on the list, would I like to add it. When I click yes I get “runtime error ‘3134’ syntax error in INSERT INTO statement” and I’m referred to this line in the code:
CurrentDb.Execute strSQL, dbFailOnError

Clark

Would it possible for you to describe this procedure for me?
Clark

Thanks again!

This macro works very well for me. However, sometimes I would like to delete an entire row because it’s a duplicate entry or for some other reason. This messes up the balance in every subsequent row. My only recourse is to leave the incorrect entry and change its value to 0. Do I need a “delete row” macro now?
Clark

Fixed it. Thanks.
Clark

Thanks, that worked nicely. Actually this is the first time since beginning to use Access 4 years ago that I have found it necessary to know which of my publications my customers had purchased. Until now it only mattered if they had bought anything.
Clark

Works great! Thanks!