Looping through a recordset

[speakers_86]

I don't usually find a need to deal with recordsets, so I usually struggle. I've got a datasheet subform and I want to force the date that is displayed to have the leading 0 if required in the time.

Private Sub Form_Current()
Dim DB As Database
Dim RST As Recordset
Dim D As String
Dim T As String
Set DB = CurrentDb
Set RST = DB.OpenRecordset("qryMSL")        'Too few variables error here

If Not RST.EOF Then RST.MoveFirst
Do While Not RST.EOF
D = Format([CurrentDate], "mm/dd/yyyy")
T = Format([CurrentTime], "hhnn")
If Len(T) = 3 Then
    Me.TodaysDate = D & " 0" & T
Else
    Me.TodaysDate = D & " " & T
End If
    RST.MoveNext
Loop

    
RST.Close
End Sub

edit - seems like there is an issue with my query.
edit - I fixed the query, and the issue is still there.
edit - is the variable in my query not getting passed correctly?

Like Format(DateAdd("h",[forms]![frmSettings].[TimeZoneAdjustment],Now()),"mm/dd/yyyy") & "*"

This is in the query. frmSettings is open.

edit - I'm makng some progress. The variable is not being passed. I removed the criteria from the query and the loop suddenly works. On the other hand, every record had the same value for me.TodaysDate. So the variable is not being passed, and there is something wrong with my loop.

 

[CBrighton]

Check this site (word doc) to see how to pass paramaters to a query in either a DAO or ADO recordset.

http://www.utsweb.net/Tips%20and%20Tricks/Access%202003/Parameterized%20Queries%20using%20VBA.doc

 

[r.harrison]

Hi Speakers,

When you're looping through the recordset you're assigning the date and time to T each time it passes through. I'm assuming that me.todaysdate is a control on your form. So maybe adding a docmd.gotorecord , , acnext inside the loop might work for you.

Rich

 

[CBrighton]

The following line moves to the next record in the recordset: RST.MoveNext

Assuming the control names references mean what they seem to, I would personally replace the D & T lines with this:

D = Format(Now(), "mm/dd/yyyy")
T = Format(Now(), "hhnn")

This is based on the assuption that thwe 2 controls hold Now() or Date(), and it will improve the code as the time will be pulled each time the code is looped rather than Now() being evaluated when the form is opened and never again.


The problem is that the query being used has something like a form reference as its criteria, and the record set can't deal with that. The link I provided explains how to pass paramaters to either a DAO or ADO recordset and should solve the problem.

 

[speakers_86]

The following line moves to the next record in the recordset: RST.MoveNext

D and T need to be assigned for each record. I thought the rst.movenext was shifting the focus to the next record, that is why there is no gotorecord command.

Assuming the control names references mean what they seem to, I would personally replace the D & T lines with this:

D = Format(Now(), "mm/dd/yyyy")
T = Format(Now(), "hhnn")

This is based on the assuption that thwe 2 controls hold Now() or Date(), and it will improve the code as the time will be pulled each time the code is looped rather than Now() being evaluated when the form is opened and never again.

The goal of this code is to force me.TodaysDate to display a leading zero in the time if necessary (military time). I tried simply formatting as you suggested, but if I use the code above and bring together D & T, the leading zero gets dropped.

The problem is that the query being used has something like a form reference as its criteria, and the record set can't deal with that. The link I provided explains how to pass paramaters to either a DAO or ADO recordset and should solve the problem.

Yes. Does the recordset actually use the query? If I specify the parameter in ADO or DAO, do I need to go into the query and remove the parameter? Actually, what I am thinking I should do is replace the form reference in the query with a variable that I define in the code. Does that sound right?


In regards to your word document link, I tried to anable DAO and I got an error. It said there was a name conflict. So should I just use ADO?

Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim RST As ADODB.Recordset
   
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Procedures("qryMSL").Command
  
cmd.Parameters("[CurrentDate]") = Like Format(DateAdd("h",[forms]![frmSettings].[TimeZoneAdjustment],Now()),"mm/dd/yyyy") & "*"

Set RsDischarges = cmd.Execute

Does that look better? Can I use Like in the parameter? I'll actually give it a try sometime, maybe tomorrow.

 

[CBrighton]

If all you are trying to do is set the value of a control based on another control, then there is no need for the recordset at all.

You are not getting data from it or writing data to it.

Try this:

Dim D As String
Dim T As String
 
D = Format([CurrentDate], "mm/dd/yyyy")
T = Format([CurrentTime], "hhnn")
If Len(T) = 3 Then
    Me.TodaysDate = D & " 0" & T
Else
    Me.TodaysDate = D & " " & T
End If

However, I'd be interested to find out why the problem (lack of leading 0) is occuring in the first place. I assume that me.TodaysDate is a date/time formatted textbox?

:edit:

That's my proposed code for a single record.

I believe you can do a loop something like this:

