Database Question?

this code read two tables and produce a query recordset linked with INNER JOIN then it pass the query to another table. The problem is that the code read only one record and add only one record, while the tables have many records. I tried with MoveNext, MoveFirst, MoveLast and it does not work. there is something wrong in this code? well, I am new in Visual Basic and I have not experience in coding, may be this code look uncommon for you, but is my first step into programming. If you find the probelm please let me know.

Here is the code:

Private Sub Form_Load()

Dim dbs As Database
Dim rstInvent As Recordset
Dim rstQry As Recordset
Dim rstReceiving As Recordset

Set dbs = OpenDatabase("C:\project\datos.mdb")
Set rstInvent = dbs.OpenRecordset("Inventory")
Set rstReceiving = dbs.OpenRecordset("receiving")

Set rstQry = dbs.OpenRecordset("SELECT ALL " _
& "Receiving.OrderNo, Receiving.VendorName, " _
& "Receiving.DateReceived, RecevItem.ItemNo, " _
& "RecevItem.Description, RecevItem.Price, " _
& "RecevItem.Quantity FROM Receiving " _
& "INNER JOIN RecevItem ON Receiving.RecordID = " _
& "RecevItem.RecordID;")

rstQry.MoveNext


With rstInvent
Do While Not rstQry.EOF
rstInvent.AddNew
!OrderID = rstQry!OrderNo
!SupplierName = rstQry!VendorName
!InventDate = rstQry!DateReceived
!ItemID = rstQry!ItemNo
!ItemName = rstQry!Description
!Price = rstQry!Price
!Quantity = rstQry!Quantity
Text1.Text = rstQry!Price
Text2.Text = rstQry!Quantity
rstInvent.Update
rstInvent.Close
Exit Do
Loop

dbs.Close

End With

End Sub

I really apreciate your BIG HELP

thank you in advance

Without Sharpening up on my SQL (I used to do a lot of it) I can see some issues with this code. I am assuming that you know your SQL so I will not bother looking at it, for now - Mainly cause I see other issues.

You Said:

"The problem is that the code read only one record and add only one record, while the tables have many records"

How do you know only one record was returned? Right after you do the SQL, try:

rstQry.MoveLast ' RecordCount may not be accurate without this
MsgBox CStr(rstQry.RecordCount)
rstQry.MoveFirst ' Point back to beginning of RecordSet

This will tell you how many record where returned by the SQL. Next we need to address the other code.

----------

Right after the SQL, you have "rstQry.MoveNext". Remove it. Unless you have a good reason for it being there? If anything, put a rstQry.MoveFirst there. However, this is already true - you just created the RecordSet.

----------

You don't need to do this (in your case, your SQL does it for you):

Set rstReceiving = dbs.OpenRecordset("receiving")

----------

OK , now your DoLoop:

With rstInvent
Do While Not rstQry.EOF
rstInvent.AddNew
!OrderID = rstQry!OrderNo
!SupplierName = rstQry!VendorName
!InventDate = rstQry!DateReceived
!ItemID = rstQry!ItemNo
!ItemName = rstQry!Description
!Price = rstQry!Price
!Quantity = rstQry!Quantity
Text1.Text = rstQry!Price
Text2.Text = rstQry!Quantity
rstInvent.Update
rstInvent.Close ' ??? Wrong !
Exit Do ' ??? Wrong !
Loop
dbs.Close ' ???
End With

1) Move the "rstInvent.Close" to AFTER the "Loop". You are closing it and there still may have records to add.
2) Get rid of the "Exit Do". You are quitting after processing the FIRST record.
3) Move the "dbs.Close" to AFTER the "End With". Just to be clean.

The modified version looks like this:

With rstInvent
Do While Not rstQry.EOF
.AddNew
!OrderID = rstQry!OrderNo
!SupplierName = rstQry!VendorName
!InventDate = rstQry!DateReceived
!ItemID = rstQry!ItemNo
!ItemName = rstQry!Description
!Price = rstQry!Price
!Quantity = rstQry!Quantity
Text1.Text = rstQry!Price
Text2.Text = rstQry!Quantity
.Update
Loop
rstInvent.Close
End With

dbs.Close

----------

Hope that helps. It looks like you have a nice start on VB. Database stuff in VB can be easy and fun.

Thank you for your response to my question, I do apreciate your big help to solve my problem. Now I have a little more clear what I doing.

Hi Jay!

I did exactly the same and the program never end and it is writing only one record the same record hundred of time. I checked my database and it finish only after booting my computer. The problem is that only store into the table only one record and the program never end. Here is the code again


Private Sub Form_Load()

Dim dbs As Database
Dim rstInvent As Recordset
Dim rstQry As Recordset
Dim rstReceiving As Recordset

Set dbs = OpenDatabase("C:\project\datos.mdb")
Set rstInvent = dbs.OpenRecordset("Inventory")
Set rstReceiving = dbs.OpenRecordset("receiving")

Set rstQry = dbs.OpenRecordset("SELECT ALL " _
& "Receiving.OrderNo, Receiving.VendorName, " _
& "Receiving.DateReceived, RecevItem.ItemNo, " _
& "RecevItem.Description, RecevItem.Price, " _
& "RecevItem.Quantity FROM Receiving " _
& "INNER JOIN RecevItem ON Receiving.RecordID = " _
& "RecevItem.RecordID;")


rstQry.MoveLast
MsgBox CStr(rstQry.RecordCount)
rstQry.MoveFirst



With rstInvent
Do While Not rstQry.EOF
.AddNew
!OrderID = rstQry!OrderNo
!SupplierName = rstQry!VendorName
!InventDate = rstQry!DateReceived
!ItemID = rstQry!ItemNo
!ItemName = rstQry!Description
!Price = rstQry!Price
!Quantity = rstQry!Quantity
Text1.Text = rstQry!Price
Text2.Text = rstQry!Quantity
.Update
Loop
rstInvent.Close
End With

dbs.Close

End Sub


Thank for your help!

The moment I began reading, I knew what I forgot...

With rstInvent
Do While Not rstQry.EOF
.AddNew
!OrderID = rstQry!OrderNo
!SupplierName = rstQry!VendorName
!InventDate = rstQry!DateReceived
!ItemID = rstQry!ItemNo
!ItemName = rstQry!Description
!Price = rstQry!Price
!Quantity = rstQry!Quantity
Text1.Text = rstQry!Price
Text2.Text = rstQry!Quantity
.Update
rstQry.MoveNext ' This is the NEW required instruction !!!
Loop
rstInvent.Close
End With

:D