 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / October 2008Need Help Handling Apostrophes with New Data
Hello. I am trying to handle the apostrophes during this NotInList event: I cannot get it right and am at my wits end. Any ideas? Thanks! Private Sub ContactID_NotInList(NewData As String, Response As Integer) Dim Result Dim Msg As String Dim CR As String ' Carriage Return 'Title = "TED version 2.1" CR = vbCrLf ' Return Control object that points to combo box. Dim ctl As Control Set ctl = Me![ContactID] 'An alias for the name of this combo-box, whichreduces typing. ' Exit this subroutine if the combo box was cleared. If NewData = "" Then Exit Sub NewData = StrConv(NewData, vbProperCase) ' Converts new entry to ProperCase. ' Ask the user if he or she wishes to add the new contact. Msg = "" Msg = Msg & "'" & NewData & "' is not in TED Site Contacts" & CR & CR Msg = Msg & "Do you want to add this Site Contact?" DoCmd.SetWarnings False If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then ' Set Response argument to indicate that data is being added. strSql = "INSERT INTO [tblsitecontacts] (contactname) " & _ "VALUES ('" & NewData & " & Chr$(34)"" & Me![ContactName] & Chr$(34)"");" DoCmd.RunSQL strSql intAnswer = Nz(DMax("[contactID]", "[tblsitecontacts]"), 0) MsgBox "The new Contact" & CR & NewData & " was Added. Please select " & NewData & " from the List and continue Entering " & NewData & " Information or Use the Undo Button" _ , vbInformation, "TED version 2.1" Response = acDataErrAdded '** MUST ** undo entry first, or you get "Can't go to specified record" 'DoCmd.GoToRecord , , acNewRec ctl.Undo DoCmd.RunCommand acCmdSaveRecord 'Supress error message Response = acDataErrContinue Else 'User clicked the No button here. 'You could display a message to "try again", but that just annoys mostof us. 'Supress error message Response = acDataErrContinue ctl.Undo SendKeys "{ESC}" Exit Sub End If Set ctl = Nothing ' ANY time you use "Set", you should "unset" it beforeleaving. 'ErrContinue Me.ContactID.Requery Well, I found the answer finally here on this board!! Thank you >Hello. I am trying to handle the apostrophes during this NotInList event: >I cannot get it right and am at my wits end. [quoted text clipped - 72 lines] >'ErrContinue > Me.ContactID.Requery I'll share a little trick with you rebecky. You have stumbled into the problem where a single qoute (apostrophe) creates problems when search for or filtering data. As you probably have discovered by now, the solution is to always use double qoutes to delimit text values; but you have also found that trying to get the right number of double quotes to create a string with a double quote would make Chuck Norris break down in tears. This used to drive me crazy until I came up with a trick that eliminated the problem. This trick is like training wheels on a bicycle. Use it for a while, then you will find you understand how it works, and you wont need it any more. When you need to put qoutes in a string, first write the code using the single qoutes....easy enough. v v strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES ('" & NewData & "' );" Now, go back and replace each single qoute with two double qoutes: v v strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" & NewData & """ );" > Well, I found the answer finally here on this board!! > Thank you [quoted text clipped - 77 lines] >>'ErrContinue >> Me.ContactID.Requery Thanks for the tip! Certainly SOUNDS easy enough! I will use it next time the occasion calls for it. I am having a dilly of a time with this: I want the user to be able to go to a new record in the subform after choosing a value from the combo box, then choose another value from the combo box for that record....is that doable? Some "jobs" have more than one contact so that is why I need it..... Thanks again! >I'll share a little trick with you rebecky. > [quoted text clipped - 27 lines] >>>'ErrContinue >>> Me.ContactID.Requery You don't need to go to a new record. You are adding the new record in the Combo if the contact name doesn't already exist. The issue you have is that the new record is added to the table, but it is not yet in your subform's recordset. To get it there, you have to requery the subform. After you do that, the subform will go back to the first record, but since you actually want to edit the new record, you can make it the current record. Here is some additional code you can add to your NotInList event to do that: strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" & NewData & """);" Currentdb.Execute strSQL, dbFailOnError Me.MySubForm.Form.Requery With Me.MySubForm.Form.RecordsetClone .FindFirst "[contactname] = """ & NewData & """" If Not .NoMatch Then Me.MySubForm.Form.Bookmark = .Bookmark End If End With > Thanks for the tip! Certainly SOUNDS easy enough! I will use it next > time [quoted text clipped - 41 lines] >>>>'ErrContinue >>>> Me.ContactID.Requery Thank you. You have been MOST helpful. I cannot seem to accomplish what I want, and though I almost Always refuse to believe "it can't be done", I don't think it can be done. This was my intention: I have a MainForm for Employers(table1) and a Subform for Job Order Information(table2) - related by EmployerID. Within the Job Order Information Subform, there is a Subform for Job Order Contacts (tblSiteContacts) related by PositionID. It is a subform because some Job Orders have more than one contact. What I wanted to do was have a Combo box on the Job Order Information Subform, so the user could choose the contact if it was already there and it would pop up in the subform, and if not there, add it(NotInList Event), and then go to the next record in the subform and choose a second contact for THAT job order from the combo box or "enter" a second contact for that job order. I could not get the combo to work at all when the form was related by positionid, but it will work when related by contactid only that doesn't keep the "contact" with the "position" as they are related by "positionID". It doesn't work because when you choose a second value from the ComboBox, it just changes the first record in the subform and does nothing at the second record. Then if you go to the next record in the subform and try to type a second contact in without using the combo box, you get an error message about not being able to assign a value to that object. I hope you are not thoroughly confused now!!! I am about out of time for this revised version of this Database, so do you know if this can actually work somehow? Thanks again rebecky >You don't need to go to a new record. You are adding the new record in the >Combo if the contact name doesn't already exist. [quoted text clipped - 24 lines] >>>>>'ErrContinue >>>>> Me.ContactID.Requery Actually, it will work. I have an almost identical subform working, but the only difference it is not the subform of a subform, but that should not make a difference. The only reason it is not the subform of a subform is because I almost never allow any editing directly in a datasheet subform. If a user wants to edit data in a subform record, they have to open a form that does the editing in a single form form. On the main form (editing form for subform), I have a combo that allows the user to add a new existing contact to the job or, if the contact doesn't exits, it opens the form to a new record with the contact name already entered and allows the user to complete the record. > Thank you. You have been MOST helpful. I cannot seem to accomplish what > I [quoted text clipped - 69 lines] >>>>>>'ErrContinue >>>>>> Me.ContactID.Requery Well, the subform is not in Data Sheet View. It is in Single Form View with buttons to go to next or previous or to add. The idea of opening a form to enter is something I will investigate...... >Actually, it will work. I have an almost identical subform working, but the >only difference it is not the subform of a subform, but that should not make [quoted text clipped - 15 lines] >>>>>>>'ErrContinue >>>>>>> Me.ContactID.Requery If you are using a single form, then you don't need another form, you just need to go to a new record. > Well, the subform is not in Data Sheet View. It is in Single Form View > with [quoted text clipped - 28 lines] >>>>>>>>'ErrContinue >>>>>>>> Me.ContactID.Requery Yeah, I know.... I was just hoping there was a way to go to a new record in that subform and pick a different value from that combo box on the parent form, but it won't work. >If you are using a single form, then you don't need another form, you just >need to go to a new record. [quoted text clipped - 4 lines] >>>>>>>>>'ErrContinue >>>>>>>>> Me.ContactID.Requery |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.