CODEWITHSUNDEEP
sqlms-accessvba
Eric King
Eric King

Reputation: 103

MS Access - Text field retuns the querys string instead of the query result value

I have tried a few variations, and all seem to give me the same result - so I am overlooking something pretty simple I think.

I have a text box for an account number, a search button, and a text box for the result of the search query. However, when I hit search - the query itself gets added to the second text box instead of the expected result of 1 or 0.

This is my latest attempt, found on this site:

Private Sub SearchAcctNumber_Click()


Dim strsearch As String
Dim Task As String

If IsNull(Me.Text0) Or Me.Text0 = "" Then
   MsgBox "Please type in your account number.", vbOKOnly, "Acct Num Needed"
   Me.Text0.SetFocus
Else
    strsearch = Me.Text0.Value
    Task = "SELECT I_Ind FROM dbo_BC WHERE (([ACCOUNT_NUMBER] Like ""*" & Text0 & "*""))"
   Me.Text2 = Task

End If

End Sub

Is anyone able to help me see the error I am making? It is driving me nuts that something so simple isn't working how I thought it should.

Edit: Wanted to add that I've also tried DLookup and get the same result in other iterations of attempts at this.

Upvotes: 1

Views: 140

Answers (2)

Kostas K.
Kostas K.

Reputation: 8518

You may want to reconsider the Like approach in this case. Anyways, the issue is that you assign a string (the SQL command) to the textbox and this is what you see.

Try this instead:

Private Sub SearchAcctNumber_Click()

    If IsNull(Text0.Value) Then
       MsgBox "Please type in your account number.", vbOKOnly, "Acct Num Needed"
       Text0.SetFocus
       Exit Sub
    End If

    Dim strSearch As String
    Dim strCriteria As String

    strSearch = Text0.Value
    strCriteria = "ACCOUNT_NUMBER Like '*" & strSearch & "*'"

    Text2.Value = Nz(DLookup("I_Ind", "dbo_BC", strCriteria), "Not found...")

End Sub

You could also "search" while you type on Text0. Set the minimum number of characters before attempting to locate it.

Private Sub Text0_Change()
    If Len(Text0.Text) > 3 Then
        Text2.Value = Nz(DLookup("I_Ind", "dbo_BC", "ACCOUNT_NUMBER Like '*" & Text0.Text & "*'"), vbNullString)
    End If
End Sub

Upvotes: 3

Eperbab
Eperbab

Reputation: 378

One possible way is, that you change Text2 type to combo box. Then you set Text2.recordsource = Task and you refresh the displayed value with Me.Text2.requery.

Another way is to open a recordset, read the value, and set Text2 value. 

Dim r as dao.recordset, db as dao.database  
set db = currentdb()  
set r=db.openrecordset(Task)  
Me.Text2 = r(0).value
Set r = Nothing

Upvotes: 0

Related Questions