Going crazy....MoveNext help!

[cerodalo]

Hi All,

I've got a table thats populated with say 10 records from a form. This
works great.

Now what I want to do is update one specific field in 5 of the
records. The records all have unique ID's so getting the ID isn't the
problem. I need to know how to open the table, find the first record,
edit it and then keep going until all 5 records are done.

Please, please help....its 10.10pm and I've been going at this for a
few hours now.

Thanks!!!!!!
Justin

 

[cerodalo]

What identifies the records to be updated?

Bob

I've got a field call RID (release ID) linked to TID (transport ID).
On the form the RID is displayed and so I can use that to return all
records in the table (TRANS table) that are equal to RID. This will
return say 10 records. The forms quantity field (relqty) is 5, so I
what to only update the first 5 records in the TRANS table with a
value.

Hope that makes sense

Ta

 

[AZ]

If that's really all you want to do, then the easiest way is to just
manually open the table, sort by ID number, and make your changes
manually to each record (or change the records manually in the form
you just made).

If you're looking for a way to do this with code, you can start with
an update query. To select the 5 unique ID's you want, you can put
something like "in (1,3,5,7,9)" in the criteria field. Then copy the
SQL code from the query, and use it as the argument for DoCmd.RunSQL

You could also try using ADO (or DAO) to create a recordset, and loop
through record by record making changes, but the DoCmd.RunSQL will
accomplish the same thing with 1 line of code.

AZ

 

[cerodalo]

If that's really all you want to do, then the easiest way is to just
manually open the table, sort by ID number, and make your changes
manually to each record (or change the records manually in the form
you just made).

If you're looking for a way to do this with code, you can start with
an update query. To select the 5 unique ID's you want, you can put
something like "in (1,3,5,7,9)" in the criteria field.   Then copy the
SQL code from the query, and use it as the argument for DoCmd.RunSQL

You could also try using ADO (or DAO) to create a recordset, and loop
through record by record making changes, but the DoCmd.RunSQL will
accomplish the same thing with 1 line of code.

AZ







- Show quoted text -

Hi, not sure that will work. The reason being that for now its fine,
but once I've partially updated I'd need to find the first "free"
record the next time.

What I'm trying to do, but my code is really base at this level, is
something like this;

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String

Set db = CurrentDb
Set rs = db.OpenRecordset("cmtran", dbOpenDynaset)
str = release = RID [I dont know how to properly define this
criteria where release is a field in the table and RID is the field on
the form]

