Keep specific record from table on form that does not match search criteria (1 Viewer)

[Steven_Bash]

Hey y’all !
I have this form that displays records from a table based on a combo search box. Once I click a certain criteria they all display correctly. I need one record to always show on the form and this issue is that it does not match the criteria. So how can I add that one record to the form ? Or call it in. Thanks!

 

[bastanu]

Can you show how your form looks? I assume you have multiple combo-boxes that are referenced in the query behind the form (the form record source). If that is the case then simply add a second line of criteria (such as ID = YourRecordThatMustAlwaysShowID) on the next row down (OR). If the combo-boxes filter the recordset you will need to look at the code and modify the filter accordingly ( add an OR part to the filter).

Cheers,
Vlad

 

[Steven_Bash]

Thanks Vlad, I cannot literally show you bc it’s proprietary info on there but it’s a continuous form that only has one combo box which is the “year/month” to search. So then the form pulls up all the records that has that date in one of the fields. This specific record does not have a date bc it’s year round. So how can I implement that record into the form for each month/search ?

 

[isladogs]

You could set your combo to show all the records for that filter value with a UNION to the other year round record

 

[Steven_Bash]

You could set your combo to show all the records for that filter value with a UNION to the other year round record

That sounds real good. How would I do that union method ridders? Once I select the filter I click a button to search. If that helps at all. Thanks

 

[isladogs]

That sounds real good. How would I do that union method ridders? Once I select the filter I click a button to search. If that helps at all. Thanks

A union query is just 2 (or more) queries joined together with the word UNION.
Make each query separately making sure they have the same number of fields and datatypes in the same order. Add 'dummy' fields to one or both if necessary. Then change to SQL view and paste together with UNION between.
The query headings will be those for the first part of the query

That's probably as clear as mud but it's easy to do.
See the attached link for more info https://support.office.com/en-gb/article/Use-a-union-query-to-combine-multiple-queries-into-a-single-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e

 

[Steven_Bash]

Gotcha ridders. okay I can see that as mud haha. So I see the two queries together and union them. Yet how would I bring that constant record to show up if it still doesn’t meet the search criteria ?

 

[bastanu]

Steven,
The first thing to do is to see how the combo-box works. It can work in two ways:
1 The record source of the form is a query that makes reference to your combo-box on the form: SELECT * FROM tblMyRecords WHERE MyYearMonth = [Forms]![MyForm]![MyComboBox]; in this case you simply edit the query that feeds the form and add your criteria for the odd record on a new row (which makes it an OR); in your case your record has no date so you put Is Null on the new row (or alternatively you add OR Is Null to the first criteria).
2 Your button that you push has code in the OnClick event that applies a filter to the form; you need to manipulate that filter and add OR Is Null to it.

Cheers,
Vlad

 

[June7]

What code are you using to apply the filter criteria? Maybe just expand with an OR parameter.

 

[isladogs]

Ok let me try again.
Reading this again, am I right in thinking you don't want the extra record showing in your combo?
If so, just use a simple select query for your combo row source.

Then in your combo after update event, use the union query to provide the output on the form

BUT it may be that using OR as described in the last two posts will work equally well

 

[bastanu]

Post #2

Can you show how your form looks? I assume you have multiple combo-boxes that are referenced in the query behind the form (the form record source). If that is the case then simply add a second line of criteria (such as ID = YourRecordThatMustAlwaysShowID) on the next row down (OR). If the combo-boxes filter the recordset you will need to look at the code and modify the filter accordingly ( add an OR part to the filter).

Cheers,
Vlad

I think a simple OR would do the trick...

 

[Steven_Bash]

June7: so I have the combo box grabbing the dates from a table. Then a search button that goes to macro builder to apply filter with a where condition to grab the specified records. The or parameter does sound well but how would I put that bc the constant record does not have a date.

Ridders: that is correct. I’d rather not use union query bc ive never used it and I’m on a time crunch. So how could I say for the where condition in my macro builder “or as well grab the constant record I want shown”?

 

[bastanu]

You need to look at the VBA code in the OnClick event of the button you push.

 

[Steven_Bash]

Post #2


I think a simple OR would do the trick...

How would I say that on the macro builder ? Right now I have [Date] Like “*” & [Forms]![Searchmonthreports]![combobox] & “*”

 

[bastanu]

[Date] Like “*” & [Forms]![Searchmonthreports]![combobox] & “*” OR Is Null

 

[Steven_Bash]

[Date] Like “*” & [Forms]![Searchmonthreports]![combobox] & “*” OR Is Null

Thank youuuu. So simple ugh ! Okay hopefully this simple as well: since that record occurs every month, in the main table that the form is looking at, there’s multiple instances of that constant record(once a month). How do I just single it out to show once ?

 

[bastanu]

Steven, now it can get quite complicated.

Are those records true duplicates (which would suggest some bad table design) or do they have at least one field unique (primary key)? You should have an autonumber or some sort of timestamp at least that would be different.

Do you need them all in the table or can you delete all the older ones and leave just the most recent one?

Do you need to edit the records in the form?

Ideally you can remove all but the most recent one; if that is not an option you have to modify the record source of the form to only include one of them (basically have the form use a query instead of the table, and in the query you add criteria to remove all but one of these records. Not seeing your data and your specific is making it hard for me to give any more suggestions (you can use a totals query using MAX on the autonumber or timestamp field to get the most recent then you use it in an equijoin to only select that record, but this would make the query not updatable.....).

Cheers,
Vlad

 

[Steven_Bash]

Actually Vlad, I just created a select query from that main table and set unique values to yes and changed the record source of the form to that query and it works. Thank you so much seriously !

 

[bastanu]

Great to hear Steven, good luck with your project.
Cheers,
Vlad