Select All Records from a Combo Box

[gscma1]

Does anybody no if it's possible to have an option to 'select all records' from a drop down combo box?

I have a database which uses 10 records for one table and 5 from another every time a specific report is run. there are only 7 fields which change everytime and these are stored within another seperate table.

I have a form for users to enter the new data into the 7 new fields:

Bank Balance
Cheques Outstanding
Date ...
Etc...

Also on this form there are combo boxes listing all records in tblStandingOrders and tblOtherPayments.

Is there anyway I can allow the user to choose 'All Records' from the combo boxes?

Therefore the report which is printed can contain all records from tblStandingOrders, tblOther and tblNewData.

Hope this makes sense, any help would be most appreciated.

 

[mp9]

Take a look at

http://www22.brinkster.com/accessory/forms/007.shtml

 

[gscma1]

Thanks for that, ive downloaded the example database and have attempted the same on mine, although i keep getting an error message.

The Record Source 'SELECT "*" AS StandingOrderId, "ALL" AS Description, 0 AS SortOrder FROM tblStandingOrders UNION SELECT StandingOrderId...' specified on this form or report does not exist.

I'm baffled, does anyone have a clue what i might be doing wrong?

 

[mp9]

PLease paste the full text of the SQL you are using as the recordsource.

 

[gscma1]

SELECT "*" AS StandingOrderId, "ALL" AS Description, 0 AS SortOrder FROM tblStandingOrders UNION SELECT StandingOrderId, Description, 1 FROM tblStandingOrders ORDER BY SortOrder, Description;

 

[mp9]

Weird, it looks okay. Is Description a reserved word maybe? Try:

SELECT "*" AS StandingOrderId, "ALL" AS [Description], 0 AS SortOrder FROM tblStandingOrders UNION SELECT StandingOrderId, [Description], 1 FROM tblStandingOrders ORDER BY SortOrder, Description;

And just to confirm, you have this SQL as the row-source of your combo?

And what triggers the error?

 

[gscma1]

does anyone have any ideas of another way to do this? it doesnt seem to want to work when i try this method.

 

[gscma1]

Can anyone help me with this i'm closing to tearing my hair out now, i just can't seem to get this method to work.

does anyone no of a different way of doing this?

 

[dhookom]

mp9 asked you two questions which you seemed to ignore. The [red]?[/red] generally means you should respond to a specific question. The error message you provided suggests your sql is being used as "Record Source ... on this form or report..." not as a Row Source.

Could you provide additional information about your current situation[red]?[/red]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

Do you have to be so rude?! you really do appear to have a chip on your shoulder!

It is in the row source.
and the error triggers when the combo box is selected.

Any help is appreciated

 

[dhookom]

I think it's not appropriate to ask for/expect answers when you don't provide the same to those who are attempting to help you.

Open a new, blank query and view the SQL. Paste in
[tt]
SELECT "*" AS StandingOrderId, "ALL" AS [Description], 0 AS SortOrder
FROM tblStandingOrders
UNION
SELECT StandingOrderId, [Description], 1
FROM tblStandingOrders
ORDER BY SortOrder, Description;
[/tt]

If that doesn't work, try
[tt]
SELECT StandingOrderId, [Description]
FROM tblStandingOrders
UNION
SELECT "*", " ALL"
FROM tblStandingOrders
ORDER BY 2;
[/tt]


Does this work or do you get an error? Do you have any code associated with events of the combo box like the Got Focus event?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gscma1]

Thank you for your reply, i have done as you said however what do I do once i've done this? Do i link my combo box to the query? and if so how do i do this?

I am unable to determine whether i would receive an error as I wasn't sure what to do once I had the query, I am a novice at this.

No associated codes on the got focus or any other event.

Thanks

 

[dhookom]

So, apparently one or the other SQL created the expected results?

The SQL should be used as the Row Source of your combo box.

You stated you are using the combo box to filter records in a report. The method that I recommend for this is similar to:

Dim strWhere as String
strWhere = "1 = 1 "
If Nz(Me.cboOrderID,"*") <> "*" Then
    strWhere = strWhere & " And [OrderID] = " & Me.cboOrderID
End If
DoCmd.OpenReport "rptStandingOrders", acPreview, , strWhere

Your exact code will depend on your control and report names. Also, the above code assumes the OrderID field is numeric. If it is text, you must use:

Dim strWhere as String
strWhere = "1 = 1 "
If Nz(Me.cboOrderID,"*") <> "*" Then
    strWhere = strWhere & " And [OrderID] = '" & Me.cboOrderID & "'"
End If
DoCmd.OpenReport "rptStandingOrders", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]