#Deleted on Form

[Terryvanduzee]

Hello

I have a form where i have a checkbox. When the checkbox is clicked, it checks to see if the value is true (checked). If it is, it takes the current record in the form, prompts for some information (input boxes) and writes the values to a table via sql insert. Then deletes the record from the form.
The form data displayed is based on values selected from 2 combo boxes. The records set could have more than 1 record. The checkboxes are for "Sold". Which means the item was sold. Probem is is that after deleting the first item (clicking the checkbox) if I try to delete a second item; or click anywhere in the form, I get an access box come up and say "Record is deleted". Is there any way to suppress this? I have tried turning off warnings, but it does not work.

Thank you
Terry

[topher23]

It looks like you may need to requery the recordset with Me.Requery after running your SQL and deleting the current record in order to repopulate with the current data. That should take care of your issue.

[Terryvanduzee]

Thank you for your response
I did try the requery, but I get an access box saying I must save the field before performing the requery action.

Any ideas about that?

Thank you
Terry

[topher23]

Is the checkbox you click that uses SQL to re-write to another table then deletes the record a bound control? If it is, you should insert

DoCmd.RunCommand acCmdSaveRecord

before running the SQL and deleting, otherwise it may cause errors like you've pointed out when you try to requery.

Also, I don't understand the point of deleting from one table and copying to another. What is the reasoning behind that?

[NeoPa]

Terry,

I think you need to review the logic of your form. Having a bound CheckBox control (for you surely do) on a record that you delete depending on it being checked, makes very little logical sense.

I feel sure that if you consider what you really want from the form and then implement that, you will avoid this problem entirely. There are other ways of getting past this, but it would be unwise to avoid the issue (as would be the effect of using other remedies) as it really doesn't make any sense to have it as you do now. Working with databases (and code generally) can only be done when everything is built logically. Anything else is a recipe for many problems.

[OldBirdman]

Using a CheckBox (Bound OR UNBOUND) where a CommandButton is called for very often results in problems exactly as described. This is not the first thread on this forum to address this problem.

If the CheckBox is Bound, then the field in the table will always be True, because the records are only saved when the CheckBox is True. If the record is edited in a form later, the CheckBox will be True when the record is loaded, and has to be unchecked, then checked to save any changes.

If the CheckBox is UnBound, it can be used as a CommandButton, but must be cleared as part of the save operation. This is not a normal Windows "Look and Feel", and may be confusing to the user. Confusion leads to errors.

[Terryvanduzee]

Thank you for the comments and solution.

The reason behind the checkbox is as follows.
A person opens the form and selects criteria. When the recordset is displayed, it is possible that the item(s) in the result could be sold. To sell the item, the checkbox is clicked. At this point it prompts for Customer information via inputbox. It then takes the collected data, including the data from the record on the form that was clicked and writes it to a "Sold" table (tbl_Sold). There is now no use for the record in the tbl_CurrentInv table so, I delete the record.

Thank you
Terry

[topher23]

My point is that this Excel spreadsheet-type thinking. If all of the inventory was in one table called "tblInvento ry," and a checkbox called "chkSold" existed, then a query called "qryCurrentInve ntory" that filters on chkSold = True would give you your Inventory list and a query called "qrySoldIte ms" that filters on chkSold = True would give you your Sold Inventory. Less mess, less hassle, less data manipulation, less probability of errors.

EDIT: Also, if you're a true customer-based store that keeps track of your customers, you'd want a customer table so you could look up the customer's information for each purchase without re-typing data, and a separate table that links customer ID's with the ID's of the products purchased.

[NeoPa]

Another way to put it would be that there is really no need for a field indicating whether an item is sold or not, within a table (or any type of recordset) where only items that are not sold may exist. The logic is fundamentally wrong (It's not FALSE, it's simply tautologous. Tautology is not good in database design - See Normalisation and Table structures), and wrong logic causes problems when you try to build anything on it.

Unless you get the design right, you can expect to keep coming across this and similar problems.

[OldBirdman]

The reason behind the checkbox is as follows.
A person opens the form and selects criteria. When the recordset is displayed, it is possible that the item(s) in the result could be sold. To sell the item, the checkbox is clicked. At this point it prompts for Customer information via inputbox. It then takes the collected data, including the data from the record on the form that was clicked and writes it to a "Sold" table (tbl_Sold). There is now no use for the record in the tbl_CurrentInv table so, I delete the record.

I'm missing something here. Why not a CommandButton? You could do the same thing, but it would NOT LOOK like you were entering data into a field of the database.

[NeoPa]

Because the underlying structure makes little sense OB, the OP is trying to save the data to the record. It's not you who is missing the point here.

[Terryvanduzee]

Well,
After some going through the comments here, I will agree that my design is flawed.
I understand the importance of a customer table.
I am not clear on a different path of logic for the sale though. I display a form/subform. The user selects the criteria in the main form. The subform displays the results. If there are 2 records in the subform, either or both have the possibility of being sold. The reason for the checkbox is because each item in the results, is in a different place (physically) and it is important to show all the specific inventory items so the user can select the item from the place it was on the floor. I do not know how to design the logic for this specific case.

Right now, I am only recording mattress inventory and the sale of mattresses/boxsprings (could be sold separately).
There are 2 main manufacturers; each manufacturer carries several models. Some mattresses come with a designated boxspring (providing it is a set that is sold). Some mattresses do get sold with Boxspring (they are not needed). For Bed displays, wrapped mattresses are put on beds (these are the different places mentioned above -- Called Bays). Each bay is numbered/named.

My responsibility is to maintain an accurate mattress inventory and to record them when sold. If a person buys only a mattress, I need to record the boxspring as being an "odd boxspring". If only a boxspring is sold, I need to record an "odd mattress" (by name).

At the moment this is not a real time sale, it is recorded after the fact.. There is a real possibility of ending up as a real time db.

Any suggestions in it's design would be received with appreciation.

Thank you so much
Terry

[NeoPa]

I think this has already been answered Terry. I'm not sure how happy you are with the answers provided, but simply asking again is unlikely to help.

See post #9 for tips on the design. Until you take these on board, any others are just a waste of time. You cannot build on sand.

Post #6 explains how you could do the interface without the flaw inherent in your current setup.