Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 22nd, 2006, 03:45 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating Recordset

Hello.

I'm working on a project and I think I do not understand the rst command.

I am working on a form where the information is disconnected from the table. So, I have some code where they type in the ISBN number and it pulls the information from the table and populates the text boxes appropriately.

The problem lies in the next few steps.
Step 1: The user edits the information.
Step 2: The user clicks the update button to update the recordset.

The problem is, I'm not sure I understand how to update the recordset. I know I need to use rst.AddNew and rst.Update, but that causes the problem of trying to create a new record. I do not want a new record created, I just want the existing record modified.

Could someone explain or help please.

Best Regards,
Arholly

 
Old December 23rd, 2006, 03:40 PM
Authorized User
 
Join Date: Jan 2006
Posts: 35
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you tried rst.Edit?

HTH RuralGuy (RG for short) acXP WinXP Pro
 
Old December 26th, 2006, 08:29 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

When you have the form open, I am not sure if your recordset or connection to the data are still open.

In order to work with disconnected data, you need to open a connection to the data, then, using that connection, you need to pull the data you want to work on in a recordset - or view depending on the recordset characteristics.

Assuming that your connection is open, and your recordset is editable, AND, you are currently on the record you want to edit, the difference between adding a new record and updating the existing one is this:

Add a new record:
rst.AddNew
.....enter data
rst.Update

Edit an existing recor:
.....modify data
rst.Update

So really only remove the rst.AddNew line from your code.

If you want to get to a particular record, open your recordset by using the PK, and a select statement:

"SELECT * FROM tblMyTable WHERE [PKField] = " & vMyPK

This SHOULD only pull one record in the recordset, and just modify without using AddNew at the beginning, just Update at the end, using the syntax you are already familiar with.

Did that help?



mmcdonal
 
Old December 26th, 2006, 03:16 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, I tried taking out the rst.AddNew and it doesn't work. What I end up getting is an error saying I'm duplicating the primary key. We I go to debug, it puts me on the rst.Update line.

mmcdonal, I already use the SQL syntax you suggested because you were the one who suggested it to me before.

One thing I tried was removing the line:
Code:
rst("ISBN") = [ISBN]
from my code and that just had it overwriting the very first entry in the table, which obviously doesn't do me a lot of good. :(

 
Old December 26th, 2006, 03:26 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You're supposed to REmove the addnew, not use it.

If you use AddNew - Update, this adds a new record.

If you use only Update, this updates whatever record you have browsed to.

If this doesn't work, please post all of the code you are using at one time.



mmcdonal
 
Old December 26th, 2006, 03:30 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, I'll post all the code used in this form. There are two different parts of it. The first is an on_exit snippet for the ISBN lookup. It is:
Code:
Private Sub ISBN_Exit(Cancel As Integer)
On Error GoTo cmdISBN_Lookup_Error
Set db = CurrentDb()
Dim strISBN, sSQL As String
strISBN = Me.ISBN
sSQL = "SELECT * FROM tbl_Books WHERE [ISBN] = '" & strISBN & "'"

Set rst = db.OpenRecordset(sSQL, dbOpenDynaset)

'all information in here where "Order#" is a column in your table

[Book_Title] = rst("Book_Title")
[Publisher] = rst("Publisher")
[Copyright] = rst("Copyright")
[Edition] = rst("Edition")
[Author] = rst("Author")
[Book_Type] = rst("Book_Type")
[Trial_Software] = rst("Trial_Software")
[Comments] = rst("Comments")

rst.Close

Exit Sub

cmdISBN_Lookup_Error:
    MsgBox ("There was an error while executing your command.")

End Sub
The code for the update button is as follows:
Code:
Private Sub Command12_Click()
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_Books", dbOpenDynaset)

rst.Edit
rst("ISBN") = [ISBN]
rst("Book_Title") = [Book_Title]
rst("Publisher") = [Publisher]
rst("Copyright") = [Copyright]
rst("Edition") = [Edition]
rst("Author") = [Author]
rst("Book_Type") = [Book_Type]
rst("Trial_Software") = [Trial_Software]
rst("Comments") = [Comments]
rst.Update
rst.Close

End Sub
And yes, I figured out I was supposed to remove the rst.AddNew, that much I've figured out. I'm just trying to wrap my hands around (the neck of) rst.

 
Old December 26th, 2006, 03:43 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

rst is just the recordset you are dealing with. It can have one or thousands of rows in it at the same time.

When you parameterize the recordset by selecting a specific record based on the PK, then there is only one record in the recordset.

The code in the first section does what you want the code in the second section to do.

It selects one record based on the ISBN, and then modifies all values in the record except the PK.

HTH

mmcdonal
 
Old December 26th, 2006, 03:50 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Okay, so then let me ask this then. Obviously, this is not the best way to handle this. What would be the best way. What needs to happen is this:

1) User needs to enter ISBN number.
2) Fields need to populate with all relevant information.
3) User can edit any information on the form.
4) User must click Update button for information to update the table.

What's the best way to do this, and please remember, I'm just wrapping my hands around VBA (ordered a few more books for me to read), so be gentle. And I actually like explanations so I can learn it, not just be given the code.

Thanks,
Arholly

 
Old December 26th, 2006, 04:15 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am assuming from your description that there is already some data in the database re: ISBN's.

So that would mean there is some table with ISBN values. If that is so, then create a look up to the ISBN values in a combo box, and allow the user to select an ISBN value.

Once they make that selection, the rest of the fields should populate, if the table is local and the form is bound to the table. Is this correct?

Just a little more on structure then:

Are the tables local, or in another database?
Is there are local data?
Is the form bound to a recordset or table?
Why can't the user have a bound form and make changes in the form, instead of clicking an update button?


mmcdonal
 
Old December 26th, 2006, 04:56 PM
Authorized User
 
Join Date: Aug 2006
Posts: 85
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello, I'll try to answer your questions and hope that my answers make sense. I really do appreciate your help and it's helping me be able to do things on my own.
Quote:
quote:Are the tables local, or in another database?
The tables are local.
Quote:
quote:Is there are local data?
  Assuming you mean is there data in the local table - yes. Otherwise, you have lost me.
 
Quote:
quote:Is the form bound to a recordset or table?

No, originally I was told this was a bad idea because I didn't want the users to be able to make changes directly to the table.
 
Quote:
quote:Why can't the user have a bound form and make changes in the form, instead of clicking an update button?

See the answer above for some of the answer. The problem is in the past, with the previous database we had been using, users had a form bound to the table and were making mistakes all over the place and overwriting data. So, it's been requested that they not be able to make changes to the table directly or in some way be prompted if they are making changes to the table. When I first posed the question to someone here, they told me the best method would be to make it so the form was not bound to the table and use an update button or something to that effect. Now, if there is a better way to handle this, I am all for it at this point. For me, I would like to do things in a best practice method.
 
Quote:
quote:I am assuming from your description that there is already some data in the database re: ISBN's.

  Correct.
 
Quote:
quote:So that would mean there is some table with ISBN values. If that is so, then create a look up to the ISBN values in a combo box, and allow the user to select an ISBN value.

  I could, the problem is that there are hundreds of lookups then. Still, I know I can do that if I needed to.

Thanks again for your help.






Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
Current Recordset does not support updating. u813222 Classic ASP Professional 1 March 3rd, 2006 08:41 PM
ADODB.Recordset (0x800A0CB3)Current Recordset does tks_muthu Classic ASP Databases 0 June 16th, 2005 07:22 AM
Huge problem updating FoxPro with ADO recordset erankin42 Classic ASP Databases 5 October 18th, 2004 03:08 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.