Having problems with AddNew

[mayen001]

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:

[code=vb]Set cncurrent = CurrentProject. Connection
Set rsDiag = New ADODB.Recordset

'Open the database table
strsql = "Select * from tblEquipment"
rsDiag.Open strsql, cncurrent, adOpenDynamic, adLockOptimisti c
.....
Open FileName For Input As #1
....
Do While Not EOF(1)
....
rsDiag.AddNew
rsDiag!NumberPl ate = NumberPlate
rsDiag!TypeOfEq uipment = TypeOfEquipment
Loop
....[/code]

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

[Stewart Ross]

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

[Stewart Ross]

[code=vb]...
Do While Not EOF(1)
....
rsDiag.AddNew
rsDiag!NumberPl ate = NumberPlate
rsDiag!TypeOfEq uipment = TypeOfEquipment
rsDiag.Update ' <<<
Loop
....[/code]

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

[mayen001]

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.

[Stewart Ross]

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