Adding records to DAO.Recordset

[bILLyYaNk]

Hello,
I am having a difficult time editing a record and making 2 records out of it. What I need to do is take one record and break it in two parts and output both parts sequentially. There has got to be an easy way to do this that I am not seeing. I Know how to break apart the record but making it two records in order is where I fail to have an easy solution. I am using DAO.Recordset with this type of code:

rstMyRecords.Edit
rstYesNo(0) = newStrLINE
rstMyRecords.Update

Right now I am using some weird queries and functions to get it done.

Thanks I received some great help last time I had a question.

 

[SkipVought]

bILLyYaNk,

You'll need to .Edit to update AND .AddNew to insert to rstMyRecords

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[bILLyYaNk]

Yes I know that but the problem is if I addNew it adds to the end of the record set and I need it to be updated in order. For example I want to break the BA record in two and leave it in the correct order.

See Below:

Records in file:
B;A;000005125459;C;R;;HUGHES LEAH;22251
S;N;000005125455;LETTER #100 PRECOLLECT
S;N;000005125453;LETTER #100 DELETED
S;N;000005125457;LETTER #100 PRECOLLECT

Needed results:
B;A;000005125459;
C;R;;HUGHES LEAH;22251
S;N;000005125455;LETTER #100 PRECOLLECT
S;N;000005125453;LETTER #100 DELETED
S;N;000005125457;LETTER #100 PRECOLLECT

Thanks!

 

[PHV]

Be aware that there is NO physical record order in relational database.
When you want to retrieve values in sequence, you have to use the ORDER BY clause of the SELECT instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bILLyYaNk]

PHV: So what your saying is it can't be done unless I tag the records first to give them an order. That kind of messes me up because I don't want to add anything to the record. I guess there isn't an easy solution. I can always tag them put them in order then filter the tag before I output but that is a little cumbersome. I run into this situation a lot. Any suggestions anyone? It doesn't even need to be done in a DOA recordset if you have a easy way.
T/Y

 

[Mute101]

PHV is correct, if you want a no nonsense 'tag' as you call it add an autonumber field into your table. This will create a key field on which you can use the ORDER BY clause.

Alternatively you could store the date you add the record and use that to order the table but this could have repercussions if you do bulk updates and end up with multiple rows with the same date/time stamp.

----------------------------------------
I was once asked if I was ignorant or just apathetic. I said, "I don't know, and I don't care."
----------------------------------------

 

[SkipVought]

Billy,

All that a well designed table is is a RELATION (data that is related)

Ordering or Aggregating the data, are actions that are performed at report (query) time.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}