I agree with aytee111, it would be much easier using a form. Not only exporting the data, but starting the execution of the code.
Since you don't want to use a form, your options are to use code to create a text file and write each record to the file, then close the text (csv) file.
Or you can create another table with the identical structure of the main table. For each mobile number, you would append the records to the duplicate table, then export the data using "Docmd.TransferText" as in aytee111's first example.
And both options require more code.
Here is the code for the duplicate table option. (this is a no form option)
Create a duplicate table to hold the records for the mobile numbers. The data gets deleted each time the mobile number changes.
The code saves the CSV files in a folder named "CSV", which is a subfolder of the currentproject path. If the folder doesn't exist, it is created.
There are a lot of code examples that allow you to select the forlder to save the CSV files..... you'll have to add this option.
Add the following code to a standard module:
Code:
Option Compare Database
Option Explicit
Public Sub ExportByMobile()
Dim d As DAO.Database
Dim s As DAO.Recordset
Dim sSQL As String
Dim sPath As String
Dim tmp As String
Dim FileName As String
Set d = CurrentDb
'save path
sPath = CurrentProject.Path & "\CSV"
tmp = Dir(sPath, vbDirectory)
'if dir doesn't exist, create it
If tmp = "" Then
MkDir sPath
End If
'select all unique mobile numbers
sSQL = "SELECT DISTINCT Field1"
sSQL = sSQL & " FROM MainTable"
sSQL = sSQL & " ORDER BY Field1;" 'Field1 is the mobile number
Set s = d.OpenRecordset(sSQL)
'check for records
If Not s.BOF And Not s.EOF Then
s.MoveLast
s.MoveFirst
Do While Not s.EOF
'delete any existing records in tblMobile
d.Execute "DELETE * FROM tblMobile;"
'append records
sSQL = "INSERT INTO tblMobile ( Field1, Field2, Field3, Field4 )"
sSQL = sSQL & " SELECT Field1, Field2,"
sSQL = sSQL & " Field3, Field4"
sSQL = sSQL & " FROM MainTable"
sSQL = sSQL & " WHERE Field1= '" & s!field1 & "';"
' Debug.Print sSQL
d.Execute sSQL, dbFailOnError
DoCmd.TransferText acExportDelim, , "tblMobile", sPath & "\mobile_" & s!field1 & ".csv", True
s.MoveNext
Loop
End If
s.Close
Set s = Nothing
Set d = Nothing
MsgBox "Done! CSV files saved in " & vbNewLine & vbNewLine & sPath
End Sub
Since you didn't provide the real table & field names, you will need to edit the code (in BLUE) change them to your table/field names.
Code without using a form is quite a bit longer, isn't it???