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.

--
Message posted via http://www.accessmonster.com

Try adding
rst.movefirst just before the Do Until

"Gary Beale via AccessMonster.com" wrote:

> 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.
>
> --
> Message posted via http://www.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

--
Message posted via http://www.accessmonster.com

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" wrote:

> 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
>
> --
> Message posted via http://www.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?!?!

--
Message posted via http://www.accessmonster.com

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.

"Gary Beale via AccessMonster.com" wrote:

> 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?!?!
>
> --
> Message posted via http://www.accessmonster.com
>

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 wrote:
> 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?!?!
>

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

--
Message posted via http://www.accessmonster.com

On Wed, 18 May 2005 16:16:35 GMT, "Gary Beale via AccessMonster.com"
<(E-Mail Removed)> wrote:

>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]

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

--
Message posted via http://www.accessmonster.com