Simple search to display a single record in a form

[Guest]

Hello Everyone.

Please can someone help. I am trying to add a simple command button with the
code shown below so that it open a inputbox where you can type the record id
and it will open in a form

Private Sub OpenSearchJob_Click()
Dim RecNum As String
RecNum = InputBox$("Enter Record No. You Wish To View", "Search")
DoCmd.OpenForm "Clients", acNormal, , , acFormReadOnly, ,
"

.[JobID]='" & RecNum & "'"
End Sub

The problem is the code does not seem to work and just keeps displying the
fisrt record in the database. Can anyone please help.

Many thanks
Steve C

 

[Albert D. Kallal]

Private Sub OpenSearchJob_Click()

Dim RecNum As String
RecNum = InputBox$("Enter Record No. You Wish To View", "Search")
DoCmd.OpenForm "Clients", acNormal, , , acFormReadOnly, ,
"

.[JobID]='" & RecNum & "'"
End Sub

You code is *almost* right!!! (close!!).

Try

Dim RecNum As String

RecNum = InputBox$("Enter Record No. You Wish To View", "Search")

if RecNum = "" then exit sub

DoCmd.OpenForm "Clients", acNormal, ,"JobID = " & RecNum , acFormReadOnly




Now, if JobID in the form clients is actually a text field, and not a
number, then you do need to surround the value with quotes..

DoCmd.OpenForm "Clients", acNormal, ,"JobID = '" & RecNum & "'" ,
acFormReadOnly

The above is hard to see, but I used single quotes, here is the same line
with extra spaces

DoCmd.OpenForm "Clients", acNormal, ,"JobID = ' " & RecNum & " ' " ,
acFormReadOnly

Note that you must remove those extra spaces, but I just placed them there
for ease of reading...

I also put in a test that if the user hits cancel, then we do a exit sub,
since recnum = "" in that case...

 

[Guest]

Thanks Albert that worked great....

Just a small question, if I use a text field to search on how, can I search
using a wildcard so not having to type in the exact field entry.

Cheers
Steve C

Private Sub OpenSearchJob_Click()
Dim RecNum As String
RecNum = InputBox$("Enter Record No. You Wish To View", "Search")
DoCmd.OpenForm "Clients", acNormal, , , acFormReadOnly, ,
"

.[JobID]='" & RecNum & "'"
End Sub

You code is *almost* right!!! (close!!).

Try

Dim RecNum As String

RecNum = InputBox$("Enter Record No. You Wish To View", "Search")

if RecNum = "" then exit sub

DoCmd.OpenForm "Clients", acNormal, ,"JobID = " & RecNum , acFormReadOnly




Now, if JobID in the form clients is actually a text field, and not a
number, then you do need to surround the value with quotes..

DoCmd.OpenForm "Clients", acNormal, ,"JobID = '" & RecNum & "'" ,
acFormReadOnly

The above is hard to see, but I used single quotes, here is the same line
with extra spaces

DoCmd.OpenForm "Clients", acNormal, ,"JobID = ' " & RecNum & " ' " ,
acFormReadOnly

Note that you must remove those extra spaces, but I just placed them there
for ease of reading...

I also put in a test that if the user hits cancel, then we do a exit sub,
since recnum = "" in that case...

 

[Albert D. Kallal]

Thanks Albert that worked great....

Just a small question, if I use a text field to search on how, can I
search
using a wildcard so not having to type in the exact field entry.

Just use:

Dim strSearch As String

strSearch = InputBox$("Compnay Name To View", "Search")

if strSearch = "" then exit sub

strSearch = "Company name like '" & strSearch & "*'"

DoCmd.OpenForm "Clients", acNormal, ,strSearch, acFormReadOnly

So, use like, and put in a *

I do the above all the time. In fact, even more slick is to build a seach
form, much like the follwing:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

If want to build a seach form likey above, then you can try the following:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName after update event you simply stuff the results
into that sub-form.


dim strSql as string


strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"


me.MySubFormname.Form.RecordSource = strSql.

 

[Guest]

Thanks Albert
Your a star this has done the trick. I will take a look at building a search
form as I can see a good use for this already.
Many thanks again
Steve C