Continuous Form, displaying records

[darinmc]

Hi
I have a form which is continuous and set as Snapshot.
I have a combo box which selects a job (its ID, eg 1) as the criteria.
the below code relates to a command button.
I assume this code ONLY finds the first record and obviously I want to display all the 1s...

I would like to use an SQL statement as I would like to use DISTINCT
eg, SELECT DISTINCT JobsID, EmpRegNo FROM tblPayInv WHERE JobsID = me.cmbJobs

The collumns in the form are:
TransID ¦ JobsID ¦ EmpRegNo ¦ WEdate
Please can u help.

Thx
Darin
(Would an applyfilter be any better???)

Dim rs As Object

Set rs = Me.Recordset.Clone
'this also partially works
rs.FindFirst "[JobsID] = " & Me.cmbJobs
'rs.FindFirst "[EmpRegNo] = " & Str(Nz(Me![cmbName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

 

[PHV]

You may try this:
Me.Filter = "[JobsID] = " & Str(Nz(Me![cmbJobs], 0))
Me.FilterOn = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darinmc]

Thx, that worked BUT let me try explain it differently.
The fields are:
JobsID¦EmpRegNo¦JobName¦Name¦WEDate

The Record Source: (at present is)
SELECT DISTINCT tblPayInv.JobsID, tblPayInv.EmpRegNo, TESTjobs.JobName, [EmpForename] & " " & [EmpSurname] AS Name FROM TESTjobs INNER JOIN (tblEmployee INNER JOIN tblPayInv ON tblEmployee.EmpRegNo=tblPayInv.EmpRegNo) ON TESTjobs.JobsID=tblPayInv.JobsID ORDER BY [EmpForename] & " " & [EmpSurname];

When form opens It will display just the 2 fields (visible), JobName¦Name

The combo box will give the required JobsID, when pressing the Search button, I would like all the records (the combo's selected JobsID to display showing who worked there, Asc... (Using SLQ and recordset, if that is best way?)

I will then have a command button on top of the names (transparent) so that when its clicked, I will change the record source to reflect the list showing the name with WEdate displayed. (Dates they worked there)

I should be able to adjust the code, having the written..
Hope this is clear lol

Thx
Darin

 

[PHV]

Hope this is clear
Sorry, not for me ...
What is WEDate ?
Which RecordSource would you change ?
Maybe you want to play with a ListBox ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[darinmc]

In our employment agency, Each week, the hours are recorded.
WEdate is the week ending date the jobs were done... so,E.g.
Name¦ Job
john ¦ City Rd ¦ 10/12/06
john ¦ City Rd ¦ 17/12/06
john ¦ City Rd ¦ 24/12/06
Max ¦ City Rd ¦ 17/12/06
Max ¦ Hinde Hs ¦ 17/12/06
and Hundreds more, therefore, I need the distinct and show just the Name and Job... I would hide the other fields as they may have an error inside the field as they wouldnt be included in the select statement.
PRODUCING:
Name¦ Job
john ¦ City Rd
Max ¦ City Rd
Max ¦ Hinde Hs

There would be a command Button on top of Name, pressing this, would do a new search of records, this time showing
Name¦ WEdate
john ¦ 10/12/06
john ¦ 17/12/06
john ¦ 24/12/06
Max ¦ 17/12/06

If I knew how to do a recordset query, ie: showing all records that meet a condition
E.g

'---- Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
'this also partially works
rs.FindFirst "[JobsID] = " & Str(Nz(Me![cmbName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Something like that BUT NOT finding the first record (rs.FindFirst) BUT all the records using, maybe Like? and that would fall into a continuous form.

Darin