Access, querys into textboxes in forms

[Scotter]

OK, I'm really really new to SQL and VBA, I have only been working with them for about a week or so now......

But heres where my question comes in. I'm trying to make a form where i can input a criteria into a text box and click a search button and then pull any matching records into other text boxes in my form.

So I'm sure this is probably and easy question that I managed to miss a tutorial for, but how can i run a query and have the rest of the record brought into the other text boxes on the form?

I'm sure I couldnt have worded that any poorer, but if someone could take a swing at it and give me any suggestions I would be gratefull.

This is kind of what I think it should be.....

Private Sub btn_beginSearch_Click()

Dim sqlquery As String

sqlquery = "SELECT [TBL_CUSTOMER].[First Name],[Last Name],[Customer ID]"
sqlquery = sqlquery + "From [TBL_CUSTOMER] where ((([Phone])=[txtSearchBox]))"


DoCmd.RunSQL "sqlquery"

End Sub

where txtSearchBox is where I'd put in the criteria. But how do I specify the text boxes I want the first name, last name, and customer id of the record I'm looking up to go into? Sorry if the logic and syntax was wrong.



Thanks

[Stang02GT]

OK, I'm really really new to SQL and VBA, I have only been working with them for about a week or so now......

But heres where my question comes in. I'm trying to make a form where i can input a criteria into a text box and click a search button and then pull any matching records into other text boxes in my form.

So I'm sure this is probably and easy question that I managed to miss a tutorial for, but how can i run a query and have the rest of the record brought into the other text boxes on the form?

I'm sure I couldnt have worded that any poorer, but if someone could take a swing at it and give me any suggestions I would be gratefull.

This is kind of what I think it should be.....

Private Sub btn_beginSearch_Click()

Dim sqlquery As String

sqlquery = "SELECT [TBL_CUSTOMER].[First Name],[Last Name],[Customer ID]"
sqlquery = sqlquery + "From [TBL_CUSTOMER] where ((([Phone])=[txtSearchBox]))"


DoCmd.RunSQL "sqlquery"

End Sub

where txtSearchBox is where I'd put in the criteria. But how do I specify the text boxes I want the first name, last name, and customer id of the record I'm looking up to go into? Sorry if the logic and syntax was wrong.



Thanks

Scotter,

Here is some vb code that will do what you asked. You will want to put this code in the onclick event of your "search" button.

Private Sub ButtonsName_Click()
   If IsNull(TextboxName) = False Then
      Me.Recordset.FindFirst "[FieldYourSearchingFor]=" & Textboxname
          If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            End If
   End If
End Sub()

This code will also display a message if no record is found

[Scotter]

Thanks for the help Stang. I tried your code on the buttons onClick, and I come up with an error,

" Compile Error:
Method or data member not found "

with the second txtsearchbox highlighted.

Here is what i changed the code to look like, maby im doing something wrong...

Private Sub findbutton_Click()

 If IsNull(txtsearchbox) = False Then
      Me.Recordset.FindFirst "[Customer ID Number]=" & txtsearchbox
          If Me.Recordset.NoMatch Then
         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            End If
   End If
   
End Sub

where "txtsearchb ox" is the text box on the form and "Customer ID Number" is the record im searching for. Is there something that I'm not getting? As of right night now the text box is unbound, should i bind it? And how can I get the rest of the record into other text boxes within the form(like first name, last name, etc.)

Thanks again

[Stang02GT]

       Me.Recordset.FindFirst "[Customer ID Number] =" & txtsearchbox

Thanks again

If that’s the line with the error i checked it against my code make sure there is a space between the ] and the = . If that doesn't work ( the space between could not even matter) txtsearchbox should be the name of the actual text box where the data you are searching for is being entered. Customer ID Number should be your customer ID number field in your table. Make sure the forms record source is set to the table where you want to search from. All of your text boxes where the information is being displayed after you search should be bound as well. The txtsearchbox doesn't need to be bound.

[Scotter]

Ok, thanks for sticking with me here Stang, I was messin around and found this out.

I did something like;

Private Sub findbutton_Click()

Dim searchfor As String

searchfor = txtsearchbox.value

If IsNull(txtsearchbox) = False Then
      Me.Recordset.FindNext "[Customer ID Number]=" & searchfor
          If Me.Recordset.NoMatch Then
          MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            End If
   End If
End Sub

Now when I do this I still have an error, but instead of the problem bieng after the "&", the problem is with Me.Recordset.Fi ndNext. I dont have a method Recordset. Under the "ME" class I have "RecordSetClone ", or "RecordSour ce". Do you know any other way to search through the records, other than Me.Recordset? I'm runing an older version of access, I suppose this is why I'm having this issue... Sorry for not mentioning earlier.

Is it possible to use a DoCmd command?

Thanks again

[Stang02GT]

Thats ok not a problem at all. How about i give you some sites you can look at that were a HUGE help to me.

http://home.clara.net/tkwickenden/

http://home.clara.net/tkwickenden/

http://www.mvps.org/access/datetime/index.html

good luck and if there is anything else i can do let me know, but take a look at these sites they will give you step by step how to do lots of different Access things like the search you are trying to do.

Have a good one!

[Scotter]

Thanks Stang. After looking at the web sites, and playing with it I figured out a way to do it(using most of your code :)

Private Sub findbutton_Click()

Dim searchfor As String

searchfor = txtsearchbox.Value
 
 If IsNull(txtsearchbox) = False Then
       Me.Filter = "[Customer ID Number]= " & searchfor
       Me.FilterOn = True
    End If  
   
End Sub

Just incase anyone else has the same problem. Its crude, but its a good starting spot.

Thanks again

[Stang02GT]

Glad I could help! And thanks for posting the solution to your problem to help others :)