Saving a form without overwriting record lhtan123 11/25/2008 6:38:00 AM I have a form that is bound to a "Company" table as recordsource. This has a listbox that displays all companies and a textbox for entering new company. The table only has one field which is called "Company" and it is also the primary key. Supposed i enter a new record called "Alpha" in the form, an existing record that also starts with "a" will be overwritten. I tried to create a button to purposely check for similar record before saving but it gave me a compile error: Sub or Function not defined". Below is the code: Private Sub btnUpdateLCo_Click() With Me.RecordsetClone FindFirst "[Liason Company] = """ & Me.txtNewLCo & """" If .NoMatch Then Me.Dirty Else MsgBox "Similar record is found!", vbInformation, "Liason Company" End If End With End Sub

Re: Saving a form without overwriting record "Jeff Boyce" <JeffBoyce_IF[ at ]msn.com-DISCARD_HYPHEN_TO_END> 11/25/2008 3:03:24 PM Are you quite certain you will never encounter two "companies" that share the same name? Using "companyname" (like using "personname") is pretty risky as a primary key. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "lhtan123" <lhtan123[ at ]discussions.microsoft.com> wrote in message news:DEFC5FA1-ADEF-4B6D-A5EA-982270495460[ at ]microsoft.com... [Quoted Text] > I have a form that is bound to a "Company" table as recordsource. This has a > listbox that displays all companies and a textbox for entering new company. > > The table only has one field which is called "Company" and it is also the > primary key. Supposed i enter a new record called "Alpha" in the form, an > existing record that also starts with "a" will be overwritten. > > I tried to create a button to purposely check for similar record before > saving but it gave me a compile error: Sub or Function not defined". Below is > the code: > > Private Sub btnUpdateLCo_Click() > > With Me.RecordsetClone > FindFirst "[Liason Company] = """ & Me.txtNewLCo & """" > > If .NoMatch Then > Me.Dirty > Else > MsgBox "Similar record is found!", vbInformation, "Liason Company" > End If > End With > > End Sub

RE: Saving a form without overwriting record Dale Fye 11/25/2008 3:47:01 PM Agree with Jeff about using the company name as a PK. it sounds like, your textbox for entering the company name is bound to the form. Is that the case? If so, then unless you move to a new record before entering a value in that textbox, you will ultimately overwrite the record that you are on. Are you displaying the navigation buttons on the form? If not, add a command button (cmd_Add_Company) to the form and give it the following code in the click event: Private Sub cmd_Add_Company_Click docmd.GoToRecord , , acNewRec End Sub -- HTH Dale email address is invalid Please reply to newsgroup only. "lhtan123" wrote: [Quoted Text] > I have a form that is bound to a "Company" table as recordsource. This has a > listbox that displays all companies and a textbox for entering new company. > > The table only has one field which is called "Company" and it is also the > primary key. Supposed i enter a new record called "Alpha" in the form, an > existing record that also starts with "a" will be overwritten. > > I tried to create a button to purposely check for similar record before > saving but it gave me a compile error: Sub or Function not defined". Below is > the code: > > Private Sub btnUpdateLCo_Click() > > With Me.RecordsetClone > FindFirst "[Liason Company] = """ & Me.txtNewLCo & """" > > If .NoMatch Then > Me.Dirty > Else > MsgBox "Similar record is found!", vbInformation, "Liason Company" > End If > End With > > End Sub

RE: Saving a form without overwriting record lhtan123 11/26/2008 6:32:00 AM Thanks for the advice. "Dale Fye" wrote: [Quoted Text] > Agree with Jeff about using the company name as a PK. > > it sounds like, your textbox for entering the company name is bound to the > form. Is that the case? If so, then unless you move to a new record before > entering a value in that textbox, you will ultimately overwrite the record > that you are on. > > Are you displaying the navigation buttons on the form? If not, add a > command button (cmd_Add_Company) to the form and give it the following code > in the click event: > > Private Sub cmd_Add_Company_Click > > docmd.GoToRecord , , acNewRec > > End Sub > > -- > HTH > Dale > > email address is invalid > Please reply to newsgroup only. > > > > "lhtan123" wrote: > > > I have a form that is bound to a "Company" table as recordsource. This has a > > listbox that displays all companies and a textbox for entering new company. > > > > The table only has one field which is called "Company" and it is also the > > primary key. Supposed i enter a new record called "Alpha" in the form, an > > existing record that also starts with "a" will be overwritten. > > > > I tried to create a button to purposely check for similar record before > > saving but it gave me a compile error: Sub or Function not defined". Below is > > the code: > > > > Private Sub btnUpdateLCo_Click() > > > > With Me.RecordsetClone > > FindFirst "[Liason Company] = """ & Me.txtNewLCo & """" > > > > If .NoMatch Then > > Me.Dirty > > Else > > MsgBox "Similar record is found!", vbInformation, "Liason Company" > > End If > > End With > > > > End Sub