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]

@GoodGirl
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 DetailedInformation 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 ([DetailedInformation] 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 DetailedInformation.

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]

@GoodGirl
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.
@GoodGirl
If it's not posted then I won't see it.
@GoodGirl
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]

@GoodGirl
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]

@GoodGirl
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]

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

[GoodGirl]

Neopa & ChipR :

4- I choose the lower case.

When I put my 5th line as Else statement without End If, it shows a compile error [Else without If], when I put ElseIf, it shows syntax error (without any other clarification) and shows [ElseIf] statement with red line.

Neopa : your code in participation #14 didn't work!! , I still click on the search button without any response or even error message.

I wrote this code and shows to me (after entered the name) : [ Wrong name] which means it didn't make any comparison, what I should add?

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

Neopa: I think I have a compilation problem (based on your explanation) because after I checked (Require variable compilation), nothing changed and [Option Explicit] didn't show in the code.

-Every time I post or take any code, I compile and run it before write or do anything.


Thanks guys.

[GoodGirl]

I'm waiting anybody answer.

[Stewart Ross]

I suspect you will need to rethink what you are doing in this one. Aside from your IF statements still being unsoundly written (even after what has posted above to help you) it is not possible for us to know what the source for your txtfirstname variable which you are comparing the input to should be.

You were asked by NeoPa in post # 5 to give us more information on which we can help you, but as yet you have not responded to the detail of that request (what you put into post 6 does not give us enough detail to know how you are intending to compare values at all).

Sorry, but I cannot see how we can help you when you have not given us anything to work on other than a somewhat redundant pair of IF statements which are not the true source of your problem.

-Stewart

[NeoPa]

@GoodGirl
Did you change the code to reflect your actual control name. I copied SearchButtonl_Click from ADezii's post. That would explain why nothing happened.
@GoodGirl
Option Explicit will appear for every module created after that is set. I'm afraid it doesn't change any existing modules, but is definitely a good idea to have it set. That way you will never experience the problems associated with this in any future projects.

For existing modules, simply type or paste in a line :

Option Explicit

@GoodGirl
That's great. I wish everyone were as clever.

[GoodGirl]

Nice NeoPa:
I post [Option Explicit] and finally it worked with me
I put this code to ensure the simplest level of comparison:

Option Explicit
Private Sub searchButton_Click()
Dim Inn As String
Inn = InputBox("Would you enter the Employee's First Name?")
If Inn = "amin" Then MsgBox ("OK")
If Inn <> "amin" Then MsgBox ("Wrong Name")
End Sub

It worked very well, when I entered “amin” it shows OK, When I entered any name other than “amin” it shows to me “Wrong Name”.
So, now I want to create a variable which holds values of FirstName column ,then I can enter this variable into a comparison with [Inn].
After that , I want to select all the information related the FirstName and show it in a message box (in case of true name).
stewart-ross-inverness:

Thanks for contribution , you are right maybe not all details are clear (I’m sorry for that) , but I’m writing as much as I can understand.

I appreciate you guys .

[NeoPa]

Right then.

We need to start with the table you're planning on keeping the name data in. What are the details (meta-data)?

META-DATA
This will work best if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

[GoodGirl]

Nice NeoPa , but I didn't get the point?
what's the relation between the code required for the button and what you said?

[NeoPa]

The code will need to refer to the items.

When I get to think about what needs to be in the code I will need to have an understanding of what I'm working with.