insufficient column info for updating or refreshing

[2mhzbrain]

Please help, i do not know whats causing the problem...

Dim miqty As Double
Dim mitc As Double
Dim siqty As Double
Dim sitc As Double
Dim diffqty As Double
Dim difftc As Double
Dim dvqty As Double
Dim dvtc As Double

dvqty = dynavqty.Text
dvtc = dynavtc.Text

Set editSIrs = New ADODB.Recordset
With editSIrs
    .Open "SELECT MainInventory.ItemCode AS mic, MainInventory.Quantity AS miq, MainInventory.TotalCost AS mit, StockIn.ItemCode AS SIC, StockIn.ID AS si, StockIn.Quantity AS sq, StockIn.UnitCost AS su, StockIn.TotalCost AS st From maininventory, stockin", editSIcn, 2, 3

        Do While Not .EOF

        If icsearchtxt.Text = !mic And icsearchtxt.Text = !sic And idtxt.Text = !si Then
            miqty = mainqty.Text
            mitc = maintc.Text
            siqty = origqty.Text
            sitc = origtc.Text
            diffqty = miqty - siqty
            difftc = mitc - sitc

            !miq = diffqty + dvqty
            !mit = difftc + dvtc

            !sq = dynavqty.Text
            !su = dvuc.Text
            !st = dynavtc

            .Update
            Exit Do
        Else
            .MoveNext
        End If
        Loop

End With

[Reverend Jim]

I'd have to see your table definitions to recommend a solution but the problem is that if you want to specify two tables in a query then the tables have to be related (share a common field). Please post the table definitions and we'll see what we can do. In the meantime, I'd like to recommend a way to simplify queries where you repeatedly use the table name. You already use aliases to create new field names on the fly (AS keyword). This also works for table names. For example, instead of repeatedly typing MainInventory.fieldname you can create a temporary (for the duration of the query) alias such as m as in

SELECT m.ItemCode AS mic, m.Quantity AS miq, m.TotalCost AS mit FROM MainInventory AS m

[2mhzbrain]

Main Inventory Table --- Stock In Table
    id                   id
    itemcode             itemcode
    itemname             itemname
    qty                  qty
    unitprice            unitprice
    totalprice           totalprice

itemcode values on both tables are the same, but StockIn tables will be populated with multiple duplicates of item codes, we can identify stockin entry using its id, which is primary key.


        main.invitemcode     stockin.itemcode
        1                    1
        2                    2
        3                    2
        4                    1

[Reverend Jim]

Then perhaps you want

SELECT m.ItemCode AS mic, m.Quantity AS miq, m.TotalCost AS mit, 
       s.ItemCode AS SIC, s.ID AS si, s.Quantity AS sq, s.UnitCost AS su, s.TotalCost AS st 
  FROM maininventory AS m, stockin as s
 WHERE m.ID = s.ID

The WHERE clause defines the linkage between the two tables. The actual term is INNER JOIN and this query does it implicitly. The explicit syntax is

SELECT m.ItemCode AS mic, m.Quantity AS miq, m.TotalCost AS mit, 
       s.ItemCode AS SIC, s.ID AS si, s.Quantity AS sq, s.UnitCost AS su, s.TotalCost AS st 
  FROM maininventory AS m INNER JOIN stockin as s
    ON m.ID = s.ID

[2mhzbrain]

this works fine but repetetive, pls simplify this so i can have reference... thanks a lot.. i think this will cause a problem if my database is empty, im newbie, pls guide me

Dim miqty As Double
Dim mitc As Double
Dim siqty As Double
Dim sitc As Double
Dim diffqty As Double
Dim difftc As Double
Dim dvqty As Double
Dim dvtc As Double

dvqty = dynavqty.Text
dvtc = dynavtc.Text

Set editSIrs = New ADODB.Recordset
With editSIrs
    .Open "SELECT ItemCode, Quantity, TotalCost FROM MainInventory", editSIcn, 2, 3

    Do While Not .EOF

        If icsearchtxt.Text = !ItemCode Then
            miqty = mainqty.Text
            mitc = maintc.Text
            siqty = origqty.Text
            sitc = origtc.Text
            diffqty = miqty - siqty
            difftc = mitc - sitc
            !Quantity = diffqty + dvqty
            !TotalCost = difftc + dvtc
            .Update
            Exit Do
        Else
            .MoveNext
        End If
    Loop
