Purchase Order / booking in form

[Ade F]

sub form visibility defined by check box validation

Here's a doozy for everybody....

I have a sub form which is part of a purchase order. This sub form is also used in part of my >>booking in form. The reason being employees would view the contents of an order to fill in the quantity delivered versus the quantity required. The items are then taken away and placed in a project bin.

The PO sub form (used in the booking in form) is in data view but can be changed to continuous if needed.....

Basically would it be possible for me to either

Add a check box to the purchase order sub form table so that when clicked this makes the particular chosen row i.e product, required and delivered invisible? This would then only leave the items that would have not yet arrived and been booked in.

Or

Have a way of making the particular PO sub row invisible if the required quantity and delivered quantity is the same?

This would make for a lot more efficient booking in process for our employees as people have a tendency to be a little slap happy with filling in delivered levels.

I'm just trying to screw this thing down and eliminate some margin for error to the end user. I will gladly provide more information should anyone have ideas for a solution.

Best Regards

Adrian

 

[Parker]

Have a way of making the particular PO sub row invisible if the required quantity and delivered quantity is the same?

I may be way of base here but why not base this on a query that way you will only show the information you need on this particular form?

 

[Ade F]

I may be way of base here but why not base this on a query that way you will only show the information you need on this particular form?

I wish for the user to directly interact with the sub form and physically click on a click box or fill in the delivered quantity at this point I wish for the visibility to be altered.

I presume running a query would be looking at information that had already been entered into the table.

I may too be way off base but if you could give me an example that would be great.

Ade

 

[Spira]

if you want to check something then does that mean you r going to make that specific record invisible?
if so there is no point beacuse the user wont be able to uncheck it as it would be invisible.
if there are thousands of rows then is it sensible for the user to check all of the rows to make them invisible as they are not neded?

this is where queries come in
select the criteria to use




As parker said that the best way to hide unwanted rows in a record is to use a query and in my knowledge it is the only way!!!

 

[Ade F]

Booking In

if you want to check something then does that mean you r going to make that specific record invisible?
if so there is no point beacuse the user wont be able to uncheck it as it would be invisible.
if there are thousands of rows then is it sensible for the user to check all of the rows to make them invisible as they are not neded?

this is where queries come in
select the criteria to use




As parker said that the best way to hide unwanted rows in a record is to use a query and in my knowledge it is the only way!!!

Sorry but my explanation must have been vague.

I'm using the purchase order sub form in a sub of my booking in page. I would imagine a person booking in items would click on the click box say "these parts are in" and they would become invisible (only when viewing on the booking in form).

Should that same user go to the order history page (another form showing job number and order contents) then the PO contents would be fully viewable even though as part of the table a click box had been flagged up this does should not affect the visibility with the order history form these are two seperate issues.

I'm trying to make it easier for the employee who is booking in not to be looking at items that have already arrived... only the undelivered items remain visible (on the booking in form).

This is why it would be great for me to have the ability for if the items delivered = items required that row dis-appear (only on the booking in form) I'm sure with continuous forms someone out there could present some code to interrogate the delivered text box and cross referrer it with the required to then make certain boxes invisible.

Regards

Ade

 

[Parker]

Ok I may have miss understood you.

This looks to me more like an organisational problem than an Access one.

Are you saying that the person viewing this form is reconciling (ie recieving goods for stock)stock as well as ordering?
and if so is the reconciliation carried out on the first form?

If this is not so, what other methods are you using to show stock recieved?

Are you tracing stock on a global bassis or on behalf of a single account?

The easiest way to solve this problem is to have a method to tell access that you have recieved stock. Then run a query to find stock that has been ordered but not yet recieved.

You can then display the results of this query (presumably in data sheet view) on your form. And your user sees only the outstanding orders.

It is no matter if this is needed on a global bassis or a by customer account bassis it still works the same just the sort criteria will be different.

Your order history form can still show everything and, (unless you specifically require it), need not show a redundant check box.

If you need to switch directly between forms then you could use a command button.

If this is still off base then give more specific information.

Pete

 

[Ade F]

Booking In

Ok I may have miss understood you.

This looks to me more like an organisational problem than an Access one.

Are you saying that the person viewing this form is reconciling (ie recieving goods for stock)stock as well as ordering?
and if so is the reconciliation carried out on the first form?

If this is not so, what other methods are you using to show stock recieved?

