Help with an append button on the form

[123Olaf]

Hello,
I built a form that has an Append Button that helps me to add records to a table. The code that I have works very well (see below code) but I would like to add additional step to it. Once the user clicked on the append button once, I would like to add a code that would prevent/stop the user from clicking on this button for the second time to prevent the duplicate data being added to the table that this button is linked to. Can some one please help me to write this part of code?

Private Sub AppendContractsCommand_Click()


Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim itm, contractID, contractname As Variant
Dim contract() As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("ChecksApplied")

For Each itm In [cboContract ID].ItemsSelected
contract() = Split([cboContract ID].ItemData(itm), ", ")

For Each contractID In contract()
Set rs2 = db.OpenRecordset("SELECT * FROM [Contacts] WHERE [Contract ID] = '" & contractID & "'")
contractname = rs2![Contract Name]

rs.AddNew
rs![Contract ID] = contractID
rs![Payment ID] = [Payment ID]
rs![Parent Company ID] = [cboParentCompanyID]
rs![Company Name] = contractname
rs.Update
Next
Next

rs.Close
rs2.Close
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing



Thanks for your help.

 

[Ranman256]

You could have done this without any code.
Just run an append query.

 

[missinglinq]

Or better yet, use Access as it is intended to be used...with Bound Forms!

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to

1. Do Data Validation
2. Prevent Duplicate Records
3. Do Formatting of Data before it's Saved
4. Decide whether or not to actually Save a New or Edited Record

which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#4, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything, has simply made a bad choice in deciding to work in Access.

Linq ;0)>

 

[123Olaf]

missinglinq and Ranman256,
Thank you for your responses.
I wanted to mention that the form where this append button is placed is a bound form and when the user clicks on the button it runs an append query and adds records to a table. The reason why I posted this question is because I am trying to build an enhancement to the VBA code that I have. This enhanced part of the code should prevent the append button to be clicked more then once and potentially give a warning to the user before the user clicks the append button again. This process will help me to eliminate possibility for duplicate records to be added to the table.
Can someone please help me with building this code?
Any help is appreciated.
Thanks.

 

[Minty]

What determines it would be a duplicate?

 

[missinglinq]

That's the approach you need, here. While you could disable the button, once clicked, on a given Record, what would keep a user from returning to an existing Record and clicking on the button? You need to prevent a duplicate Record from being stored, and possibly pop a Messagebox advising the user of this if they attempt to do so.

As Minty said...what comprises a 'duplicate' Record?

Linq ;0)>

 

[123Olaf]

Minty and missinglinq,
Thank you for your responses.
To answer your question:"What determines it would be a duplicate? " If user clicked on the append button more then once, a new record is added to the table that is linked to this process and when I open up this table I can see these dups.
The above listed table looks as follows:
PaymentID; CompanyID; CompanyName; ContractID; AmountPaid
111; abc12; Fruit; AAAAA; 1.00
123; def23; Veritable; BBBBB; 2.00
145; abc12; Fruit; DDDDD; 1.00
so if the append button was clicked for the second time it will create dup
145; abc12; Fruit; DDDDD; 1.00

"what would keep a user from returning to an existing Record and clicking on the button?" - nothing keeps the user from doing this. This is not a concern as the user could go back and make updates if needed. I prefer to leave this as is.

'You need to prevent a duplicate Record from being stored, and possibly pop a Messagebox advising the user of this if they attempt to do so" - You are absolutely correct. But the question is what code/task to I need to do to accomplish this?

Thanks again.