|
| |
|
|
maduko
Posts: 168
From: Tulsa OK USA
Status: offline
 |
Write to Access - existing record - 11/15/2001 17:28:00
I'm working on an intranet form connected to an Access database. I want to insert values into blank fields of existing records.On my first try all I get is a new record with the ID number I submitted. How do you make the form modify the values for existing records with that ID number? UPDATE_
My Access file is connected as a file in the current web and verified through the database properties. The SQL query I have kluged together is... UPDATE DISTINCTROW
PEIdir
SET proof=': :proof::', logo='::logo::', payment=': :payment::'
WHERE 'Member Class'='::Member Class::' Thanks in advance.
[This message has been edited by maduko (edited 11-19-2001).]
|
|
|
|
Mojo
Posts: 2429
From: Chicago
Status: offline
|
RE: Write to Access - existing record - 11/15/2001 18:30:00
That all depends on how your connecting to the database to make the update.If your using the DRW, check out some of Spooky's tutorials and links about updating at:
http://www.outfront.net/spooky/update.htm If your built your own connections confirm that you don't have recordset.AddNew if your using that method. You just need recordset.UpDate. If your using a SQL statement you need to have a WHERE clause in that statement. Joe
| |
|
|
|
maduko
Posts: 168
From: Tulsa OK USA
Status: offline
|
RE: Write to Access - existing record - 11/15/2001 19:16:00
I'm connecting to the Access file through the standard FP connection. It's on my intranet and doesn't have a DSN or anything like that.I just discovered the tutorials and attempted the SQL statement. It didn't seem to work though.
| |
|
|
|
Mojo
Posts: 2429
From: Chicago
Status: offline
|
RE: Write to Access - existing record - 11/15/2001 20:19:00
Are you using the Spooky diet in your DRW?What is your SQL statement? Joe
| |
|
|
|
maduko
Posts: 168
From: Tulsa OK USA
Status: offline
|
RE: Write to Access - existing record - 11/15/2001 20:59:00
Negative. I need to look into that.I tried pasting the SQL from the example, modifying the field names to match mine. That must not be enough to make it click.
| |
|
|
|
Mojo
Posts: 2429
From: Chicago
Status: offline
|
RE: Write to Access - existing record - 11/15/2001 23:56:00
You can do updates without using the Spooky diet, but going on the diet opens up more possibilities.If you post your actual SQL statement we may be able to help you with it. Joe
| |
|
|
|
maduko
Posts: 168
From: Tulsa OK USA
Status: offline
|
RE: Write to Access - existing record - 11/16/2001 23:31:00
quote:
Originally posted by jbennett:
If you post your actual SQL statement we may be able to help you with it.Joe
I thought I was getting a grip on it last night. It still puts the results in to a new row. Grrrr. Here's the SQL... UPDATE DISTINCTROW
PEIdir
SET proof=': :proof::', logo='::logo::', payment=': :payment::'
WHERE 'Member Class'='::Member Class::'
[This message has been edited by maduko (edited 11-16-2001).]
| |
|
|
|
Guest
|
RE: Write to Access - existing record - 11/19/2001 20:21:00
connection?
| |
|
|
|
Spooky
Posts: 26598
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Write to Access - existing record - 11/19/2001 16:29:00
UPDATE DISTINCTROW
PEIdir
SET proof=': ![]("http://www.frontpagewebmaster.com/ubb/tongue.gif") roof::', logo='::logo::', payment=': ayment::'
WHERE [Member Class]='::MemberClass::'Make sure MemberClass (the form input) is one word. If its numeric, then it should have no quotes ------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart
| |
|
|
|
maduko
Posts: 168
From: Tulsa OK USA
Status: offline
|
RE: Write to Access - existing record - 11/19/2001 17:33:00
Thanks!I got it working and quickly discovered the Access zero-length trap. That's all happy now. But.... Now the concern is my update form can overwrite existing data without anyone knowing. For instance, we log a proof has arrived then another shows up a week later. The only idea so far is to append the input to the field instead of replacing it. But I can't seem to figure out how to make that happen! ------------------
++++++++++++++++++
® Hobby Time_ www.places2ride.com
Home Page_ www.maduko.com
Day Gig_ www.pei.org
| |
|
|
|
Spooky
Posts: 26598
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: Write to Access - existing record - 11/19/2001 22:16:00
Ideally you will only update the record associated with a unique ID.Updating based on a text field may overwrite records. The other way is to add a new dated record? ------------------
§þððk¥
"I am Spooky of Borg. Prepare to be assimilated, babycakes!"
Subscribe to OutFront News
Database / DRW Q & A
The Spooky Login!
VP-ASP Shopping cart
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
