Form and dates problem

[Lindie]

We have an Access sickness tracker. The form FrmDetails&sick shows a subform with the dates of the sickness absences.
The dates are not showing in chronological order. I just cannot work out why.
Any help would be greatly valued.
Lindie

[missinglinq]

As Dorothy Parker said about the city of Seattle, "There's no there there!"

The zip file you posted is empty! Try again!

Welcome to Bytes!

Linq ;0)>

[Lindie]

OOps- Hope it works this time.
thanks
L

[PianoMan64]

Hey Lindie,

Since you're making things really difficult for yourself by basing your forms on a table instead of a query, you limit yourself to what you can sort and limit the records returned. This is the reason sometimes you want to base your form on a query or a SELECT Statement like I've done.

I'll attach your database again, and look in the subform for the recordsource, and you'll see what I did. Just go into the query designer mode, and you can see what it looks like?

Hope that helps,

Joe P.

[Lindie]

Thanks Joe, that does the trick. So you have created a querie and based the form on that.
You say that sometimes one wants to dothis. When it is better then to base the form on a table?
I still don't understand why the dates were not chronological- is there a simple explanation?
Lindie

[missinglinq]

You always should base a form on a Query, rather than a Table, for the very reason you've seen here; a Query allows you to manipulate the data, such as sorting by the dates!


Linq ;0)>

[PianoMan64]

Hey Lindie,

The simple explanation is this. If you're going to want to manipulate the data in any order other than what it naturally is in the table, which is most of the time, you're going to want to base your form on something other than a table.

If the form that you're attaching to a table is simply for data input, and nothing more, then you would simply bind it to the table directly without a query.

That's the simple explanation of what I'm saying.

If you look in the record source of the form, you'll see that there is a SELECT Statement in there. That way, you don't have to create a query, but you would create the fields, and order that you want a form based on.

If you have any more questions, please feel free to ask,

Hope that helps,

Joe P.

[Lindie]

Ah, its beginning to sink in. the Select statement; I had not spotted that.

So, does that then mimic a Query? I already have a Query; QryAll. I have just tried to base the subform on that. I get an error statement saying that
"The specified field 'StaffID' could refer to more than one table listed in the FROM clause of your SQL statement."

I cannot see how to fix that. Where amI going wrong?
Lindie

[ChipR]

In your query, you must have 2 tables with the StaffID field. Change the one query field to say TableName.StaffID, and it won't gripe at you anymore.

[Lindie]

Sorry to get back after such a long time. I just looked at your answer again.
I'm lost! I cannot work out what is meant by '..Change the one query field..'.
Which filed?
Happy 2009
L

[ChipR]

That error suggests that your SQL statement has

SELECT StaffID, ..., StaffID FROM Table1, Table2

so you are trying to select the StaffID from 2 different tables. You can change it to

SELECT Table1.StaffID as NewName, ..., Table2.StaffID as OtherName ...

That way you can specify which table it's from and give the fields different names.