Re: Scroll through records and get match

Hi Ken,

Thanks for explaining how to step through code. From what you say you
ALWAYS need to click on a button to start the code working. In Excel you can
simply be in the code and press F8 or F5 and away you go.

Anyway, when I try to run your code I get an error

"User-defined type not defined" at Dim dbs As DAO.Database. If I comment
that line out (I know I shouldn't but I thought I'd try anyway) I get an
error:

"Invalid argument" Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

PS Thanks for all of your assistance and guidence to date.


--
Andrew


"Ken Snell (MVP)" wrote:

You can use any of the events attached to controls and forms to run code.
You need to decide which event would be useful; however, for your situation
I would think that using a command button is probably the best method as
that means the code runs only when you want it to (explicitly clicking the
button).

DoCmd.RunSQL is used to run action queries, not select queries. The query
that I posted is a select query. You can use the DoCmd.OpenQuery to open
select queries, but they must be ones already stored in the database -- you
cannot use it to open an SQL statement that you generate via code.

If you want the Period and Week values from the query that you're generating
in the code, you must open a recordset based on the SQL statement, and then
read the values from the recordset and use them in your code. Something like
this:


Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngPeriod As Long, lngWeek As Long
Dim strSQL As String
Set dbs = CurrentDb()
strSQL = "SELECT T.[Period], T.[Week] " & _
"FROM Tbl_Dates AS T WHERE " & _
"T.W_C_Date = (SELECT Max(Q.W_C_Date) " & _
"FROM Tbl_Dates AS Q WHERE " & _
"Q.W_C_Date <= Date());"
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngPeriod = rst![Period].Value
lngWeek = rst![Week].Value
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
' now you can use the lngPeriod and lngWeek values
' in your code for what you want to do next


--

Ken Snell
<MS ACCESS MVP>


"Andrew" <Andrew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8A7CB9D9-A01A-4618-B481-AB244560002F@xxxxxxxxxxxxxxxx
Hi Ken,

I have used your code and placed it on a button (under build event).

2 Questions

First Question
To run code is there anyway of writing the code and getting it to run
without having to attach it to a button (sorry if this seems a stupid
question but it's the only way I know how to kick it off).


Second Question

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


sSQL = "SELECT T.[Period], T.[Week]FROM Tbl_Dates AS T WHERE T.W_C_Date
=(SELECT Max(Q.W_C_Date) FROM Tbl_Dates AS Q WHERE Q.W_C_Date <= Date());"
DoCmd.RunSQL sSQL

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub


When I run the above I get the error


A RunSQL statemnt requires an argument consisting of an SQL statement.

From my limited knowledge it is telling me I need to make an update query.
I want to pass the T and Q values through to the next SQL statement (I
haven't put that in this query).

--
Andrew


"Ken Snell (MVP)" wrote:

Something like this?

SELECT T.[Period], T.[Week]
FROM Tbl_Dates AS T
WHERE T.W_C_Date =
(SELECT Max(Q.W_C_Date)
FROM Tbl_Dates AS Q
WHERE Q.W_C_Date <= Date());

--

Ken Snell
<MS ACCESS MVP>

"Andrew" <Andrew@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BDB7293F-24FB-490A-8929-413F49185778@xxxxxxxxxxxxxxxx
Hi Ken,

Thanks for replying.

Hi Ken,

Thanks for replying.

I used the Animal table as an example to try and get a simple answer.
Then
with the reply I was going to adapt it to my requirements.

The real requirement is to get the period from the following table
names
Tbl_Dates:

W_C_Date Year Season Month Period Week
02/07/06 2006 W July 12 1
09/07/06 2006 W July 12 2
16/07/06 2006 W July 12 3
23/07/06 2006 W July 12 4
30/07/06 2006 S August 1 1
06/08/06 2006 S August 1 2
13/08/06 2006 S August 1 3
20/08/06 2006 S August 1 4

I want the period (12) and week (4) if today's date [date()] is
25/07/06.
I'm sorry if I confused you by saying to scroll through.

I'm wanting to pass the value of Period and Week through to some more
SQL
(I
have this part working!!!!!)

Thanks Ken
--
Andrew



< snipped >



.