Search facility query

[scubadiver]

Okay, to recap:

The fields I am searching are called [customername] and [address1]

The fields I am using to do the search are on the main and are called
[Cname] and [address].

This is the code:

Private Sub CustSrch_Click()

Dim rstClone As Recordset

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Cname & "' And [Address1] =
'" & Me.Address & "'"
If rstClone.NoMatch Then
MsgBox "No Matching Customer Found"
Else
Me.Bookmark = rstClone.Bookmark
End If

Set rstClone = Nothing

End Sub

I am still having a problem with ".address".

thanks

Okay, but I think that may be old news.
How about posting back the code as you have it now and we can take a look?

Argh! No it isn't.

Refer to my response to your fifth message!

Good idea. So, is it working now?

:

thanks for your patience!

I have put the three boxes on the main form. It was something that I hadn't
considered.

:

See some answers below. If you want to continue to use the popup form, I can
show you how that works, but I need the following info to make that happen:

1. The Name of the main form
2. The name of the control on the main form for customer name
3. The name of the control on the main form for address
4. The Name of the popup form
5. The name of the control on the popup form for customer name
6. The name of the control on the popup form for address
7. The name of the table or query that is the record source for the main form.
8. The row source of the combo you are using to search with.

I know even Microsoft erroneously refers to form controls as fields, but to
keep clear communications, refer to fields as elements of a table or query.
If it is on a form, it is a control.

The only commonality is that fields are bound to controls.

:

I was trying to do it with a combo box on a pop-up form.

The fields I am searching are [CustomerName] and [Address1].
Are these field in a recordset or controls on a form?
If they are on a form, which form?

The fields I am using to do the search are [Cname] and [Address].
Same question on these.

So the following should be okay? What is the purpose of the underscore?

It is not okay if this code is in the popup form. If you use a popup form,
then, as I previously stated, it has to have its own recordset. and it has to
pass the info back to the calling form so it can position the recordset.

The underscore in this context is a line continuation character. It allows
you to continue a line so all the code fits on the screen without having to
scroll left or right to read it.


Private Sub CustSrch_Click()

Dim rstClone As Recordset

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CustomerName] = '" & Me.Cname & "' And [Address1] =
'" & _ Me.Address & "'"
If rstClone.NoMatch Then
MsgBox "No Matching Customer Found"
Else
Me.Bookmark = rstClone.Bookmark
End If

Set rstClone = Nothing

End Sub




I am still getting a bug with ".address".





:

I was under the imression search facility was a combo box. Is there any
special reason you are using a popup form instead of a combo on the main form
to do your search? It is a lot easier that way.

If you are using a different form, it will have to have it's own recordset.
It will also have to pass the values back to the main form to get the job
done. You do that with:

Forms!MainForm!CustomerName = Me.CustomerName
(for example).

You may try a different approach that is much easier. Use an unbound combo
box on your main form to look up the customer. If you want it hidden until
the user clicks a search button, one technique is to put the combo in the
header section of the form and set the form header visible property to False.
Then when the user clicks the search button, make the form header visible
and set the focus to the combo box. Use the Lost Focus event to hide the
header. I think you will find this a lot easier.

:

I probably wasn't clear that the search facility box is a pop-up form opened
using a button on "query entry".

I assume that recordset refers to the information in the main form?

How does the code know which form to refer to if there is more than one?

thanks

:

1) I have a bug which states that ".Address" is not found.
Address is probably Address1

What do the field names in the square brack refer to? If it is the fields in
the search box then "me." isn't going to work.

Those fields in square brackets are fields in the recordset.

The two fields in "Query Entry" that contain the information are called
"CustomerName" and "Address1".

If you are saying those are the field names in your recordset, then
replace [CName] with [CustomerName] and [Address] with [Address1]

The brackets are not required, but do prevent ambiguity. Brackets are
really only necessary when you don't use good naming conventions. They tell
Access that what is enclosed in the brackets is a name. People have a bad
habit of naming things using reserved words or unsupported characters. For
proper naming, use only letters, numbers, and the underscore. Do not use
spaces or reserved words as names.

