need some help pls with using a combobox as criteria for a query

[martinr]

i've built a form with a Combo box that works well as the criteria selection tool, to run a query that returns and displays the data in a subform
and also serves as the recordSource for a report.

The query gets it's criteria from the combobox: eg Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue]

This works well but the User also wants the option to clear the Combobox and see ALL Records (ie Unfiltered data) and
view/print the corresponding report with ALL records listed.

I was able to clear the contents of the Combobox with the simple commands:
Me.ComboType.SetFocus
Me.ComboType.Text = ""

I've tried an iif statement in the query to show all records when the combobox is Empty but it returns 0 records:
the sql WHERE Clause is something like:
Where (TblTransactions.Type) =IIf([Forms]![FrmTransactions02]![ComboType].[Text]="",([TblTransactions].[Type]) Like "*",[Forms]![FrmTransactions02]![Textvalue]))

Is there a way to get this to work or Is there a better way to toggle the query from displaying selected records and
ALL records based on the form Controls???

thanks for any suggestions offered!

 

[arnelgp]

Where (TblTransactions.Type) =IIf(Len([Forms]![FrmTransactions02]![ComboType] & "")=0, [Type],[Forms]![FrmTransactions02]![ComboType])

 

[oleronesoftwares]

Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or Is Not Null(TblTransactions.Type)

In the image file attached, two records exists in the table, and it returns all two records, this is a guide, you can apply same principle to yours.

 

[martinr]

The way to clear a field is:

Me.ComboType = Null

"" is a ZeroLengthString. If your combo is supposed to return a numeric value, this will be a problem.

The .text property is only used when a control has the focus. Each control has three buffers:
.OldValue = original value when the record became current. It will be the saved value from the table or it will be null if this is a new record.
.text = the holding area that collects each character as it is being typed. In most versions of Access, the .text property is only available when the control has the focus.
.value = the default property and so it is frequently omitted for convenience. Me.SomeControl = Me.SomeControl.Value. This is the value of the control at all other times.

.OldValue = .Value when a record obtains the focus. Between the form's BeforeUpdate event and the form's AfterUpdate event the .OldValue is changed to the .Value so in the AfterUpdate event, the two buffers again hold identical values.


Not quite. This should be:
Where (TblTransactions.Type) =[Forms]![FrmTransactions]![Textvalue] Or [Forms]![FrmTransactions]![Textvalue] Is Null) if you want to select all rows when the user doesn't provide a specific value for the criteria.

Thanks Pat,

Using your code and it's working well.

btw, What is the best way to check if the ComboBox is cleared or has a
Value selected – would any of these statements work?

• If Me.ComboBox1 = Null
• If Me.ComboBox1 <> Null
• If Me.ComboBox1.value = Null
• If Me.ComboBox1 Is Null
• If Me.ComboBox1 Is Not Null

 

[The_Doc_Man]

The best/most reliable way to verify a combo box has no selection (assuming that the combo DOES NOT support "Not in list" events, i.e. no type-in selections that aren't already in the table) is to do a test of Me.ComboBox1.ListIndex = -1, because that would say that nothing has been selected. Also works for listboxes in single-select mode. Combo boxes start zero-based. That is, the first row of possible selections in a combo box is 0 and you have numbers from 0 to n-1 representing the various rows you could have selected. So the non-selected index is -1, not 0.

 

[martinr]

Thanks Pat & Doc_Man for your suggestions and explanations.