Update Table

[lschlitt]

I need to update selected records in a table, each with a unique value. I
have tried selecting the records into a temporary table, then using a WHILE
statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[Guest]

I don't think you need a temporary table. I assume you cannot do these
updates using an Update query, but you can fill your recordset with an
updateable query and modify its records.

Barry

 

[lschlitt]

I am willing to do it however it works. I am not real familiar with VB in
access, but program in several other languages. I am willing to do
recordsets, but can't get that to work either. Can you give me a code
example?

 

[Guest]

Would an straight update query work or do you need to pass in values from
code variables?

There are a bunch of examples in Access help and on MS's site on how to
instantiate and use a DAO recordset.
What specific problems are you having?

Barry

 

[lschlitt]

I need to update a single field, but the field increments on each record that
is updated. I looked for examples on MS site, but have not been able to find
any. Perhaps I was looking in the wrong area? I tried different searches....
..

Would an straight update query work or do you need to pass in values from
code variables?

There are a bunch of examples in Access help and on MS's site on how to
instantiate and use a DAO recordset.
What specific problems are you having?

Barry

I am willing to do it however it works. I am not real familiar with VB in
access, but program in several other languages. I am willing to do

[quoted text clipped - 11 lines]

 

[Guest]

I understand now. I don't know of an easy way to do this with an Update
statement. I think your simplest solution would be to walk a recordset in
code.

Barry

I need to update a single field, but the field increments on each record that
is updated. I looked for examples on MS site, but have not been able to find
any. Perhaps I was looking in the wrong area? I tried different searches....
..

Would an straight update query work or do you need to pass in values from
code variables?

There are a bunch of examples in Access help and on MS's site on how to
instantiate and use a DAO recordset.
What specific problems are you having?

Barry

I am willing to do it however it works. I am not real familiar with VB in
access, but program in several other languages. I am willing to do

[quoted text clipped - 11 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt]

Sounds good to me.
Could you provide me with an example of how to do this? I have never had
much luck with recordsets.

I understand now. I don't know of an easy way to do this with an Update
statement. I think your simplest solution would be to walk a recordset in
code.

Barry

I need to update a single field, but the field increments on each record that
is updated. I looked for examples on MS site, but have not been able to find

[quoted text clipped - 15 lines]

 

[Guest]

There's a bajillion examples in the Access help and online. Here's a little
snippet to get you started:

