2 queries in one form (4 Viewers)

[TMK]

i have a order form with a sub form orderdtl, we got the option to change the status of the order from processing to shipping, only if the desired items in the order are all in stock or in other words if the quantity is less than or equal to the available stock, but for this to happen i hav to combine 2 queries one called stock which has all the stock on hand details which is calculated in the query and other query is the orderdtl query where the item total is calculated with unitprice and quantity.

i have tried two ways:

i made just one query with only orderdtl query, and added two text boxes to dlookup the stock and if funtion to see if the quantity is available and with tht data i count all items in the order and also count no of instock items in tht order, and staus can only change if total count of items equals to instock item. for ths to happen i need to send the dlookup and if function data to a table field to be stored inother to be proccessed for counting, but it does not work becuse the event only occurs in the current selected record of the continous form orderdtl.

second option i tried was combine 2 queriess the orderdtl and stock ones so that i get the needed data already stored in a table and check to order form but then when the queries combine i cannot add a new field to the continous form orderdtl which has the recordsocuce of the 2 queies

 

[TMK]

hi guys maybe if there is any way i could count the number of items. In the order using a text field with control source "=count(*)" i ge the full amount of items, but to get the other one is the problem im having, tht is the count of how many items are in stock form the other. i cant count it as the field used to calculate field temporay as is not stored in a table so it send #Error msg instead, if i could get tht count, i can make a another equation to allow change of status to shipped if total count = stock present count only.

 

[plog]

Words aren't going to work to explain this. We either need a sample database and precise instructions as to what you need where. Or we can just convert this to a data problem and you can provide sample data to demonstrate your issue.

 

[The_Doc_Man]

Your question is difficult to answer without knowing something of the structure of the relevant tables. There is also the issue of how you are keeping track of the inventory, for which there are two popular methods - updating a master record, or storing all transactions and doing a summation of the transaction amounts.

To get best results, we would also need to know a little bit about relationships among the tables - if any are defined.

 

[Mark_]

Before giving any theory advice, how quickly does your stock change? Do you normally have plenty to sell during the day and still keep stock for the next day? Do you sell out by the end of the day? Do you sell out during the day and have to stop selling that item?

Depending on how quickly your stock amount changes, you can either show "Quantity on hand" when the order is started and expect it will be ready to go when you complete your order OR you will have to check and allocate stock as you enter detail lines.

This is a design question that you would want to answer before you start building this.

 

[Ken Sheridan]

The following query references a query qryStockInHand which computes the current inventory per product and returns columns ProductID and StockInHand, and an OrderDetails table with columns OrderNumber, ProductID, Quantity, and UnitPrice. The query returns any orders for which there is not current stock in hand to supply all products ordered:

SELECT DISTINCT
    OrderNumber
FROM
    OrderDetails
WHERE
    EXISTS
       (SELECT
            *
        FROM
            qryStockInHand
        WHERE
            qryStockInHand.ProductID = OrderDetails.ProductID
            AND qryStockInHand.StockInHand < OrderDetails.Quantity);

 

[mike60smart]

hi guys maybe if there is any way i could count the number of items. In the order using a text field with control source "=count(*)" i ge the full amount of items, but to get the other one is the problem im having, tht is the count of how many items are in stock form the other. i cant count it as the field used to calculate field temporay as is not stored in a table so it send #Error msg instead, if i could get tht count, i can make a another equation to allow change of status to shipped if total count = stock present count only.

Can you upload a copy of the database?

 

[TMK]

hi guys,
really sry for the late responce, i just got my pc fixed.
so i did find a solution to my problem, which was i inserted another sub from with just only stock related stuff and then had a separate sub form for order dtls that way access allows me to edit order dtl table and keep stock data from changing, finally i just used some vba coding to pull data rom the sub form to main form so i can use it in my calculations

 

[TMK]

I do hav another issue though, so I'm created a stock tbl in away u add stock details like product and quantity and date to a table thru a form which was a single form only.
But now the guys want me to fix it so that it can be bulk import stock problem, So I'm using a continuous forms and now it shows all records and another issue is my stock in id for each record is a number field with a form field that has the code "=DMax("StockIn_ID","StockIn_tbl")+1", and when I use this the first 2 new records always have the same id, btw i cant use autonumbering cuz the guys want continuous serials when a record is deleted, any feedback I could use?

 

[The_Doc_Man]

Tell the folks who want continuous serials to go jump in a lake because they are too dense to understand what they are asking. If they allow deletion of something with an ID field then they are clearly ignorant of the meaning of "Primary Key."

Note that I cleaned that up a bit. But I have no patience with people who ask for silly things. I WILL say that if you had a properly trained auditor involve, that person would take a bullwhip to the person that wanted to change numbering like that.

 

[GPGeorge]

i have a order form with a sub form orderdtl, we got the option to change the status of the order from processing to shipping, only if the desired items in the order are all in stock or in other words if the quantity is less than or equal to the available stock, but for this to happen i hav to combine 2 queries one called stock which has all the stock on hand details which is calculated in the query and other query is the orderdtl query where the item total is calculated with unitprice and quantity.

