Any M$ Access experts/guru here

[Hawkeye-X]

That knows how to display data in a form?

PM me, and i'll explain what I'm trying to do.

[Misunderestimator]

1. Or you could explain what you're trying to do to get the...

maximum response. I might be able to help... what are you trying to do?

On edit... but I'm running to a meeting right now :) I'll check later.

[Hawkeye-X]

2. Ok here it is..

Screenshot:



What I am trying to do is display, on form, the last five entries of the data on the blowers form. (It's one of six that I have to do..)

Hawkeye-X

[Misunderestimator]

3. So, you want to limit the user's ability to

browse records to just the last five from this form? Is there any other criteria for limiting the selection... I think I'm not understanding clearly. Simple answer is to create a query on the target table and link that query to this form... but I'm sure you already know that.

(I know I'm not getting what you're trying to do though :) )

[Hawkeye-X]

5. Yes.

The last five data entries are displayed. It is limited to what they see so they can make sure that they have the right information when entering the next data.

Hawkeye-X

[Misunderestimator]

6. So is your form attached to a query that selects the data....

if so... can you paste the SQL?

[Hawkeye-X]

7. No..

How do I do that?

FYI, I'm a M$ Access novice. I know the basics. That's all.

[Misunderestimator]

10. Ah.... than this thread will turn into a novel...

It's a bit too much to explain, but the only way you can limit the selection of a table on a form is to do it in an underlying SQL. You can experiment and play around with simple queries, then when you choose the default table... choose the query instead. I'm not sure how you're going to limit to just the LAST five rows of data without any other criteria... unless you just sort the data descending on a key field that would act as your counter. You would have to get more into the SQL statement to limit to just a specific rowcount.

Try it first though with a simple query, like SELECT * from TABLE WHERE STATE = "NY"... then attach that query to the form and see how it limits the data to just NY.

Wish I could help more, but feel free to ask more. :)

[TriadLeftist]

4. Data connection?

Did you set one up that is attached to the form?

[LynneSin]

8. I know how to do it

But I'm not sure if I can explain it in a way that you would now how to do it

[Hawkeye-X]

9. How about if I sent the whole thing over?

:evilgrin:

[Misunderestimator]

11. LOL.... If you can pay me enough.... I'm game :)

[orthogonal]

12. Here's the SQL

If you were prescient enough to assign a sequential synthetic key (
"auto-increment" or whatever access calls it) something along the lines of:

set rowcount 5
-- probably optional, if the form is designed to only display five rows

select * from blowers
order by YOUR_SEQUENTIAL_ROW_ID_HERE desc

[orthogonal]

13. No key column? I can still help you

If you didn't set up a sequential row id, I'd suggest re-designing the table to include one.

If you make that key the first column, you can copy the data pretty simply:

insert new_table
select * from existing_blowers_table

[Hawkeye-X]

16. I just did.

:)

I added "sequence" with autonumber.

:)

Hawkeye-X

[orthogonal]

20. Being pedantic

Incidentally, if you're trying to display the last five readings (that the last five in temporal sequence), you should have a field/column that captures the date (datetime) of the reading, and order by on that, rather than autoincrement.

You mentioned that you need to do "six more" of these; if it's the same reading for different locations, are all readings in one table with a column indicating location (probably the best approach) or one table per location (bad and unmaintainable)?

If you did it right and all readings are in one table, but you only want to show that last five reading for a particular location, you'll need a where clause too (or better, but maybe more than you care to do, views for each location.

Here's how I'd do it, if new locations were not often added:

create table location ( id AUTOINCREMENT, name varchar( 255 ) )

create table readings ( id AUTOINCREMENT, when_taken datetime, location_id int, data_1 whatever ... )

create view readings_location_1 as select * from readings where location_id = 1

Create view readings_location_2 as select * from readings where location_id = 2

[Hawkeye-X]

21. All i wanted to do now is..

put the query results in the M$ Access form. How do I go about doing that?

Thanks!

[orthogonal]

22. See my post #19, this thread

But I already answered that in post #19.

[Hawkeye-X]

14. How do I set the rowcount?

tHanks!

[Hawkeye-X]

17. Great..

Now how do I put the result of the query in the form?

Thanks again! You guys are the greatest!

[orthogonal]

19. Try this

Ok, this is a bit more difficult, because you have to use the GUI.

First, just design the form as you would normally do, as if you just wanted to select from the table without worrying about which five rows you'd get back.

Do this however you normally do it (you might use the GUI; I'd type the SQL, whichever, make yourself happy).

That that, and make sure you get rows back (assuming the table has any rows in it, of course).

Next, there's a menu option to edit the text version of the SQL.

Go in to the SQL editor, and put a hard-return (enter key) after the end of it.

if there's not already a bit that starts out "order by", and at the end, on the new line:
"order by AUTO_INCREMENT_COLUMN_NAME desc"

if there is an "order by" clause, keep it, but insert
"AUTO_INCREMENT_COLUMN_NAME desc," as the first operand.

That is if you have:
"order by foo, bar"

turn it into
"order by AUTO_INCREMENT_COLUMN_NAME desc, foo, bar"

[Cocoa]

15. throw a "top 5" clause in your SQL

and then an "order by desc"

"select top 5 ... order by ... desc"

[Hawkeye-X]

18. kick

still waiting for the answer on how to insert the query results into the form..

Hawkeye-X

[Misunderestimator]

23. Here's from MS Access Help on the subject:

Or you can go to the help menu yourself and type "Recordsource" (which is a little easier to comprehend, since it contains links)... Just bind the new query you built as the recordsource to the form.

-------------------- Quote
RecordSource Property

You can use the RecordSource property to specify the source of the data for a form or report. You can display data from a table, query, or SQL statement. For example, to display and edit data from the Employees table in a form, you set the form's RecordSource property to Employees. Then you can bind controls on the form or report to specific fields in the Employees table by setting the control's ControlSource property to the name of a field in the table. For example, you can bind a control to the LastName field in the Employees table by setting the control's ControlSource property to LastName.

Setting

The RecordSource property setting can be a table name, a query name, or an SQL statement. For example, you can use the following settings.

Sample setting Description
Employees A table name specifying the Employees table as the source of data.
SELECT Orders!OrderDate FROM Orders; An SQL statement specifying the OrderDate field on the Orders table as the source of data. You can bind a control on the form or report to the OrderDate field in the Orders table by setting the control's ControlSource property to OrderDate.


You can set the RecordSource property by using the form's or report's property sheet, a macro, or Visual Basic.

In Visual Basic, use a string expression to set this property.

Note Changing the record source of an open form or report causes an automatic requery of the underlying data. If a form's Recordset property is set at runtime, the form's RecordSource property is updated.

Remarks

After you have created a form or report, you can change its source of data by changing the RecordSource property. The RecordSource property is also useful if you want to create a reusable form or report. For example, you could create a form that incorporates a standard design, then copy the form and change the RecordSource property to display data from a different table, query, or SQL statement.

Limiting the number of records contained in a form's record source can enhance performance, especially when your application is running on a network. For example, you can set a form's RecordSource property to an SQL statement that returns a single record and change the form's record source depending on criteria selected by the user.

--------------------End Quote