Disallow new record when order outstanding

[Kereng]

Hi,

I'm currently creating a database for product orders (for us to use to place an order to buy something from a supplier). As part of this, there is a data entry form for the shift supervisor to complete to create an order. However, we have a situation where one shift leader may not realise that the previous shift leader has already created an order for the same part number, which hasn't yet been received. I want to be able to give a warning message to highlight there is already an outstanding order for the part and therefore not let them save the record to create the order (or possibly to give them the option to proceed after the warning).

I have an 'Outstanding Order' query sorted by part number, but am not sure how to link this to the data entry form.

Anyone have a suggestion on how to do this?

 

[Uncle Gizmo]

The normal way to prevent an extra entry being added is to use an index which prevents duplicate values. However in some situations, and this is one of them, normally you would have a set of unique values but occasionally you want a duplicate value. In other words it’s not possible to use an index to prevent duplicates.

The way round this problem is to use a query that counts the number of records that meet your criteria, if there are zero row records, then add the new order, if there is one or more records then notify the user and allow them to make a decision whether to continue or not.

If that’s sounds about right then post the SQL of the query that would check to see if an order already exists. From this I should be able to show you how to count the number.

 

[Kereng]

Thanks for the reply. I'm very new to Access, but the code I have for the 'outstanding orders' query is below. Probably more complicated than I need it be, but hopefully it makes sense.

SELECT [Product Codes and Descriptions].[Product Code], [Product Codes and Descriptions].Description, Sum([Plate Orders].[Qty Sets Ordered]) AS [Qty Sets Ordered], Sum([Plate Receipts].[Qty Sets Received]) AS [Qty Sets Received], Sum(((NZ([Plate Orders].[Qty Sets Ordered])-(NZ([Plate Receipts].[Qty Sets Received]))))) AS [Outstanding Order Amount]
FROM ([Product Codes and Descriptions] LEFT JOIN [Plate Orders] ON [Product Codes and Descriptions].[Product Code] = [Plate Orders].[Product Code]) LEFT JOIN [Plate Receipts] ON [Product Codes and Descriptions].[Product Code] = [Plate Receipts].[Product Code]
GROUP BY [Product Codes and Descriptions].[Product Code], [Product Codes and Descriptions].Description
HAVING (((Sum(((NZ([Plate Orders].[Qty Sets Ordered])-(NZ([Plate Receipts].[Qty Sets Received]))))))>0));

 

[Uncle Gizmo]

Hi,

I'm currently creating a database for product orders (for us to use to place an order to buy something from a supplier). As part of this, there is a data entry form for the shift supervisor to complete to create an order. However, we have a situation where one shift leader may not realise that the previous shift leader has already created an order for the same part number, which hasn't yet been received. I want to be able to give a warning message to highlight there is already an outstanding order for the part and therefore not let them save the record to create the order (or possibly to give them the option to proceed after the warning).

I have an 'Outstanding Order' query sorted by part number, but am not sure how to link this to the data entry form.

Anyone have a suggestion on how to do this?

I'm expecting to see a simple query something like:
SELECT fldPartNumber FROM tblProductOrders WHERE fldPartNumber = PartToOrder

Where tblProductOrders is the table that stores the orders

Where fldPartNumber is the field that may contain 0 (Zero) matches to the part number, 1 match or more than one match.

Where PartToOrder is the part number of the part that the on duty shift leader wants to check has not been ordered by a previous shift leader.

I suspect you should have a method to indicate the part has been received, possibly a boolean (Check box) field which you can use to exclude processed parts. You will need to write the query criteria accordingly.

 

[Kereng]

To hopefully clarify, the 'part number' in this case is actually the 'product code' referred to in my query.

We don't generate an order number when we create an order, as we have a general standing order with the supplier. We will receipt in the quantity of plates received for the product code. That's possibly why my 'outstanding orders' query is more complicated than you expected - to get the 'outstanding qty', I have to subtract the 'sum of receipted' from the 'total ordered' for each product code.

 

[Uncle Gizmo]

