Sql "select Top 1" as filter for report 'on load' event.

[oldfryeguy]

I'm new to VBA but know a bit of SQL im trying to call a report. i want it to open and see only the highest id from a query. could i make a working "on load" event using the docmd.runsql then select top 1 from queryname? im getting an error ive tried looking at tables (instead of queries) too-no better. i can't find much in the forum on top1 as filter.

 

[Gasman]

Just set the recordsource of the report with the SQL string?

Me.RecordSource = "Select Top 1 StepsID, StepsDate from tbldates ORDER BY StepsID DESC"

 

[CJ_London]

If you get an error, provide the error number and description. Also provide the exact code you are using (i.e. copy and paste it)

It may be your code 'select top 1 from queryname' is not valid sql (the error message will tell you that). At the least it needs to be 'select top 1 * from queryname' and also requires that whatever your query is called, it does not have spaces or non alpha numeric characters in its name. Or it may be you have not included your sql in quotes. Without the error message, we a guessing. And many responders here get tired of having to keep asking and you end up on their ignore list.

Top 1 is not a filter, it is a criteria. It usually requires the query to be sorted in the order you want to get the top 1.

you might find this site a good resource for sql grammar and what it does

SQL SELECT TOP, LIMIT, FETCH FIRST ROWS ONLY, ROWNUM

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

 

[cheekybuddha]

i want it to open and see only the highest id from a query.

Sounds like you really want a MAX() aggregate query rather than a TOP N query:

SELECT
  t.*
FROM YourTable t
INNER JOIN (
  SELECT
    MAX(ID) AS MaxID
  FROM YourTable
) m
        ON t.ID = m.MaxID
;

 

[oldfryeguy]

Just set the recordsource of the report with the SQL string?

Me.RecordSource = "Select Top 1 StepsID, StepsDate from tbldates ORDER BY StepsID DESC"

its probably the correct solution. i was hoping to make a form do something without several more queries or getting a recordset and building the whole form in code. the form is complicated and has a lot of static text with lots of formatting. ( beyond my control ) the dynamic part is by comparison simple. my noob idea is to make the form good at doing one thing - opening the query and getting the highest id and printing it. i sortof have it working by limiting the printer out put to one page. cringe. i'm probly going to have to try this again. thank to you al for your kind help ill carefully review the suggestions

Just set the recordsource of the report with the SQL string?

Me.RecordSource = "Select Top 1 StepsID, StepsDate from tbldates ORDER BY StepsID DESC"

 

[jdraw]

May be helpful to provide an overview description of what you are trying to accomplish. Then, when readers have some context, more details can be added and assist in options/solutions.

 

[Pat Hartman]

its probably the correct solution. i was hoping to make a form do something without several more queries or getting a recordset and building the whole form in code

I don't understand your reluctance to use the suggestion. You are using some other query we know nothing about but somehow you KNOW that you want the first record. We don't know if the other query is the recordsource of the form. We don't know if the form is sitting on the record you want to report on. There may be other alternatives but your question is out of context and you are assuming that you know the best answer and asking how to implement it. So, the responses you are getting are assuming that you know what you are doing and you know the correct solution. This is very different from telling us what you need to do using English and having us offer suggestions as to how to accomplish the task.

 

[CJ_London]

if the error message in post #5 relates to Gasman's suggestion - a) unless he is an all seeing god, it is highly unlikely the code he provided matches your table and field names and b) the error message relates to a click event. As I suggested in post #3, provide the code you are actually using - both vba and sql.

If you are opening the report from vba (via a click button or something) using docmd.openreport, then complete the where parameter with something like "[ID]=" & dmax("ID","myTable")

 

[Gasman]

if the error message in post #5 relates to Gasman's suggestion - a) unless he is an all seeing god, it is highly unlikely the code he provided matches your table and field names and b) the error message relates to a click event. As I suggested in post #3, provide the code you are actually using - both vba and sql.

If you are opening the report from vba (via a click button or something) using docmd.openreport, then complete the where parameter with something like "[ID]=" & dmax("ID","myTable")

It does not.
It was just an example that I knew worked, as I tested it on my diabetes DB.

Sheesh !

 

