How to stop form automatically adding records

[RuralGuy]

Well you are certainly choosing to do this the hard way which is fine but will continually frustrate you at every turn. Have you looked at the Nz() function?

[linuxson]

Hi RG

I've decided to go with your idea for simplifying the whole structure I had going. I split my table into two parts, one with the main purchase order info...the other with the item line details. I have however hit another wall...created a form that launches when I want to add new line items. Problem I am experiencing is that when I create new line items, it doesn't associate the line item records with the primary key I am using in my purchase order table, which is the PO number. Also, when I run the find function inside the Main form (Tab - Orders) and I want to add new line items, I cant get the Add Items popup window to go to a new record, as it only allows me to go to the very last line item which is associated with the primary key in POrders. Any idea how I can solve this? I basically just want the Add Items popup form to add new items when its a new purchase order, or modify and save existing ones and also be able to add New records to currently existing ones

PS
I attached another copy of the db

[RuralGuy]

You should really just forget Lookup Field altogether. They only cause grief. Everything you need to accomplish can be done without them. The PO_Items table should have the PrimaryKey field of the POrder table in a field and not a Lookup Field. I would strongly recommend you use an AutoNumber field as the PK of your table and not a text PO Number field. You can still have the text PO Number field and you can make it a uniquely indexed field if you want to eliminate duplicates. A Form/SubForm SubFormControl has LinkMaster/ChildFields properties that have several functions. One of those functions is to add the PK from the LinkMaster field to the ChildField field of the SubForm. It also filters the SubForm on this same field. Give it a shot. Access will do most of the work for you behind the scenes.

[linuxson]

Thanx for all the help RG, think I am gonna redo everything anyway and apply what I have learnt so far. Will keep you posted, and also upload the finished product once I am done with it

[RuralGuy]

Pretty good idea. Thanks for the update.

[linuxson]

Hi

Ok....this is what I got so far. It took my a while to iron out most of the kinks, but it's coming along famously. One or two minor things...on the Orders tab, there is an annoying "shadow" on the right of the POItems report which shows the list items. It wasn't always there, but just started to do this recently, not sure why. Also, when I try and print a Purchase Order report, it splits all records and places them on separate pages. Also, it prints every single record. What am I doing wrong here? All I want basically is just all items which are associated with the current Purchase Order to display on one page, and for the Purchase Report to only print the current purchase order, not ALL of them.

[RuralGuy]

I'll take some time and look it over.

[linuxson]

I'll take some time and look it over.

Thanx!!

[linuxson]

Hi RG

Think I nailed it....had a look again at my report, scrapped it, and started a fresh with the Record Wizzard. Got it sorted now, only matter left is figuring out how to print only one record (PO_Number) at a time. Will edit my master copy at work tomorrow and keep you updated

thanx again for all the help!!

[RuralGuy]

You're certainly welcome. It was enjoyable to watch you learn. Maybe this link can help with your "last" issue: http://access.mvps.org/access/reports/rpt0002.htm

[linuxson]

Hi RG

Okay....so I re-did the Purchase Order report, and also had a look at that link you shared in connection with single-report print-outs. Haven't been able to sort that out yet, but I managed to finish my Quotes report as well. Could you maybe take a look and see what I am doing wrong with regards to the whole single record print-out mishap. Also, having a spot of trouble calculating totals on both my Purchase Order and Quote documents. Not too sure how to address this

[linuxson]

Ok...got the single-record-print thing down, just modified some of the code that I found at this link: http://www.techrepublic.com/article/...-in-form-view/
It is as follows:



Private Sub PrintForm_POrders_Click()


Private Sub cmdPrint_Click()
'Print current record
'using PurchaseOrder.
If IsNull(Me!ID) Then
MsgBox "Please select a valid record", _
vbOKOnly, "Error"
Exit Sub
End If
DoCmd.OpenReport "PurchaseOrder", acPreview, , _
"ID = " & Me!ID


End Sub

This is also after I included ID fields in both the POrder form and the Report, which are hidden. Works like a charm now!! Just need to sort out the totals thing

[RuralGuy]

Does this get you a little further along with the totals?

[linuxson]

Thanx!! That is exactly what I needed! Going to finish up the rest of my reports, and fine-tune a couple of other things. Will hopefully have a finished product before the end of this week
Thanx again bud, appreciate it!!

[linuxson]

Hey RG

Well, it is done....FINALLY!! Now I can start and populate both my purchase order and quote tables. I have attached an empty copy of the access db, just to say thank you. Hopefully someone out there finds anything we have discussed so far in this thread helpful, as it surely helped me alot! I will check back every now and then, maybe to upload newer versions of the db when I decide to spice it up a bit

Take care