Open form with matching surname

[l3111]

Hiiiiii
I was wondering if anyone could help me.

I've successfully created a "search" function within my Database. It works perfectly. User enters id number, clicks "Search" and that persons Referral form opens, search form disappears. Champion

Hoooooooooowever, i've been requested to allow users to search by Surname as well but when i copy the form (yes two sepreate search forms because being a novice means multiple search criteria will make my head explode), and alter the code to match the surname, it opens a parameter box with the surname in [ ]....and doesn't open the form.

Below is the code i used for the original search form, am i correct in thinking that i can modify this, or will i have to because rather than just matchin numbers, i'm matching text?


Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmReferralsDischarges"

stLinkCriteria = "[RAISENumber]=" & Me![txtRAISE]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frmSearch"


Exit_btnSearch_Click:
Exit Sub
Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click



Many Thanks y'all

l3111, Manchester, UK

[Rixxe]

I've created something like this in the past, and the best way would be to use an IF and ELSE setup.

Use ONE form, and two different textboxes with your search button.

On the On_Click event of the button you can use your code above, and incase it in an IF, ELSE

IF textSurname != "" && textID == ""
THEN (use the code above here for the surname section)
ELSE IF textSurname == "" && textID != ""
THEN (use the code above for the ID)
END IF

This saves you a form, and really doesnt affect the button much, just change the name of the textbox in question, and it should work with your code above.

I'm afraid i can't help you with the VBA code itself but i'm sure you'll figure it out.

Good Luck

[alansidman]

Here is a tutorial that you might find suits your purpose

http://www.datapigtechnologies.com/f...tomfilter.html

Alan

[l3111]

Hi Rixxe

I'm having a slight problem with this if else idea. heres my code:

If textSurname = "" & textID = "" Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmReferralsDischarges"

stLinkCriteria = "[RAISENumber]=" & Me![txtRAISE]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frmSearch"

Else If textSurname = "" & textRAISE = "" Then
Dim stLinkCriteria As String
stDocName = "frmReferralsDischarges"

stLinkCriteria = "[Surname]=" & Me![txtSurname]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frmSearch"

End If

The section in red is where my error is occuring,with this message
Compile Error: Syntax Error

Confused

l3111, Manchester, UK

[l3111]

also gettin this error (same line highlighted)

Compile Error: Must be first statement on the

[Rixxe]

Morning,

Well it looks like these both do nearly the same thing, what is TextRAISE?

None the less, i'm not a VB pro, but i shall point you to something that declares the variables outside the IF-ELSE structure, which is something that is usually fairly common in most languages (from my xp).

Example:

Dim number, digits As Integer
Dim myString As String
number = 53
If number < 10 Then
digits = 1
ElseIf number < 100 Then
digits = 2
Else
digits = 3
End If


Also should your 'Else If' be 'Elseif' ? It might be minor things like that which give the error.
For example Python is 'Elif' which is just a pain to remember....

Good Luck

[orange]

l3111,
Did you watch the free video tutorial than Alan recommended?
The tutorials at Datapig are very well done and are excellent for
training/refresher.

For clarity:

You have a form; it has a search field and a SEARCH button.
It expects a number data type (User Id Number)
This what was working originally. Is this correct?

You were asked to provide for a search on Surname ( a text data type).
Is this correct?

This datapig tutorial may also help
http://www.datapigtechnologies.com/f...earchform.html

[l3111]

rixxe - i'm more confused than ever now. textRAISE was type now corrected to txtRAISE

orange -
Your understanding is correct re RAISE is number type, surname is a text type. The search needs to allow users to search by either RAISE number or by surname as some entries may not have a RAISE number (however 95% of the time they will)
I was going to have TWO search forms, however i'm sure i could have them on the same form (using rixxe's IF [raise number has a value in, search that] ELSE [see if surname has been entered and search that]

Hoooooowever, whn i tried to create a seperate form to search the surname using the same code as the RAISE search, it came up with a parameter search box.

Puzzled

l3111, Manchester, UK

[orange]

I did a small mockup using an existing table (tblEmployees) and existing fields just to show the SQL syntax.

A form with a textbox called txtSearchValue and a button called btnSearch

Here is the code to place in the button's On Click event

Private Sub btnSearch_Click()
On Error GoTo Err_btnSearch_Click
If IsNull(Me.txtSearchValue) Or Len(Me.txtSearchValue) = 0 Then
MsgBox "You must enter a Value to Search for in the textBox"
Me.txtSearchValue.SetFocus
Exit Sub
End If
If Left(Me.txtSearchValue, 1) Like "[0-9]" Then
'searching for number
MsgBox "Use code to search for Number " & vbCrLf _
& "select * from tblEmployees where [employee number] =" & CLng(Me.txtSearchValue)
Else
'searching for Name
MsgBox "Use code to search for Surname " & vbCrLf _
& "select * from tblEmployees where [name] = '" & Me.txtSearchValue & "'"
End If




Exit_btnSearch_Click:
Exit Sub

Err_btnSearch_Click:
MsgBox Err.Description
Resume Exit_btnSearch_Click

End Sub