Do i search or filter a query table

[castle]

Using access 2003.



I have a saved table query called timeSchedule.



timeSchedule, StaffNo, TimeTableNo, DayNo, StartTime, EndTime, ChargeNo,
NoOfHours,
103, 1000, 11, 09:30, 12:00, 11, 2.5
103, 1001, 10, 09:30, 11:00, 10, 2
104, 1005, 13, 09:00, 12:30, 15, 3.5
104, 1006, 13, 12:30, 15:00, 13, 2.5




In code, i don't know if it best to filter the correct data i need, or if i
should use the find or seek method.



Well in the form, i use a list box to select the correct staffno need, then
i want to get all the records for that member.



But, when i find the first record i need then to go to the dayno and get
all that correspond to (first of all monday i.e. dayno 10), in this example
13, so then i can count up the noofhours and if the no of hours is >8 then
the 13 (ie thursday) is taken out of the next list box because all the time
has been taken up for that staff member.



So if i used the docmd.applyfilter method this may take out all the data
that i dint need for monday, i add the time then remove monday if needed,
then do i need to stop the filter to apply it again for tuesday?



Or if using find method it says in help "This method does not support
multi-column searches".



I think the seek method may be my best option but unsure how to search 2
columns , my problem is the index being the primary key , but the table being
a query does not have a primary key.





Option Compare Database
Option Explicit
Dim cn As ADODB.Connection
Dim rs_Timetable As ADODB.Recordset
Dim rs_Client As ADODB.Recordset
Dim rs_Cleaning As ADODB.Recordset
Dim rs_timeSchedule As ADODB.Recordset
Dim diff, diff1, diff2 As Double



Private Sub Form_Load()
DoCmd.Maximize
Lbl_date.Caption = Date

Set cn = CurrentProject.Connection


Set rs_Timetable = New ADODB.Recordset

rs_Timetable.Open "Timetable", cn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect

rs_Timetable.Index = "PrimaryKey"


Set rs_Client = New ADODB.Recordset

rs_Client.Open "Client", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

rs_Client.Index = "PrimaryKey"


Set rs_Cleaning = New ADODB.Recordset

rs_Cleaning.Open "CleaningDays", cn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect

rs_Cleaning.Index = "PrimaryKey"


With rs_Timetable
If .EOF = True Then
lbl_TimeTableNo.Caption = 1000
Else
.MoveLast
lbl_TimeTableNo.Caption = !TimeTableNo + 1

End If
End With


Detail.Visible = False

lst_Client.SetFocus


Set rs_timeSchedule = New ADODB.Recordset

rs_timeSchedule.Open "timeSchedule", cn, adOpenDynamic, adLockOptimistic,
adCmdTableDirect

rs_timeSchedule.Index = "PrimaryKey"

End Sub





Private Sub lst_Staff_AfterUpdate()
Dim staffsearch As Integer

staffsearch = Me.lst_Staff.Column(0)

End Sub


Thanks for the help

 

[Jeanette Cunningham]

Hi castle,
you can use a filter or the Find First method to show all the matching
records.

The filter is fairly simple, if you watch out for the flaws in filter.
There is a sample download of a form filtered by combo boxes on this site
http://allenbrowne.com/ser-62.html

It is more complicated than you need, but you can use to find out how to set
up filtering, then just adapt the bits you need.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia