I can't get acNewRec to work in a list box in a subform

[Douglas J Steele]

Union queries only accept a single ORDER BY, which applies to the entire
query, not just to subqueries. Get rid of the parentheses.

SELECT 0, " - All -"
FROM MasterEvent
UNION
SELECT MasterEventID, [MasterLocationName] & ": " & [MasterEventDate]
FROM MasterEvent
ORDER BY 1 DESC;

Yes, this means that your "All" row will be at the end. If that's a problem,
try adding a 3rd field for sort purposes only. For the first subquery, you'd
put a very large number there, whereas you'd repeat MasterEventID in the
second subquery:

SELECT 0, " - All -", 99999999
FROM MasterEvent
UNION SELECT MasterEventID, [MasterLocationName] & ": " & [MasterEventDate],
MasterEventID As SortKey
FROM MasterEvent
ORDER BY 3 DESC;

 

[David Wilch]

Doug,

Thanks.
The second choice will work better for testing and learning.
I'll use the first in the final.

Questions:

I thought I understood SELECT 0 but I'm lost when I read
SELECT 0, " - All -", 99999999

Can you translate it.

What is the difference etween ORDER BY 1 and ORDER BY 3?

What is sortkey?

Thanks,

David

 

[Douglas J Steele]

In the 2nd approach, I added a 3rd column for the express purposes of
sorting.

Your first subquery was

SELECT 0, " - All -" FROM MasterEvent

I added 99999999 as the value for the 3rd column (so that it would show up
first in a descending sort order)

SELECT 0, " - All -", 99999999 FROM MasterEvent

Since I was adding the same column (MasterEventID) twice to the second
subquery, I chose to rename the 2nd occurrence to SortKey so that there
wouldn't be any issues referring to. Not really necessary (and, in fact, it
doesn't actually do anything in this case: a Union query gets it field names
from the first subquery, and since none of the fields are named in the first
subquery, if you were to run that SQL as a query, you'd find that the 3
fields would likely be named Expr1, Expr2 and Expr3.

ORDER BY 1 sorts by the first column, ORDER BY 3 sorts by the 3rd column.

 

[David Wilch]

Next I want to set the Default Value to the Maximum MasterEventID. I tried
=Max([MasterEvent.MasterEventID])

That didn't work.

??????

 

[David Wilch]

Next I want to set the Default Value to the Maximum MasterEventID. I tried
=Max([MasterEvent.MasterEventID])

That didn't work.

??????

 

[Steve Schapel]

Try it like this...
DMax("[MasterEventID]","MasterEvent")

 

[David Wilch]

Try it like this...
DMax("[MasterEventID]","MasterEvent")

Worked great!

Steve, I converted the other listboxes (for reports) to comboboxes.



My next project with this db is a tournament registration section.

I am looking for some opinions as to the structure of this section: subform,
individual form, or something I'm not aware of.

The registration section I have now is set up for events such as workshops
and scrimmages.

For tournaments, each event will have different divisions.
So, I thought that another subform in continuous view would be well suited.
This new subform would have to be slightly bigger (wider) than the existing
one to accomadate the division descriptions.

My first thought is to toggle to switch subforms. Is that possible? This is
my favorite idea.

My second thought is to have an individual form to open and stay open. Does
access allows forms to be "always on top"? Will this create onfocus or any
other problems ?

The other option is to place a second subform on the main form. This is
less attractive due to the unused space of the second subform.

Any other ideas that I might be missing?

 

[David Wilch]

Steve,

After doing my homework, I've decided on option #1, 2 subforms with a toggle.


I am having a little problem however.

Right now I have 2 identical subforms open and referencing the same table at
the same time. They work fine if the MasterSel comboboxes in each are set
the same.

When I select a different event in the tournament subform, the events do now
show in this subform.

What are the possible problems?