Query Question (Again)

[k209310]

i have q uery within VBA that requires a user to input a Table Name via an input box. The query fails if the name contains a _ (underscore).

Is there a way that this can be over come?

 

[BLeslie88]

Have you tried...

Assigning the value of the inputbox to a variable string..?

Regards,

 

[k209310]

Thanks for the reply.

If you dont mind explaing. How would i go about doing that?

 

[CBragg]

at the top of your code put

Dim InputBox as String


If you need more help, paste the code and ill have a go for you.

 

[k209310]

ive tried what you have said and i still get eh errors. This is really annoying. if i change the title so thatit has no _ in then everything works fine. If you are really bored then heres is the code. Thanks for the reply tho.

Private Sub CommandButton1_Click()

Dim dbs As Database
Dim rst As Recordset
Dim strMsg As String
Dim strFileName As String
Dim strFileName1 As String
Dim strName As String
Dim iReturn As Integer

Line0:
'input box to ask user for the IWO number
strFileName1 = inputbox("Enter Table", "IWO Input")
strName1 = "FileName" & strFileName

strFileName = inputbox("Enter Note Number", "IWO Input")
strName = "FileName" & strFileName

'open the database
Set Db = OpenDatabase("J:\DATA\ESTIMATI\Chedley\testCert4.0.mdb")
'the databasee query

Set rst = Db.OpenRecordset("SELECT * FROM " & strFileName1 & " WHERE " & strFileName1 & ".note= " & strFileName & "")

'Test to discover if the required ident exists
If rst.RecordCount > 0 Then GoTo line1 Else GoTo line2

line1:
If Not (rst.EOF And rst.BOF) Then

'declare the worksheet as an object and as active
Dim xlSht As Object
Set xlSht = ActiveSheet

'the spreadsheet is declared as visible
xlSht.Application.Visible = True

'declare the x location as cell 14
Dim X As Integer
X = 15

'Delete Data Currently in Cells
rst.MoveFirst
Worksheets("Exxon_01465_3").Range("B15:B17").Value = ""
Worksheets("Exxon_01465_3").Range("C15:C17").Value = ""


'search the table for the stated recordsets with relation to the required idents
Do Until rst.EOF
xlSht.Range("B" & X) = rst![Tube Identity]

xlSht.Range("F" & X) = rst!Si

'find the next set of results
X = X + 1
rst.MoveNext
Loop
'end the query
rst.Close

'message box to alert the user of the data that has been entered
strName = MsgBox("Data entered for IWO: " & strFileName, vbOKOnly + vbInformation, "IWO Number")
End If
Exit Sub

line2:
'Message box to altert the user of an unknokn IWO
strMsg = strFileName & " Unknown IWO " _
& " Would You Like to enter a new IWO?" 'asks the user if another number is to be entered
iReturn = MsgBox(strMsg, vbQuestion + vbYesNo, _
"Error")
'If no number is required then then exit the loop
If iReturn = vbNo Then
Exit Sub
Else
GoTo Line0
Exit Sub
End If
End Sub