Search Button Code?

[GoodGirl]

Hi :

I would like to ask about : how can I write the (simplist) code for search button. I'm bignner in using Access 2007 & VBA . This button will search by name in a query which contains all employees informations then if the name is valid, message box will show and disply all the related informations for the employee.

[NeoPa]

Hi & Welcome to Bytes!

I've moved your question to the Access Questions area as it was mistakenly posted in the Insights (article) area.

[NeoPa]

I would like to ask about : how can I write the (simplist) code for search button. I'm bignner in using Access 2007 & VBA . This button will search by name in a query which contains all employees informations then if the name is valid, message box will show and disply all the related informations for the employee.

I'm not sure what you're asking for. It's hard to know without any information about how the data you're talking about is stored.

Basically, you'd probably want a form with a control where the operator would enter a search phrase. When entered you would search through the data and put up the message where necessary. DLookup() is probably the function to use.

Without more information I can't be much more help.

[GoodGirl]

NeoPa

Thanks for reply and you understood what I mean (sorry to not explain it). I want a form (which I named it : start : ) which contains a search button which will search by name in q query and then the result will come as a Message Box contains the information about the employee.


How can I use DLookup() method , I want to use it with DetailedInforma tion query. then if the input name is valid in the query: the information will come, if not message box will show that it's not valid. how can I do this comparison?



thanks

[NeoPa]

I will need to know :

1. The name of the query ([DetailedInforma tion] I expect) that you're trying to search in.
2. The name of the field within the query that you're trying to match.
3. The name of the control on your form where the user will enter the search value.
4. Whether the match needs to be exact, or if wildcards should be allowable.

If wildcards are allowable :

1. Are they to be assumed (or would the operator need to enter it/them where required)?
2. If assumed, would the search stub be for anywhere in the field (or just the start)?

[GoodGirl]

1- The name of the query is DetailedInforma tion.

2- The name of the field is FirstName.

3- I don't want to put a text field: I just put a form button and name it (search) and right in it's code the when I click on it , it will give me an Input box then complete the process (the code is attached) , and after the comparison will done, I want another message box to show the results.

4- I don't worry about this side.


I just want the simplisit way for search, after I do it, I will start to learn the more complex.

[NeoPa]

3- I don't want to put a text field: I just put a form button and name it (search) and right in it's code the when I click on it, it will give me an Input box then complete the process ...

I would advise against this. If you already have a form, then using an inputbox is like having a dog and barking yourself. Neither is particularly easier to code mind you, but the result looks less polished.

(the code is attached)

If it's not posted then I won't see it.

4- I don't worry about this side.

I just want the simplisit way for search, after I do it, I will start to learn the more complex.

I worry about it. That's why I asked for it. I don't plan to give one solution only to find that it's not right because you haven't given me enough information.

If you want my help, then I think I should be able to expect answers to all my questions.

[GoodGirl]

This is the code

Private Sub searchButton_Click()
Dim Inn As String
Inn = InputBox("Would you enter Employee's First Name?")
MsgBox Inn
End Sub

when I tried to write

If Inn = txtFirstname Then MsgBox ("OK")
Else MsgBox ("This name is not valid")

As try to make simple comparison, it shows to me an error message said (you have to put ElseIf and when I put it , it shows the line which starts with Else as red line.


3- just now I put a text field and button, so how can I do thier code to be match with each other?


2- I post the code above.

1- I'm happy because you are helpfull , but I answred to which I understood , Neoplo, I don't have that experience which you have, so please don't be angry from me.

[ADezii]

NeoPa is correct in everything he says, and it would be wise to follow his advice. If you do, however, insist on your present course of action, the code would be:

Private Sub SearchButtonl_Click(Cancel As Integer)
Dim strInn As String
strInn = InputBox$("Would you enter Employee's First Name?")

If strInn = "" Then
  Exit Sub
ElseIf strInn = Me![txtFirstName] Then
  MsgBox "OK"
Else
  MsgBox "This name is not valid"
End If
End Sub

[NeoPa]

I'm sorry if that sounded angry GoodGirl. Sometimes I'm less patient than I should be. It was simply meant to sound clear :
I need an answer for my question #4 before I can help with your first questioon.
If you have no preference, at least choose one so that an answer can make sense in that context.

Now I can see the code though, I can tell you why it doesn't like the Else line :
You have tried to put another statement on the same line (Else is a statement on its own. After Else should be a new line).

See ADezii's post for a good example of how the If ... End If should be used. There is (only) one instance where another statement can share the same line, which is when there is a simple If followed by a single statement. In this case no Else, ElseIf, or End If statements are required (or even allowed).

It is actually possible, using a special character, to join two logical lines together. This is never recommended though as it's considered very bad style. It is generally only used when you want to make your code hard to understand (a weak level of security).

[GoodGirl]

Private Sub searchButton_Click()
Dim Inn As String
Inn = InputBox("Would you enter the Employee's First Name?")
If Inn = txtFirstName Then MsgBox ("OK")
End If
If Inn <> txtFirstName Then MsgBox ("This name is not valid")
End If
End Sub

ADezii : Thanks, but your code doesn't work! (I don't know why?), I still clicking on the button after I paste the code , but it didn't work and didn't show to me any error message!

After that, I tried to write the code above, but it shows the (End If without block If) , I didn't understand what to do.

[NeoPa]

After that, I tried to write the code above, but it shows the (End If without block If) , I didn't understand what to do.

Your line #5 should be an Else statement rather than an End If.

[ChipR]

If...Then can be a single line statement, or a multiple line block.

If condition Then action '*implied End If here*

'or

If condition Then
  action
  action
  action
End If

So when you write

If Inn = txtFirstname Then MsgBox "OK"
Else MsgBox "This name is not valid"

The first line is a complete statement and stands alone. Just think of it as having the invisible End If on the end. The second line starts a new statement with Else, which doesn't make sense to a compiler.

[NeoPa]

ADezii : Thanks, but your code doesn't work! (I don't know why?), I still clicking on the button after I paste the code , but it didn't work and didn't show to me any error message!

I'm afraid that code is only checking the entered value against the current record. You would need to use something like :

Private Sub SearchButtonl_Click(Cancel As Integer)
  Dim strInn As String

  strInn = InputBox("Please enter employee's First Name?")
  If strInn = "" Then Exit Sub
  If IsNull(DLookup("[FirstName]", _
                    "[DetailedInformation]", _
                    "[FirstName]='" & strInn & "'")) Then _
    Call MsgBox("This name is not valid")
End Sub

NB. It's always a good idea to attempt compilation of code first, before posting.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

[NeoPa]

So when you write

If Inn = txtFirstname Then MsgBox "OK"
Else MsgBox "This name is not valid"

The first line is a complete statement and stands alone. Just think of it as having the invisible End If on the end. The second line starts a new statement with Else, which doesn't make sense to a compiler.

Chip's response is better explained, as well as more correct, than my earlier post. Ignore mine and read his :)