Filter Sub that does not work in Windows 7

[weavind]

Hi,

I have a filter sub that has worked perfectly fine in Windows XP, but will not work when used in Windows 7. The database is in 2003 format.

Basically, I have a text box in the form header (frmStockInfo). When you type in a keyword and press enter, it searches the table "tblStocklist". It then brings all the records which have this keyword in them and places them in a sub form (subfrmStockInfoStock).

What does windows 7 do differently to windows XP? I am using Access 2003 on both operating systems.

here is my code:

'code to search table tblStockList
Private Sub FilterSub()
SISFSto
Form_frmStockInfo.subfrmStockInfo.Form.RecordSource = "SELECT tblStockList.SRN, tblStockList.[Part Description], tblStockList.[Type/Mat'l] AS TypeMaterial, tblStockList.Spec, tblStockList.[Size-type], tblStockList.Tolerances, tblStockList.PartTypeID, tblStockList.SupplierID, tblStocklist.UOM, tblstocklist.pstatus, tblstocklist.cstatus, tblStockList.Total FROM tblStockList WHERE (((tblStockList.[Part Description]) like '*" & cboFindRecords & "*')) OR (((tblStockList.[Type/Mat'l]) like '*" & cboFindRecords & "*')) OR (((tblStockList.Spec) like '*" & cboFindRecords & "*')) OR (((tblStockList.[Size-type]) like '*" & cboFindRecords & "*'));"
End Sub

--------------------------------------
'code to list results in subform "subfrmStockInfoStock"
Function SISFSto()
Call SISetSubForm("subfrmStockInfoStock", "", "", "Stock List")
End Function

Function SISetSubForm(strSourceObject As String, strChildField As String, strMasterField As String, strCaption As String)
With Form_frmStockInfo.subfrmStockInfo
.SourceObject = "subfrmStockInfoLoad"
.LinkMasterFields = ""
.LinkChildFields = ""
End With
With Form_frmStockInfo.subfrmStockInfo
.SourceObject = strSourceObject
.LinkMasterFields = strMasterField
.LinkChildFields = strChildField
End With
Form_frmStockInfo.lblsubfrm.Caption = strCaption
End Function

 

[vbaInet]

I haven't gotten round to playing around with 7 (even if I've got the CD). However, here are somethings I thought I should point out.

Redundancy here:

'code to list results in subform "subfrmStockInfoStock"
Function SISFSto()
    Call SISetSubForm("subfrmStockInfoStock", "", "", "Stock List")
End Function

I noticed all you're doing in that function is calling another function. You could call it directly. Or do you have your reasons?

Function SISetSubForm(strSourceObject As String, strChildField As String, strMasterField As String, strCaption As String)
With Form_frmStockInfo.subfrmStockInfo
.SourceObject = "subfrmStockInfoLoad"
.LinkMasterFields = ""
.LinkChildFields = ""
End With
With Form_frmStockInfo.subfrmStockInfo
.SourceObject = strSourceObject
.LinkMasterFields = strMasterField
.LinkChildFields = strChildField
End With
Form_frmStockInfo.lblsubfrm.Caption = strCaption
End Function

What's the idea behind setting these properties twice?

Also try requerying the subform after you've done the above.

.Requery

 

[weavind]

HI.

I got the script working. The .requery worked. However only when I compile the database on a XP computer and use the mde file on a windows 7 pc. If I work on the mdb file on a windows 7 PC, then I get all kinds of problems. I think it could be that i'm still using a 97-2003 database.

However, thanks for your help

 

[vbaInet]

Great job! Glad to know it's working. Consider converting to 2007 but read up on this first because there are some features that 2007 does not offer.