2) I can't figure out the quotation marks. You have single and double quotes.
The single quotes are to enclose the value sent to Jet in quotes.
Single or Double quotes are acceptable. The only problem you will have with
single qoutes is that if there is a possibility that the data in the field
may contain a single qoute, it will cause a problem. The delimiters you use
are dictated by the data type of the recordset field.
Text - Single or double quotes
Numeric - No delimiters
Date - Pound sign #

I get the feeling the code needs tweaking!

My code never needs tweaking. I don't make mistrakes


Dim rstClone As Recordset

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CName] = '" & Me.CName & "' And [Address] = '" & _
Me.Address & "'"
If rstClone.NoMatch Then
MsgBox "No Matching Customer Found"
Else
Me.Bookmark = rstClone.BookMark
End If


:

The search facility form is called "search" and the main form is called
"Query Entry".
I have some queries about the code.

1) I have a bug which states that ".Address" is not found.

What do the field names in the square brack refer to? If it is the fields in
the search box then "me." isn't going to work.

The two fields in "Query Entry" that contain the information are called
"CustomerName" and "Address1".


2) I can't figure out the quotation marks. You have single and double quotes.

I get the feeling the code needs tweaking!

Thanks

:

If you are going to use a command button to initiate the search, yes.

:


I didn't quite understand, thanks.

So do I put this code into an "onclick" event?

:

You don't need to go to all the trouble to get a record count. The code
below is designed to do exactly what you are asking. When you click on the
search button, the FindFirst method will search for a record based on the
criteria. If no record is found, the Message box will tell the user;
otherwise, it will make the record found the current record.

Dim rstClone As Recordset

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[CName] = ' " & Me.CName & " ' And [Address] = ' " & _
Me.Address & "'"
If rstClone.NoMatch Then
MsgBox "No Matching Customer Found"
Else
Me.Bookmark = rstClone.BookMark
End If

Set rstClone = Nothing


:

Thanks for the reply,

1) they are cascading.

These three boxes are in a popup form.

The first combo box ("Initial") is the first 3 letters of the customer
The second combo box ("CName") is the list of customers with those three
letters
The third combo box ("Address") is the list of addresses for the choice of
customer (for customers who have more than one address).

2) I currently have a query containing "customername" and "address" of the
customers currently in the table. Each field has a criteria:

[forms]![search]![CName]
[forms]![search]![Address]

So if the record is in the table, then it will bring up the record. It is a
bit clumsy.

What I would like to have is a button.

If the record count in the query = 1 then the user would be taken straight
to that record

If the record count = 0 then the user will be taken to a blank record in the
form.

cheers

:

You mentioned the 3 combos, but you did not say if they are cascading. You
also did not say how you initiate the search. So, for sake of example, I
will assume the 3 combos are independant and you have a "Search" command
button to initiate the search. In the command button Click event:

Dim rstClone As Recordset

Set rstClone = Me.RecordsetClone

rstClone.FindFirst "[Field1] = " & Me.Combo1 & " And [Field2] = " & _
Me.Combo2 & " And Me.[Field3] = " & Me.Combo3
If rstClone.NoMatch Then
MsgBox "No Matching Customer Found"
Else
Me.Bookmark = rstClone.BookMark
End If

Set rstClone = Nothing

:

I have created a basic search facility to look for customer information. It
is in a form with three combo boxes. Information is compared to a query
containing the three same information with criteria linked back to the form.

What I would like to have is a button that will either put a message in a
text box or an alert box to say whether the information in the three boxes
have been found in the query.

Basically, I would like to have two options

(A) if the records in the query = 0 then I would like to have a message
which says "There is no record for this customer in the database"

(B) If the records in the query > 0 then I would like the db to take the
user straight to that record. I have noticed there is an "applyfilter"
command which can be used in a macro so I am wondering whether this might be
the way to go?