Allow Additions New Problem

[Sameal]

This is in regards to the last thread marked "Allow Additions". I have come across an interresting phenomena now that I can't really discern how it is working.

I set the form to allow additions but I set the tab cycle property to same record, and also removed all navigation, and record selectors. This all worked great using Philly44's idea of using DELETE sql to clear the table before INSERT INTO the data that is kept in the two text boxes on the form.

Now comes the strange occurance. If I load the form and change both text boxes to something and click DONE (the button to run my two SQL lines.) The code works perfectly, first deleting the table, then adding in the values from the text boxes. This effectively keeps the table at only one record which is our current record.

Now if I come back into the form, see that the values in there are correct and choose not to edit any of them and simply click Done. The first sql is ran, deleting all entries from the table, then the INSERT INTO runs and inserts blank data into the fields. I am curious to why under the first scenario the DELETE sql didn't seem to delete what was in the text boxes, but under the second scenario it clears the table and the text boxes as well.

If you have any suggestions or input on this problem I'd appreciate it. If you don't understand what I'm talking about try reading the short thread from earlier today labeled "Allow Additions". I would like to code some kind of fix so that the end users would not be able to cause this kind of bug.

 

[gol4]

I suggest you place some sort of error checking into the code
something like
if me.textbox ="" then
exit sub
else
DoCmd.RunSQL("DELETE * FROM tblDate;&quot
DoCmd.RunSQL("INSERT INTO tblDate (DateField) VALUES
(Forms!frmName!TextBox);&quot
end if

but I question why if you are allowing it to cycle thru only the one record why not just set the forms source to the table then you will be editing the record directly.

 

[Sameal]

I found a way to fix it, set the values from the controls into two variables before running the delete query. I then used the variables for the insert into query instead of the controls. Seems to work well enough.

 

[JoeMiller]

Question:

Why are you deleting the table and then inserting again? Why not just run an Update query?
Joe Miller
joe.miller@flotech.net

 

[Sameal]

Originally I didn't do that because you could tab through to add new entries into the table. But now I have found the Tab Cycle property so I may change it to a single RunSQL command. Thanks for the heads up.