can somebody help newby ?

[jd_boss_hogg]

Hi all - i've copied this code from somewhere (can't rememebr, but may even have been this forum?) and then butchered it to fit my needs. I'm completely new to VBA coding, and can tell that it's not structured correctly, but can't seem to get it to work.

Private Sub Command18_Click()
On Error GoTo Export_Invoice_Err
 
        Dim strPasswd

    strPasswd = InputBox("Enter Password", "Restricted Form")

    If strPasswd = "" Or strPasswd = Empty Then
        MsgBox "No Input Provided", vbInformation, "Required Data"
        Exit Sub
    End If

     If strPasswd = "fred" Then
       
    DoCmd.OpenQuery "NewCsvExport", acViewNormal, acReadOnly
    
   DoCmd.TransferText acExportDelim, , "NewCSVExport", "C:\sageexport\Sage_Export_" & Format(Now, "yyyymmdd") & ".csv"
  
    DoCmd.Close acQuery, "NewCsvExport"
    
    DoCmd.OpenReport "Invoices_to_sage"
    DoCmd.SelectObject acReport, "Invoices_to_sage"
    DoCmd.PrintOut acSelection
    
    DoCmd.OpenQuery "NewCsvNotExported"
       
    Forms!new_csv_Export!.Refresh
 
 Else
        MsgBox "Sorry, you do not have access to this form", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If
    
Export_Invoice_Err:
    MsgBox "All Done !"
  
End Sub

If the user hits the correct password, then the open query/write file.close query works, then the open report/print works fine, but i just can't get the last query to do anything. I get no erro messages, but it's as though this ine fails to run... DoCmd.OpenQuery "NewCsvNotExported"

Also, the sub routine "export_invoice_err" actually runs as aprt of the main "if" loop, and not when there is an error, so i know i'm missing something somewhere !

Hoping someone can help? Thanks !

 

[DCrake]

I think your last query is only a select query and not an action query.

Query NewCsvNotExported must be an update/append/delete type query.

David

 

[jd_boss_hogg]

The query is an update query.....

 

[DCrake]

You may need to insert a DoEvents just above this line to render Access to complete everything above the command before continuing. It may be that something done previously affects the running of the query.

To prove this place a breakpoint at the begining of the code and step through it using F8.

David

 

[jd_boss_hogg]

Thanks for the feedback, but a 'do events' doesn;t change anything.

Also, how do i step through it? If i'm in the private sub, then debug/f8 doesn't step through anything?

 

[DCrake]

When you say the last query does not work. What is not actually working? Is it a syntax or logical problem? Is it in any way dependant on the previous query? What is the sql for this query?

If you place your breakpoint on the private sub and run as normal the code should be interupted at theat command line (yellow bar). Then use F8 to step through it.

Also try turning the set warnings to true as well. This will tell you if the query runs ok but no records are affected. Thus giving the impression that it did not run.


David

 

[Atomic Shrimp]

Also, the sub routine "export_invoice_err" actually runs as aprt of the main "if" loop, and not when there is an error, so i know i'm missing something somewhere !

You need an Exit Sub statement immediately before your error handler - otherwise it will always execute the code there when execution gets down that far
Like this:

... Else
        MsgBox "Sorry, you do not have access to this form", _
               vbOKOnly, "Important Information"
        Exit Sub
    End If

[COLOR="Red"]  Exit Sub

    [/COLOR]Export_Invoice_Err:
    MsgBox "All Done !"
  
End Sub

This way, the error handler code will only ever execute when it is explicitly called (i.e. when there is an error)

 

[jd_boss_hogg]

Thanks guys....the 'exit sub' makes sense. That's whay it was always processing the error sub ! That's cleared that one up.

Also, i've added a break into the code, and stepped through, and found the reason that the query wasn;t running - obviously there is some error in programming and the routine was jumping straight to the error sub routine. Here is the offending code...

DoCmd.OpenReport "Invoices_to_sage"
DoCmd.SelectObject acReport, "Invoices_to_sage"
DoCmd.PrintOut

After the report is open, if i select it and then print it, that's when i get the error. Could you help me with that part? Effectively, i'm just trying to open a report and print it to the default ptinter...

thanks

 

[DCrake]

You don't need the following lines

DoCmd.SelectObject acReport, "Invoices_to_sage"
DoCmd.PrintOut

all you need is

    DoCmd.OpenReport stDocName, acNormal

where stDocName is the name of your report.

David