Multiple orders from 1 form

Question

Hi All,

I have a form which allows users to enter new orders. The form's Data Entry property is set to 'Yes'

Rather than give users direct access to the fields, I have a number of unbound combo boxes and text boxes to receive the data.

When the user clicks the 'Save' button, vba checks the validity of the unbound controls data, then updates the actual fields.

eg

    'Validity checks go here

    [Customer] = ComboCustomer

    [Product ID] = ComboProducts

    [Qty] = TextQty

    [Order received] = TextReceived

    [Invoiced] = "No"

    [Job Status] = "Current"

    [Invoice Number] = TextInvNo

where controls on the left are bound, and those on the right are the unbound ones that the user sees.

This works.

But the issue is that an order is often broken up. The customer orders 5000 items, but wants delivery over 5 months.

Currently, users have to create 5 separate orders, which is tiresome, since much of the information is the same for each order.

So I introduced a combobox containing the numbers 1 thru 10, representing the number of deliveries.

I then added 9 more unbound controls for some fields, making them visible or not, as required by the number of deliveries.

So, for example, ComboCustomer is unchanged, but TextQty is now TextQty1, TextQty2, TextQty3, etc.

The user can now include the multiple delivery information without having to repeat the static info.

I want to run through the code, inserting varified data into the bound controls, then loop back, running thru as many times as there are deliveries, creating as many orders as there are deliveries.

At the moment, this only enters 1 order (the last one) into the orders table, because the code just over-writes the same record.

SO:

I'm looking for a line of code that 'closes off' or 'enters' a record and opens a new one at the end of each loop iteration.

Is this possible?

Thanks in advance - Dave.

Answer 1

Hi John,

I don't know how to post tables to this group. I don't see any 'attach' button

This database is not yet in use. The intention is for it to replace a current Excel system (which we use to track jobs through our factory) to allow multiple users to update a job's progress. There is currently no call for this to interact with billing, which is a separate system.

Here is a copy/paste of the query in SQL:

SELECT [Orders Tbl].[Order ID], [Orders Tbl].[Owner ID], [Orders Tbl].[Product ID], [Orders Tbl].Customer, [Orders Tbl].Qty, [Orders Tbl].[Order received], [Orders Tbl].Due, [Orders Tbl].MYOB, [Orders Tbl].Invoiced, [Orders Tbl].[SMT ready], [Orders Tbl].[SMT working], [Orders Tbl].[SMT progress], [Orders Tbl].[TH ready], [Orders Tbl].[TH working], [Orders Tbl].[TH progress], [Orders Tbl].[FA ready], [Orders Tbl].[FA working], [Orders Tbl].[FA progress], [Orders Tbl].[Tech What], [Orders Tbl].[Tech ready], [Orders Tbl].[Tech working], [Orders Tbl].[Tech progress], [Orders Tbl].[Assembly Notes], [Orders Tbl].[Job Status], [Products Tbl].SMT, [Products Tbl].TH, [Products Tbl].FA, [Products Tbl].Tech

FROM [Products Tbl] INNER JOIN [Orders Tbl] ON [Products Tbl].[Product ID] = [Orders Tbl].[Product ID];

I tried to insert some screen shots of table designs, and the relationships screen, but I couldn't submit the post due to too many characters...

Answer 2

Hmm, I guess I did not understand it well enough but I see you found a solution so all is right in the world!

Answer 3

Thanks for your consideration.

Me.Requery did what I needed.

Regards - Dave

Answer 4

Hi Gina. I had a look at your link and did a little research on normalising a database.

I'm not sure if I explained myself well or not.

As far as I can tell, my database is normalised, with no data repeated in tables. The form I'm working on is based on a query which pulls fields from several tables.

What I was hoping to know is:

Each time I close the data-entry form, then re-open it, I can enter another new record.

Is there a vba way of being able to enter another new record without actually closing then opening of a form?

Regards - Dave.

Answer 5

Actually, I'm going to make a suggestion, what you want to do is normalize your database.  Have a look at...

http://www.access-diva.com/dm12.html

The main part of the Order (which stays the same) should go in the main table, in the examples case tblPrurchaseOrder and the lines of the actual items should go in the *sub*-table, in this case tblPurchaseOrderDetail.  This will not only streamline data entry but use far less to have to *update*.  It also takes care of not storing the same information over and over again which is the exact opposite of what a normalized database does.

If you take this time now to adjust your tables now it will pay for itself in time saved down the road.