i have tried two ways:

i made just one query with only orderdtl query, and added two text boxes to dlookup the stock and if funtion to see if the quantity is available and with tht data i count all items in the order and also count no of instock items in tht order, and staus can only change if total count of items equals to instock item. for ths to happen i need to send the dlookup and if function data to a table field to be stored inother to be proccessed for counting, but it does not work becuse the event only occurs in the current selected record of the continous form orderdtl.

second option i tried was combine 2 queriess the orderdtl and stock ones so that i get the needed data already stored in a table and check to order form but then when the queries combine i cannot add a new field to the continous form orderdtl which has the recordsocuce of the 2 queies

The Northwind Developer template in Access handles inventory on a binary basis, which is similar to what you describe. You either ship 100% of the items on an order, or none.

While this is probably not the predominant approach, it is valid.

You can download the Northwind Developer template and inspect how we handled this process.

However, we've had feedback that the current version (2.4) of the template doesn't always instantiate properly. I maintain a downloadable copy of the full accdb here.

The_Doc_Man is unfailingly polite. You need not risk the integrity of your data by trying to maintain an unmaintainable procedure that permits deletion of records and also wants to maintain sequential IDs.

Hide your Autonumber Primary Keys from the "guys". Give them what they want, a sequential ProductCode, using your current method, but only use that ProductCode for display purposes so the "guys" can see it and be reassured.

 

[Ken Sheridan]

I do hav another issue though, so I'm created a stock tbl in away u add stock details like product and quantity and date to a table thru a form which was a single form only.
But now the guys want me to fix it so that it can be bulk import stock problem, So I'm using a continuous forms and now it shows all records and another issue is my stock in id for each record is a number field with a form field that has the code "=DMax("StockIn_ID","StockIn_tbl")+1", and when I use this the first 2 new records always have the same id, btw i cant use autonumbering cuz the guys want continuous serials when a record is deleted, any feedback I could use?

Quite what unbroken serial numbering of rows achieves is hard to see, a primary key need have no semantic significance and need only be distinct, but it is a not uncommon requirement, and in my line of work it is a legal requirement in some quasi judicial contexts. The many solutions put forward over the years fall into two categories, firstly that published by Ken Getz et al, in which the last number used is stored in an external database which is opened exclusively to get the next number, and then updated, secondly, and by far the most popular, that popularised by Roger Carlson in which the DefaultValue property is set at runtime by calling the DMax function in the way you are, but with the addition of error handling to cater for when two or more users are inserting new records simultaneously.

You'll find examples of both methods, modified by myself, in the attached file, along with other associated routines.

Whether the form is in single form view, or continuous forms view is immaterial.

If it is necessary to maintain an unbroken sequence if rows are deleted, then the numbers should be computed at runtime, not stored in a column, which means they cannot be the primary key of course. The following query is an example which sequentially numbers transactions in date order. Note the use of the TransactionID autonumber primary key as the tie breaker where two or more transactions take place on the same day:

SELECT
    COUNT(*) AS RowNumber,
    T1.TransactionDate,
    T1.CustomerID,
    T1.TransactionAmount
FROM
    Transactions AS T1
    INNER JOIN Transactions AS T2 ON (
        T2.TransactionID <= T1.TransactionID
        OR T2.TransactionDate <> T1.TransactionDate
    )
    AND (T2.TransactionDate <= T1.TransactionDate)
GROUP BY
    T1.TransactionDate,
    T1.TransactionID,
    T1.TransactionAmount,
    T1.CustomerID;

 

[Pat Hartman]

Ask the user how he wants to physically renumber the units if the 10th out of 30 is deleted. Who pulls the inventory from the warehouse and puts a replacement serial number label on each item.

I am surprised that you are using an autonumber as the serial number. I would think that the serial number would be assigned at the factory when the item is made and the item is then labeled with a hard to remove tag to actually prevent the serial number from being changed.

my stock in id for each record is a number field with a form field that has the code "=DMax("StockIn_ID","StockIn_tbl")+1", and when I use this the first 2 new records always have the same id,

This expression should include Nz() because if the function returns no record, the result is null.

=Nz(DMax("StockIn_ID","StockIn_tbl"),0) +1

 

[TMK]

hi guys,
So the item code is a hardcodes id from the factory itself, the serial unique id case comes up only when stock is added to the shelve, in the sense that when a new stock arrives that record is entered to the system, THAT RECORD IS HAVING A UNIQUE CODE, which is the one that must be serial because each day they get alot of stock and they wanted series for there stuff internally, so each time a new stock is entered that record has a "STOCKIN ID", but actually tht isnt the problem here anyways, i was wondering if there was a method to insert bulk stock, in the sense, so far i can only add stock one item at a time, but its requested that they wanted multiple different items to be entered to stock in one go so thay dont hav to open the single form for like 60 times, ibstead just open one form and enter like a continous form and carry on with simply adding the item code and quantity of stock in.

 

