Recodset Trouble

[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

 

[Amy Blankenship]

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

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

 

[J_Goddard via AccessMonster.com]

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

 

[aceavl via AccessMonster.com]

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!