If rs.EOF And rs.BOF Then Exit Sub
rs.FindFirst str [also want to include condition "where CARRIER is
null"]
'rs.MoveFirst
' Do Until rs.EOF
Do Until Counter = relqtylbl ' this will run the number of times
equal to the release quantity - so say 5 times
rs.Edit
rs!CARRIER = RID ' this now updates the record with the RID
rs!CREDIT = ratelbl ' this updates the record with the value
rs.Update
Loop
rs.Close

Am I close here, or is the way off? Thanks for the help so far.

Justin

 

[a a r o n . k e m p f]

You're way off.

DAO hasn't been included with MDAC, Windows or Office for close to a
decade.

Move to ADO.

-Aaron

If that's really all you want to do, then the easiest way is to just
manually open the table, sort by ID number, and make your changes
manually to each record (or change the records manually in the form
you just made).

If you're looking for a way to do this with code, you can start with
an update query. To select the 5 unique ID's you want, you can put
something like "in (1,3,5,7,9)" in the criteria field.   Then copy the
SQL code from the query, and use it as the argument for DoCmd.RunSQL

You could also try using ADO (or DAO) to create a recordset, and loop
through record by record making changes, but the DoCmd.RunSQL will
accomplish the same thing with 1 line of code.

On Apr 14, 4:11 pm, [email protected] wrote:

- Show quoted text -

Hi, not sure that will work. The reason being that for now its fine,
but once I've partially updated I'd need to find the first "free"
record the next time.

What I'm trying to do, but my code is really base at this level, is
something like this;

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim str As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("cmtran", dbOpenDynaset)
    str = release = RID [I dont know how to properly define this
criteria where release is a field in the table and RID is the field on
the form]

    If rs.EOF And rs.BOF Then Exit Sub
    rs.FindFirst str [also want to include condition "where CARRIER is
null"]
    'rs.MoveFirst
   ' Do Until rs.EOF
    Do Until Counter = relqtylbl ' this will run the number of times
equal to the release quantity - so say 5 times
        rs.Edit
        rs!CARRIER = RID ' this now updates the record with the RID
        rs!CREDIT = ratelbl ' this updates the record with the value
        rs.Update
    Loop
    rs.Close

Am I close here, or is the way off? Thanks for the help so far.

Justin- Hide quoted text -

- Show quoted text -

 

[cerodalo]

You're way off.

DAO hasn't been included with MDAC, Windows or Office for close to a
decade.

Move to ADO.

-Aaron

Hi, not sure that will work. The reason being that for now its fine,
but once I've partially updated I'd need to find the first "free"
record the next time.

What I'm trying to do, but my code is really base at this level, is
something like this;

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim str As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("cmtran", dbOpenDynaset)
    str = release = RID [I dont know how to properly define this
criteria where release is a field in the table and RID is the field on
the form]

    If rs.EOF And rs.BOF Then Exit Sub
    rs.FindFirst str [also want to include condition "where CARRIER is
null"]
    'rs.MoveFirst
   ' Do Until rs.EOF
    Do Until Counter = relqtylbl ' this will run the number of times
equal to the release quantity - so say 5 times
        rs.Edit
        rs!CARRIER = RID ' this now updates the record with the RID
        rs!CREDIT = ratelbl ' this updates the record with thevalue
        rs.Update
    Loop
    rs.Close

Am I close here, or is the way off? Thanks for the help so far.

Justin- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Ok, got code from sample....bad idea.

I'm trying to go the query route you mentioned. Will use dlookup to
find TID of first available record. Can then use ID's to run update
query.

For interest, how would you have gone about what I was trying to do in
code....simply if possible.

Thanks again,

 

[AZ]

I think you're getting there.

1) As far as setting the criteria, you can do it like this:
str = "release =" & Me!RID & " AND carrier is null"
[assuming the code is in the form itself, RID is the name of the field
and is numeric]

2) Not sure if FindFirst is the way I would do it (I'm not used to
this method). I would open a recordset that contains exactly the
records you need to work with, something like:
Set rs = db.OpenRecordset("select * from cmtran WHERE " & str,
dbOpenDynaset)
Then you can loop through 5 times, making the changes you need, and
doing a MOVENEXT at the bottom of the loop.

3) What happens if there aren't enough records in cmtran? You might
want to test for this and take appropriate action before you start
looping.

Adam

- Show quoted text -

Hi, not sure that will work. The reason being that for now its fine,
but once I've partially updated I'd need to find the first "free"
record the next time.

