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
access:

Function TERMINE()
On Error GoTo TERMINE_Err


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


TERMINE_Exit:
Exit Function

TERMINE_Err:
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"
'EXAMPLE
' 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?
'[Forms]![SDG_picker]![List2]
'
'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
hist.MoveLast
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
hist.MoveFirst

'process each record until we run out - note that the record
count
'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 (|)
delimited
'a single string, properly delimited, will be constructed
and
'then written to the text file. for each field to be
appended,
'modifications may be necessary.
With hist
s1 = .Fields(0) & "|" '[laboratory
id]
s1 = s1 & .Fields(1) & "|" '[project id]
s1 = s1 & .Fields(2) & "|" '[sdg]
s1 = s1 & .Fields(3) & "|" '[analytical
fraction]
'[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
already.
s1 = s1 & Left$(.Fields(4), 25) & "|" '[site sample
id]
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
id]
s1 = s1 & .Fields(10) & "|" '[lab sample
id]
s1 = s1 & .Fields(11) & "|" '[lab sample
type]
s1 = s1 & .Fields(12) & "|" '[matrix]
s1 = s1 & .Fields(13) & "|" '[field sample
classification]
s1 = s1 & .Fields(14) & "|" '[filtration
method]
s1 = s1 & .Fields(15) & "|" '[extraction
date]
s1 = s1 & .Fields(16) & "|" '[prep date]
s1 = s1 & .Fields(17) & "|" '[analysis
date]
s1 = s1 & .Fields(18) & "|" '[instrument
id]
'[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")
Else
s2 = ""
End If
s1 = s1 & s2 & "|" '[rough percent
moisture]
s1 = s1 & .Fields(20) & "|" '[dilution
factor]
s1 = s1 & .Fields(21) & "|" '[analyte type]
s1 = s1 & .Fields(22) & "|" '[analytical
method]
s1 = s1 & .Fields(23) & "|" '[cas]
s1 = s1 & .Fields(24) & "|" '[Parameter]
s1 = s1 & .Fields(25) & "|" '[retention
time]
s1 = s1 & .Fields(26) & "|" '[detection
limit]
s1 = s1 & .Fields(27) & "|" '[result]
s1 = s1 & .Fields(28) & "|" '[lab
qualifier]
s1 = s1 & .Fields(29) '[units]
'done processing record, s1 is now a string containing one
complete
'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
hist.MoveNext
Wend
End If
'close the text file
Close #1
'close the query
hist.Close

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
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" ...
    (microsoft.public.access.macros)
  • 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 ...
    (microsoft.public.access.formscoding)
  • 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 ...
    (microsoft.public.access.formscoding)
  • 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 ...
    (microsoft.public.access.queries)
  • 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. ...
    (microsoft.public.access.formscoding)