Export from an Access Form to Exel one record at a time 1

[lr999]

I need a sample VBA code to transfer a form to Exel by Script ID

 

[randy700]

You don't transfer forms to Excel, you transfer data.
Take a look at the TransferSpreadsheet method.


Randy

 

[lr999]

sorry of course it's data, but I need to transfer one record at a time: here is my code:

Public Sub ExportScript()
'Export Scripts to Exel
Dim strmsg As String
strmsg = "Are you ready to export the Script?"
strmsg1 = "Export completed and the files are located in the directory:"

If MsgBox(strmsg, vbYesNo, "export File") = vbYes Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qryScriptQuery", "C:\Scripts.xls", True
MsgBox strmsg1 & CurDir & "."
Else
MsgBox "Export cancelled."

End If

End Sub

 

[randy700]

What is / isn't working?


Randy

 

[lr999]

it's transfers all the records and I need one record at a time,
my record have a field Script ID which is the key, whenever I press the button on the form EXPORT Script, I need Only one record to be exported to Exel ,for that Script Id key


thanks

 

[Andrzejek]

Just a guess here, but what's in [tt]qryScriptQuery[/tt]?

Have fun.

---- Andy

 

[randy700]

Sounds like you need a WHERE clause in your query.
How about posting the code?


Randy

 

[lr999]

SELECT Script.ScriptID, Script.[Script#], Script.ScriptEntryCriteria, Script.ScriptExitCriteria, Script.ScriptDataConstraints, Script.Comments, Script.[Reviewer Comments], Script.ScriptDeveloper, Script.[Ready for Review], Script.Verified
FROM Script
WHERE (((Script.[Script#])="Where[forms]![frmScript]![Script#]"))
ORDER BY Script.[Script#];

 

[randy700]

How about..

WHERE (((Script.[Script[b][red]ID[/red][/b]])="Where[forms]![frmScript]![Script[b][red]ID[/red][/b]]"))

Randy

 

[smedvid]

Yep be careful using symbols (#, !, $, %) within field names as randy700 mentions. Is problematic espeically if data is used with web/browser interface.

Steve Medvid
IT Consultant & Web Master

 

[randy700]

Although I agree with Steve's comments, my suggestion is actually aimed at filtering by the KEY field, which I suspect is ScriptID, not Script#.


Randy

 

[lr999]

SELECT Script.ScriptID, Script.[Script#], Script.ScriptEntryCriteria, Script.ScriptExitCriteria, Script.ScriptDataConstraints, Script.Comments, Script.[Reviewer Comments], Script.ScriptDeveloper, Script.[Ready for Review], Script.Verified
FROM Script
WHERE (((Script.ScriptID)="Where[forms]![frmScript]![ScriptID]"))
ORDER BY Script.[Script#];

Hi Guys
Here is the changed code. I'm getting: 'Data type mismatch in criteria expression' error, when I try to run the query.

 

[randy700]

WHERE (((Script.ScriptID)="[s]Where[/s][forms]![frmScript]![ScriptID]"))

I believe you can lose the quotes a well.


Randy

 

[lr999]

SELECT Script.ScriptID, Script.[Script#], Script.ScriptEntryCriteria, Script.ScriptDataConstraints, ScriptDetails.[Step#], ScriptDetails.UserAction, ScriptDetails.SystemResponse
FROM Script INNER JOIN ScriptDetails ON Script.ScriptID = ScriptDetails.ScriptID
WHERE (((Script.ScriptID)=[Forms]![Script]![ScriptId]));


When I expo this data from the form and subform to Exel, I need it in the spreadsheet for Script data to be Vertical and ScripDetail data Horizontal. How can I control this from this query or any other VBA code? Thanks

 

[SkipVought]

Are you referrring to a TRANSFORM, like a PIVOT TABLE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[lr999]

Public Sub ExportScript()
'Export Scripts to Exel
Dim strmsg As String
strmsg = "Are you ready to export the Script?"
strmsg1 = "Export completed and the files are located in the directory:"

If MsgBox(strmsg, vbYesNo, "export File") = vbYes Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qryScriptQuery", "C:\Scripts.xls", True
MsgBox strmsg1 & CurDir & "."
Else
MsgBox "Export cancelled."

End If

End Sub
The above SQL code submitted before is "qryScriptQuery

 

[lr999]

Hello!

Can anybody provide a sample VBA code to make the 4 fields from the Script form data in Vertical and ScriptDetail subform data Horizontal,on the exel spreadsheet at the time of export.

Thanks

SELECT Script.ScriptID, Script.[Script#], Script.ScriptEntryCriteria, Script.ScriptDataConstraints, ScriptDetails.[Step#], ScriptDetails.UserAction, ScriptDetails.SystemResponse
FROM Script INNER JOIN ScriptDetails ON Script.ScriptID = ScriptDetails.ScriptID
WHERE (((Script.ScriptID)=[Forms]![Script]![ScriptId]));

 

[PHV]

Have a look here:
faq701-4233

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886