How to stop form automatically adding records

[linuxson]

Hi

I am fairly new to Access, and have to decided to make the move after realizing my Excel spreadsheet would soon meet it's full potential. I have created a database linked to a form (which includes a sub-form) which handles all my daily purchase orders. Still busy building it, but have run into a bit of a snag...as soon as I press Tab or Enter to move focus to the next Control, the form automatically adds what I have entered into it so far to the table as a new record. Obviously, when I move on to the sub-form to enter the rest of the information and save it, it tells me that it cannot add duplicate entries into the table unless I enable it. I have a "Save Record" button on the form as well, but so far this is pretty useless. Is there an on-load macro or something I can run which will keep the form from updating the table automatically UNTIL after I have clicked the Save Record button?

[RuralGuy]

On the OTHER tab of the property sheet for the Main Form, change the CYCLE property to Current Record.

[linuxson]

Thanx for the tip RuralGuy, but it didn't work. After filling in the first control, which is the purchase order number, when I press Tab or Enter, it immediately jumps to the Line A Qty control which is the sub-form. When I go check the table, it entered what I had typed in already, which was basically just the purchase order number

[RuralGuy]

What tab order have you set up for the controls on the main Form? Are you aware that when you move the focus from the Main Form to the SubForm that any changes made to the controls on the Main Form will automatically be saved? The same is true of the SubForm. When you move the focus from the SubForm back to the Main Form, and changes to a SubForm record will be saved. Just moving to a different SubForm record will save the changes as well.

[linuxson]

I realized this when I tried entering the line item data first in the sub-form, and then adding all the data in the controls in the main form. Clicking on Save then updates the record. Do it the right way around, and it wants to create duplicate records. I am not sure what you are referring to with "tab order", but I have also tried setting the sub-form's cycle to "Current Page"...this also didn't help. If you want to, I can attach a copy of the database file? I am also not sure if I am actually doing the whole table setup correctly, as I am creating field entries for 5 pages worth of list items, that's almost 45 line items that I need to create about 4-5 fields for each

[RuralGuy]

Attaching the zipped up db would probably speed up the troubleshooting process, if you want to do that. Do a Compact and Repair before zipping.

[linuxson]

Here you go. It's fairly small still, luckily
Hope it helps...any pointers would be much appreciated

[RuralGuy]

In design view, if you place your cursor in the Detail section of the Main Form and right click, you will see the Tab Order of the controls. You have the SubForm set as the second item in the list which is why tabbing or hitting enter from the PO Number control jumps to the SubForm.

[RuralGuy]

You are also going to want to redesign the POrder table and the PO_Items form. Each item should be on a single record and the PO_Items form should be in continuous view mode. You should read up on Normalization a bit to get a better understanding of the concept.

[linuxson]

Placing items on separate records, that not going to mess up my report which I draw up end of the day? I basically need it to look like the attached pdf. I was under the impression placing all line items in one record would also help with looking up for any one purchase order? Placing all the line items on separate records, how would that link up to the original record I am actually trying to keep, which is the purchase order itself?

[RuralGuy]

Each record would have the PONumber in it as a ForeignKey. The SubFormControl can select these records automatically with the LinkMaster/ChildFields properties. It can also put the PONumber in each SubForm record automatically.

[RuralGuy]

I see no barrier to creating the report you have using single records. The method you chose would limit each PO to 8 entries I believe.

[RuralGuy]

Did I lose you or did you solve it?

[linuxson]

Hehehe....nope, still trying to solve it. Just been busy populating the rest of my forms and drawing up a main form with all sub-forms included. I came across a link the other day, which suggested a work-around to the same situation. So for now I am just basically gonna include a macro in either the "On Lost Focus" event, or a Go_TO_Control with a macro which will undo any record additions to the table when moving from the form to the sub-form. I basically just need it to reset the fields in the table so that I can save the record after having included all my line items. Like I mentioned previously, the form works great when I add records by starting in the sub-form first and then moving to the rest of the information. This lets me update the record when I click the Save button....which is weird, but it works for me. Right now I am sitting with a situation where after I have finished adding all the data to my PO, when I print the record it includes all the null values of all the fields that weren't used. (Find attached)
Is there a macro or query I can run which will remove all empty/null fields from my PO Report so that it only prints the relevant information, instead of always printing all 5 pages of the report?

[linuxson]

Another thing I noticed now....I have a HUUUUUUGE expression in my Grand Total field, which basically adds up all the line totals of every line item. It's extremely long, because I need to state that all fields should be calculated, even if the value is null. It took me a while to copy and paste this data in the expression builder, which consisted of the following:

"IIf(IsNull([LineA_Total]);0;[LineA_Total])+....."

The problem with this is that I need to repeat this 26 times! Is there a shorthand I can use which basically means the same thing?
What's happening now is when I close the window, everything calculates just fine...but when I open up the calculated field's expression to edit it, it's truncated the expression up to Line Item F. If I then click okay, it updates the expression to only add up till Field F. Is there a character limit on how much you can add to this expression field? Why would it then let me add all this data and then just conveniently delete it when you try and edit the expression?