Populating the sub with all products.

[andrep]

How to explain this !

I have sales reps on the road, the boss wants them to count their house and stocks and sold items on a daily basis and calculate their comissions for each product!

So i have a table called repsales with repid (primary), name, account#, date, amountscollected and a bill#.

Product table! code(primary), repID(lookup) descrption,
housestock, carstock, items sold, returns dans comission!

The problem at hand is that i need the subform to show all
the available products and the ability to filter the category because there are far too many products to show
in the sub form!

I know this is not the best way to go but the boss wants this, and no other way will do?

Basically its like a billing system, with the exception that the sub form is prepopulated with all the products!

Help please going nuts here !
Thanks

 

[AccessGuru]

I would use these commands where i control the LinkChildField and LinkMasterField property programmatically to make the filter work for you.

Ie, an unbound control on the main form is linked to the field to search for on the sub form. Before the user selects the combo box option on the main form the main form, all products can be displayed. i.e. no link in child or master. Then after selection, assign the link, one or more fields. e.g.

Me![ContractDate] = Null 'on main form
Me![ContractDate].Requery ' refresh records in subform

'Default LinkChildFileds = inv_date;inv_type;inv_no
'Default LinkMasterFileds = invdate;invtype;invno

'next few commented lines optional:
'On Error GoTo InvDateSelectErr

'Dim mydb As Database
'Dim myset As Recordset

'Set mydb = DBEngine.workspaces(0).databases(0)

'Set myset = mydb.OpenRecordset("InvNosPS&quot

'myset.FindFirst "[Inv_No] = " & forms![pmtsmf]![InvNo] & " and [Inv_Type] = " & forms![pmtsmf]![InvType] & " and #" & Format(Inv_Date, "d/m/yy&quot & "# = #" & Format(forms![pmtsmf]![InvDate], "d/m/yy&quot & "#"

'Me![conttype] = myset![conttype] 'to know which subform to choose
'this is the actual code:
If Forms![pmtsmf]![PmtsSF].LinkChildFields = "note_date;note_type;note_no" Then 'check for the type of link forst
Forms![pmtsmf]![PmtsSF].LinkChildFields = Null
Forms![pmtsmf]![PmtsSF].LinkMasterFields = Null
Forms![pmtsmf]![PmtsSF].LinkChildFields = "inv_date;inv_type;inv_no" 'assign desired link, notice multiple columns, 3 cols here.
Forms![pmtsmf]![PmtsSF].LinkMasterFields = "invdate;invtype;invno"
End If