What is wrong with this code?

[agehoops]

If strInputFileName = "" Then
 If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = vbNo Then GoTo Line1
  Else
    MsgBox "The Database Will Now Close.", vbCritical, "Warning"
    DoCmd.CloseDatabase
 End If

End If

The code half works and half fails.

Basically it is checking (earlier before this code) if the current backend file actually exists, if it does then do nothing, if not then bring up a file browser and tell the user they need to choose the backend for it to work. Once the file browser opens this code is run.

It checks whether the user chooses a file or selects cancel. If they select a file then it will write the new location into the system table (haven't put that code in yet) and if they click cancel, it shows an error message stating they have to have a back end for it to work, and asks if they're sure they want to cancel. If they click no, it opens the file dialogue box again, which is working, but if they click yes, it is SUPPOSED to give them an error saying the database is going to close, and then shut it, however it is not running the else statement.

Why not??
Thanks

 

[RuralGuy]

You have mixed single line and multiline If...Then code. Try:

If strInputFileName = "" Then
   If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = vbNo Then
      GoTo Line1
   Else
      MsgBox "The Database Will Now Close.", vbCritical, "Warning"
      DoCmd.CloseDatabase
   End If
End If

 

[agehoops]

Ahh thanks so much! Working perfectly. Always something small in coding

 

[agehoops]

Ok little problem with the code after that. I want it now to update the MSysObjects table with the new location selected by the file browser, and the following doesn't work:

DoCmd.RunSQL "UPDATE MSysObjects " & _
             "SET Database = BackendLocation " & _
             "WHERE Database = " Not IsNull(Database) ";"

Thanks

 

[RuralGuy]

You should start a new thread in the query area.

 

[agehoops]

Alright cool, thanks

 

[stopher]

Ahh thanks so much! Working perfectly. Always something small in coding

As and aside, I'm with the folk who believe that GOTO statements should be avoided in high level languages (excepting error handling) because it leads to unstructured code. There are several structured tools available in VBA e.g. WHILE and DO loops.
Stopher

 

[agehoops]

Well yea i thought so to, and tried to use a Loop to do it, however I couldn't figure out how to jump out of the loop, as it was dependent on the results of a message box. Here is the full code:

Private Sub Form_Load()

Dim BackendLocation As String

BackendLocation = DLookup("[Database]", "MSysObjects", "Not IsNull(Database)")

If Dir$(BackendLocation) = "" Then
MsgBox "The Backend Cannot Be Found. Please Choose The Location of The Backend", vbCritical, "Cannot Find Backend"

Line1:
Dim strFilter As String
Dim strInputFileName As String
Dim Question As String

strFilter = ahtAddFilterItem(strFilter, "Database File (*.MDB)", "*.MDB")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please Select a Backend File...", _
                Flags:=ahtOFN_HIDEREADONLY)
                
If strInputFileName = "" Then
 If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = vbNo Then
  GoTo Line1
  Else
    MsgBox "The Database Will Now Close.", vbCritical, "Warning"
    DoCmd.CloseDatabase
 End If
Else

End If
End If
End Sub

I wanted to repeat the bringing up of the file browser box, depending on what the result was of the error message box asking if the user is sure, but couldn't really figure it out??

 

[stopher]

Here's my stab (not tested)...

Private Sub Form_Load()

Dim BackendLocation As String
Dim strFilter As String
Dim strInputFileName As String
Dim Question As String

[COLOR="Red"]Dim bRepeat As Boolean[/COLOR]

BackendLocation = DLookup("[Database]", "MSysObjects", "Not IsNull(Database)")

If Dir$(BackendLocation) = "" Then
    MsgBox "The Backend Cannot Be Found. Please Choose The Location of The Backend", vbCritical, "Cannot Find Backend"
    
[COLOR="Red"]    bRepeat = True
    While bRepeat       'or WHILE bRepeat = True[/COLOR]
    strFilter = ahtAddFilterItem(strFilter, "Database File (*.MDB)", "*.MDB")
    strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please Select a Backend File...", _
                Flags:=ahtOFN_HIDEREADONLY)
    If strInputFileName = "" Then
        If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = [Color="Red"]vbYes[/color] Then
            MsgBox "The Database Will Now Close.", vbCritical, "Warning"
            DoCmd.CloseDatabase
        End If
    Else
        [COLOR="Red"]bRepeat = False      'this is where you signal that you want to stop the loop[/COLOR]
    End If
    [COLOR="Red"]Wend[/COLOR]
    
End If
End Sub

Note that you should generally put the declarations at the top of your procedure like I've done.
Also note that you've declared Question but not used it.
Please note I'm no coding expert (by a long shot ). If my comments sound picky they are meant only to help inspire good coding (well at least up to the point I understand).

Stopher

 

[agehoops]

Ah yea sorry, the Question declaration was me experimenting with looping and seeing the entire msgbox to that variable, then trying to call it and use it as the get out for the loop but it didn't work

Isn't that example still using the GoTo function though? As it is still there?

 

[stopher]

Isn't that example still using the GoTo function though? As it is still there?

Yes sorry, I was just editing as you posted

Stopher

 

[agehoops]

brilliant! Works perfectly. Thanks so much. As i say i had tried to do it with a loop as they're better, just couldn't

Thanks very much, big help!

Aidy