Help - onclick sub never runs

  • Thread starter Thread starter esn
  • Start date Start date
E

esn

I'm struggling my way through putting some code together but I don't
know VBA very well. I'm working with a form where a user enters the
name of a table and selects a few options to modify the data in that
table. Each option should call a vba function I've saved in a
module. If I call the functions from a separate macro they all work
just right, but when I put this code in the onclick event for the
button, nothing happens. I've tried hitting the button with nothing
entered in the table name field, and I don't even get the message box
instructing the user to enter a table name. I'm sure I've done
something stupid in here somewhere...

Code:
Private Sub ResultsGo_Click()
On Error GoTo Error_Handler
If Me!TableName Is Null Then
MsgBox "Enter a table name."
Else
Dim strTable As String
strTable = CHR34 & Me!TableName & CHR34
Dim ResultsTbl As AccessObject
Set ResultsTbl = Application.CurrentData.AllTables(strTable)
If ResultsTbl.IsLoaded = True Then
MsgBox "Close the table and try again."
Else
If Me![OptionTransform] = -1 Then
TransformVars (strTable)
End If
If Me![OptionModify] = -1 Then
OutlierMod (strTable)
End If
If Me![OptionStandardize] = -1 Then
Standardize (strTable)
End If
End If
End If
DoCmd.Close acForm, "Prepare Results for SAS"
DoCmd.OpenTable strTable

GoButtonExit:
Set db = Nothing
Exit Sub

Error_Handler:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume GoButtonExit

End Sub
 
Problem solved.
Click to expand...

Spoke too soon - if I enter a table name then all is well, the code
does exactly what it should. But if I leave the table name blank and
hit the button I never get the message box.
 
esn said:
Spoke too soon - if I enter a table name then all is well, the code
does exactly what it should. But if I leave the table name blank and
hit the button I never get the message box.
Click to expand...

IsNull(Me.TableName) is what you might use.
Using a combobox would be a much better way as there will be no problems and
no need to type information.

Allowing users direct Access to tables can be a dangerous situation. It may
also require you to write a lot of codr that Access would do for you if you
use forms.
 
IsNull(Me.TableName) is what you might use.

Perfect. The If Then statement checking if the table was open was
also causing problems, so in a mad rush I got rid of it and put a
label on the form instructing the use to close the table before
hitting the button. I've tried using the IsLoaded custom function
that floats around and the isloaded property but always had trouble
with both.
Using a combobox would be a much better way as there will be no problems and
no need to type information.
Click to expand...

Good suggestion - if I do a good job with the row source I can even
keep the important tables out of reach.
Allowing users direct Access to tables can be a dangerous situation. It may
also require you to write a lot of codr that Access would do for you if you
use forms.
Click to expand...

I admit it's a little scary but I think it's the only way to do what I
want. The table in question should be a temporary table anyway -
essentially I'm dumping query results into a table and modifying the
results a little for export to SAS. We do some tricky things with our
data to get identify and get rid of outliers that I just can't pull
off in SQL.

Thanks so much for your help!
 
Back
Top