• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

Findfirst using a textbox value

What I am trying to do is use the findfirst function passing a value from a text box as the value to be found
The following code works with out errors and will find the value –85 in a check ledger database using a bound database control. I have a textbox where they type in the value of the check they are searching for and in the following code I want it to use this value instead of –85. What I can not get it to do is accept the syntax that would normally be used to pass this variable. The “” are needed because this statement is being passed as an SQL statement and if I replace –85 with the name of a variable or textbox field such as amountvar.text it reads it as I am literally looking for amountvar.text in the database table in the amount field.

Private Sub findnow_Click()
Data1.Recordset.findfirst "amount='-85'"
If Data1.Recordset.NoMatch Then
    MsgBox "No Such Record Found"
End If

End Sub

Keep in mind I am a very novice programmer so please be very clear and detailed.
0
srose6
Asked:
srose6
  • 4
  • 2
  • 2
  • +2
1 Solution
 
clockwatcherCommented:
Data1.Recordset.findfirst "amount='" & amountvar.text & "'"
0
 
TimCotteeCommented:
clockwatcher is correct, though if this is a numeric field, the additional single quote characters are not necessary.

Data1.Recordset.FindFirst "amount = " & amountvar.Text

0
 
deightonCommented:
Data1.Recordset.FindFirst "amount=" & Chr(39) & CStr(amountvar.Text) & Chr(39)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
srose6Author Commented:
Adjusted points to 150
0
 
srose6Author Commented:
deighton you are the man. I have increased the points and requested more information. Either way you will get the 100 points with a grade of "A"

One more detail I could use. I have several fields for query information. One for amount, check number, and one for date. They are all fields in the same dataset. How do I get it to check say the check number and the amount. The text box for check number is chnum.text and the text box for check amount is amountval.text. Is there an anding feature that will only return a response if the values for both fields match in a record. I have tried using AND between two statements formated as the one you gave but I get the error "type mismatch".
Private Sub findnow_Click()
Data1.Recordset.FindFirst "amount=" & Chr(39) & CStr(amountval.Text) & Chr(39) And "Check=" & Chr(39) & CStr(chnum.Text) & Chr(39)
If Data1.Recordset.NoMatch Then
    MsgBox "No Such Record Found"
End If
0
 
Éric MoreauSenior .Net ConsultantCommented:
This line must be like this:
Data1.Recordset.FindFirst "amount=" & Chr(39) & CStr(amountval.Text) & Chr(39) & " And Check=" & Chr(39) & CStr(chnum.Text) & Chr(39)
0
 
srose6Author Commented:
You guys have been great. Works like a charm. I have two books on VB. It sounds like I need to by one on VB Programming to get information this specific.

Thanks again
Steve
MCSE+I
0
 
deightonCommented:
The basic theory behind it is to create a string in a variable which looks like the one you would write in a program to do the select you want.  

The & operator is probably explained in a text under 'string operations'

0
 
srose6Author Commented:
What does Chr(39) stand for.

What I meant to say is I need to buy a book on VB Database programing. Do you have any books you would recomend on VB Database programing or VB in general.
0
 
TimCotteeCommented:
chr(39) is the single quote character ', chr(34) is the double quote character "
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now