HELP with two queries

[Guest]

I need help with a couple of things:

First Query:

I have two unbound text boxes on a main form that I want to use to search
two bound text boxes.

The unbound text boxes are [Cname] and [address] and the equivalent text
boxes I want to search are [CustomerName] and [Address1]

I have been given the following code and I am getting a bug with ".address":

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

Also, when a record is found I want the unbound text boxes to be empty.


Second query:

Not everyone customer has an email address so there are times when the
text box is empty.

I have a button which opens up Outlook Express. How do I disable the button
if there is no email address. I have given it a go but it doesn't work.

 

[Svetlana]

You may need to include Address to brankets as there is a property
named like this or rename it.

On current event of your form you can enable/disable your button
If Nz(Me.Email)="" Then
Me.NameOfButton.Enabled=False
Else
Me.NameOfButton.Enabled=True
End If

Or on click event of your button you can check the value of email first
then open or not outlook.
If Nz(Me.Email)<>"" Then
'Open outlook
End If

 

[Guest]

Hi,

thanks for the reply.

I sorted out the email button.

As far as the address name goes, I am still getting a compile error, even if
I rename it. Not too sure whats going on!

 

[BruceM]

Try Dim rst as Object

Hi,

thanks for the reply.

I sorted out the email button.

As far as the address name goes, I am still getting a compile error, even
if
I rename it. Not too sure whats going on!

 

[BruceM]

That was supposed to be (using the names you have used):
Dim rstClone as Object

 

[Guest]

thanks. It works!

When the record is found, how can I return the search fields to blank.

 

[Guest]

I've worked out how to blank the boxes.

That was supposed to be (using the names you have used):
Dim rstClone as Object

 

[Guest]

As well as the customer name and address box, I have a third unbound box
which lists the first three letters of the customers name. The cascading
combos work fine for the search facility.

Since the combo's work fine, to make it easier to find a customer name in
order to create a new record, I thought I would simply copy and past the SQL
from the unbound box to the bound box (I hope you are following this!)

SELECT [Qry_CustName].[CustomerName] FROM Qry_CustName WHERE
((([Qry_CustName].[letter])=[forms]![Query Entry]![Initial]));

If I select the first three letters from the search facility and click on
the bound customername text box there is no list even though I am using
exactly the same SQL. If I go into the properties for this box and open up
the SQL in datasheet view, the list is there.

So I can't see why it isn't coming up on the form?

Any ideas? thanks.

 

[Guest]

I've managed to work it out. My formatting wasn't quite correct

oops!

 

[BruceM]

I'm not sure I follow this, but if the idea is that you will use a combo box
to select a customer name for a new record, all you need to do is use the
appropriate combo box row source (table, Select statement, or named query),
and to store the CustomerID. For instance, if you are creating an invoice
you will want to store the customer information. Create a combo box row
source with CustomerID as the first column and CustomerName as the second
column. Set the combo box Record Source to CustomerID (that is, bind it to
the CustomerID field). Set the combo box column count to 2, the column
widths to 0";1.5", and the bound column to 1. When you select the customer
name (the visible column) the CustomerID (the bound column) will be stored.
Or maybe I'm missing the point completely.

As well as the customer name and address box, I have a third unbound box
which lists the first three letters of the customers name. The cascading
combos work fine for the search facility.

Since the combo's work fine, to make it easier to find a customer name in
order to create a new record, I thought I would simply copy and past the
SQL
from the unbound box to the bound box (I hope you are following this!)

SELECT [Qry_CustName].[CustomerName] FROM Qry_CustName WHERE
((([Qry_CustName].[letter])=[forms]![Query Entry]![Initial]));

If I select the first three letters from the search facility and click on
the bound customername text box there is no list even though I am using
exactly the same SQL. If I go into the properties for this box and open up
the SQL in datasheet view, the list is there.

So I can't see why it isn't coming up on the form?

Any ideas? thanks.

That was supposed to be (using the names you have used):
Dim rstClone as Object