asked on

Help with Recordsetclone and DAO.Recordset

Hi there I use the following code to report via message box.

'begin here
Function getCardValuesTest()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.MoveFirst

Do While Not rs.EOF

MsgBox "Card: " & rs!CardID & vbNewLine & _
"Usage: " & rs!Usage & vbNewLine & _
"Description: " & rs!CardDes

rs.MoveNext

Loop

rs.Close
Set rs = Nothing

End Function

'end here

The code runs well through the recordset that is diplayed on the form. But if i change the last record on the form it does not display the change in my message box and will still use the old info, only when I move the focus to a previous record and run the code again does it display the correct info. Is there a way of updating or requering the recordsetclone before i run the code.

Regards

fouche

Rey Obrero (Capricorn1)

just do a

Me.requery after you change the record,

better post the codes you are using for this form

SQL_Stu

As you have declared, Set rs = Me.RecordsetClone - this means that rs will contain a snapshot of your recordset AT THAT POINT IN TIME.

When will the getCardValuesTest function be called?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Try inserting these few lines:

Dim rs As DAO.Recordset
If Me.Dirty = True Then
' Save current record.
Me.Dirty = False
End If
Set rs = Me.RecordsetClone

/gustav

fouchev

ASKER

At the moment i call the function via a cmd buttons click event.

If I can get it to report it correctly on my message box i can then continue with the rest of what i want to do.

the form is based on the following:

SELECT tblCards.CardID, tblCards.CardColourID, tblCards.CardTypeID, tblCards.InStock, tblCardType.Type, tblCardColour.Colour, [type] & " (" & [colour] & ")" AS CardDes, tblCards.Usage, tblCards.ColourPhoto FROM tblCardType INNER JOIN (tblCardColour INNER JOIN tblCards ON tblCardColour.CardColourID = tblCards.CardColourID) ON tblCardType.CardTypeID = tblCards.CardTypeID;

Regards

Fouche

fouchev

ASKER

Thanks ALL for the prompt response...appreciated!!

Cactus Data, you also had it correct, but I had to go with the first correct response of LSMConsulting who also provided the same scenario and worked.

Cheers all

have a lovely day.

Fouche