Problems understanding .RecordSource

[maetrix]

Hello,

I'm working at learning VB for Access and I've run into a snag with my current project.

The code below is executed in the ON CURRENT event for a form where the RecordClone retrieves a value from the previous recordset and then enters the number into an unbound textbox. This part works fine.

Where I'm having a problem is limiting the displayed records by date and retaining the values gathered by the RecordClone.

If I remove the "If Rs1.EOF..." Then-Else lines, the code gets trapped in a loop which requires me to CTRL-BREAK.

My other attemps at re-working the SQL code has resulted in Access not able to find any matching data.

Another problem, Should I have created another range of fields in my table to store the "Old Data"?

Maintaining a running tally of the gas pumps (The DB is for a Gas station ) is critical to the success of this DB and I've been racking my brains over the logic for serveral days now.

Thank you,
--Toby Kliem--

---
Dim F As Form: Set F = Me
Dim Rs1 As DAO.Recordset
Dim sq As Variant
Dim BKM As Variant
Dim vRdol As Variant
...
Dim vCvol As Variant

BKM = Me.Bookmark
Set Rs1 = Me.RecordsetClone
Rs1.Bookmark = BKM
Rs1.MovePrevious
If Rs1.BOF = False Then
'Retrieve the running dollar value from Regular Pump
vRdol = Rs1!regdollar
... '6 similar lines removed to save space.
vCvol = Rs1!CylLitre
Rs1.Close
Set Rs1 = Nothing
'Paste Running dollar value into Unbound box.
Me.txtOldRegDollar.Value = vRdol
... '6 similar lines removed to save space.
Me.txtOldCylLitre.Value = vCdol
Else
End If
If Rs1.EOF = False And Rs1.BOF = False Then
'Search tblVolume for Dates that match
' User entered Value.
sq = "SELECT * " & _
"FROM tblVolume " & _
"WHERE VolDate = " &_
"#" & Forms!frmDate.txtGetDate.Value & "#"
F.RecordSource = sq
End If
--- sapere aude: Dare to be wise

 

[Darrylles]

Hiya,

I'm not conversant with recordsets as variables, but I do understand what you are attempting to do.

I also understand programming constructs well (if..else..endif, while..wend etc.).

It seems that the first IF condition will more than likely always be true (BOF will be false) and the code will usually run. Within this code you close RS1 and nullify it.

You then have the 2nd IF condition, which because you've closed RS1 and set it to NULL - will ALSO always be true, RS1.EOF and RS1.BOF will ALWAYS be true if your 1st IF condition is entered - you set them both to null, so the code will always run also.

Tell me what conditions you want to check for and what you want to do if the conditions are met.

Please summarise.

Regards,

Darrylle




"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[maetrix]

Okay, here's the pseudo-code as well as an explained summery:

Set clone
Bookmark current recordset
Move clone to the previous record.
If it is not the BOF, then
Save values to be copied over.
Close clone
--Not sure why reset the Recordset?
Paste saved values into unbound textboxes.
end if

If not BOF, then
limit the Recordsource where the date field & _
matches what the user entered.
End If

SUMMERY:

The form is where the user enters the current readings from the gas pumps, an incrementing number. By subtracting today's value from yesterdays, you get the total fuel pumped.

Each day may have more then one entry, depending on if the price of gas has changed and how often. Because it's important to track how much gas was pumped at what price, each day may have up to 4 entries (though not limited to 4).

It seemed like a simple concept when I accepted the project, I never realized how difficult it would be.

If I limit the list to only those dates that are the same, the first record in the set would not have the old values.

If I set it up to copy over the old values, I can't get it to select only those of a specific date.

As usual, any help is greatly appreciated!

Thnx,
--Toby Kliem-- Novice Programmer;
Expert Hack

sapere aude: Dare to be wise