Append Query

[integritycare]

Hi Guys,
I'm in the process of designing a Chefs Order system. While its working as required I have been asked to provide the following.
The order form works as per a continuous form using the Order No to link both main and sub forms.
However as they order some 120 items each week, they don't want to have to create an order form with this number of items each week.
I have created an append query from the lists. While this will insert the data into the orders table, it does not associate its self with the order No (Links to main form)to display the order.
Question is, How can I associate each record with the order No?, once I have appended the data in the table.
Thank you
KP

 

[Andrzejek]

Isn't the Order_No field the required field in Orders table?
"I have created an append query from the lists" - could you show your code?
And what's the structure of your table(s)?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[integritycare]

Hi Andy,
Yes the order No is in the Orders table so the Master and Child fields are linked by the Order No. In the relationships both the Order table and the Order Data table are linked by Order No. The Order form consists of the Mainform and a subform (continuous)

If a record is added to the subform there is no issue. So as each record in the subform is added it will link via the Order No.
On the subform I have used combo boxes to select each item, (types of meats, fruit etc) So to do a short order this is the way in which the order will be created.And that works ok.

But the user wants to be able to have all his selected items available on the Order form, without having to individually use the combo box to select them. The only way I could think of to do this is to create a list of items in another table, inwhich the data will remain constant, and then update this table via an append query to the subform order table. The append query works ok as the data is inserted into the Order data table.
Each time an Order is sent to the suppliers it must have a new Order No

Where I am having issues is that the Order no on the Main form needs to be linked to all the items in the order data table.
When the order data table is viewed all the data is there except for the order No.
So when the order is viewed no data is seen on the subform because the Order no is not included in the order data table.

As a work around,I have placed a command button on the suborder form with this code

Public Sub AddOrderNo_Click()
Dim rst As DAO.Recordset
Set rst = Me.Recordset
With rst
   .MoveFirst
   Do
      OrderNo = Nz([OrderNoa])
      .Edit
      .UpDate
      .MoveNext
      
   Loop Until .EOF
End With

End Sub

This will scroll though the order data table and add the current Order No to each record. While this works is it best practise to get around this issue.

Thank you,
KP

 

[Andrzejek]

So what do you have in Order_No field in Orders table before you run the code in your AddOrderNo button?

Also, could you show your INSERT (append) statement?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[integritycare]

Hi Andy,Thanks for your reply.

Andy, I don't want to waste your valuable time so I will ask you a question instead.

At the moment they use an excel spreadsheet which lists many items, this is printed out and emailed to the supplier.

The management has requested that this data be held in a database where the data can be used and managed more effectively for audit and P/L purposes, as well as being able to use it for purchases.

Its a long list. This list remains static apart from adding a few items when required.
What is the best way of generating a report in access to achieve this. If the management was to use the drop down boxes and add each item,that I have provided on the Order form, there would be no issue. But they say it takes to much time to do this.

However they just want an order list without to much of a user input. They just want to open a form, see the list, add an order no, Order date, Supplier address etc, Print it to PDF and email.
They are using Ms access 2010
Is there a way to do this effectively? With the Order form I have already created.
Thank you,
KP

 

[Andrzejek]

If you want to do this in Access, I would strongly suggest to do it as a relational data base. Quiet different than Excel spreadsheet. Some basic information about Fundamentals of Relational Database Design you can find here.
If you read it you will understand why I was asking about your fields and relation between the tables, I was looking for PK - FK (Primary Key - Foreign Key) relations between the tables. Very important.

As far as displaying to the users all order items available, that depends how many of them you have. If there are around 20, I would give them a list to choose from. They can highlight what they want to order.
If you have 100's of items, I would group them in some kind of logical groups. I would display groups in the drop-down combo, and for each selection of a group, I would give them the list to choose from for that group.
And I would display their selection of the order in a grid, items for this order. This way they can choose 5, 10, 50, or 150 items per order, easily find any item, and see whet they are ordering.

With this approach I would even give them a way to create a template(s) of an order, something they may use on regular basis. Let's say they have regular orders that they choose out of 100 items, but most of the time they order 85 out of the 100. I would create a template of 100 items and allow them to remove items they do not want this time around. it is a lot easier to remove 15 items rather than add 85 of them every time.

Hope this helps.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[integritycare]

Thanks Andy.
The data base is a relational database as per the norm. Both tables are related, by the Primary & foreign keys. I had already created templates for them to use and with the option of removing items with a Yes/no option. I have created 5 templates which they can choose from.
I was just not sure if this was the way to do it.
So thank you for your last post. It has just confirmed to me that this was the way.

Have a great day,
KP