I have 3 table
1. cat_Tbl = category table which consist of fields:
catNo = category number (primary key)
catDesc = category description
2. prod_Tbl = product table which consist of fields:
pIndex = index number of a product (primary key)
pText = product description
3 FM_Tbl = items ordered(in an order slip) table which consist of fields:
OSno = Order slip number
FMitemNo = item number
Pindex = index number of an ordered product
FMqty = quantity ordered
Now, I have an order slip record #12704. in that slip, i ordered several items, each has its unique item number. but when I run the code below, the result set exceeds the number of items i actually ordered. when I check the result, few items were duplicated. this is my code:
Is there a problem with the strSQL? What's wrong with my code?
strSQL = "SELECT cat_Tbl.catDesc,FM_Tbl.FMitemNo,FM_Tbl.Pindex,FM_Tbl.FMqty,prod_Tbl.Ptext,prod_Tbl.Pum,prod_Tbl.Pprice FROM cat_Tbl,FM_Tbl,prod_Tbl WHERE (FM_Tbl.OSno = " & lngNum & ") AND FM_Tbl.Pindex=prod_Tbl.Pindex AND cat_Tbl.catNo=prod_Tbl.Pcat ORDER BY FM_Tbl.FMitemNo"
'clear order table
grdOrder.Rows = 1
rsMain.Open strSQL, cnxMain, adOpenForwardOnly, adLockReadOnly, adCmdText
Do Until rsMain.EOF
.TextMatrix(.Rows - 1, 0) = rsMain!FMitemNo 'Item no.
.TextMatrix(.Rows - 1, 1) = rsMain!catDesc 'Category
.TextMatrix(.Rows - 1, 2) = rsMain!Ptext 'Description
.TextMatrix(.Rows - 1, 3) = rsMain!FMqty 'Qty
.TextMatrix(.Rows - 1, 4) = rsMain!Pum 'Unit of measurement
.TextMatrix(.Rows - 1, 5) = rsMain!Pprice 'Unit Price'Total
.TextMatrix(.Rows - 1, 6) = rsMain!Pindex 'Product id
What I want is to display just what I ordered; exact number of items, exact items. and just referencing to other tables its category and product description.