Pulling records from a query in VBA

[WSC]

I have code I am executing in VBA through the onclick of a button on a form. I want to open a query, count the records, and step through the records one by one and pull out data to dump in to a table. I have the open query and record count working. When I try to pull a field of the record and assign it to a variable, I keep getting null values. What am I doing wrong?

source query = Get_Data_PPEW
source records = START_DAY, START_HOUR, START_MINUTE, END_DAY, END_HOUR, END_MINUTE, FACILITY

Here is the code I have...

Dim rcount As Integer
Dim START_DAYx(10000) As Date
Dim START_DAY As Date
Dim START_HOURx(1000) As Integer
Dim START_HOUR As Integer
Dim START_MINUTEx(1000) As Integer
Dim START_MINUTE As Integer
Dim END_DAYx(1000) As Date
Dim END_DAY As Date
Dim END_HOURx(1000) As Integer
Dim END_HOUR As Integer
Dim END_MINUTEx(1000) As Integer
Dim END_MINUTE As Integer
Dim FACILITYx(1000) As String
Dim FACILITY As String

DoCmd.OpenQuery "Get_Data_PPEW", acViewNormal, acReadOnly

rcount = DCount("[FACILITY]", "Get_Data_PPEW")

'Go to first record in query
DoCmd.GoToRecord acDataQuery, "Get_Data_PPEW", acFirst

I = 1
For I = 1 To (rcount + 1)
DoCmd.GoToRecord acDataQuery, "Get_Data_PPEW", acNext
START_DAYx(I) = START_DAY
MsgBox "START DATE = " & START_DAYx(I)
START_HOURx(I) = START_HOUR
MsgBox "START HOUR = " & START_HOURx(I)
START_MINUTEx(I) = START_MINUTE
MsgBox "START MINUTE = " & START_MINUTEx(I)
END_DAYx(I) = END_DAY
MsgBox "END DATE = " & END_DAYx(I)
END_HOURx(I) = END_HOUR
MsgBox "END HOUR = " & END_HOURx(I)
END_MINUTEx(I) = END_MINUTE
MsgBox "END MINUTE = " & END_MINUTEx(I)
FACILITYx(I) = FACILITY
MsgBox "Facility = " & FACILITYx(I)

'If the end date is null, set it equal to today minus 1
If END_DAY = "" Then
END_DAY = Date - 1
End If
MsgBox "End Date = " & END_DAY

I = I + 1
Next I

'Close query
DoCmd.Close acQuery, "Get_Data_PPEW", acSaveNo

 

[SamDeMan]

i am not sure of your method, i don't use this type of coding. i am not sure, but it may be that your qurey isn't constantly open. i think you make a few calls to it, but it doesn't stay open. maybe somebody better than me can correct me on this. however, i don't know why you don't use ADO to open your query.

basic structure:
Dim rs1 As ADODB.Recordset
Dim cmd1 As ADODB.Command
Set rs1 = New ADODB.Recordset
Set cmd1 = New ADODB.Command
cmd1.CommandText = "Get_Data_PPEW"
cmd1.CommandType = adCmdTable
cmd1.Parameters.Refresh
Set rs1 = cmd1.Execute

and then use
myVariable = rs1("Field_x")
to assigne the values.

at the end use this statement to close
Set rs1 = Nothing

good luck,

sam