Code to update fields from a "Non Updateable" query.

[Guest]

I see this question comes up all the time, but I still haven't got it right.

Users want to update a Yes/No field (VolLet) on table "TREG" a record at a
time by clicking a command button on a continuous form (FVolLet) which is
based on a non updateable querry(Group by with sums and count totals).

This is the first time a am trying to write "Update" code.

The following code gives me a type mismatch error.

VolLet is Yes/No format
RecordNo is a text field.

Can someone determine the error (s), or maybe suggest that I am going about
this the wrong way and point me in a more appropriate direction.


Private Sub CmdUpdate_Click()
On Error GoTo Err_CmdUpdate_Click

Dim db As Database
Dim rst As Recordset
Dim RecordNo As String
Dim VolLet As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TREG")
rst.Seek "=", Forms!FVolLet!RecordNo
VolLet = -1
rst.Update


rst.Close


Exit_CmdUpdate_Click:
Exit Sub

Err_CmdUpdate_Click:
MsgBox Err.Description
Resume Exit_CmdUpdate_Click

End Sub


Thank you

 

[Allen Browne]

You could update the record like this:

Set rs = db.OpenRecordset("SELECT VolLet FROM TREG WHERE [ID] = " &
Forms!FVolLet!RecordNo & ";")
If rs.RecordCount > 0 Then
rs.Edit
rs!VolLet = -1
rs.Update
End If

This code:
a) loads the recordset with only the record you need;
b) tests that a record was found;
c) uses Edit and Update around the change.

If you need to determine why your query us not updatable, this new article
might help:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thank you.

For some reason I am getting a compile error 461 (Method or data member not
found) on the rs.Edit line.

I have reviewed the help message for this error and can't find the problem.

What may be causing this?

I do use your site to ease my learning curve. In this case I understand why
my querry is not updatable.

You could update the record like this:

Set rs = db.OpenRecordset("SELECT VolLet FROM TREG WHERE [ID] = " &
Forms!FVolLet!RecordNo & ";")
If rs.RecordCount > 0 Then
rs.Edit
rs!VolLet = -1
rs.Update
End If

This code:
a) loads the recordset with only the record you need;
b) tests that a record was found;
c) uses Edit and Update around the change.

If you need to determine why your query us not updatable, this new article
might help:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

I see this question comes up all the time, but I still haven't got it
right.

Users want to update a Yes/No field (VolLet) on table "TREG" a record at
a
time by clicking a command button on a continuous form (FVolLet) which is
based on a non updateable querry(Group by with sums and count totals).

This is the first time a am trying to write "Update" code.

The following code gives me a type mismatch error.

VolLet is Yes/No format
RecordNo is a text field.

Can someone determine the error (s), or maybe suggest that I am going
about
this the wrong way and point me in a more appropriate direction.


Private Sub CmdUpdate_Click()
On Error GoTo Err_CmdUpdate_Click

Dim db As Database
Dim rst As Recordset
Dim RecordNo As String
Dim VolLet As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TREG")
rst.Seek "=", Forms!FVolLet!RecordNo
VolLet = -1
rst.Update

rst.Close

Exit_CmdUpdate_Click:
Exit Sub

Err_CmdUpdate_Click:
MsgBox Err.Description
Resume Exit_CmdUpdate_Click
End Sub

 

[SteveS]

PMFJI,

Check your references. In the code window, click on TOOLS/REFERENCES. You
should have a reference for "Microsoft DAO 3.x Object Library". For A2K, it is
DAO 3.6.

The DAO reference should be above the reference to "Microsoft Active X Data
Objects"


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Thank you.

For some reason I am getting a compile error 461 (Method or data member not
found) on the rs.Edit line.

I have reviewed the help message for this error and can't find the problem.

What may be causing this?

I do use your site to ease my learning curve. In this case I understand why
my querry is not updatable.


:

You could update the record like this:

Set rs = db.OpenRecordset("SELECT VolLet FROM TREG WHERE [ID] = " &
Forms!FVolLet!RecordNo & ";")
If rs.RecordCount > 0 Then
rs.Edit
rs!VolLet = -1
rs.Update
End If

