I've looked at the code and have some suggestions.
You should not begin an object name with a number. (2ColumnTags, 9CRRoster)
Do not use spaces, special characters or punctuation (exception is the underscore) in object names (City-ST-ZIP / better would be City_ST_ZIP)
'~~~~~~~~~~~~~~~~~
You do not check to see if "DivisionInputBox" has two numbers. What happens if someone enters "ZZ" instead of numbers?
I changed Public Function exportDivision() to Public Sub ExportDivision() and moved the code to a standard Module. I would have a form with a text box to enter the division and button that calls Public Sub ExportDivision()
Code:
Private Sub btnTest_Click()
' if the entry is a number then
Call ExportDivision
'else
' exit sub
'end if
End Sub
'~~~~~~~~~~~~~~~~~
This
Code:
GetDBPath = CurrentProject.Path & "\ToIndesign" & "Division" & DivisionInputBox & "_to_Indesign"
ExportFileName = GetDBPath & ".txt"
can be replaced with
Code:
ExportFileName = CurrentProject.Path & "\ToIndesignDivision" & DivisionInputBox & "_to_Indesign.txt"
'~~~~~~~~~~~~~~~~~
Why do you have "[2ColumnTags]![DivisionName]" in the SQL string? (several places). You should use the DOT (.) instead of the BANG (!)
'~~~~~~~~~~~~~~~~~
The FROM clause worries me. You have
Code:
FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID
You have an inner join "Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID" then a CARTESIAN JOIN with table "2ColumnTags".
'~~~~~~~~~~~~~~~~~
I made some changes to the code (in BLUE)
Code:
Public Sub ExportDivision()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim DivisionInputBox As String
Dim ASCII As String
Dim GetDBPath As String
Dim ExportFileName As String
Dim strSql As String
Dim fso As Variant
Dim a As Variant
Dim i As Integer
'===================================================================
'These variables are not used in the code and can be deleted
' Dim Unit As String
' Dim Expr1 As String
' Dim ID As String
' Dim Office As String
' Dim MemberName As String
' Dim OfficeAndName As String
' Dim LAST_NM As String
' Dim Street_1 As String
' Dim Street_2 As String
' Dim City_ST_ZIP As String
' Dim EMAIL As String
' Dim HOME_NM As String
' Dim WORK_NM As String
' Dim FAX_NM As String
' Dim CELL_NM As String
' Dim UnitNo As Integer
'===================================================================
Set db = CurrentDb
DivisionInputBox = InputBox("DivisionInputBox", "Division input", "Enter 2 digit division:")
ASCII = "<ASCII-WIN>"
' GetDBPath = CurrentProject.Path & "\ToIndesign" & "Division" & DivisionInputBox & "_to_Indesign"
' ExportFileName = GetDBPath & ".txt"
'the following line can used this instead of the two lines above
ExportFileName = CurrentProject.Path & "\ToIndesignDivision" & DivisionInputBox & "_to_Indesign.txt"
'Debug.Print ExportFileName
'Call CreateNewFile(ExportFileName)
Set rs = db.OpenRecordset("Divisions")
strSql = "SELECT [2ColumnTags].[DivisionName] & [Office] & ""<0x0009>"" & [MemberName] AS OfficeAndName,"
strSql = strSql & " [2ColumnTags].[DivisionAddress] & [ADD1] AS Street_1, [9CRRoster].ADD2 AS Street_2,"
strSql = strSql & " [2ColumnTags].[PMTag] AS Expr2, [CITY] & "", "" & [STATE] & "" "" & [ZIPCODE] AS [City-ST-ZIP], [9CRRoster].EMAIL,"
strSql = strSql & " [9CRRoster].HOME_NM, [9CRRoster].WORK_NM, [9CRRoster].FAX_NM, [9CRRoster].CELL_NM"
strSql = strSql & " FROM 2ColumnTags, Divisions INNER JOIN 9CRRoster ON Divisions.MemberNumber = [9CRRoster].EMP_ID"
strSql = strSql & " WHERE Right([Divisions]![Unit], 2)) = " & DivisionInputBox
strSql = strSql & " ORDER BY Divisions.ID;"
'Debug.Print strSql
Set rs1 = db.OpenRecordset(strSql)
If Not rs1.BOF And Not rs1.EOF Then
rs1.MoveLast
rs1.MoveFirst
' print column names
'Debug.Print rs1.Fields.Count
'For i = 0 To rs1.Fields.Count - 2
' Debug.Print rs(i).Name; ' & vTab print col names separated with a tab one from each other
'Next i
'Debug.Print rs1(rs.Fields.Count - 1).Name 'last one without ; so it adds the newline
Set fso = CreateObject("Scripting.FileSystemObject")
Set a = fso.CreateTextFile(ExportFileName, True)
a.WriteLine ASCII
Do While Not rs1.EOF
For i = 0 To rs1.Fields.Count - 2
If rs1(i) > "" Then
'Debug.Print rs1(i) & vbCr; 'print values separated with a carriage return from
a.WriteLine rs1(i)
End If
Next i
rs1.MoveNext
Loop
End If
'Clean up
On Error Resume Next
a.Close 'Close the recordset
rs.Close 'Close the recordset
rs1.Close 'Close the recordset
Set fso = Nothing
Set rs = Nothing
Set rs1 = Nothing
Set db = Nothing
End Sub
Good luck with your project......