VBA programming - Loop Coding Question

M

Michele

I'm having problem with the Loop VBA code within a macro. I'm running
something that produces reports based on ssn/names for each entry in a form.
Here's my code:

THIS ONE DOESN’T WORK:
Function Export_Statement()
On Error GoTo Export_Statement_Err

DoCmd.OpenReport "Rpt-Account Statement", acViewPreview, "", "[SS Number] =
[Forms]![frm_statements]![SSN]", acNormal
DoCmd.OutputTo acReport, "Rpt-Account Statement", "SnapshotFormat(*.snp)",
"X:\Rewards Domain\Compensation Administration\BP\Statements\" &
[Forms]![frm_statements]![Last Name] & "_" & [Forms]![frm_statements]![First
Name] & ".snp", False, "", 0
DoCmd.Close acReport, "Rpt-Account Statement"
DoCmd.GoToRecord acForm, "frm_statements", acNext

Export_Statement_Exit:
Exit Function

Export_Statement_Err:
MsgBox Error$
Resume Export_Statement_Exit

End Function


However, it keeps halting saying it doesn't recognize the form. However, if
I remove the line first conditional stmt or hardcode the ssn instead, it runs
fine. Any thoughts?

I bascially copied a previous VBA Code that ran beautifully in a different
macro which is a follows:

THIS ONE WORKS IN ANOTHER DB:
Function Export_Statement()
On Error GoTo Export_Statement_Err

DoCmd.OpenReport "*Commission Sheets - Export", acViewPreview, "",
"[id]=[forms]![frm_id for export]![id] and [home site]=[forms]![frm_id for
export]![site]", acNormal
DoCmd.OutputTo acReport, "*Commission Sheets - Export",
"SnapshotFormat(*.snp)", [Forms]![frm_id for export]![Location] & "Current
Activity Statements\" & [Forms]![frm_id for export]![id] & ".snp", False, "",
0
DoCmd.Close acReport, "*Commission Sheets - Export"
DoCmd.GoToRecord acForm, "frm_id for export", acNext

Export_Statement_Exit:
Exit Function

Export_Statement_Err:
MsgBox Error$
Resume Export_Statement_Exit

End Function


I'm baning my head against a brick wall so any help would be greatly
appreciated.

Thanks!
Michele
 
P

Perry

DoCmd.OpenReport "Rpt-Account Statement", acViewPreview, "", "[SS Number]
=
[Forms]![frm_statements]![SSN]", acNormal

DoCmd.OpenReport "Rpt-Account Statement", acViewPreview, "", "[SS Number] =
" & _
Chr(34) & [Forms]![frm_statements]![SSN] & Chr(34), acNormal

Krgrds,
Perry

Michele said:
I'm having problem with the Loop VBA code within a macro. I'm running
something that produces reports based on ssn/names for each entry in a
form.
Here's my code:

THIS ONE DOESN'T WORK:
Function Export_Statement()
On Error GoTo Export_Statement_Err

DoCmd.OpenReport "Rpt-Account Statement", acViewPreview, "", "[SS Number]
=
[Forms]![frm_statements]![SSN]", acNormal
DoCmd.OutputTo acReport, "Rpt-Account Statement", "SnapshotFormat(*.snp)",
"X:\Rewards Domain\Compensation Administration\BP\Statements\" &
[Forms]![frm_statements]![Last Name] & "_" &
[Forms]![frm_statements]![First
Name] & ".snp", False, "", 0
DoCmd.Close acReport, "Rpt-Account Statement"
DoCmd.GoToRecord acForm, "frm_statements", acNext

Export_Statement_Exit:
Exit Function

Export_Statement_Err:
MsgBox Error$
Resume Export_Statement_Exit

End Function


However, it keeps halting saying it doesn't recognize the form. However,
if
I remove the line first conditional stmt or hardcode the ssn instead, it
runs
fine. Any thoughts?

I bascially copied a previous VBA Code that ran beautifully in a different
macro which is a follows:

THIS ONE WORKS IN ANOTHER DB:
Function Export_Statement()
On Error GoTo Export_Statement_Err

DoCmd.OpenReport "*Commission Sheets - Export", acViewPreview, "",
"[id]=[forms]![frm_id for export]![id] and [home site]=[forms]![frm_id for
export]![site]", acNormal
DoCmd.OutputTo acReport, "*Commission Sheets - Export",
"SnapshotFormat(*.snp)", [Forms]![frm_id for export]![Location] & "Current
Activity Statements\" & [Forms]![frm_id for export]![id] & ".snp", False,
"",
0
DoCmd.Close acReport, "*Commission Sheets - Export"
DoCmd.GoToRecord acForm, "frm_id for export", acNext

Export_Statement_Exit:
Exit Function

Export_Statement_Err:
MsgBox Error$
Resume Export_Statement_Exit

End Function


I'm baning my head against a brick wall so any help would be greatly
appreciated.

Thanks!
Michele
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top