End With

Set editSIrs1 = New ADODB.Recordset
With editSIrs1
    .Open "SELECT ID, Quantity, UnitCost, TotalCost FROM StockIn", editSIcn1, 2, 3
    Do While Not .EOF
        If idtxt.Text = !ID Then
            !Quantity = dynavqty.Text
            !UnitCost = dvuc.Text
            !TotalCost = dynavtc.Text
            .Update
            Exit Do
        Else
            .MoveNext
        End If
    Loop
End With


icsearchtxt.Enabled = True
End Sub

[Reverend Jim]

Earlier you said the fields in MainInventory were

id
itemcode
itemname
qty
unitprice
totalprice

but now they are

ItemCode
Quantity
TotalCost

so can you please tell me

1. what is the actual structure of the tables
2. what are the fields
3. what is the purpose of the tables

I ask the second question because it looks like you have some redundancy. For example, you have both ID and ItemCode fields. My first impression is that ItemCode is already a unique value so why do you need an ID field? Also, you shouldn't need a TotalPrice field in an inventory table. So I would have the fields

ItemCode    a unique number identifying a stock item
ItemName    well, duh
Quantity    the number of this item in stock
UnitPrice   cost per item

For that matter, you shouldn't need TotalPrice in any table at all since it can be calculated on the fly in the query iteself. I don't know what the StockIn table is for and I don't know what you are trying to do with the above code. Some comments would be nice. Once I know what the tables are for and what you are trying to do I can probably suggest a better approach.

[2mhzbrain]

MainInventory and StockIn table, both of them have ID ItemCode ItemName Quantity and UnitPrice fields. Main has all the unique ItemCodes, StockIn table has many repetetive ItemCodes with different Quantity UnitPrice and TotalCost, i can only identify uniqueness of data in StockIn table by its ID. If the user edit data in StockIn table, MainInventory will be affected. That code is working well now but it open two connections. Is there a problem with two connections being opened? Can you please suggest a good coding practice.

[Reverend Jim]

You don't need two connections. Declare one at the class level (or in a module) and use that one everywhere. As for the tables, you told me what they contain (which I can get from the field names (sort of) but not their purpose. For example, I would imagine a system would have an Product table which contains details about every item. This could include things like size, weight, shape, colour, manufacturer, cost, tax rate, etc. Each item in the Inventory table would hava unique ID. There would also be an Inventory table. This table would contain information on how many items of each Product are actually in stock. It might also contain a field which would flag when to issue a reorder when the number of items in stock falls below that level. Further tables might include a Customer table which assigns a unique CustomerID to each uniquue customer as well as an Orders table which would contain information about a specific order (what customer, which products and in what quantities, various dates - orderDate, ShippingDate, etc).

[Reverend Jim]

Here's an example of a possible database layout. This example is not necessarily complete.

**Products**

    Contains one record for each unique product carried. Provides details (unchanging) about each product.

   *ProductID      unique
    Weight         unit weight in kilograms
    Dimensions     unit size (WIDTH x DEPTH x HEIGHT)
    Colour
    UnitCost
    TaxRate        expressed as 0.05 = 5%

**Stock**

    Contains one record for each unique product. These records will changes as stock is received and sold.

   *ProductID      foreign key from **Products**
    NumItems       number of items in stock
    MinReorder     reorder if NumItems < this
    Location       1 = on site, 2 = warehouse, etc

**Customers**

    Contains one record for each customer

   *CustomerID     unique
    CustomerType   0 = regular, 1 = businiess, 2 = preferred, etc
    LastName
    FirstName
    Address
    Telephone

**Orders**

    Contains one record for each order. Each order wil consist of one or more order items. The items appear in the OrderItems table

   *OrderID        unique
    CustomerID     foreign key from **Customers**
    OrderDate      datetime when order created
    ShippingDate   datetime when order received/shipped
    Address        delivery address (if order for delivery)

**OrderItems**

    Contains one record for each item in an order.

   *OrderID        foreign key from **Orders**
    ProductID      from **Products**
    UnitCost       from **Products**
    Quantity       number of each item purchased
    TaxRate        from **Products**

**Accounts**       one record per customer

   *CustomerID     foreign key from **Customers**
    Balance        amount owing

[2mhzbrain]

thanks for the reply... i get the idea, but what if the same item changed its price, for example, if different supplier