So you already have a query that tells you if there are items already on order, you just need some way to indicate this at the time someone tries to order another item?

If that’s the case, can you modify this query so that it only returns a single field displaying a count of the number of items already on order? If you can please post this query.

 

[Kereng]

So you already have a query that tells you if there are items already on order, you just need some way to indicate this at the time someone tries to order another item?

Correct

If that’s the case, can you modify this query so that it only returns a single field displaying a count of the number of items already on order? If you can please post this query.

Not sure what you mean by 'count'. I have the query set up so that it performs a calculation like 'sum of plates ordered' - 'sum of plates received' and grouped by product code.

What I guess I need a way of doing is that when they click 'submit' on the new order data entry form, something runs to check if the product code on the data entry form exists in the 'outstanding order' query with a number > 0 (I have the criteria in the query set up so that it shows those above 0 only). I have no idea how to do this though.

 

[Uncle Gizmo]

I have the query set up so that it performs a calculation like 'sum of plates ordered' - 'sum of plates received' and grouped by product code.

This query “sum of plates ordered” would also return the product code if you were to adapt it slightly.

You could also add criteria to make it return the sum of a particular 'product code' that has been ordered.

If you can prepare that query and post it here, then I should be able to show you how to adapt it to flag a message if there is already a product on order.

 

[Kereng]

I already have it returning the product code, which is how I then group it. I assume you mean return it in a different way, but I'm not sure what you mean exactly.

I know how to add criteria to return the sum of a particular product code, i.e. by typing the specific product code in the 'criteria' row. However, there are around 200 different product codes, any of which the guys might need to order at any time. Do you mean to do something different?

Apologies for my lack of clarity, but I'm pretty much teaching myself Access.

Thanks.

 

[vbaInet]

I'm currently creating a database for product orders (for us to use to place an order to buy something from a supplier). As part of this, there is a data entry form for the shift supervisor to complete to create an order. However, we have a situation where one shift leader may not realise that the previous shift leader has already created an order for the same part number, which hasn't yet been received. I want to be able to give a warning message to highlight there is already an outstanding order for the part and therefore not let them save the record to create the order (or possibly to give them the option to proceed after the warning).

I haven't read the other posts so my apologies for that. I'm sure there's some great advice and some great chats in between

Here's what I would imagine you want to do as per your OP:

If DCount("[Field]", "TableOrQuery", "PartID = " & Me.OrderID) <> 0 Then
    If Msgbox("This part has already been ordered." & vbNewline & vbNewLine & "Proceed?", vbExclamation + vbYesNo, "Part already ordered") = vbNo Then
        Cancel = True
    End If
End If

You will put this sort of code in the Before Update event of the form.

 

[Kereng]

Here's what I would imagine you want to do as per your OP:

If DCount("[Field]", "TableOrQuery", "PartID = " & Me.OrderID) <> 0 Then
    If Msgbox("This part has already been ordered." & vbNewline & vbNewLine & "Proceed?", vbExclamation + vbYesNo, "Part already ordered") = vbNo Then
        Cancel = True
    End If
End If

You will put this sort of code in the Before Update event of the form.

Thanks for this. I'm just trying to understand what specific details I need to replace though. I'm guessing that [Field] would be the field from my form that I want to check against (in this case 'Product Code') and the "TableorQuery" would be the name of the Query I use to detect outstanding orders. However, what would "PartID" relate to, would that be the corresponding field name in the Query? (which is also 'Product Code')

I don't know what the 'OrderID' in 'Me.OrderID' should be replaced by?

 

[vbaInet]

Thanks for this. I'm just trying to understand what specific details I need to replace though. I'm guessing that [Field] would be the field from my form that I want to check against (in this case 'Product Code') and the "TableorQuery" would be the name of the Query I use to detect outstanding orders.

Correct and correct! However, if your query already counts and all you want to do is lookup the corresponding value, then you need a DLookup() and not a DCount().

However, what would "PartID" relate to, would that be the corresponding field name in the Query? (which is also 'Product Code')

I don't know what the 'OrderID' in 'Me.OrderID' should be replaced by?

