Return all records in report

[halt4814]

Hey all,



I have a form, a report, and a query.

When an ID is typed in a text box on the form, the query outputs the correct record with that ID. However, I cannot figure out how to make the query output all records when that text box is null.

Can anyone help me out here?

I have tried to type an "*" in the text box as well, but no records populate in the query when doing so either.

[Robeen]

How are you getting the record for an ID onto the Form?
Are you using VBA to get the correct record onto the Form when you put a valid ID into the TextBox?
Or are you using built-in MS Access functionality?

[halt4814]

I am using a combo box, but when the form first populates that box is blank, so I am just typing in "*".

[Robeen]

Your first post said you are using a TextBox. You didn't mention a ComboBox.
??
Either way - my guess is that wherever you are putting the ID - Access is searching for the value you put in that control [TextBox/ComboBox].
So - if you put a valid ID [Eg: 1234] . . . Access searches for a record with the ID 1234.
If you leave it blank - Access will look for an ID that has no value - or a Null value.
If you put a * in there - Access will look for a record with an ID of * . . .

Without knowing in detail how your Form works, I'm not sure how to advise but - What I've done in the past has been to put some simple VBA code in that, if there is a valid ID, it will retrieve the record with that ID.
If the ID is not found [Eg: * {if * is not an ID}] - then you can change the Record Source property to "Select * From [TableName]".

When you say [original post]

the query outputs the correct record

- do you mean that the Report opens up with the correct record?

I might be able to give more detailed help if you can give more information about how your db is working.

[halt4814]

Sorry, yes, it is a combo box on the form, not a text box.
And yes, that is what I meant by the query outputs the correct record.

The combo box is simply connected to a field in a table. This is how all the ID's are populated in the combo box.

So when you say to change the record source property to "Select * FROM [TableName]", where I am supposed to do that?

[Robeen]

With the ComboBox 'bound' to that field, all you can do with it is get Access to return the record with the ID you put in there.

One way to get Access to open the report with ALL records would be to add a control [Command Button . . . CheckBox . . .] and call it 'ShowAllRecords' [for instance].
Create a "Select * . . ." query for your table. Name it "QrySelectAll" [for example].
Behind the cmd button - Properties -> Events -> On Click --> Click the [...] to the right -> and choose 'Code Builder'.

In the Code Window that opens . . . put this Code [that says 'Open YourReportName using the data that QrySelectAll returns']:
The last parameter - "QrySelectAll" . . . is in the 'OpenArgs' position of the OpenReport action.

DoCmd.OpenReport "YourReportName", acViewPreview, , , acWindowNormal, "QrySelectAll"

Open the report in design View - Properties -> Event -> On Open . . . Code Builder . . . put in this Code:
This code says 'If there is an OpenArgs being passed to the report then use it as the recordsource'.

If Me.OpenArgs <> vbNullString Then
      Me.RecordSource = Me.OpenArgs
End If

Hope this helps!