Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Export a table automatically with a certain output.

    Hi all,
    I have a tricky question (for me ) and I'm happy for anybody who could help me out with this:


    In my main table, I have 3 fields for example, which are called "country1", "country2", "country3"
    With the LookUp-Assistant, I defined, that in each field (country1, country2 and country3), there should be a combo-box which offers me all countrys from the table "country" to choose when entering data.
    In my example, this table just contains 2 countries, USA and Canada

    Now, my question is:
    Is there a program, which first checks the lookup-table "country" for the field names it serves as lookup table (in my example: country1, country2, country3).

    Then, it produces this text:

    ADD VALUE LABELS country1
    1 USA
    2 Canada
    .
    EXECUTE

    ADD VALUE LABELS country2
    1 USA
    2 Canada
    .
    EXECUTE

    ADD VALUE LABELS country3
    1 USA
    2 Canada
    .
    EXECUTE


    This output can be saved either in one txt file or 3 separate ones, it doesn't matter.
    The only thing important is that the output is automatically reproduced with each variable the table "country" serves as a lookup-table, as already shown in the example above.

    Adding to this, it would be cool if, at the beginning, the program could ask to choose a certain table, or even to do this whole procedure with several tables I'v chosen one after another like a batch process.

    Thank you so much in advance for your help, Mike
    Last edited by zooz; 04-15-12 at 19:04.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should not be difficult to write the necessary procedure, however some clarifications are needed:
    Quote Originally Posted by zooz View Post
    Hi all,
    In my main table, I have 3 variables (a) for example, which are called "country1", "country2", "country3"
    For each of these variables i look up the values in a certain lookup table (b), which is called "country". In my example, it just contains 2 countries, USA and Canada, like this
    ...
    This output can be saved either in one txt file or 3 separate ones (c), it doesn't matter.
    a) Tables don't have variables. Do you mean Row (Line) or Column (Field)?
    b) How do you determine which value(s) to pick up from the lookup table?
    c) Under which name(s)?
    Have a nice day!

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    Hi Sinndho, thanks for the quick answer!

    Sorry for any ambiguities.


    a) Tables don't have variables. Do you mean Row (Line) or Column (Field)?
    What I meant:
    In my main table, I have 3 Fields/Columns, which have different names e.g. country1, country2, country3

    b) How do you determine which value(s) to pick up from the lookup table?
    With the LookUp-Assistant, I defined, that in each field (country1, country2 and country3), there should be a combo-box which offers me all countrys from the table "country" to choose when entering data.

    The program which I need now, should just insert all values that are in the table "country" into the text file

    The table "country" in my example contains just 2 values and looks like this:

    ID Field1
    1 USA
    2 Canada

    c) Under which name(s)?
    If the program/code creates 3 separate files, best would be the name of the field, e.g. country1.txt, country2.txt and country3.txt

    If the program/code creates the whole text in one file, the text file may have the name of the table, in my example just country.txt


    Thank you!
    Last edited by zooz; 04-15-12 at 18:56.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this?
    Code:
    Sub CreateTextFiles(Optional ByVal Path As String)
    
        Const c_SQL As String = "SELECT ID, Field1 FROM Country;"
        
        Dim rst As DAO.Recordset
        Dim intHandle As Integer
        Dim i As Long
        
        If Len(Path) > 0 Then
            If Right(Path, 1) <> "\" Then Path = Path & "\"
        End If
        Set rst = CurrentDb.OpenRecordset(c_SQL, dbOpenSnapshot)
        With rst
            For i = 1 To 3
                intHandle = FreeFile
                Open Path & "Country" & i & ".txt" For Output As #intHandle
                Print #intHandle, "ADD VALUE LABELS country" & i
                Do Until .EOF
                    Print #intHandle, CStr(!ID) & " " & !Field1
                    .MoveNext
                Loop
                Print #intHandle, "."
                Print #intHandle, "EXECUTE"
                Close #intHandle
                .MoveFirst
            Next i
            .Close
        End With
        Set rst = Nothing
          
    End Sub
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •