Saving a form without overwriting record

[lhtan123]

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

 

[Jeff Boyce]

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


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Dale Fye]

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]

Thanks for the advice.

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.

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