[cheekybuddha]

Sheesh !

Bit grumpy this morning!

(Not sure what it was about CJL's reply that got your goat though!)

 

[Gasman]

Bit grumpy this morning!

(Not sure what it was about CJL's reply that got your goat though!)

Well I put an example of code that I checked worked before posting it.
I do expect the recipient to identify that the names need to be changed to suit their needs.
This just tells me that none of the code is making any sense to them, it is just words.

Nothing @CJ_London said 'got my goat'
The reply was directed to the O/P, just using CJ's words as that was what my reply was about.
CJ knew it was just an example.

 

[cheekybuddha]

The reply was directed to the O/P, just using CJ's words as that was what my reply was about.
CJ knew it was just an example.

Ah! Misinterpreted who it was aimed at!

 

[oldfryeguy]

Since I'm pretty new, looking for commonly implemented solutions. It seems like my idea is not that. But since JDraw asked, here's an almost brief description:
The output of this basic database is A Bill Of Lading form for a shipping company. they do art transportation - need 3,000 a year. Their bol has several legal disclaimers, checklists and other verbose static info. (all of this is in a table) The most important feature is the BOL# which is to be completely sequential - but orders come in chaotically.

As you would expect, i have all the core data in separate tables and linked appropriately.
A "workspace" table / form that allows "potential" Bols to be created, another form to "make a bol"- with a big special button.
Finally, another table where the Bol# is generated and all the finalized order keys are stored. It is this table that holds the official record of the buisness.

Now here's the hard (for me) bit, on this "make bol" form there is a button which 1 transfers all the important keys from the workspace to the bol table, (i hope to) 2 print a report of the newly minted bol and 3 the former workspace order is made inactive and disappears from the workspace.

The printing of the Report (Bol) is the last thing for me to code. Ive managed to name the files based on variables, print to pdf, in a folder of my choosing, set conditions on clicks, hide/show baised on conditions, select and add info on click. huge accomplishments for me! but when it prints the report - its the whole table each time.

ive almost found solutions - all involve using the current form id to select the id for the query the report is based on. however, im not "on" the form or table driving the report. idon't know what the id will be on the bol table it increments to the next. printing a form from anywhere is easy- but doesn't do what i want it to.

so, dim a recordset - right?

 

[cheekybuddha]

so, dim a recordset - right?

Probably not.

but when it prints the report - its the whole table each time.

How do you open the report?

Do you use DoCmd.OpenReport ... ?

If so, that method can take a WHERE clause argument to limit the recordset being opened.

Something like:

DoCmd.OpenReport "YourReportName", , , "BOL_ID = 12345"
Reports.YourReportName.PrintOut

 

[oldfryeguy]

Probably not.


How do you open the report?

Do you use DoCmd.OpenReport ... ?

If so, that method can take a WHERE clause argument to limit the recordset being opened.

Something like:

DoCmd.OpenReport "YourReportName", , , "BOL_ID = 12345"
Reports.YourReportName.PrintOut

I'm traveling. Thanks so much! Can't wait to try this.

 

[CJ_London]

Suggested in post #8

 

[oldfryeguy]

First, thanks for the help.
I do use the Docmd.openreport. as for the where clause... we come back to my original post. I'd like the report to open the highest number (Id or date) on a table. Can a where clause do this?

Here's why - sorry if I didn't make it clear, I don't know the id of bol. Maybe I'm wrong but I want the bol table disconnected from user input. Only the button itself can send the keys to it and this increments the bol# . I'll change method if needed. But honestly I thought it was a easy thing to get the top result.

 

[oldfryeguy]

Sounds like you really want a MAX() aggregate query rather than a TOP N query:

SELECT
  t.*
FROM YourTable t
INNER JOIN (SELECtMAX(ID) AS MaxID
  FROM YourTable) m
        ON t.ID = m.MaxID
;

Thanks for this. I sorta skipped it because I didn't want to join it. But, I need to unpack this more carefully and read about max().

 

[cheekybuddha]

See @CJ_London's colouring in Post #8

If you are opening the report from vba (via a click button or something) using docmd.openreport, then complete the where parameter with something like "[ID]=" & dmax("ID","myTable")