Do Until Me.Record = Me.RecordCount
   'code
   docmd.gotorecord , , acnext
Loop

Me.Record might not be exactly right, but I recall seeing similar code before.

However, if the control which you are setting is bound to a field in a table I'd likely favour an update query over using VBA to loop through each record.

 

[speakers_86]

I thought to loop through a records in a datasheet you had to use the recordset.

Error at me.record. I saw nothing that look like it would do the trick.

For clarity, all I am trying to do is have this

    D = Format(Me.CurrentDate, "mm/dd/yyyy")
    T = Format(Me.CurrentDate, "hhnn")
    If Len(T) = 3 Then
        Me.TodaysDate = D & " 0" & T
    Else
        Me.TodaysDate = D & " " & T
    End If

occur for every record in my subform. The subform is in datasheet. I still didn't try that DAO ADO link from above yet though.

 

[CBrighton]

It's .currentrecord, not .record.

Sorry.

However, you may be right about it not being usable in a datasheet, I never use datasheets myself so it's not something I have come across.

In that case I think we will need to go back to recordsets, but lets try a recordset clone:

Dim db as Database
Dim rst as Recordset
Dim D as string
Dim T as string
 
Set rst = Me.RecordsetClone
 
If rst.eof then
    msgbox "No records, procedure aborted."
    Exit Sub
End If
 
rst.movefirst
Do Until rst.eof
    D = Format(Me.CurrentDate, "mm/dd/yyyy")
    T = Format(Me.CurrentDate, "hhnn")
    rst.edit
    If Len(T) = 3 Then
        rst![COLOR=red]TodaysDate[/COLOR] = D & " 0" & T
    Else
        rst![COLOR=red]TodaysDate[/COLOR] = D & " " & T
    End If
    rst.Update
    rst.MoveNext
Loop

NOTE: This relies on the control Me.TodaysDate being bound to a field called TodaysDate. The 2 instances iof this field name are highlighted in red and should be changed if the field name differs from the control name.


:edit:

The above code assumed that Me.CurrentDate is usable within the loop. I don't know if that control is part of a specific record (in which case it should be rst!FieldName instead of Me.ControlName in the baove code) or a generic control in the header or something.

 

[speakers_86]

Thats better, because the parameter works now. But every record is getting the same value stuck in it. The only change I made was rst!TodaysDate to me.TodaysDate. This is an unbound field, that is only representing CurrentDate in a format I am okay with. I added a message box that returns the same date for all records.


Dim db As Database
Dim rst As Recordset
Dim D As String
Dim T As String

Set rst = Me.RecordsetClone

If rst.EOF Then
MsgBox "No records, procedure aborted."
Exit Sub
End If

rst.MoveFirst
Do Until rst.EOF
MsgBox CurrentDate
D = Format(Me.CurrentDate, "mm/dd/yyyy")
T = Format(Me.CurrentDate, "hhnn")
rst.Edit
If Len(T) = 3 Then
Me.TodaysDate = D & " 0" & T
Else
Me.TodaysDate = D & " " & T
End If
rst.Update
rst.MoveNext
Loop

 

[CBrighton]

Again, you are doing nothing to the recordset whatsoever. You are only trying to send & get data from form controls which will completely ignore the rst.MoveNext.

If you are not going to do anything with the recordset then there is no purpose in looping through it (except to increase the number of times it runs the code within the loop, but it will just be the number of times it's run, not the number of records it effects as it is always using the same controls).




It sounds like you might do better adding a field in the recordsource query to display the date in the format which you want and bind it to that control. I didn't realise it was unbound, I thought we were editing data in a recordset.

If you get the format sorted in the query then there's no need to loop through anything in VBA.


:edit:

Lets clear this up; TodaysDate is an unbound control, what about CurrentDate?

 

[speakers_86]

CurrentDate is bound. TodaysDate is not. I can't figure out how to get the format correct in the...eh, nevermind. I got it.

I thought I had already tried that, and the result was the zeroes being dropped off. meh.

Can you explain the record set though? You said I was not going to do anything with it. I though the record set was the simply the records my form was displaying. How is the record set different than the actual records I am looking at?

 

[CBrighton]

The VBA recordset stands alone from the form, even if they are based on the same SQL. That's why we were working with a RecordsetClone, it's a clone/copy of the recordset on the form not the form itself.

Which record you are currently accessing in the recordset has no relation to which record your form is focused on.

When using a recordset you don't update the controls which are bound to the fields, you update the fields directly like this: RecordsetName!FieldName

The .Edit & .Update were to open the record for editing and to save the changes respectively.

 

[vbaInet]

You're using a Datasheet, whatever you do will only affect the CurrentRecord. You can't have mixed Formats like that unless you update the records (in an UPDATE statement) to reflect this format. But this isn't a sensible thing to do.

Just use IIF(Len(T & "") = 3, Format(), Format()) in the Control Source of your Date field. Obviously this will cause your control to be read-only.