Deleting records that have been checked or adding a record after a checked record

[sponge]

Hi,

I was wondering how to delete records that have been checked (through a checkbox) in a form...

Also, I want to add a record after the record that has been checked. I only want this ability to add records available if only one record is checked. Otherwise, if more than one record is checked, the "Add Record" button would be disabled.

Any help would be much appreciated.

TIA.

 

[FoFa]

A yes/no field can be checked with [FieldName] = true or [FieldName] = false
true = checked, false = not checked.
DCOUNT("FieldName","TableName","FieldName = TRUE" AND Key = something) will return a count of all records meeting the criteria, so if it returns a 1 (one) you can enable your button, else disable it.

 

[Pauldohert]

Would a yes no field to a table for purposes of deleting records not be a dangerous thing to do - ie other uses could run a delete or add before the person who checked the record.

One could be checked for add that another person then deletes!

The add record check yes would habve to be set back to no etc etc

( I realise the point of the original question may be for one user to action the checking of another, so nullifying my concerns somewhat)

Can the checkbox be used in this way unbound?

I would use a use a listbox to let the user choose records to delete/add - but would lose the sort, filter etc builtin capabilities of a form.

Is there a solution for all the above? To let user chiise record in form view with a checkbox , so enabling sort and filter so the user can find the record they want.

Sorry if thats a really dumb question and I am overlooking the obvious but its been a long day.

Thanks

 

[RV]

Would a yes no field to a table for purposes of deleting records not be a dangerous thing to do - ie other uses could run a delete or add before the person who checked the record.

Pauldohert,

I don't understand the point you're trying to make.
One one hand you're asking for a feature to delete records, on the other hand you don't want this feature.

Or am I missing your point (which doesn't make sense to me anyway to be frankly honest )

RV

 

[Pauldohert]

It makes sense to me!

I want to delete what the user selects (hopefully via a checkbox on a form because this gives the sort filter, find options) but I do not want to save data on which records are to be deleted so other users can access it accidentily.

I just wanted to know if there was a way of using the checkbox unbound, so it applies in the same way as selecting rows/records in a listbox.

 

[Pat Hartman]

Pauldohert,
There is no way to use unbound checkboxes for this purpose since unbound controls will show the same value for all rows in the recordset. Because they are unbound, there is no place to store individual values. The form only maintains one instance of its properties no matter how many instances are visible.

sponge,
You need to change the way you think about your data. Relational tables are very different from spreadsheets and their basic definition is an UNORDERED set. No record depends on another record for its position. Your question implies some order you think you want to maintain with the data. In a relational table, all records are added to the end of the recordset. They are NEVER added between two records. If you have a primary key defined, the table will "appear" to maintain a particular order but that is only because the table is viewed via a query that is built behind the scenes that actually sorts the data when you open the table in datasheet view.

 

[ScottGem]

As usual Pat has some very good points to make. I just wanted to add one thing here. Its rare that records should be physically deleted from a database. In most instances the preferred method is to simply mark the records as inactive then use filters to hide those records.

 

[FoFa]

I do this exact thing in one application, it is required. But I have the Yes/No field on the table itself, and run a delete query when they close the form. It deletes all the records marked (checked), the default of course is unchecked at the table level. ScottGem is correct ini most applications are better served using an inactive flag instead, but like I said, I do have an application where it is required. But those a child records from the parent in my case.

 

[Pauldohert]

Thanks everyone.

So is a listbox is the only way to select multiple records without having to write to a table?

 

[ScottGem]

A list box is the only CONTROL that allows you to select multiple Items.

 

[Pat Hartman]

In a continuous form you can select multiple adjacent records by using the shift key while you click. The cntl key doesn't work to allow selection of non-adjacent records.