Recodset Trouble

  • Thread starter Thread starter aceavl via AccessMonster.com
  • Start date Start date
A

aceavl via AccessMonster.com

hi!
i'm having some probles with this, hope someone can help me.


i have 2 tables: tblPrestamos and tblPagosPrestamos y created the querys and
opened 2 recorsets

Dim db As Database
Dim rsPagos As DAO.Recordset
Dim rsPrestamos As DAO.Recordset
Dim qryPagos As String
Dim qryPrestamos As String

qryPagos = "SELECT [tblPagosPrestamos].[PagosPrestamosIDPK],
[tblPagosPrestamos].[PagosPrestamosMonto], [tblPagosPrestamos].
[PagosPrestamosFecha], [tblPagosPrestamos].[ClientesIDFK], [tblPagosPrestamos]
..[PrestamosIDFK] FROM [tblPagosPrestamos] WHERE ([tblPagosPrestamos].
[ClientesIDFK]= " & lngCliente & ")"

qryPrestamos = "SELECT [tblPrestamos].[PrestamosIDPK], [tblPrestamos].
[PrestamosMonto], [tblPrestamos].[PrestamosFecha], [tblPrestamos].
[ClientesIDFK], [tblPrestamos].[PrestamosMeses] FROM [tblPrestamos] WHERE (
[tblPrestamos].[ClientesIDFK]= " & lngCliente & ")"

Set db = CurrentDb
Set rsPagos = db.OpenRecordset(qryPagos)
Set rsPrestamos = db.OpenRecordset(qryPrestamos)

i'm going in a loop in rsPrestamos and need to filter rsPagos with
rsPrestamos!PrestamosIDPK=rsPagos!PrestamosIDFK

how do i put a WHERE clause inside the SELECT statement to reference another
recordset?

thanks
 
aceavl via AccessMonster.com said:
hi!
i'm having some probles with this, hope someone can help me.


i have 2 tables: tblPrestamos and tblPagosPrestamos y created the querys
and
opened 2 recorsets

Dim db As Database
Dim rsPagos As DAO.Recordset
Dim rsPrestamos As DAO.Recordset
Dim qryPagos As String
Dim qryPrestamos As String

qryPagos = "SELECT [tblPagosPrestamos].[PagosPrestamosIDPK],
[tblPagosPrestamos].[PagosPrestamosMonto], [tblPagosPrestamos].
[PagosPrestamosFecha], [tblPagosPrestamos].[ClientesIDFK],
[tblPagosPrestamos]
[PrestamosIDFK] FROM [tblPagosPrestamos] WHERE ([tblPagosPrestamos].
[ClientesIDFK]= " & lngCliente & ")"

qryPrestamos = "SELECT [tblPrestamos].[PrestamosIDPK], [tblPrestamos].
[PrestamosMonto], [tblPrestamos].[PrestamosFecha], [tblPrestamos].
[ClientesIDFK], [tblPrestamos].[PrestamosMeses] FROM [tblPrestamos] WHERE
(
[tblPrestamos].[ClientesIDFK]= " & lngCliente & ")"

Set db = CurrentDb
Set rsPagos = db.OpenRecordset(qryPagos)
Set rsPrestamos = db.OpenRecordset(qryPrestamos)

i'm going in a loop in rsPrestamos and need to filter rsPagos with
rsPrestamos!PrestamosIDPK=rsPagos!PrestamosIDFK
Click to expand...


You probably actually want

"SELECT [tblPagosPrestamos].[PagosPrestamosIDPK],
[tblPagosPrestamos].[PagosPrestamosMonto],
[tblPagosPrestamos].[PagosPrestamosFecha],
[tblPagosPrestamos].[ClientesIDFK], [tblPagosPrestamos].[PrestamosIDFK],
[tblPrestamos].[PrestamosIDPK], [tblPrestamos].[PrestamosMonto],
[tblPrestamos].[PrestamosFecha], [tblPrestamos].[ClientesIDFK],
[tblPrestamos].[PrestamosMeses] FROM tblPagosPrestamos INNER JOIN
tblPrestamos ON tblPagosPrestamos.PrestamosIDFK =
tblPrestamos.PrestamosIDPK"