What I'm trying to do, but my code is really base at this level, is
something like this;

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim str As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("cmtran", dbOpenDynaset)
    str = release = RID [I dont know how to properly define this
criteria where release is a field in the table and RID is the field on
the form]

    If rs.EOF And rs.BOF Then Exit Sub
    rs.FindFirst str [also want to include condition "where CARRIER is
null"]
    'rs.MoveFirst
   ' Do Until rs.EOF
    Do Until Counter = relqtylbl ' this will run the number of times
equal to the release quantity - so say 5 times
        rs.Edit
        rs!CARRIER = RID ' this now updates the record with the RID
        rs!CREDIT = ratelbl ' this updates the record with the value
        rs.Update
    Loop
    rs.Close

Am I close here, or is the way off? Thanks for the help so far.

Justin- Hide quoted text -

- Show quoted text -

 

[George Nicholson]

Ignore Aaron. He's the neighborhood troll spreading bias and deliberate
misinformation in his wake just to get a reaction from people who know
better (which is pretty much everyone).

DAO hasn't been included with MDAC, Windows or Office for close to a
decade.

Is patently false and he knows it. That doesn't stop him from saying it.

--
HTH,
George

You're way off.

DAO hasn't been included with MDAC, Windows or Office for close to a
decade.

Move to ADO.

-Aaron

Hi, not sure that will work. The reason being that for now its fine,
but once I've partially updated I'd need to find the first "free"
record the next time.

What I'm trying to do, but my code is really base at this level, is
something like this;

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String

Set db = CurrentDb
Set rs = db.OpenRecordset("cmtran", dbOpenDynaset)
str = release = RID [I dont know how to properly define this
criteria where release is a field in the table and RID is the field on
the form]

If rs.EOF And rs.BOF Then Exit Sub
rs.FindFirst str [also want to include condition "where CARRIER is
null"]
'rs.MoveFirst
' Do Until rs.EOF
Do Until Counter = relqtylbl ' this will run the number of times
equal to the release quantity - so say 5 times
rs.Edit
rs!CARRIER = RID ' this now updates the record with the RID
rs!CREDIT = ratelbl ' this updates the record with the value
rs.Update
Loop
rs.Close

Am I close here, or is the way off? Thanks for the help so far.

Justin- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Ok, got code from sample....bad idea.

I'm trying to go the query route you mentioned. Will use dlookup to
find TID of first available record. Can then use ID's to run update
query.

For interest, how would you have gone about what I was trying to do in
code....simply if possible.

Thanks again,

 

[Rick Brandt]

You're way off.

DAO hasn't been included with MDAC, Windows or Office for close to a
decade.

Move to ADO.

Looking at MSDN articles right now...

Office Development
2007 Microsoft Office System
Microsoft Data Access Objects (DAO)
What's New in DAO

....it would appear that your statement is not entirely accurate.

 

[a a r o n . k e m p f]

it's not called DAO, home-slice

-Aaron

Looking at MSDN articles right now...

Office Development
 2007 Microsoft Office System
  Microsoft Data Access Objects (DAO)
   What's New in DAO

...it would appear that your statement is not entirely accurate.

 

[Rick Brandt]

it's not called DAO, home-slice

Dude, those are the exact words on the page. Even the two explicit "DAO"s.

DAO has been included in every version of Access and will be in the next one
as well. You're wrong. Be a man and admit it.

 

[a a r o n . k e m p f]

Those are not the exact words as the library shows up. That is not
the name of the library.

What you are claiming-- is that 'Creedence Clearwater Revisited' is
the same as 'Creedence Clearwater Revival'.

Yes-- CCR was a great tribute band. But it is _NOT_ the original
band.

It's like the new VW beetle vs the old VW beetle.
Yes-- it was a great car (until the year 1998) and then they stopped
offering DAO. And then a decade later- they came out with the new
beetle (which-- coincidentally-- is not known as DAO).

So now which is it.. is DAO the 'old beetle' or the 'new beetle'?

DAO is not included with some of the optional database _ENGINES_ for
Microsoft Access.
Just like mySql has different _ENGINES_ so does MS Access.

DAO is not included in _ANY_ of the formats for 4+ years.. and it's
not included / available in the MOST IMPORTANT formats of Microsoft
Access.

Your so called DAO is a joke.
Any library- where you have to instantiate and dispose-- in a
particular order-- is complete crap.
It is a complete waste of time.

The next thing that you're going to be claiming is that DAO.net is
going to be the default library for VB 2010!!!

_WHATEV_ kid.

DAO is not included or available for any version of MDAC that has come
out in the past decade.


-------------------------------------------------------------------------------------------
MDAC version 2.6 and _LATER_ do not contain Jet or Desktop ODBC
drivers
View products that this article applies to.
Article ID : 271908
Last Review : March 9, 2007
Revision : 7.1
This article was previously published under Q271908
On This Page

SUMMARY

MORE INFORMATION

How to install the Jet 4.0 components

REFERENCES
SUMMARY
Starting with Microsoft Data Access Components (MDAC) version 2.6,
MDAC no longer contains Jet components. In other words, MDAC versions
2.6 and later do not include Microsoft Jet, Microsoft Jet OLE DB
Provider, and the ODBC Desktop Database Drivers.

 

[Rick Brandt]

Those are not the exact words as the library shows up. That is not
the name of the library.

It is clearly the DAO library and it is used to execute the same DAO code
that people have been writing for well over a decade. Squirm all you want,
but you are still wrong.

[snipped some pathetic attempts at analogy]

DAO is not included with some of the optional database _ENGINES_ for
Microsoft Access.

So now the goalposts have been moved to "some of the optional engines"?

Just like mySql has different _ENGINES_ so does MS Access.

Irrelevent to your original statement.

DAO is not included in _ANY_ of the formats for 4+ years.. and it's
not included / available in the MOST IMPORTANT formats of Microsoft
Access.

The first point is WRONG. The second is irrelevent to your original
statement.

Your so called DAO is a joke.
Any library- where you have to instantiate and dispose-- in a
particular order-- is complete crap.
It is a complete waste of time.

I was not making any judgement about the merits of DAO. Only correcting
your incorrect statement that it is not included in Office (every single
version that includes Access).

The next thing that you're going to be claiming is that DAO.net is
going to be the default library for VB 2010!!!

No. That would be irrelevent to the discussion.

_WHATEV_ kid.

A lot of us have seen your shot er...picture Aaron. We know who the "kid"
is. I think the frequency of the words "kid" and "bully" in your posts
explain the pathology of your usenet history quite a bit.

DAO is not included or available for any version of MDAC that has come
out in the past decade.

More moved goalposts? Who was discussing what is and is not included in
MDAC? Lots of things are not included in MDAC. DAO however is included in
every version of Access despite your incorrect claims to the contrary.

 

[a a r o n . k e m p f]

it is not clearly the DAO library.

And I don't think that people have been writing it for a decade.

At least-- none of the 'real developers' that are my friends would use
such an obsolete DB layer.

If your library-- is buggy enough.. that you've got to explicity
.CLOSE

and Set rst = nothing

If you're really still stuck using a library like that.. the go and
take a class, kid

-Aaron

Those are not the exact words as the library shows up.  That is not
the name of the library.

It is clearly the DAO library and it is used to execute the same DAO code
that people have been writing for well over a decade.  Squirm all you want,
but you are still wrong.

[snipped some pathetic attempts at analogy]

DAO is not included with some of the optional database _ENGINES_ for
Microsoft Access.

So now the goalposts have been moved to "some of the optional engines"?

Just like mySql has different _ENGINES_ so does MS Access.

Irrelevent to your original statement.

DAO is not included in _ANY_ of the formats for 4+ years.. and it's
not included / available in the MOST IMPORTANT formats of Microsoft
Access.

The first point is WRONG.  The second is irrelevent to your original
statement.

Your so called DAO is a joke.
Any library- where you have to instantiate and dispose-- in a
particular order-- is complete crap.
It is a complete waste of time.

I was not making any judgement about the merits of DAO.  Only correcting
your incorrect statement that it is not included in Office (every single
version that includes Access).

The next thing that you're going to be claiming is that DAO.net is
going to be the default library for VB 2010!!!

No.  That would be irrelevent to the discussion.

_WHATEV_ kid.

A lot of us have seen your shot er...picture Aaron.  We know who the "kid"
is. I think the frequency of the words "kid" and "bully" in your posts
explain the pathology of your usenet history quite a bit.

DAO is not included or available for any version of MDAC that has come
out in the past decade.

More moved goalposts?  Who was discussing what is and is not included in
MDAC?  Lots of things are not included in MDAC.  DAO however is included in
every version of Access despite your incorrect claims to the contrary.

 

[a a r o n . k e m p f]

it is not included in Office or Access or Windows _BY_DEFAULT_.

It is available.

It's still not included in Access 2000 _BY_DEFAULT_.
It's still not included in Access 2002 _BY_DEFAULT_.
It's still not included in Access 2003 _BY_DEFAULT_.
It's still not included in Access 2007 _BY_DEFAULT_.

-Aaron

Those are not the exact words as the library shows up.  That is not
the name of the library.

It is clearly the DAO library and it is used to execute the same DAO code
that people have been writing for well over a decade.  Squirm all you want,
but you are still wrong.

[snipped some pathetic attempts at analogy]

DAO is not included with some of the optional database _ENGINES_ for

Microsoft Access.

So now the goalposts have been moved to "some of the optional engines"?

Just like mySql has different _ENGINES_ so does MS Access.

Irrelevent to your original statement.

DAO is not included in _ANY_ of the formats for 4+ years.. and it's
not included / available in the MOST IMPORTANT formats of Microsoft
Access.

The first point is WRONG.  The second is irrelevent to your original
statement.

Your so called DAO is a joke.
Any library- where you have to instantiate and dispose-- in a
particular order-- is complete crap.
It is a complete waste of time.

I was not making any judgement about the merits of DAO.  Only correcting
your incorrect statement that it is not included in Office (every single
version that includes Access).

The next thing that you're going to be claiming is that DAO.net is
going to be the default library for VB 2010!!!

No.  That would be irrelevent to the discussion.

_WHATEV_ kid.

A lot of us have seen your shot er...picture Aaron.  We know who the "kid"
is. I think the frequency of the words "kid" and "bully" in your posts
explain the pathology of your usenet history quite a bit.

DAO is not included or available for any version of MDAC that has come
out in the past decade.

More moved goalposts?  Who was discussing what is and is not included in
MDAC?  Lots of things are not included in MDAC.  DAO however is included in
every version of Access despite your incorrect claims to the contrary.

 

[Rick Brandt]

it is not included in Office or Access or Windows _BY_DEFAULT_.

It is available.

It's still not included in Access 2000 _BY_DEFAULT_.
It's still not included in Access 2002 _BY_DEFAULT_.
It's still not included in Access 2003 _BY_DEFAULT_.
It's still not included in Access 2007 _BY_DEFAULT_.

Okay, I get it now. You are so ignorant that you think that not having a
reference set to a library in a new file by default means the same thing as
"it is not included". Of course the former was only true of Access 2000 and
2002. 2003 restored the default DAO reference and I frankly have no idea
what 2007 does in that regard.

The fact remains that one can perform a standard installation of Office (any
of those versions) and then run an Access file on that PC that uses the DAO
library and everything will work. By any sane person's definition that
means DAO "is included" in all of those versions.

Again, I am not arguing for or against using DAO over using ADO. If you
prefer ADO then hey, knock yourself out. Just don't come into technical
groups pretending you know what you're talking about.

 

[George Nicholson]

I frankly have no idea

what 2007 does in that regard.

FYI: Default references in a blank 2007 accdb file:

Visual Basic for Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object library (points to
ACEDAO.DLL)

So, looks to me like DAO (or its current incarnation) is a default reference
in 2007 as well.

 

[a a r o n . k e m p f]

you are wrong.

DAO is not the default in 2003 and 2007.

Sorry-- I won't put up with your mis-information.

-aaron

 

[a a r o n . k e m p f]

looks to me like

a) someone else finally caught on that it depends on which filetype
you choose
b) it is called 'Access Database Engine Object Library'.

I find it a travesty that

1) you think that this is DAO
2) you think that DAO is 'faster' than ADO
3) you think that DAO is 'simpler' than ADO
4) you think that DAO is _FUNCTIONAL_. It is not. it is a buggy buggy
buggy library. I should have to .CLOSE or SET = NOTHING for anything
I do.
Sorry. but it's written in stone; I'd rather use mySql / php than
DAO. but when it comes to Access? I choose the default library--
_ADO_.