Are you tracing stock on a global bassis or on behalf of a single account?

The easiest way to solve this problem is to have a method to tell access that you have recieved stock. Then run a query to find stock that has been ordered but not yet recieved.

You can then display the results of this query (presumably in data sheet view) on your form. And your user sees only the outstanding orders.

It is no matter if this is needed on a global bassis or a by customer account bassis it still works the same just the sort criteria will be different.

Your order history form can still show everything and, (unless you specifically require it), need not show a redundant check box.

If you need to switch directly between forms then you could use a command button.

If this is still off base then give more specific information.

Pete

These items are being booked in on an individual job basis. The original PO sub page table contains

order id, Product ID,required, delivered

The delivered field is not shown in the purchase order but only in the booking in form.

This PO sub form is used on the booking in sub form, the required and product ID are locked so only the delivered quantity can be input. I have not put the check box in yet as I dont know if this is possible.

See this image for more of an idea

http://www.norwichlife.co.uk/booking_in.jpg

I have to go out now but will write more should you require it.

Regards

Ade

 

[Parker]

Ok so you are entering the amount of stock items delivered.

How are you working the balance? Is it run as a calculation, a query or manual input on this form?

If you run this as a query then you can use that to display the information you require.

Run a query against the underlying rs to find the amount of goods outstanding.

Display these results as a data sheet on your form.

Using the "on update" function behind the text control for stock delivered, too refresh the query.

This way you will only show outstanding items and if you enter an amount delivered against an item the query will refresh displaying only goods not delivered.

Thus if you have ordered 5 widgets and only 3 have been delivered then the record for that item will still show 2 on order. But if 5 widgets have been delivered then the item will disappear from this list as it is no longer outstanding.

I am assuming that the control for items ordered is getting it's content from somewhere and that this is not entered manually on this same form as well.

 

[neileg]

I agree with Parker. You need to use a query and refresh.

Can I make some points about your business process?

Do you insist that quantity delivered exactly matches the quantity ordered? Is there a tolerance on under and over deliveries? Do you want to allow users to mark an order line complete where the order and delivery quantities don't match? Suppose you order 140 metres of a product, but the supplier only holds 50 metre rolls and so delivers 150 metres and you want that to be OK.

How are you dealing with part deliveries? If you order 10 units and 5 are delivered, will the user have to update this 5 to 8 when the next 3 arrive? What if different users accept deliveries, how will you know who received what? Where is the audit trail on deliveries?

As you might deduce, I don't think it is acceptable to hold the deliveries as a single field on the order line. You need to hold the data on deliveries as separate records so that you know who recieved what and when. You also need to decide how to deal with delivery quantities that don't match the order. You may want to force users to revise the order so that exact matches are ensured.

This is a non-trivial subject!

 

[Ade F]

I agree with Parker. You need to use a query and refresh.

Can I make some points about your business process?

Do you insist that quantity delivered exactly matches the quantity ordered? Is there a tolerance on under and over deliveries? Do you want to allow users to mark an order line complete where the order and delivery quantities don't match? Suppose you order 140 metres of a product, but the supplier only holds 50 metre rolls and so delivers 150 metres and you want that to be OK.

How are you dealing with part deliveries? If you order 10 units and 5 are delivered, will the user have to update this 5 to 8 when the next 3 arrive? What if different users accept deliveries, how will you know who received what? Where is the audit trail on deliveries?

As you might deduce, I don't think it is acceptable to hold the deliveries as a single field on the order line. You need to hold the data on deliveries as separate records so that you know who recieved what and when. You also need to decide how to deal with delivery quantities that don't match the order. You may want to force users to revise the order so that exact matches are ensured.

This is a non-trivial subject!

So many questions but happy to answer......

The "quantity to come" is arrived at by just taking the required by the delivered with a simple control source equation.

The top half of the form records who, when and what is to come on the order in a separate table. The bottom part of the screen shows what orders have been previously booked in on this order using the for mentioned table and the middle is what has been ordered form the PO sub table.

Yes I know that having the memo field to record what has not arrived is a little strange but unless I record every order id against every product ordered I cant think of how to do this effectively as a single item can contain many deliveries.

I fully appreciate that this complex nature and therefore with to devise an effective route before putting it live; this is still in progress so any solid suggestions would be appreciated. Please see my ammended graphic above to show the tables.

So to re-cap I have

PO form date ordered etc

Order details table Order ID product, received, quantity, delivered

