Using recordset to insert blank record

[Motion]

Hi there

I have data in a table that reads:

1
1
1
1
2
2
2
3
3

I would like to insert a blank row between each block of data ie:

1
1
1
1

2
2
2

3
3

I have a query (called qryDifferences)that counts the rows of each group, somehow i now need to use the recordset method to insert a blank row using this query as a reference to how often it inserts the row.

I have this probably laughable attempt at this stage:


Dim dbs As Database, qdf As QueryDef, strSQL As String
Dim rst As Recordset

Set dbs = CurrentDb

strSQL = "SELECT [tblExportInvoices].[Invoice ID], Count([Invoice ID]) AS Difference, " & _
" FROM [tblExportInvoices] GROUP BY [tblExportInvoices].[Invoice ID];"

Set rst = dbs.OpenRecordset("qryDifferences", dbOpenForwardOnly)

While (Not rst.EOF)
For i = 1 To rst!Difference

insSQL = "INSERT into tblExportInvoices ([Invoice ID]) VALUES ('')"

' MsgBox (insSQL)
dbs.Execute (insSQL)
Next
rst.MoveNext
Wend

Set rst = Nothing
Set dbs = Nothing


It currently adds a row for each group but not in between the records. Any help would be greatly appreciatd.

Thanks

Richard

 

[boblarson]

Why? this is definitely NOT what Access is for. If you need this functionality - go to Excel. There should NOT be a blank record between "groups" in an Access table. A Relational database, of which Access is, should have a unique value per row, and a blank value is not unique. It really doesn't make sense to me (and probably a lot of others).

So, why are you thinking you need this and we might be able to come up with a method of helping you deal with what your ACTUAL business need is.

 

[gemma-the-husky]

bob is most defrinitely correct

if you are looking to extract the number (count) of each type of record however, access can easily do that for you.

 

[Motion]

Sorry, I should have explained further. I am needing to export invoice data from an access database to an accounting account programme (see my thread here: http://www.access-programmers.co.uk/forums/showthread.php?t=147842 ) and am using the DoCmd.TranserText method.

I need a blank row to seperate the data.

Furthermore, i have managed to acheive my goal detailed in this thead but now have another problem as my blank row that i have inserted has resulted in a row of commas in my output file (see attached). This is obviously beause i have exported a blank row complete with blank fields.

Any ideas on how to remove the comma's from the export file or insert a line?

Thanks

 

[Motion]

I have just checked and the accounting software does seem to recognise the row of commas as line (sorry should of checked this first).

My problem is solved. Thanks for everyone's help.

Richard