But if not, you could use a dynamic recordset to allow you to use FindFirst,
FindNext, etc. to find matching records.

HTH;

Amy
 
Hi -

The best way might be to re-select the rsPagos recordset inside the loop for
the rsPrestamos recordset. Don't forget the .movenext's!


Dim db As Database
Dim rsPagos As DAO.Recordset
Dim rsPrestamos As DAO.Recordset
Dim qryPagos As String
Dim qryPrestamos As String

qryPrestamos = "SELECT [tblPrestamos].[PrestamosIDPK], [tblPrestamos].
[PrestamosMonto], [tblPrestamos].[PrestamosFecha], [tblPrestamos].
[ClientesIDFK], [tblPrestamos].[PrestamosMeses] FROM [tblPrestamos] WHERE
(
[tblPrestamos].[ClientesIDFK]= " & lngCliente & ")"

Set rsPrestamos = db.OpenRecordset(qryPrestamos)

while not rsPrestamos.EOF
qryPagos = "SELECT [tblPagosPrestamos].[PagosPrestamosIDPK],
[tblPagosPrestamos].[PagosPrestamosMonto], [tblPagosPrestamos].
[PagosPrestamosFecha], [tblPagosPrestamos].[ClientesIDFK],
[tblPagosPrestamos]
. [PrestamosIDFK] FROM [tblPagosPrestamos] WHERE ([tblPagosPrestamos].
[ClientesIDFK]= " & lngCliente & ") AND [tblPagosPrestamos] = " &
rsPrestamos! PrestamosIDPK
Set rsPagos = db.OpenRecordset(qryPagos)
while not rsPagos.EOF
'
' Whatever processing you need
'
rsPagos.movenext
wend
rsPagos.close
rsPrestamos.movenext
wend
rsPrestamos.close

HTH

John


hi!
i'm having some probles with this, hope someone can help me.

i have 2 tables: tblPrestamos and tblPagosPrestamos y created the querys and
opened 2 recorsets

Dim db As Database
Dim rsPagos As DAO.Recordset
Dim rsPrestamos As DAO.Recordset
Dim qryPagos As String
Dim qryPrestamos As String

qryPagos = "SELECT [tblPagosPrestamos].[PagosPrestamosIDPK],
[tblPagosPrestamos].[PagosPrestamosMonto], [tblPagosPrestamos].
[PagosPrestamosFecha], [tblPagosPrestamos].[ClientesIDFK], [tblPagosPrestamos]
.[PrestamosIDFK] FROM [tblPagosPrestamos] WHERE ([tblPagosPrestamos].
[ClientesIDFK]= " & lngCliente & ")"

qryPrestamos = "SELECT [tblPrestamos].[PrestamosIDPK], [tblPrestamos].
[PrestamosMonto], [tblPrestamos].[PrestamosFecha], [tblPrestamos].
[ClientesIDFK], [tblPrestamos].[PrestamosMeses] FROM [tblPrestamos] WHERE (
[tblPrestamos].[ClientesIDFK]= " & lngCliente & ")"

Set db = CurrentDb
Set rsPagos = db.OpenRecordset(qryPagos)
Set rsPrestamos = db.OpenRecordset(qryPrestamos)

i'm going in a loop in rsPrestamos and need to filter rsPagos with
rsPrestamos!PrestamosIDPK=rsPagos!PrestamosIDFK

how do i put a WHERE clause inside the SELECT statement to reference another
recordset?

thanks
Click to expand...
 
Thank you to both
I went with J_Goddard and it worked!
if any one is trying this solution we missed before:

qryPrestamos = "SELECT [tblPrestamos].[PrestamosIDPK], [tblPrestamos].
[PrestamosMonto], [tblPrestamos].[PrestamosFecha], [tblPrestamos].
[ClientesIDFK], [tblPrestamos].[PrestamosMeses] FROM [tblPrestamos] WHERE (
[tblPrestamos].[ClientesIDFK]= " & lngCliente & ")"
Set rsPrestamos = db.OpenRecordset(qryPrestamos)

to put:

Set db = CurrentDb

otherwise it doesn't work

thanks!
 
Back
Top