That should have been

"PartID = " & Me.PartID

where Me.PartID refers to the PartID textbox on the form, and PartID inside the quotes refers to the PartID field in the query.

 

[Kereng]

Correct and correct! However, if your query already counts and all you want to do is lookup the corresponding value, then you need a DLookup() and not a DCount().

That should have been

"PartID = " & Me.PartID

where Me.PartID refers to the PartID textbox on the form, and PartID inside the quotes refers to the PartID field in the query.

My query is a calculation and I have the criteria set that it only includes any values where the resulting calculation is >0 (to give me outstanding orders). The query is grouped so that it only returns one line per product code. Does this mean I need DLookup instead?

I'm getting an error message saying 'expected list separator or )', highlighting the word 'code' in Me.Product Code Is that because I have a space in there? If so, how do I get around that?

 

[vbaInet]

My query is a calculation and I have the criteria set that it only includes any values where the resulting calculation is >0 (to give me outstanding orders). The query is grouped so that it only returns one line per product code. Does this mean I need DLookup instead?

Correct.

I'm getting an error message saying 'expected list separator or )', highlighting the word 'code' in Me.Product Code Is that because I have a space in there?

and correct. You're on the ball!

If there's a space in a field name, which you should avoid, you need to enclose it in square brackets like this [Product Code]. Change the name of the textbox bound to Product Code to txtProductCode. Here's the amended code:

If [COLOR=Blue]Nz([/COLOR]DLookup("[Field]", "Query", "[COLOR=Red][[/COLOR]Product Code[COLOR=Red]][/COLOR] = " & Me.[COLOR=Red]txtProductCode[/COLOR])[COLOR=Blue], 0)[/COLOR] <> 0 Then
    If Msgbox("This part has already been ordered." & vbNewline & vbNewLine & "Proceed?", vbExclamation + vbYesNo, "Part already ordered") = vbNo Then
        Cancel = True
    End If
End If

Notice the bit I added in blue? If a product code isn't found in the query then Null is returned, so we use Nz() to trap Null and give it an alternative value, which in your case 0 is sufficient.

Here are two sites on naming conventions and characters you should avoid:

http://www.acc-technology.com/namconv.htm
http://www.mvps.org/access/general/gen0012.htm

 

[Kereng]

Thanks. I'm now getting 'runtime error 438. Object doesn't support this property or method' and it's highlighting the whole 'DLookup' line.

I changed it to txtProductCode for the 'Name' of the textbox label in the 'other' tab on the property sheet. Is that correct? I obviously need to keep the label itself showing as Product Code

 

[vbaInet]

Change the Name property of the textbox, not the name of the label attached to the textbox.

A control can be bound to a field and can be attached to a label. Different terminologies to be aware of.

 

[Kereng]

Yeah, I realised that as soon as I posted, typically. I've now changed it and as soon as I type in the product code I am getting:

Run-time error '2471'

The expression you entered as a query parameter produced this error: 'BLFT12345'

(where BLFT12345 is the product code I have entered in the textbox on the form)

 

[vbaInet]

If the data type of Product Code is Text, this is how the IF line should look like:

If Nz(DLookup("[Field]", "Query", "[Product Code] = [COLOR=Red]'[/COLOR]" & Me.txtProductCode & "[COLOR=Red]'[/COLOR]"), 0) <> 0 Then

Notice the single quotes in red

 

[Kereng]

Thank you! That works and gives me 'this part has already been ordered. Proceed?' question fine now. However, if I select 'yes' to proceed, it clears the product code value I have just entered.

In theory, they should never need to proceed, but there may be an instance where, due to someone missing another step to receipt an existing order delivery, they do actually need to proceed. Is there a way around this, or will I just need to make sure they always follow the receipt system?

 

[Kereng]

Actually, what it seems to be doing is giving me the warning message as soon as I tab to the next field after I enter the product code. If I say no and then fill in the rest of the fields and click the 'submit' command button it gives me the warning message again.

However, if when I first enter the product code, I click into the next field, rather than pressing tab, it doesn't give me the warning message.