Dim rsThis as DAO.Recordset
Dim intCounter as Integer
Set rsThis = CurrentDb.OpenRecordset("SELECT Blah From tblBlah WHERE
Blah.Jibber = '" & strJabber & "'", dbOpenDynaset)
With rsThis
Do Until .EOF
!Blah = intCounter
intCounter=intCounter+1
Loop
End With
rsThis.Close
Set rsThis=Nothing

HTH,
Barry

Sounds good to me.
Could you provide me with an example of how to do this? I have never had
much luck with recordsets.

I understand now. I don't know of an easy way to do this with an Update
statement. I think your simplest solution would be to walk a recordset in
code.

Barry

I need to update a single field, but the field increments on each record that
is updated. I looked for examples on MS site, but have not been able to find

[quoted text clipped - 15 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt]

Yes, that did fill in a couple gaps I was missing, and hadn't found info on
in Access help or online.

I tried running it, and on the
Dim rsThis as DAO.Recordset line, I get the error that the User defined
type is not defined. I have tried similar things before, and gotten similar
errors. I am running VB 6.3, in Access 2000. Are you familiar with this?
(I feel like I may be finally making some headway)

There's a bajillion examples in the Access help and online. Here's a little
snippet to get you started:

Dim rsThis as DAO.Recordset
Dim intCounter as Integer
Set rsThis = CurrentDb.OpenRecordset("SELECT Blah From tblBlah WHERE
Blah.Jibber = '" & strJabber & "'", dbOpenDynaset)
With rsThis
Do Until .EOF
!Blah = intCounter
intCounter=intCounter+1
Loop
End With
rsThis.Close
Set rsThis=Nothing

HTH,
Barry

Sounds good to me.
Could you provide me with an example of how to do this? I have never had

[quoted text clipped - 11 lines]

 

[Guest]

You have to have a reference to the DAO library. In the VBE, go to Tool,
References and look for a Microsoft DAO Object Library reference. Usually,
the later the revision, the better, as long as all you users have the same
version of MDAC as you. When in doubt, it's usually ok to use 3.51.

Barry

Yes, that did fill in a couple gaps I was missing, and hadn't found info on
in Access help or online.

I tried running it, and on the
Dim rsThis as DAO.Recordset line, I get the error that the User defined
type is not defined. I have tried similar things before, and gotten similar
errors. I am running VB 6.3, in Access 2000. Are you familiar with this?
(I feel like I may be finally making some headway)

There's a bajillion examples in the Access help and online. Here's a little
snippet to get you started:

Dim rsThis as DAO.Recordset
Dim intCounter as Integer
Set rsThis = CurrentDb.OpenRecordset("SELECT Blah From tblBlah WHERE
Blah.Jibber = '" & strJabber & "'", dbOpenDynaset)
With rsThis
Do Until .EOF
!Blah = intCounter
intCounter=intCounter+1
Loop
End With
rsThis.Close
Set rsThis=Nothing

HTH,
Barry

Sounds good to me.
Could you provide me with an example of how to do this? I have never had

[quoted text clipped - 11 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt]

Thank you so much for your help. I have made some progress, slowly.
On the openrecordset, what is referenced is a table that contains the
correct records to update. I tried to do the SELECT as you suggested, and it
kept giving me errors about the wrong number of values specified. So, I
tried this, and made a little progress.
Next question -- It said I needed to do an EDIT in order to update, but the
value is not being updated in the table. Ideas? It is correctly looping
through the recordset.


Here is what I have now:
Set dbs = CurrentDb
Set rsThis = dbs.OpenRecordset("Barber Renewal Processing")
With rsThis
Do Until .EOF
rsThis.Edit
rsThis!RENEWALNO = "XXXX"
RenewalNoIn.Value = RenewalNoIn.Value + 1
rsThis.Update
rsThis.MoveNext
Loop
End With
rsThis.Close


*****************************************************************

You have to have a reference to the DAO library. In the VBE, go to Tool,
References and look for a Microsoft DAO Object Library reference. Usually,
the later the revision, the better, as long as all you users have the same
version of MDAC as you. When in doubt, it's usually ok to use 3.51.

Barry

Yes, that did fill in a couple gaps I was missing, and hadn't found info on
in Access help or online.

[quoted text clipped - 29 lines]

 

[Guest]

Thank you so much for your help. I have made some progress, slowly.
On the openrecordset, what is referenced is a table that contains the
correct records to update. I tried to do the SELECT as you suggested, and it
kept giving me errors about the wrong number of values specified. So, I
tried this, and made a little progress.

You could also use the recordset's Filter property to filter records. This
way you'r enot walking the entire recordset looking for your records.

Next question -- It said I needed to do an EDIT in order to update, but the
value is not being updated in the table. Ideas? It is correctly looping
through the recordset.

This looks right to me. Is the RenewalNo field set up to take the values
you're passing? Does it raise an error or just not update the values?

Here is what I have now:
Set dbs = CurrentDb
Set rsThis = dbs.OpenRecordset("Barber Renewal Processing")
With rsThis
Do Until .EOF
rsThis.Edit
rsThis!RENEWALNO = "XXXX"
RenewalNoIn.Value = RenewalNoIn.Value + 1
rsThis.Update
rsThis.MoveNext
Loop
End With
rsThis.Close


*****************************************************************

You have to have a reference to the DAO library. In the VBE, go to Tool,
References and look for a Microsoft DAO Object Library reference. Usually,
the later the revision, the better, as long as all you users have the same
version of MDAC as you. When in doubt, it's usually ok to use 3.51.

Barry

Yes, that did fill in a couple gaps I was missing, and hadn't found info on
in Access help or online.

[quoted text clipped - 29 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt]

THANK YOU!!! YOU ARE WONDERFUL!!! I THINK IT MIGHT BE WORKING NOW.
Thanks for filling in all the gaps. I may still have another question, but I
am getting things updated.

Any idea why I couldn't put a SELECT in the OPENRECORDSET statement!

Thank you so much for your help. I have made some progress, slowly.
On the openrecordset, what is referenced is a table that contains the
correct records to update. I tried to do the SELECT as you suggested, and it
kept giving me errors about the wrong number of values specified. So, I
tried this, and made a little progress.

You could also use the recordset's Filter property to filter records. This
way you'r enot walking the entire recordset looking for your records.

Next question -- It said I needed to do an EDIT in order to update, but the
value is not being updated in the table. Ideas? It is correctly looping
through the recordset.

This looks right to me. Is the RenewalNo field set up to take the values
you're passing? Does it raise an error or just not update the values?

Here is what I have now:
Set dbs = CurrentDb

[quoted text clipped - 24 lines]

 

[Guest]

The OpenRecordset method does accept Select statements. There may have been
an issue with the construction of your Where clause. It requires just the
right use of quotation marks if you're referring to other values in your code
or form. If you still want to use this method, post you Select statement and
we'll take a look.

Glad I could help.
Barry

THANK YOU!!! YOU ARE WONDERFUL!!! I THINK IT MIGHT BE WORKING NOW.
Thanks for filling in all the gaps. I may still have another question, but I
am getting things updated.

Any idea why I couldn't put a SELECT in the OPENRECORDSET statement!

Thank you so much for your help. I have made some progress, slowly.
On the openrecordset, what is referenced is a table that contains the
correct records to update. I tried to do the SELECT as you suggested, and it
kept giving me errors about the wrong number of values specified. So, I
tried this, and made a little progress.

You could also use the recordset's Filter property to filter records. This
way you'r enot walking the entire recordset looking for your records.

Next question -- It said I needed to do an EDIT in order to update, but the
value is not being updated in the table. Ideas? It is correctly looping
through the recordset.

This looks right to me. Is the RenewalNo field set up to take the values
you're passing? Does it raise an error or just not update the values?

Here is what I have now:
Set dbs = CurrentDb

[quoted text clipped - 24 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt via AccessMonster.com]

I am trying something a little different now. This is what I have have.
I am getting an error on the openrecordset command :

Too few parameters: Expected 1

Set dbs = CurrentDb
Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where barber.
[license_number] =
licenseno.value")

With rsThis
Do Until .EOF
rsThis.Edit
rsThis!ACT_INACT = "A"
rsThis.Update
rsThis.MoveNext
Loop
End With
rsThis.Close
Set rsThis = Nothing





**************************************************************************************************

The OpenRecordset method does accept Select statements. There may have been
an issue with the construction of your Where clause. It requires just the
right use of quotation marks if you're referring to other values in your code
or form. If you still want to use this method, post you Select statement and
we'll take a look.

Glad I could help.
Barry

THANK YOU!!! YOU ARE WONDERFUL!!! I THINK IT MIGHT BE WORKING NOW.
Thanks for filling in all the gaps. I may still have another question, but I

[quoted text clipped - 23 lines]

 

[Guest]

Change it to:
Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where
barber.[license_number] = " & licenseno)
assuming [license_number is a numeric field. If it's a text field, it should
be:

Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where
barber.[license_number] = "'" & licenseno & "'")

Barry

I am trying something a little different now. This is what I have have.
I am getting an error on the openrecordset command :

Too few parameters: Expected 1

Set dbs = CurrentDb
Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where barber.
[license_number] =
licenseno.value")

With rsThis
Do Until .EOF
rsThis.Edit
rsThis!ACT_INACT = "A"
rsThis.Update
rsThis.MoveNext
Loop
End With
rsThis.Close
Set rsThis = Nothing





**************************************************************************************************

The OpenRecordset method does accept Select statements. There may have been
an issue with the construction of your Where clause. It requires just the
right use of quotation marks if you're referring to other values in your code
or form. If you still want to use this method, post you Select statement and
we'll take a look.

Glad I could help.
Barry

THANK YOU!!! YOU ARE WONDERFUL!!! I THINK IT MIGHT BE WORKING NOW.
Thanks for filling in all the gaps. I may still have another question, but I

[quoted text clipped - 23 lines]

statement to read these records, and update the corresponding records in the
table. I am having problems getting this to work. Any ideas?

 

[lschlitt via AccessMonster.com]

Yes, Thank you! That worked!

Change it to:
Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where
barber.[license_number] = " & licenseno)
assuming [license_number is a numeric field. If it's a text field, it should
be:

Set rsThis = dbs.OpenRecordset("Select Act_inact from barber where
barber.[license_number] = "'" & licenseno & "'")

Barry

I am trying something a little different now. This is what I have have.
I am getting an error on the openrecordset command :

[quoted text clipped - 33 lines]