Continuous forms - add data between two dates

[Gary Beale via AccessMonster.com]

I have a continuous form which is the result of a query, giving a list of
results between two dates. This works fine.
What I want to do is add (or change) data to one of the fields in the list.
e.g. the user will enter a value in an unbound textbox, click the button
and this will change the field in each record.
I have written code which works really well, but for only five records!!
Any more than that and it doesn't throw an error but doesn't do anything
either. Code is as follows:

RunCommand acCmdSaveRecord
sqlstmt = _
"select BinLoc from qrySpvs where ([JobNo] = " & Me.JobNo & ")"
rst.Open sqlstmt, CurrentProject.Connection, adOpenKeyset, _
adLockOptimistic, adCmdText
Do Until rst.EOF
rst![BinLoc] = Text66
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

I'd be grateful for any help.

 

[Guest]

Try adding
rst.movefirst just before the Do Until

 

[Gary Beale via AccessMonster.com]

Thanks for taking the time to reply.

The result was the following error:

"Error Number 3021, Either BOF or EOF is True, or the current record has
been deleted. Requested operation has been deleted."

What is annoying is that I have a similar form using a single date that
works just fine.
Any further suggestions would be gratefully received.

Thanks in anticipation

 

[Guest]

Then you have no data in the recordset. check it with:
If rst.RecordCount = 0 then
msgbox "No Data Found"...
End If

 

[Gary Beale via AccessMonster.com]

Yes you are right. No data. This would lead me to believe that my sql
statement, as follows, is wrong.

sqlstmt = "select binloc from qrysspvs where ([AnalysisDate]) >= #" &
Me.Text68 & "#" & " and ([AnalysisDate]) <= #" & Me.Text70 & "#" & " and (
[AnalystName]) = """ & Me.Text71 & """"

But it works if I have less than 6 records?!?!

 

[Guest]

Gary,
Embarrasingly you have hit on my weakest point. SQL syntax. I can write a
complex program in a couple of days. Then I have to spend a week working out
SQL syntax problems. Maybe if you repost, someone with better SQL skills can
help.

 

[Nikos Yannacopoulos]

Gary,

To begin with, rewriting the SQL statement like:

sqlstmt = "select binloc from qrysspvs where [AnalysisDate] >= #" _
& Me.Text68 & "# and [AnalysisDate] <= #" & Me.Text70 _
& "# and [AnalystName] = '" & Me.Text71 & "'"

improves its readability (at least in my opinion )

Now I'm comming to the guessing part: VBA handles dates as
year/month/day format, regardless of whatever regional settings; US
format is handled just fine, but if your date format settings are
non-US, your filtering is not what you would expect... actually a very
common problem with SQL expressions involving dates in VBA, ouside the
US. If this is indeed your case, try this:

vDate1 = DateSerial(Year(Me.Text68), Month(Me.Text68), Day(Me.Text68)
vDate2 = DateSerial(Year(Me.Text70), Month(Me.Text70), Day(Me.Text70)
sqlstmt = "select binloc from qrysspvs where [AnalysisDate] >= #" _
& vDate1 & "# and [AnalysisDate] <= #" & vDate2 _
& "# and [AnalystName] = '" & Me.Text71 & "'"

HTH,
Nikos

 

[Gary Beale via AccessMonster.com]

Thankyou to Klatuu for helping me as far as you did. My SQL is obviously a
little weak too.

Nikos, thanks for replying. You were spot on with your guess about the
dates. I'm in the UK (if only Bill Gates had been British, then everyone
else would have had these date problems)

Having now read up on DateSerial, I feel that your solution should work.
But it doesn't. However, I changed the search dates to ambiguous ones (in
the US/UK date context) e.g. I swapped 11/5/05 to 5/11/05 in the backend
records and the code then worked. At least I now know that the problem is
simply (!!) the US/UK date thing. All I have to do now is get Access to
transpose the relevant bits of the date. I'll be reading up on it all as
soon as I've finished this and if I find the solution I'll post it here.

In the meantime, if you know the answer, I'd be grateful, since it will
save me a (probably)heap of time.

Thanks again.

Gary

 

[John Vinson]

All I have to do now is get Access to
transpose the relevant bits of the date.

One possibility is to use an unambiguous format such as "11-May-2005";
another is to coerce the data into US format by appending

Format(<date value>, "mm/dd/yyyy")

into the SQL string.

John W. Vinson[MVP]

 

[Gary Beale via AccessMonster.com]

Hi All,

Thankyou John for your reply. Your suggestion worked and the final sql
statement looks as follows:

sqlstmt = "select binloc from qryspvs where [AnalysisDate] >= #" _
& Format(vDate1, "mm/dd/yy") & "# and [AnalysisDate] <= #" & _
Format(vDate2, "mm/dd/yy") & "# and [AnalystName] = '" & Me.Text71 & "'"

I also found the solution in Beginning Access 2000 VBA (Smith/Sussman) on
page 231, a book that has dug me out of many holes.

Thanks very much to you all for your input. I can honestly say that I
couldn't have done it without you.

Happy Coding

Regards,

Gary Beale