Need to filter rows on a Continuous Form (1 Viewer)

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
I have a continuous form. The form displays rows from a query. An important field is the WorkNbr, which is a number field. I need to find a way to display only one WorkNbr row if there is more than one WorkNbr in the Query. I can't use a Totals query because the user needs to input data into three other fields; PayAmt, CheckNbr and WorkDate. An alternative to filtering out a row in the query if there is more than one WorkNbr, would be to set the .visible property of a row to False if it was the second or more occurrence of a WorkNbr. Any ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,358
Hi. You could right-click on the WorkNbr and select Filter By Selection or use VBA code.
 

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
Hi dbg. As far as I can tell, right-clicking on the WorkNbr field doesn't allow you to filter out a record if it is the second or more occurrence of that WorkNbr, number.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,358
Hi dbg. As far as I can tell, right-clicking on the WorkNbr field doesn't allow you to filter out a record if it is the second or more occurrence of that WorkNbr, number.
Is there an ID field you can use for the filter? If not, what is the purpose of filtering out the rest of the "duplicate" rows? If to only make sure the user is entering data on the correct row, maybe you can highlight the current row.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2013
Messages
16,553
think the question is simply not clear enough and we are guessing at what it means

suggest provide some example data and from that a) what you want to happen and b) when you want it to happen (i.e, after a user selects a row?)
 

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
There is an ID field, but since the ID field is different for each row I'm back to trying to figure out how do determine if a row is the second occurence of a WorkNbr. CJ London, maybe the attached PDF will help.
 

Attachments

  • Screenshots of Work Completed Form.pdf
    129.7 KB · Views: 73

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,358
There is an ID field, but since the ID field is different for each row I'm back to trying to figure out how do determine if a row is the second occurence of a WorkNbr. CJ London, maybe the attached PDF will help.
Can't you use the Job Order Number to determine that?
 

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
Job Order numbers can repeat, but not for the same employee. I don't think that will help. The issue in my mind keeps going back to how do I find the first, (or last, or unique), WorkNbr for the query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,358
Job Order numbers can repeat, but not for the same employee. I don't think that will help. The issue in my mind keeps going back to how do I find the first, (or last, or unique), WorkNbr for the query.
In my mind, something like JobOrder = 2 AND WorkNbr="xxx" should give me a specific row, no?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2013
Messages
16,553
you can only determine second occurrence by specifying an order - and since there is nothing that specifies a real order such as a timestamp all you can fall back on is the ID - which may or may not be the order you want. If it doesn't matter which record the boss completes, that should suffice.

I would suggest that the date completed field should be disabled using conditional formatting where the order record has a higher ID

the conditional rule would be something like

dcount("ID","myTable","WorksOrder=" & [WorksOrder] & " AND ID>" & ID)

if you are using the buttons then you would need to change them to textboxes formatted to look like buttons since buttons cannot be conditionally formatted.
 

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
CJL, thank you, your suggestion worked. It resulted in either a 0 or a 1 for the "DCount" calculation which then made it easy to set criteria = 0. Thank you very much!
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 19, 2013
Messages
16,553
for your information False=0 whilst true is any other number - so even if dcount returned 2, 3 or more they would still be true
 

chuckcoleman

Registered User.
Local time
Today, 05:30
Joined
Aug 20, 2010
Messages
357
for your information False=0 whilst true is any other number - so even if dcount returned 2, 3 or more they would still be true
Thanks. I'll do some more testing but I think your suggestion has solved my problem. Now, I'll go wash my hands and keep my distance, other than from my wife and dog. Take care.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:30
Joined
Oct 29, 2018
Messages
21,358
Thanks. I'll do some more testing but I think your suggestion has solved my problem. Now, I'll go wash my hands and keep my distance, other than from my wife and dog. Take care.
Hi Chuck. Glad to hear you got it sorted out. Good luck!
 

Users who are viewing this thread

Top Bottom