TopValue Property in a Query Question... 1

[hathman23]

Hey there, any help would be appreciated. I have some queries that update different date fields. I have this one query that ultimately I would like the user to input the bottom number of records to have it update the date field.

Basically say we have like 4000 employees and each of these employees has a seniority score. For a certain amount of people with the lowest score letters will be sent out to those people. That number of letters to be sent out is flexible depending on the time of the month. How would the user get a message box (or something like it) that asked them for how many of the bottom scores they would like the letter date field updated.

I can't figure out how to set the TopValue property on the query from some sort of input parameter. I don't want the user to go into the query and set that property is basically what I am saying.

Any and all help would be greatly appreciated.

 

[scriverb]

Give this a try. Create your query to select the TOP 100 with all the fields necessary. Save and Name the query. Copy the SQL and then use it to update the code below. I am creating a new SQL string with the appropriate number of TOP records indicated from the user in the prompt. Update the red names with your query name.

Dim db as DAO.database
Set db = CurrentDB
db.QueryDefs("qryYourQueryName&quot.SQL = "Select TOP " & _
InputBox("Enter # of letters to be sent this month: ", "Parameter Prompt&quot & _
" A.Name, A.Address, A.City, A.State, A.ZipCode " & _
"FROM tblYourTableName as A ORDER BY A.Name;"
db.close
DoCmd.OpenQuery "qryYourQueryName"


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

Thank you very much, but I am confused where to put this code and the address fields are not fields in this table or query, I just want to be able to indicate with individuals we sent letters to by saying I sent letters to the bottom 50 or what no. Thanks for the help so far.

Jeremy

 

[scriverb]

Jeremy: This is just an example of how to do this. The table name and field names are just examples of a table and fields in query. the important part of this is being able to prompt the user for the number of letters to be sent out and get that number in the Select TOP xxx statement.

Now if you would give me more information concerning table and field names I could make this more specific.

If you have a query already to go just post it here and I will modify the response and give you directions on how to make this work.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

not a problem, truthfully it is a very very simple query

SELECT TOP 100 SurplusLetter.[Date Sent], Employee.[Total Score]
FROM Employee INNER JOIN SurplusLetter ON Employee.SSN = SurplusLetter.SSN
ORDER BY Employee.[Total Score]

Like I said all I want it to do is ask how many of the bottom scores you sent letters to and then I will have it do an update that puts that current date in there.

Thank you so much you have been great so far.

 

[scriverb]

I am leaving for the day and will answer this evening for you. This is doeable and I will post this evening.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Sorry, didn't get back until late. The SQL you posted yesterday should be in a Saved query. I will just refer to it as qrySavedQuery below. Now the following code should be put in OnClick event procedure where you would be initiating the running of this query to perform your update:
Dim db as DAO.database
Set db = CurrentDB
db.QueryDefs("qrySavedQuery&quot.SQL = "Select TOP " & _
InputBox("Enter # of letters to be sent this month: ", "Parameter Prompt&quot & _
Mid$(dbQueryDefs("qrySavedQuery&quot.SQL, Instr(1, dbQueryDefs("qrySavedQuery&quot.SQL, "SurplusLetter&quot-1)
db.close
DoCmd.OpenQuery "qrySavedQuery"

This should select the Top X number of records from your original query, with X being prompted and entered by the User. Since the query sorts by Top Score in Ascending order the Top X records should be the lowest X Top Score in the recordset.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

Thanks again, I think I got it in the right place and everything but when I ran it it gave me a compile error and said invalid outside procedure. The name of the stored query is "SurplusDate". Thanks for the help so far, you have been awesome.

 

[scriverb]

Open up this query to the SQL window and post the SQL back for me. It should reflect the last run which we need to make sure is looking correctly. It must be misinterpreting something that we have created.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

Ok, I put the Vb code you posted on an onclick event on a control box on a form, is that where I am supposed to put it. Here is the SQL view coding:

SELECT TOP 100 SurplusLetter.[Date Sent], Employee.[Total Score]
FROM Employee INNER JOIN SurplusLetter ON Employee.SSN=SurplusLetter.SSN
ORDER BY Employee.[Total Score]

 

[scriverb]

Is SurplusLetter a table or query? Just try running the query from your database window. Let's see if the query runs as is. I don't know what an invalid outside procedure would be. this is your query that supposedly was running okay but you needed the Top X added on a "on the fly" type of arrangement. If it doesn't run with the Top 100 then remove that part of it and just get the query to run and return a recordset. Let me know how it goes.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

surplus letter is a table, the query runs fine I get the run error when I click on the button on the form? where was I supposed to put that code, I created a button on the form and on the onclick event for that button (in the form) I put that code in the code section.

 

[scriverb]

Should be fine. When you get this error do you get the code window opened up and a line of code highlighted in yellow? If so then please post the code behind the button and indicate the line where the highlight is.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

Private Sub Command0_Click()
Dim db As DAO.database
Set db = CurrentDb
db.QueryDefs("surplusdate&quot.SQL = "Select TOP " & _
InputBox("Enter # of letters to be sent this month: ", "Parameter Prompt&quot & _
Mid$(dbQueryDefs("surplusdate&quot.SQL, InStr(1, dbQueryDefs("surplusdate&quot.SQL, "SurplusLetter&quot - 1)
db.Close
DoCmd.OpenQuery "surplusdate"
End Sub

The private sub command0_click() is highlighted yellow and the db As DAO.database is highlighted in blue when the error message pops up.

 

[scriverb]

Okay, we can fix that. Open a form or create a new form in design view. Click on the Code button in the button bar. Now from the Menu select Tool/References. If you are using A97 then the library MS 3.5 DAO Object Library must be selected. If A2K then MS 3.6 DAO Object Library must be selected. I am predicting that the 3.6 is not selected.

Just select it and close the window. Rerun the code.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

I very much appreciate your patience, and obviously that worked more but this time I got a compile error: sub or function not defined.

The private sub was highlighted yellow

The Mid$(dbQueryDefs was highlighted blue

 

[scriverb]

There is a missing period in that line:

Mid$(db.QueryDefs

Make the correction run it again.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hathman23]

You are AWESOME it worked perfectly. Exactly what I wanted. Thank you so very very much.

 

[scriverb]

Great!!!

We just had to keep tinkering with it. Thanks for the Star.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???