Booking In table Order ID, name of employee, delivery note number

Regards

I like parkers idea of making the fully booked items to disappear but don’t really know how to fully implement this so some help would be good.

Ade

 

[neileg]

Ah, right, sounds like your structure is much better than I feared.

You need to record the number of units received in the booking in table. You can use a totals query to sum the records by order number and prodct. Left join this to your orders table and you will have the data on quantity ordered and received, and you can do a calculation in the query to give you the balance outstanding.

In the query detailed above, if you add a criterion on the outstanding balance to exclude orders with no balance, the completed lines will not be returned in the query.

So you can use the same query design twice. Once in your orders details subform, without the criterion, to show all the order lines, with quantity delivered and balance. When you complete the booking in entry, requery the order details subform and it will show the new balances. Secondly, as part of the data for your booking in table, with the >0 criterion to ensure that the goods are not being booked into a line that is fully delivered.

 

[Ade F]

Ah, right, sounds like your structure is much better than I feared.

You need to record the number of units received in the booking in table. You can use a totals query to sum the records by order number and prodct. Left join this to your orders table and you will have the data on quantity ordered and received, and you can do a calculation in the query to give you the balance outstanding.

In the query detailed above, if you add a criterion on the outstanding balance to exclude orders with no balance, the completed lines will not be returned in the query.

So you can use the same query design twice. Once in your orders details subform, without the criterion, to show all the order lines, with quantity delivered and balance. When you complete the booking in entry, requery the order details subform and it will show the new balances. Secondly, as part of the data for your booking in table, with the >0 criterion to ensure that the goods are not being booked into a line that is fully delivered.

The penny has dropped neileg.... I have gone the query route and the bookee having filled in the delivered quantity activates the query with a refresh. This then only shows the items that have not yet arrived.

>>My final question is an append query related one.

Basically I would like to insert a 0 into every record in the delivered table as at the moment it has a null value (I added the delivered field having already started taking order info).

I wondered if you could advise on how this would be done effectively I would have to be insane not to use an append query to acheive this. Only thing is I’m not hot with criteria and queries etc some help would be great.

Cheers everybody who has helped

Ade

 

[neileg]

Basically I would like to insert a 0 into every record in the delivered table as at the moment it has a null value (I added the delivered field having already started taking order info).

I wondered if you could advise on how this would be done effectively I would have to be insane not to use an append query to acheive this. Only thing is I’m not hot with criteria and queries etc some help would be great.

Easier than that. Make zero the default value in the table.

Happy to have helped.

 

[Ade F]

Easier than that. Make zero the default value in the table.

Happy to have helped.

Yeh I did that but previously unformatted records in the table do not get zero as these are assigned only when a new OrderID is created.

This is why I needed the append mehtod I have got as far as using the nz command to put zero in all delivered table entries that have a null value but when I go to actually run the append query it tell me that it has broken certain violations..... I cannot get past this point..

Shucks.

Ade

 

[neileg]

Append? Don't you mean Update?

 

[Ade F]

Finding nulls and replacing them with zero's

Append? Don't you mean Update?

I know you have helped out a lot neileg but what how would you go about this I have the following table structure.

Order Details

OrderID From PO Table
Product
Required
Delivered

How would you go about finding the null values in the delivered field and changing any nulls to zero. I have been tinkering for ages with the and cant get it to update the blooming table with zero's.

Cheers

Ade

 

[neileg]

Here's the SQL

UPDATE tblOrderDetails SET tblOrderDetails.Delivered = 0
WHERE (((tblOrderDetails.Delivered) Is Null));

If you're not happy with SQL, create a new query and add your Order Details table. Add the fields to the query grid. Click the Query Type button and change this to Update. This will insert the 'Update to' line. In the column for Delivered, put 0 in the update line. In the criterion line for Delivered put Is Null. Run the query.

 

[Ade F]

Like a charm

Here's the SQL

UPDATE tblOrderDetails SET tblOrderDetails.Delivered = 0
WHERE (((tblOrderDetails.Delivered) Is Null));

If you're not happy with SQL, create a new query and add your Order Details table. Add the fields to the query grid. Click the Query Type button and change this to Update. This will insert the 'Update to' line. In the column for Delivered, put 0 in the update line. In the criterion line for Delivered put Is Null. Run the query.

Brill! One more step down the line to a solid booking in system.

Cheers Mate

Ade