473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Can't Make a Simple Change to a Record in a Single Table

Hello

I'm trying to save data to an Existing Record in a single table. These
fields
are not on my Form.
It is not saving the data to the record ???
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
strSQL = "SELECT * FROM [T-ADXOR] Where ID = 19"
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.BOF = False And rs.EOF = False Then
[Field1] = strPassStamp
[Field2] = strPermStamp
MsgBox [Field1] '<------- The values are showing but not saving
MsgBox [Field2] '<------- " "
End If

ALSO I am in desperate need of good reference material advice. I spend
ALL day
hunting for syntax & rules information. Anything one can recommend?

ThankYou

May 16 '06 #1
5 1230
"Ap******@gmail.com" <Ap******@gmail.com> wrote in
news:11*********************@g10g2000cwb.googlegro ups.com:
Hello

I'm trying to save data to an Existing Record in a single
table. These fields
are not on my Form.
It is not saving the data to the record ???
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
strSQL = "SELECT * FROM [T-ADXOR] Where ID = 19"
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.BOF = False And rs.EOF = False Then
[Field1] = strPassStamp
[Field2] = strPermStamp
MsgBox [Field1] '<------- The values are showing but
not saving MsgBox [Field2] '<------- " "
End If

ALSO I am in desperate need of good reference material advice.
I spend ALL day
hunting for syntax & rules information. Anything one can
recommend?

ThankYou

add the two statements. I'd also qualify the two statements
explicitly
If rs.BOF = False And rs.EOF = False Then rs.edit '<---- added rs![Field1] = strPassStamp '<---- improved
rs![Field2] = strPermStamp '<---- improved rs.update '<---- added End If

as to documentation, buy or borrow Access Developer's Handbook
by Getz et al.

--
Bob Quintal

PA is y I've altered my email address.
May 16 '06 #2
Suggestions:

1. Make sure you have:
Option Explicit
at the top of every module. This will prevent Access from just making up a
variable for any name it does not recognise.

2. Before you can change the fields of a record, you need to Edit, and
afterwards to Update:
rs.Edit
'change the fields here
rs.Update

3. Refer to the fields of the recordset like this:
rs![Field1] = strPassStamp

Instead of opening a recordset, you could also do it by executing an Update
query statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<Ap******@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...
Hello

I'm trying to save data to an Existing Record in a single table. These
fields
are not on my Form.
It is not saving the data to the record ???
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
strSQL = "SELECT * FROM [T-ADXOR] Where ID = 19"
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
If rs.BOF = False And rs.EOF = False Then
[Field1] = strPassStamp
[Field2] = strPermStamp
MsgBox [Field1] '<------- The values are showing but not saving
MsgBox [Field2] '<------- " "
End If

ALSO I am in desperate need of good reference material advice. I spend
ALL day
hunting for syntax & rules information. Anything one can recommend?

ThankYou

May 16 '06 #3

ThankYou

Took Your Advice and It Worked!

May 16 '06 #4
CurrentDb.Execute "UPDATE [T-ADXOR] SET [Field1] = '" & strPassStamp &
"', [Field2] = '" & strPermStamp & "' WHERE ID = 19"

(which will result in this query string being executed)
UPDATE [T-ADXOR] SET [Field1] = 'qwerty', [Field2] = 'asdfgh' WHERE ID
= 19
assuming
strPassStamp = "qwerty"
strPermStamp = "asdfgh"

I discourage the use of DAO Recordsets except in unusual circimstances.
They are slow, inefficient, dangerous and require (too) many lines of
code.

May 16 '06 #5
rkc
Ap******@gmail.com wrote:
ALSO I am in desperate need of good reference material advice. I spend
ALL day
hunting for syntax & rules information. Anything one can recommend?


The Help files via the Object Browser or any web search engine.
May 16 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Charles Robinson III | last post by:
The AutoNumber sequence in my database is currently formatted as '000000'. When a new record is created, the AutoNumber would go from 000001, 000002, 000003 and so on. I want to track via...
1
by: darrel | last post by:
I'm trying to whip up a fancy repeater control that will put records into a two-column table for me. This is how I envision it working: itemtemplate if record count = odd then write out the...
4
by: PercyPercy | last post by:
For some reason I can't write the proper query to get a simple total from a single table. Here is the basic table design: Transaction ID___Product ID___ Transaction Date____Transaction Quantity...
1
by: gaurkamal | last post by:
I want to delete record in table B when i delete record in Table A both table have a common column. I want to do it using trigger .Can any body give some idea. Table details are. Table A: ...
0
by: Serman D. | last post by:
Hi, I can't understand why mysql allows me to grant lock tables to all tables in a particular database, while it fails to accept the same command applied to a single table only. I've tested on...
10
by: jambonjamasb | last post by:
Hi all, I am new to this and am looking for some direction I have the ideas, but am having trouble putting it into practice. Any help would be greatly appreciated. I have set up three tables. ...
14
by: Mark | last post by:
I have a table with a field that uses a combobox to populate values. The Lookup tab within table design mode is the following: Display Control Combo Box Row Source Type ...
2
by: mvsmsh | last post by:
Hi , all We have hundreds of tables in on tablespace , but I'm planning to change as one table per one tablespace. . I wonder how other shops does ? Are there any special reasons in UDB...
0
by: Neven Huynh | last post by:
Hi Everyone, Here i my LINQ query to get record in Table Menu with condition are parentID == 0(get root menu) and ID != (parentID list) (which is parent ID list is are id of menu record that have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.