473,320 Members | 1,865 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,320 software developers and data experts.

Having problems with AddNew

Hi First time i am posting a question so here goes...

I have created a bit of VBA for pupils where the aim is: input line of text from file;parse the input line and extract values; using the AddNew, populate the respetive fields in the table. General outline is:

Expand|Select|Wrap|Line Numbers
  1. Set cncurrent = CurrentProject.Connection
  2. Set rsDiag = New ADODB.Recordset
  3.  
  4. 'Open the database table
  5. strsql = "Select * from tblEquipment"
  6. rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimistic
  7. .....
  8. Open FileName For Input As #1
  9. ....
  10. Do While Not EOF(1)
  11. ....
  12. rsDiag.AddNew
  13. rsDiag!NumberPlate = NumberPlate
  14. rsDiag!TypeOfEquipment = TypeOfEquipment
  15. Loop
  16. ....
Pupils are updating to suit their application. Some pupils have had no problems however, some have. The problem occurs second time through the loop where control is transfered back to the form (where the above code is linked with a button) when the rsDiag.AddNew statement executes. No run time errors are generated, just a very graceful return to the calling form. It is as if rsDiag.AddNew is seen as End Sub but only the second time through the loop.

Totally baffled and have some very frusterated pupils, who, in order to achieve came to my Saturday session today!!

Please help. By the way, how will I know when their is a response to this post? Certainly don't want to miss it. If you do reply can you please advice me by email so I know to look ********
Thanks in advance
Mar 29 '08 #1
4 2160
Stewart Ross
2,545 Expert Mod 2GB
Hi. I have removed your e-mail address from the message for your own protection. The inclusion of e-mail addresses in posted messages is against the site rules - sorry.

MODERATOR
Mar 29 '08 #2
Stewart Ross
2,545 Expert Mod 2GB
Expand|Select|Wrap|Line Numbers
  1. ...
  2. Do While Not EOF(1)
  3. ....
  4. rsDiag.AddNew
  5. rsDiag!NumberPlate = NumberPlate
  6. rsDiag!TypeOfEquipment = TypeOfEquipment
  7. rsDiag.Update ' <<<
  8. Loop
  9. ....
Hi. If the code fragment posted is correct you are missing an update statement - the addnew adds a new record and makes it current, and after the two field values are set you need to call the Update method to store the updated new record in the recordset (identified by <<< above). The code fragment you posted is fairly short, and there are no other issues immediately obvious.

-Stewart
Mar 29 '08 #3
Hi Thanks for your reply. I did actually try the update but still did not work.

However, had a thought after submiting post. The table being populated was on the many side of a 1-to-many relationship. I had expected pupils to only populate from file tables on one side of 1-to-many. I suppose not possible to populate a child (on many side) when parent is non-existant (on one side)

Once I deleted the relationship, all was well. I have just picked up this group, so will really need to have a good look at the relationships they have set up!!

By the way, is Update essential. Code is working fine without it?

Thanks again. This is a great forum - have learnt a lot from it.
Mar 29 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
Hi. Glad you found the problem - relational integrity rules do indeed prevent many-side records being added where there is no one-side record to match.

An extract from the Help for the Update method is shown below. It shows that an automatic update is occurring when you move away from the current record. I would still explicitly include the call to update, for clarity however.
Recordset
Use the Update method to save any changes you make to the current record of a Recordset object since calling the AddNew method or since changing any field values in an existing record. ...

If you move from the record you are adding or editing before calling the Update method, ADO will automatically call Update to save the changes. ...

Record
The Update method finalizes additions, deletions, and updates to fields in the Fields collection of a Record object.

For example, fields deleted with the Delete method are marked for deletion immediately but remain in the collection. The Update method must be called to actually delete these fields from the provider's collection. ...
-Stewart
Mar 30 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Jan van Veldhuizen | last post by:
I have an application which is running fine with MS SqlServer, but it should be working with Oracle as weel. At a lot of places we rely upon the ADO Recordset to return incremented identity...
0
by: Jose_Csharp | last post by:
Hi group. I´m trying to add new records to a sql server data table with: BindingContext.AddNew(); This sentence clear all my textboxes and not add any record to the database, the I use the next...
1
by: Brian | last post by:
Hey all - I have a windows form (using VB) in which I display basic information about a person, all of which is housed (for now) in a single Access table, which I'll call "tblPerson". I would...
2
by: guy | last post by:
if i use Generics.AddNew how do I pass an object to the items constructor? I have a set of class all inheriting from a base class, all requiring one parameter on their constructor - the data...
2
by: pillmill | last post by:
I replaced AddNew statments with INSERT INTO, but am unable to write to the same tables. Foreign keys violations are the main errors. Why are these occuring ? Before: set rs3=...
8
by: MLH | last post by:
Here's a snippet from A97 HELP on AddNew... The record that was current before you used AddNew remains current. If you want to make the new record current, you can set the Bookmark property to...
5
by: BeruthialsCat | last post by:
First go with trying to import xml to a database and whilst i have managed to do what i want i find that the xml files we have here at work are gonna cause me problems. I have a function that...
5
by: amey.gupte | last post by:
Hi, I am using the AddNew function in VBA to populate my access table from data in excel. The following is the piece of code: With WriteRS .AddNew !column_Names(0) = "acctNo" !B = "CoName"...
1
by: teenagelcruise | last post by:
hi, i have a problem with my code which is i cannot update and addnew data into the database but i can delete the data.plz give me an idea.this is my code that i wrote. <html> <head> <meta...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: 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)...
0
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...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
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
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.