Keeping info in form fields

[gcomp]

I will try my best to give as much information as possible in this post. I have a purchase order form. The form allows the user to input one item into the purchase order. This becomes one record. At times there are several items that need to go into one purchase order. At these times the steps are repeated for each item and these become new records. I would like to put some code behind some buttons to help automate this process. One button would be for "new item". This button would keep information in the following fields: [PurchaseOrderNumber], [VendorName] and [ShipToName] so they would not need to be re-entered. The second button would be for "new purchase order". The only automated process for the code behind this button would be to increment the purchase order number by one from the last record.

I hope this is enough info. One warning, I know Access pretty well, but I'm kind of new to VBA.

 

[John Big Booty]

Have you thought about using a Form/Sub-Form set up for your purchase orders?

In this way the main form would hold all the info that is common to the order, i.e. client name, PO#, date, ship to add. the Sub-form would then hold your order details i.e. product ordered quantity ordered.

IF the form sub-form set is configured correctly there should be no need for additional coding.

 

[gcomp]

Thank you John!!! The subform idea works great. That solves 95% of my problem. Now...how do I increment the PO number. I just want to add 1 to the PO number in the last record and have it appear in the appropriate form field.

 

[John Big Booty]

Have a look at the Dmax() function +1

I've posted a couple of sample demonstrating this so will try and find a link for you.

 

[John Big Booty]

Have a look at the sample here.

 

[gcomp]

Still not sure how I should use Dmax. My table name is POData and the field is PONumber. I want the default value to be the maximum number in the PONumber field +1.

 

[John Big Booty]

Try:

DMax("PONumber", "POData") + 1

Just as an aside, consider implementing a naming protocol for your DB objects, so that it is easier to identify what they are, for example; TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName. Avoid spaces and other special characters, limit yourself to alpha numeric charters and the underscore (_). It then becomes clear what what in code, for example the above would look like;

DMax("PONumber", "TBL_POData") + 1

 

[Dairy Farmer]

You may also look at dulicate record.

    DoCmd.GoToRecord , , acLast
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
    DoCmd.GoToControl "PONumber "
  
    'Here is a simple +1. Use dmax for a better solution.
    PONumber = PONumber + 1
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70