Recordset SQL problem 1

[Maypen]

Hi Guys, I would be grateful for some help on a 2 problems i have. I am using VB6, ADO, Access

1)I have a Transaction Form with a datagrid for entering details of orders received. I am having problems opening the recordset.I get a "type mismatch in expression error". I am getting data from three tables and i used Access Query to write the SQL. I am sure there is a simple explanation but i can't spot it.

2)

I will be able to add a new invoice detail to the Purchase Invoice table. How can i ensure that the newly received quantity is added to the quantity presently in stock in the Stock Table

I am a novice so write slowly please. Thanks for any help forthcoming!!

Private Sub Form_Load()
Dim rSql As String
Me.Height = 7000
Me.Width = 12000

Set stockCon = New ADODB.Connection

stockCon.ConnectionString = _
"Provider = Microsoft.Jet.OLEDB.4.0; Data Source=C:\My Documents\GP project\gpframings.mdb"

stockCon.Open

Set stockRS = New ADODB.Recordset
'Prepare the recordset.

rSql = "SELECT purchaseinvoice.purchaseinvoiceno, purchaseinvoice.purchaseinvoicedate, purchaseinvoice.purchaseorderno, purchasesupplier.supplierID, purchaseinvoicestock.stockno, purchaseinvoicestock.quantity, purchaseinvoicestock.cost, purchaseinvoice.subtotal FROM (stock INNER JOIN (purchaseinvoice INNER JOIN purchaseinvoicestock ON purchaseinvoice.purchaseinvoiceno = purchaseinvoicestock.purchaseinvoiceno) ON stock.stockno = purchaseinvoicestock.stockno) INNER JOIN purchasesupplier ON purchaseinvoice.purchaseinvoiceno = purchasesupplier.purchaseinvoiceno"

With stockRS
.ActiveConnection = stockCon
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open rSql

End With

If stockRS.BOF = True Or stockRS.EOF = True Then
Exit Sub

End If

Set tranGrid.DataSource = stockRS
tranGrid.Refresh

End Sub

 

[JerryKlmns]

Copy that rSql to your access in a query SQL view window and see if it runs Ok or fix it as needed. Then copy from there and paste it at your VB.

If you need to check for any records returned, this
----------------
If stockRS.BOF = True Or stockRS.EOF = True Then
Exit Sub
End If
----------------
Should be
----------------
If stockRS.BOF And stockRS.EOF Then
Exit Sub
End If
----------------

 

[Maypen]

Hi
Thanks JerryKlmns you were right. I ran the SQL in Access and found some errors such as the auto number Primary Key in one Table cannot be a auto number primary key with the same name in two tables even if one is a joint primary key (should be "number"). After a little work i got the datagrid to work.

 

[MARELUAD]

Hello

How can I run MS-Access97 in the background starting by VB6.0

Thanks

 

[JerryKlmns]

MARELUAD,

create a new thread as this is a different issue.

And be more specific about what you are trying to achieve!