Re: Need help to for access programming

ma_041964@xxxxxxxx wrote:
I have a query with the name "A" and "B". I want ro write a program
which do following steps:

1. Open the query "A"
2. Copy all the query "A"
3. Create a text file in c:\temp\ab.txt
4. Paste the copied query "A" into c:\temp\ab.txt
5.Open the query "B"
6. Copy all the query "B"
7. Paste the copied query "B" into c:\temp\ab.txt (append mode)
8. close the file c:\temp\ab.txt
9. Open the file c:\temp\ab.txt with the notepad

I was able to do step 1 but the rest goes beyond what i know about

Function TERMINE()
On Error GoTo TERMINE_Err

DoCmd.OpenQuery "A", acViewNormal, acEdit
ND RB NO LOCALISATION", acViewNormal, acEdit

Exit Function

MsgBox Error$
Resume TERMINE_Exit

End Function

Thank You for Your help!

Here's an example that writes a single query to a text file.
It includes more than you asked for, but the examples illustrate
points that are non-intuitive and can burn you later. If your query
doesn't get criteria from forms, just comment out those parts.

You would do the same thing for query B, only you want to
use Open (f) For Append As #1 instead of Open (f) For Output As #1.

Private Sub Command38_Click()
'export incoming to TB
'On Error GoTo Err_Command38_Click

Dim db As Database
Dim qdf As QueryDef
Dim hist As Recordset
Dim rec As Long
Dim the_sql, who, where, f, proj As String
Dim s1, s2 As String

'path & filename taken from Form
proj = [Forms]![terrabase_incoming_SDG_picker]![List36]
who = [Forms]![terrabase_incoming_SDG_picker]![List2]
where = [Forms]![terrabase_incoming_SDG_picker]![path]

f = where & "\" & proj & "_" & who & "_incoming" & ".txt"
' if default path on form is D:\dataout
' if project 1000 selected from form project list
' if SDG 6F666666 selected from form SDG list
' then f = D:\dataout\1000_6F666666_incoming.txt

Set db = CurrentDb()

'use an existing query
Set qdf = db.QueryDefs("labdeliverable_SDG_export_to_TB")

'set parameter used by criteria?
'yes, just like xtabs that must have parameters set when using form
' objects as criteria, a regular query that uses such criteria
' must have the corresponding "parameter" set when using VB
' to do an OpenRecordset

'this is the criteria for the [sdg] field in the query. it will not
' be set automatically, we have to get it and stuff it into
' the querydef (even if it is already set in the querydef
parameter list)
' add project criteria for those SDGs that have samples from
multiple projects
'List2 is the SDG selected
s1 = [Forms]![terrabase_incoming_SDG_picker]![List2]
'List36 is the project selected
s2 = [Forms]![terrabase_incoming_SDG_picker]![List36]

'Note no brackets inside brackets
qdf![Forms!terrabase_incoming_SDG_picker!List2] = s1
qdf![Forms!terrabase_incoming_SDG_picker!List36] = s2

'open the query
Set hist = qdf.OpenRecordset

'report how many records query returnd
rec = hist.RecordCount
MsgBox (rec & " lab records")

'skip if query returned nothing
If rec > 0 Then
'open the text file for writing - use "For Append" to add to
existing file
Open (f) For Output As #1

'return to first record to begin output

'process each record until we run out - note that the record
'reported earlier could change while we are running! Better to
'check for EOF than try to track how many we've written.
While Not hist.EOF
'process a single record
'in this example, the text output desired is pipe (|)
'a single string, properly delimited, will be constructed
'then written to the text file. for each field to be
'modifications may be necessary.
With hist
s1 = .Fields(0) & "|" '[laboratory
s1 = s1 & .Fields(1) & "|" '[project id]
s1 = s1 & .Fields(2) & "|" '[sdg]
s1 = s1 & .Fields(3) & "|" '[analytical
'[site sample id] CANNOT exceed 25 chars - will NOT
import into TB!
' excess MUST be trimmed off resulting in truncated
date codes,
' FD markers (which are incorrect format anyway) or
other LABQC
' flags. EDD processing should have set critical fields
s1 = s1 & Left$(.Fields(4), 25) & "|" '[site sample
s1 = s1 & .Fields(5) & "|" '[sample date]
s1 = s1 & .Fields(6) & "|" '[top depth]
s1 = s1 & .Fields(7) & "|" '[middle depth]
s1 = s1 & .Fields(8) & "|" '[bottom depth]
s1 = s1 & .Fields(9) & "|" '[sample point
s1 = s1 & .Fields(10) & "|" '[lab sample
s1 = s1 & .Fields(11) & "|" '[lab sample
s1 = s1 & .Fields(12) & "|" '[matrix]
s1 = s1 & .Fields(13) & "|" '[field sample
s1 = s1 & .Fields(14) & "|" '[filtration
s1 = s1 & .Fields(15) & "|" '[extraction
s1 = s1 & .Fields(16) & "|" '[prep date]
s1 = s1 & .Fields(17) & "|" '[analysis
s1 = s1 & .Fields(18) & "|" '[instrument
'[rough percent moisture] field in TB can't hold
floating point
'output from Excel. Idiots.
If Not IsNull(.Fields(19)) Then
s2 = Format$(Val(.Fields(19)), "0.0")
s2 = ""
End If
s1 = s1 & s2 & "|" '[rough percent
s1 = s1 & .Fields(20) & "|" '[dilution
s1 = s1 & .Fields(21) & "|" '[analyte type]
s1 = s1 & .Fields(22) & "|" '[analytical
s1 = s1 & .Fields(23) & "|" '[cas]
s1 = s1 & .Fields(24) & "|" '[Parameter]
s1 = s1 & .Fields(25) & "|" '[retention
s1 = s1 & .Fields(26) & "|" '[detection
s1 = s1 & .Fields(27) & "|" '[result]
s1 = s1 & .Fields(28) & "|" '[lab
s1 = s1 & .Fields(29) '[units]
'done processing record, s1 is now a string containing one
'line for the output file
End With
'write the line to the output file
Print #1, s1
'advance to the next record in the query, EOF with set if
there are no more
End If
'close the text file
Close #1
'close the query

Exit Sub

MsgBox Err.Description
Resume Exit_Command38_Click

End Sub


Relevant Pages

  • Programatically Changing Query Criteria
    ... Save your query in SQL. ... the following to determine your criteria. ... Dim strSQL As String ... MsgBox "There was a problem building the SQL String" ...
  • RE: Counting of Records in a Before opening a Data Form
    ... First make sure that the query returns records. ... select the criteria. ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
  • RE: Counting of Records in a Before opening a Data Form
    ... the form "Criteria Building Form" MUST be open. ... Would you post the SQL of the query "Criteria"? ... Dim rstCriteria As DAO.Recordset ... Dim stDocName As String ...
  • RE: Complex query criteria - desperate appeal
    ... The following will search for query by form: ... > appropriate SQL string if it is not. ... > Dim qdf as DAO.QueryDef ... > criteria in text fields, you would have to modify these somewhat to give the ...
  • Re: Too few parameters
    ... Dim txb_MonthStartDate as Date etc... ... > There are 4 criteria in Qry_Orders. ... > click in the query window and open the Parameters dialog box. ... >> I am experimenting with recordsets. ...