This code:
a) loads the recordset with only the record you need;
b) tests that a record was found;
c) uses Edit and Update around the change.

If you need to determine why your query us not updatable, this new article
might help:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

I see this question comes up all the time, but I still haven't got it
right.

Users want to update a Yes/No field (VolLet) on table "TREG" a record at
a
time by clicking a command button on a continuous form (FVolLet) which is
based on a non updateable querry(Group by with sums and count totals).

This is the first time a am trying to write "Update" code.

The following code gives me a type mismatch error.

VolLet is Yes/No format
RecordNo is a text field.

Can someone determine the error (s), or maybe suggest that I am going
about
this the wrong way and point me in a more appropriate direction.


Private Sub CmdUpdate_Click()
On Error GoTo Err_CmdUpdate_Click

Dim db As Database
Dim rst As Recordset
Dim RecordNo As String
Dim VolLet As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TREG")
rst.Seek "=", Forms!FVolLet!RecordNo
VolLet = -1
rst.Update

rst.Close

Exit_CmdUpdate_Click:
Exit Sub

Err_CmdUpdate_Click:
MsgBox Err.Description
Resume Exit_CmdUpdate_Click
End Sub

 

[Guest]

I am using Access 2000 and I had previously selected "Microsoft DAO 3.6
Object Library". I added this and it has a priority 6 after the libraries
that were selected as part of the windows instal(I imagine). I have now also
run a "Compact and Repair", but this has not aleviated the problem.

Any other ideas?

Thank you

PMFJI,

Check your references. In the code window, click on TOOLS/REFERENCES. You
should have a reference for "Microsoft DAO 3.x Object Library". For A2K, it is
DAO 3.6.

The DAO reference should be above the reference to "Microsoft Active X Data
Objects"


HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Thank you.

For some reason I am getting a compile error 461 (Method or data member not
found) on the rs.Edit line.

I have reviewed the help message for this error and can't find the problem.

What may be causing this?

I do use your site to ease my learning curve. In this case I understand why
my querry is not updatable.


:

You could update the record like this:

Set rs = db.OpenRecordset("SELECT VolLet FROM TREG WHERE [ID] = " &
Forms!FVolLet!RecordNo & ";")
If rs.RecordCount > 0 Then
rs.Edit
rs!VolLet = -1
rs.Update
End If

This code:
a) loads the recordset with only the record you need;
b) tests that a record was found;
c) uses Edit and Update around the change.

If you need to determine why your query us not updatable, this new article
might help:
Why is my query read-only?
at:
http://allenbrowne.com/ser-61.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

I see this question comes up all the time, but I still haven't got it
right.

Users want to update a Yes/No field (VolLet) on table "TREG" a record at
a
time by clicking a command button on a continuous form (FVolLet) which is
based on a non updateable querry(Group by with sums and count totals).

This is the first time a am trying to write "Update" code.

The following code gives me a type mismatch error.

VolLet is Yes/No format
RecordNo is a text field.

Can someone determine the error (s), or maybe suggest that I am going
about
this the wrong way and point me in a more appropriate direction.


Private Sub CmdUpdate_Click()
On Error GoTo Err_CmdUpdate_Click

Dim db As Database
Dim rst As Recordset
Dim RecordNo As String
Dim VolLet As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TREG")
rst.Seek "=", Forms!FVolLet!RecordNo
VolLet = -1
rst.Update

rst.Close

Exit_CmdUpdate_Click:
Exit Sub

Err_CmdUpdate_Click:
MsgBox Err.Description
Resume Exit_CmdUpdate_Click
End Sub

 

[Nick via AccessMonster.com]

PMFJI as well,

Ralph, one thing you may want to do is disambiguate your references.

Instead of:
Dim db as Database
Dim rs as Recordset

Use:
Dim db as DAO.Database
Dim rs as DAO.Recordset

That way, your program knows to go to the DAO library. It may be that your
program is going to the wrong library... I don't know what else could be
causing your "Method not found" error.

HTH,
Nick

I am using Access 2000 and I had previously selected "Microsoft DAO 3.6
Object Library". I added this and it has a priority 6 after the libraries
that were selected as part of the windows instal(I imagine). I have now also
run a "Compact and Repair", but this has not aleviated the problem.

Any other ideas?

Thank you

PMFJI,

[quoted text clipped - 87 lines]

 

[SteveS]

Ralph,

Both DAO and ADO have "Database" and "Recordset". If you don't disambiguate
your references, Access uses the one with lowest priority (closest to the top).
I usually just move the DAO reference above the ADO reference in the references
list or uncheck the ADO reference. The "proper" way, like Nick said, is to use
DAO.Database
DAO.Recordset
or
ADO.Database
ADO.Recordset.


Sorry. I should have been more clear.

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

PMFJI as well,

Ralph, one thing you may want to do is disambiguate your references.

Instead of:
Dim db as Database
Dim rs as Recordset

Use:
Dim db as DAO.Database
Dim rs as DAO.Recordset

That way, your program knows to go to the DAO library. It may be that your
program is going to the wrong library... I don't know what else could be
causing your "Method not found" error.

HTH,
Nick

I am using Access 2000 and I had previously selected "Microsoft DAO 3.6
Object Library". I added this and it has a priority 6 after the libraries
that were selected as part of the windows instal(I imagine). I have now also
run a "Compact and Repair", but this has not aleviated the problem.

Any other ideas?

Thank you

PMFJI,

[quoted text clipped - 87 lines]

Resume Exit_CmdUpdate_Click
End Sub

 

[Douglas J. Steele]

<picky>
There is no Database object in the ADO model.
</picky>

<not picky>
Objects in the ADO model are declared as:

Dim rs As ADODB.Recordset

not as

Dim rs As ADO.Recordset

</not picky>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ralph,

Both DAO and ADO have "Database" and "Recordset". If you don't
disambiguate your references, Access uses the one with lowest priority
(closest to the top). I usually just move the DAO reference above the ADO
reference in the references list or uncheck the ADO reference. The
"proper" way, like Nick said, is to use DAO.Database
DAO.Recordset
or
ADO.Database
ADO.Recordset.


Sorry. I should have been more clear.

Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

PMFJI as well,

Ralph, one thing you may want to do is disambiguate your references.

Instead of:
Dim db as Database
Dim rs as Recordset

Use:
Dim db as DAO.Database
Dim rs as DAO.Recordset

That way, your program knows to go to the DAO library. It may be that
your
program is going to the wrong library... I don't know what else could be
causing your "Method not found" error.

HTH,
Nick

I am using Access 2000 and I had previously selected "Microsoft DAO 3.6
Object Library". I added this and it has a priority 6 after the
libraries that were selected as part of the windows instal(I imagine). I
have now also run a "Compact and Repair", but this has not aleviated the
problem.

Any other ideas?

Thank you

PMFJI,


[quoted text clipped - 87 lines]

Resume Exit_CmdUpdate_Click
End Sub

 

[SteveS]

.... I just can't seem to get it right today!

Thanks Doug. You have to be picky (I prefer precise, but...), otherwise the
code doesn't do what you want it to do.


Steve S.

 

[Guest]

Thank you all:

I was still having problems - probably because I don't really understand all
this stuff - but I am learning. I did have to go to a programer and he put
together this - using ADODB, and then added some bells and whistles.


Private Sub CmdUpdate_Click()

Dim db As Database
Dim rs As New ADODB.Recordset

On Error GoTo Err_CmdUpdate_Click

'Set the connection properties
rs.CursorLocation = adUseClient
'Open the recordset
rs.Open "Select * from TREG where reportno = '" & Me.Recordset!ReportNo
& "'", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

'Check to see if the recordset is at the BOF or EOF
If rs.EOF Or rs.BOF Then
MsgBox "You do not have any records selected to work with.",
vbInformation, "Warning"
'Close out the recordset and free up system memory
rs.Close
Set rs = Nothing
Exit Sub
End If

rs!VolLet = vbTrue
'Must update in order to save
rs.Update

'Close out the recordset and free up system memory
rs.Close
Set rs = Nothing

Me.Refresh
Exit Sub
Err_CmdUpdate_Click:

Select Case Err.Number
Case Else
MsgBox Err.Description
End Select

End Sub