Need to filter rows on a Continuous Form

[chuckcoleman]

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]

Hi. You could right-click on the WorkNbr and select Filter By Selection or use VBA code.

 

[chuckcoleman]

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]

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]

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]

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.

 

[theDBguy]

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]

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]

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]

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]

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]

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]

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]

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!