[CJ_London]

What is the user using as a reference document? A piece of paper? A pdf or excel file? Something else?

And why not use a continuous form?

Or a main form to capture delivery details and continuous subform to capture product details?

 

[Ken Sheridan]

i was wondering if there was a method to insert bulk stock, in the sense, so far i can only add stock one item at a time, but its requested that they wanted multiple different items to be entered to stock in one go so thay dont hav to open the single form for like 60 times, ibstead just open one form and enter like a continous form and carry on with simply adding the item code and quantity of stock in.

One way would be to open an unbound dialogue form containing a multi-select list box in which the products to be added to stock can be selected, with the following or similar as its RowSource property

SELECT
    Products.ProductID,
    Products.Product
FROM
    Products
ORDER BY
    Product;

In the Click event procedure of a Confirm Selections button on the form put code along these lines:

Private Sub cmdConfirm_Click()

    Dim varItem As Variant
    Dim strSQL As String
   
    With Me.lstProducts
        For Each varItem In .ItemsSelected
            strSQL = "INSERT INTO StockAcquisitions(AcquisitionDate, ProductID, Quantity) " & _
                "VALUES(#" & Format(VBA.Date, "yyyy-mm-dd") & "#, " & .ItemData(varItem) & ", 0)"
                CurrentDb.Execute strSQL, dbFailOnError
        Next varItem
    End With
   
    ' Open continuous form view form 'frmAddStock'
    DoCmd.OpenForm "frmAddStock"
   
    DoCmd.Close acForm, Me.Name, acSaveNo
   
End Sub

The frmAdd form's RecordSource would be a query restricted to those items where the quantity is zero, e.g.

SELECT
    StockAcquisitions.*
FROM
    Products
    INNER JOIN StockAcquisitions ON Products.ProductID = StockAcquisitions.ProductID
WHERE
    Quantity = 0
ORDER BY
    Product;

All that is then necessary is to enter the quantities of each product to be added to stock.

 

[Pat Hartman]

It is easy to create a loop to add "10" records. The problem is that each record needs to have a serial number associated with it. If the items come to you with sequential serial numbers, you can ask for a starting value and increment that. I'm very confused. Which will be the official "serial number"? The one on the non-removable label on the item or the one you are generating?

 

[GPGeorge]

hi guys,
So the item code is a hardcodes id from the factory itself, the serial unique id case comes up only when stock is added to the shelve, in the sense that when a new stock arrives that record is entered to the system, THAT RECORD IS HAVING A UNIQUE CODE, which is the one that must be serial because each day they get alot of stock and they wanted series for there stuff internally, so each time a new stock is entered that record has a "STOCKIN ID", but actually tht isnt the problem here anyways, i was wondering if there was a method to insert bulk stock, in the sense, so far i can only add stock one item at a time, but its requested that they wanted multiple different items to be entered to stock in one go so thay dont hav to open the single form for like 60 times, ibstead just open one form and enter like a continous form and carry on with simply adding the item code and quantity of stock in.

Please clarify.

Is it the case that you actually have two different "Codes"?

That is what this paragraph suggests.

1. "...the item code is a hardcode[d] id from the factory itself..."
2. "...the serial unique id case comes up only when stock is added to the shelve.."

Please confirm: Is there a manufacturer's code and also your own inventory code?

If so, I assume (hope, actually) that you have two different fields for each of these codes in the Product table.

And further, I assume that you are only struggling with your own, unique, serial ID which you create for each item as it is added to your inventory? The existence of a separate manufacturer's code is, therefore, a distraction, is it not? Or is it somehow involved in your attempt to create and manage unique serial numbers?

As others have suggested, there are multiple ways to handle this, from list boxes to continuous forms to coding solutions.
I am not yet clear on what needs to be done, exactly.

 

[gemma-the-husky]

I do hav another issue though, so I'm created a stock tbl in away u add stock details like product and quantity and date to a table thru a form which was a single form only.
But now the guys want me to fix it so that it can be bulk import stock problem, So I'm using a continuous forms and now it shows all records and another issue is my stock in id for each record is a number field with a form field that has the code "=DMax("StockIn_ID","StockIn_tbl")+1", and when I use this the first 2 new records always have the same id, btw i cant use autonumbering cuz the guys want continuous serials when a record is deleted, any feedback I could use?

Well what you do is issue serial numbers from a way that guarantees a sequence (I prefer a seed table to dmax, personally), and don't delete records. Instead of delete mark it as "ignore".

 

[Ken Sheridan]

The problem is that each record needs to have a serial number associated with it.

The OP refers to a Quantity column, so if each individual physical item has a distinct serial number, then these would need to be in a column in a separate referencing table. If the serial numbers are sequential per product, it would be a simple task to generate the numbers and insert the requisite number of rows on the basis of the Quantity value just added in the referenced table. If not the numbers would be inserted manually in the same way that the Quantity value is after each row with zero Quantity has been updated. The appropriate interface for this would be an unbound form with two bound correlated subforms in continuous forms view.