show last n records - n is unbound txtbox value

[8l2255]

I have a form with

CategoryID - frn key
ProductID - frn key
DateAcquired - enter date
StatusID - frn key
TxtN - unbound text box
create - button
openform - button


The idea is to fill out all those fields and on clicking create, txtN (unbound txtbox) amount of records with identical catid, prodid, date aq and statid appear in the itemtbl.

On clicking a second button - openform (this will eventually be on the same button... one step at a time) "MultipleItemAddDetails" should open, I want it to ONLY show the last txtN amount of records created... i have set up qry ststupid which sorts the itemtbl by itemid desc. so if i could only tell it to open up with txtN from the TOP and have it work!!!

I have included my code below. Thanks!

I get runtime error 3075 Syntax error missing operator in query expression 'TOP4* '

'Button to open form
Private Sub Openform_Click()
Dim strSQL As String
strSQL = [COLOR=darkred][B]"SELECT TOP " & Me.TxtN & " " & " itemid FROM ststupid"[/B][/COLOR]
DoCmd.openform "multipleitemadddetails", , , , , , strSQL
End Sub
 
'on open event of multipleitemadddetails
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
End Sub
 
'sql of qry ststupid
SELECT itemtbltest.ItemID, itemtbltest.CategoryID, itemtbltest.ProductID, 
itemtbltest.Serial_Number, itemtbltest.Date_Aquired, itemtbltest.StatusID,
itemtbltest.AssetNo, itemtbltest.IMEI, itemtbltest.SIMCardNo,
 itemtbltest.SIMPhNo, itemtbltest.PlanID, itemtbltest.PIN, itemtbltest.PUK,
 itemtbltest.Comments
FROM itemtbltest
ORDER BY itemtbltest.ItemID DESC;

Cheers,
Lou

 

[ajetrumpet]

the correct syntax is this I think:

strSQL = "SELECT TOP " & Me.TxtN & " " & " itemid FROM ststupid"


"SELECT TOP '" & cstr(Me.TxtN) & "' [itemid] FROM ststupid"

or maybe this:


"SELECT TOP " & cstr(Me.TxtN) & " [itemid] FROM ststupid"

also, i'm not sure what argument in the docmd you are using for the variable, but if it's the args portion, don't you just need the WHERE clause? just spitballing here

 

[smig]

if it won't work you can fill a table in code and add a "counter" field, raising it by 1 for every record you put in this table.


some more info taken from http://www.blueclaw-db.com/accessquerysql/select_top.htm

Well, in case you want to know how to do it here is the select top records query solution:

1) Create a query (Query1) to get the top three records. We are interested in field called Submit_Date from a table called M_Revisions:​

Select Top 3 M_Revisions.Submit_Date from M_Revisions
Order by M_Revisions.Submit_Date;​

2) Use Query1 as input to a new query:​

Select Top 1 Query1.Submit_Date from Query1
Order by Query1.Submit_Date DESC;​

Now you have the 3rd submit date. Note that sorting the Query1 records in descending order makes the 3rd record go to the top.
You can use Select Top query to retrieve any number of records such as:

• Select Top 10​
• Select Top 100​
• Select Top 123​

As shown above you can use the sort option (Group By) to arrange the records in the order you want to select the appropriate top records. You can combine this with the scalar query option to perform complex operations with your queries.

The select top query is not without its problems. We have noticed a bug in the select top predicate query when you are selecting from long text fields where the first 20 to 30 characters are the same in the text data. For instance, if you use Select Top 100 you may get back 110 records.​

Scalar Query

Access scalar query allows you to do in one SQL statement what you are used to doing in two or more queries.

This function allows retrieval of single values from a table, usually aggregate functions, from within the from clause.

Get individual values while at the same time getting max, min, avg, etc values from the same source without having to use the Group By clause... this greatly simplifies query design. Here's the setup for our Access subquery example:

Our goal is the retrieve SSN, Pay_Rate, Max Pay_Rate, Min Pay_Rate, and calculate each employees' percent of maximum pay rate.
See the following SQL subquery statement:

Don't bother trying to create this in the design grid. You must get into SQL view. The key point of the query is:
[select max(pay_rate) as Max_R from m_emp_pay]. as Q_Max There are two aliases in this scalar subquery - Max_R for the field and Q_Max for the source name. Not that an Access SQL alias is an typically an abbreviation for the table or calculated field name. See how the alias is used in the Select clause of the query. No group by required!! Therefore you get the individual pay rates for employees while, at the same time, retrieving min, max, and most importantly the percent of max.
See the results of the scalar subquery below:

The main restriction with scalar subquery SQL statement is that the function can only return a single value, although you can have multiple subquery statements in one main query.
​

 

[MStef]

When you work with the TOP (in query), you have to know next;
1) If there is a one column with SORT (asc or desc), the TOP refer on this column, (independenty where the column is in the query).
2) If ther is a more column with SORT, in that case the TOP refer on first column (with sort) right of TOP.
3) If ther is no column with SORT , in that case the TOP refer on Primary Key.

 

[MStef]

Look at "DemoTopNQryA2000.mdb" (attachment, zip).
Open Form1 and try.