Some guidance required on filtering recordsets.

[Dave]

Hi All,

I need some guidance or pointing in the right direction please.

I have a form bound to workstation, the form has 2 sub forms - one has all the assets for a workstation and the other has faults and notes for each asset.

Because the sub form with all the workstation assets is just that, I cannot filter on the entire asset table to locate an asset.

I started setting a new form to allow me to input a search string to filter the main asset table and replace the contents of the asset sub form with the results.

That's where I started thinking that there must be a better way to do this. I started to envisage problems with the sub form originally being linked to a station and overwriting its' contents may not
be the best idea.

Can anyone point me towards a better solution?

Many thanks,
Dave

 

[Allen Browne]

Presumably you have these tables:
- Workstation, with a WorkstationID primary key
- Asset, with an AssetID primary key, and a WorkstationID foreign key

Now you want to provide an unbound text box on the main form where the user
can enter an AssetID value, and you respond by bringing up the correct
workstation in the main form, so the asset is shown in the subform.

If that's the idea, use the AfterUpdate event procedure of the text box to
DLookup() the WorkstationID in the Asset table, and then find that
workstation in the form. This kind of thing:

Private Sub txtAssetID_AfterUpdate
Dim rs As DAO.Recordset
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If Not IsNull(Me.txtAssetID) Then
'Save first
If Me.Dirty Then Me.Dirty = False

'Look up the workstation for this asset.
strWhere = "AssetID = " & Me.txtAssetID
varResult = DLookup("WorkstationID", "Asset", strWhere)
If IsNull(varResult) Then
strMsg = "No such AssetID."
Else

'Make that workstation the current record
strWhere = "WorkstationID = " & varResult
set rs = Me.RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
strMsg = "Workstation not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End If
End If

If strMsg <> vbNullString Then
MsgBox strMsg, vbInformation, "Asset search"
End If
Set rs = Nothing
End Sub

 

[Dave]

Thanks Allen,

That seems just what I need.

I'll try it out when I figure out what it's all doing - there are a few bits of code in there that I'm not familiar with.

Thanks again for your time.

Regards,
Dave