Clone field then Filter Form? 1

[maetrix]

Hi there, I'm tring to clone a field where the previous field may be filtered out. If I turn off the filter this script works perfectly, however I need to restrict the form to only the relevant records.

This is the filter I'm using,
[Forms]![frmDepartment]![deptID]

And this is the recordclone proceedure.
Private Sub Form_Current
Dim rs1 As DAO.Recordset
Dim BKM As Variant
Dim vPRdolar As Variant

BKM = Me.Bookmark
Set rs1 = Me.RecordsetClone
rs1.Bookmark = BKM
rs1.MovePrevious
vPRdolar = rs1!regdollar
rs1.Close
Set rs1 = Nothing
Me.txtRegOldDol.Value = vPRdolar
End Sub

Is what I'm tring to accomplish here possible, or do I need to find another route?

Thanks,

--Toby Kliem-- sapere aude: Dare to be wise

 

[Steve101]

I think that the recordsetclone is associated with the recordset as determined by the recordsource of the form (ie. I dont think the filter property is incorporated into the recordsetclone.

What you might want to consider is to incorporate the filter into the recordsource and in so doing, not have to use a filter. This way, the recordsetclone and the form's underlying recordset will have their one-to-one correspondence.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[maetrix]

Ah ok, I believe I understand what you are talking about, however I'm still not too familiar with working with VBA and recordsets.

What would the code for setting a filter in a recordset look like? Would that be similar to what the code for a Goto Record command button would look like?

Thank you for the explination anyways

--Toby Kliem--

Novice Programmer,
Expert Hack. sapere aude: Dare to be wise

 

[Steve101]

Toby,

The following is an example of setting the recordsource for a form dynamically. You would typically add the following code to the OnOpen event of the form:

dim F as form:Set F = me
sq = "SELECT * " & _
"FROM tblYourTable " & _
"WHERE txtYourField = 'My Company' "
F.Recordsource = sq

In this example, the sq variable can be any valid sql select statement. The point is, because the criteria is built into it, the recordsetclone will relfect the same records.

Hope this helps,


Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au