Find Method not working properly

  • Thread starter Thread starter lmcc007
  • Start date Start date
L

lmcc007

I have an unbound combo box called cboSearch. I am trying to get it to do
five things:

1. When I click cboSearch, display a list of Email Numbers to choose from.

2. After I make a selection, search to see if there is a record using that
Email Number for this company.

3. If no match, display a message box: “Create new e-mail?â€

4. If I choose yes, puts the Email Number selected in lblEmailNum and
display a blank record for me to enter data.

5. If I choose no, do nothing.

Private Sub cboSearch_Change()

Dim rs

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmailNumID] = " & Me![cboSearch]
'If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If rs.NoMatch Then
MsgBox ("Create new e-mail?"), vbOKOnly
Me.EmailAddress = NewRecord
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'End If
End If
End Sub


The lblEmailNum label doesn’t change to the one selected and there is 0
(zero) in the EmailAddress field.

I don’t see what I am doing wrong? Need help! Thanks!
 
I have an unbound combo box called cboSearch. I am trying to get it to do
five things:

1. When I click cboSearch, display a list of Email Numbers to choose from.

2. After I make a selection, search to see if there is a record using that
Email Number for this company.

3. If no match, display a message box: “Create new e-mail?â€

4. If I choose yes, puts the Email Number selected in lblEmailNum and
display a blank record for me to enter data.

5. If I choose no, do nothing.

Below is the code I am using:

Private Sub cboSearch_Change()

Dim rs
Dim prompt$
Dim reply

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmailNumID] = " & Me![cboSearch]
'If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If rs.NoMatch Then
prompt$ = "Enter the new e-mail address."
reply = MsgBox(prompt$, vbOKCancel, "Add Record")
If reply = vbOK Then
EmailAddress.SetFocus
Me.Recordset.AddNew
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
End If
End Sub
[/QUOTE]​


The lblEmailNum label doesn’t change to the one selected and there is a 0
(zero) in the EmailAddress field.

I don’t see what I am doing wrong?

Need help!

Thanks!
 
1. When I click cboSearch, display a list of Email Numbers to choose from.
2. After I make a selection, search to see if there is a record using that
Email Number for this company.
Click to expand...

(unless I'm missing something...)

If you want to have an email address table based on companies, you should
probably go about it a little differently. You should have a table of
customers, and a table of emails, with a one to many relationship,
respectively (one customer, many emails).

Then make a form for your customers, and use the subform wizard to a subform
in for related email addresses. Now, you can easily see, for every customer
to navigate to, what email addresses are there. Much much easier than having
a dropdown list of all emails, and trying to match it to the company. Let
access do the work for you...


tblCustomers
fldID (PK)
fldCustomerName (text)

tblEmails
fldID (PK)
fldCustomerID (long, FK)
fldEmail (text)



The way you are trying it, by selecting the email and then searching by it,
indicates that you already have records entered for the email addresses. But
they would be entered incorrectly. When an email address goes in, it should
already have it's customer attached to it. This is a prime example of how a
non-normalized data structure can make things a serious pain (sometimes
impossible) for the developer.


and I'm going to pick apart your code a bit...

Dim rs
Dim prompt$
Dim reply

always declare the datatypes when declaring a variable. They default to
Variant if you don't, which is not desireable

Dim rs As DAO.Recordset
Dim prompt As String
Dim reply as Integer


EmailAddress.SetFocus

What is EmailAddress? A control I assume? Use the Me keyword if thats the
case.

Me.EmailAddress.SetFocus


Me.Recordset.AddNew

This is an incorrect way of using the recordset's AddNew method. If you
want to go to a new record in a form, use the DoCmd.GotoRecord acNewRec

An example of how you would use a Recordset AddNew (though you really
shouldn't need it):

Dim rs As DAO.Recordset
Set rs = CurrentDb.Openrecordset("thistable")
With rs
.AddNew
.Fields("fldID") = 125
.Fields("fldCustomer") = "John's Doe"
.Update
End With
rs.Close
Set rs = Nothing

You should never really need this inside a form interface, access natively
handles this stuff for you (and is far better at it, I might add. Recordsets
are viewed as slow, clunky, last resort (but reliable) methods of dealing
with data). Far more efficient would be an INSERT INTO query.


Also, one other thing I noticed is your reference to lblEmailNum... a Label
should never have any data associated with it. Use a disabled textbox
instead. Data is not made to be entered into or taken from a label. If, you
really need to (but you shouldn't), change the Caption property of a label,
do it like so:

Me.LabelName.Caption = "Display this text"


hth
 
lmcc007 said:
I have an unbound combo box called cboSearch. I am trying to get it to do
five things:

1. When I click cboSearch, display a list of Email Numbers to choose from.

2. After I make a selection, search to see if there is a record using that
Email Number for this company.

3. If no match, display a message box: “Create new e-mail?”

4. If I choose yes, puts the Email Number selected in lblEmailNum and
display a blank record for me to enter data.

5. If I choose no, do nothing.

Private Sub cboSearch_Change()

Dim rs

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmailNumID] = " & Me![cboSearch]
'If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If rs.NoMatch Then
MsgBox ("Create new e-mail?"), vbOKOnly
Me.EmailAddress = NewRecord
Else
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
'End If
End If
End Sub


The lblEmailNum label doesn’t change to the one selected and there is 0
(zero) in the EmailAddress field.

I don’t see what I am doing wrong? Need help! Thanks!
Click to expand...


One thing Jack did not mention is that you did not change
the Else part of the main If to get rid of the
If Not rs.EOF Then
so the line would only be:
Me.Bookmark = rs.Bookmark

Since the outer If already tested for NoMatch, the inner If
is redundant and if you really feel you must repeat the
check, it should check for Not NoMatch instead of Not EOF.
 
Okay. The reason I did the form this way because I don't like the way the
subform look. On the code I was reading a lot of stuff, so I guess I got it
all wrong. My tables are set up:

tblCompanies
CompanyID (PK)
CompanyName (text)
CompanyType and other pertinent fields

tblEmails
EmailID (PK)
CompanyID (Number)
PersonName
DisplayAs
EmailAddress

I have another form I plan to use to actually fill in this e-mail
information, but the main form I just want to show: Email and Email Address.
 
I have another form I plan to use to actually fill in this e-mail
information, but the main form I just want to show: Email and Email Address.
Click to expand...

I'm sorry, but I don't understand what you mean by wanting to show the
"email and email address" on the main form... isn't that the same thing? Is
there a default for the company that you want to have as part of your
companies main dataset? It's not unheard of to do that.

The reason I did the form this way because I don't like the way the
subform look.
Click to expand...

Between subform formatting, subform control formatting, and the main form
formatting, you can make a subform (or any other control for that matter)
look like just about anything you want. Play around with some border styles
and format properties of the subform control and the subform to get the
desired look... I can almost guarantee you can find a setup that looks the
way you want. Be sure to experiment with different form views as well. Then
you don't have to go crazy trying to override access's natural way of doing
business. In almost all cases, you want to avoid that... things become
extremely difficult at that point.

Keep it simple... even for the most complex functions, utilities and
projects, the very best of them are all simple little peices put together in
a simple manner (even if they don't seem like it when looked at on the
whole). If you find yourself working "against the flow", it's time to step
back and go the easy route. Always work on asthetics last. Access gives us
ample ways to adjust how things look, but in most cases there's only one good
way to handle data, and access takes care of that also, if you let it.

I don't mean to bash your ideas on how you want to set things up, but being
self taught I've been through all of this and learned it the hard way. I
hate to see someone go through all this trouble just to eventually turn
around and start from scratch again (which is where you will be... believe
me, I've been there over and over again before it finally sunk in).

Just a few (hopefully) helpful hints.



--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
frmCompanies is my main form, which has all the info about the company.
Since there are many e-mail addresses I created a subform called frmEmails,
which I put on frmCompanies. And, I did the same for phone numbers,
addresses, and contacts (these contacts are not separate entity with
different addresses--they are within the company--like secretary, office
manager, attorney, buyer and so on).

I tried creating different subforms but couldn't get the look I want. I
only want one e-mail to display at a time. If I need to see more I want to
be able to hit a button and select Email 2, Email 3... I gotten used to
using MS Outlook Contact Mgr--I like the look.
:
 
Ok, I think I see what you're getting at now. I was a little confused with
what you were previously referring to as an Email number...

This may be a little bit of a design quandry. Are your emails based
directly on the company? Or are they based on the contact...

If they're based on the contact (such as in Outlook), you would have a table
structure that looks like this:

tblCompanies
tblContacts
tblEmails

each with a one-to many going to the next level. This allows as many emails
as you want to be entered per contact, but admittedly does make it a pain to
set up an interface for.

If you are sure that you never want more than 3 emails per contact, you can
add these as three different fields in the child table Contacts and do away
with the extra table for emails.

tblContacts
fldID
fldCompanyID
fldName
fldEmail1
fldEmail2
fldEmail3

But, because the emails aren't dependant upon the company itself (rather the
contact), you don't want these in a table related to the company.

So lets say you have three different email address fields within the
contacts table as depicted above, and you want to only see one at a time
with a dropdown (combo recommended... using a button to get the exact feel of
the Outlook version is going to take some custom popups which are no walk in
the park).

So you have a combo, that you want to default to Email1, and a textbox next
to it that will display the email address from fldEmail1. In order to have
this same textbox refer to different fields, you're probably going to have to
change the control source of the textbox. Because of that, you probably want
to make sure that every time you're on a new record, you default to Email1.
So we'll use the OnCurrent event for this (runs every time the record
changes).

Private Sub Form_Current()
Me.cboEmailNumber = "Email 1"
Me.txtEmailAddress.ControlSource = "fldEmail1"
Me.txtEmailAddress.Requery
End Sub

This defaults to Email 1 in your dropdown and the correct field for Email1
every time the record changes.

Then you need some code to edit the controlsource of txtEmailAddress when
the combo selection is changed. The Change event of the combo may not be the
best bet... if someone starts typing something in, its going to run every
time a letter is entered. I would recommend the AfterUpdate event, with a
LimitToList property set to true.

Private Sub cboEmailNumber_AfterUpdate()
Select Case cboEmailNumber
Case "Email 1"
Me.txtEmailAddress.ControlSource = "fldEmail1"
Case "Email 2"
Me.txtEmailAddress.ControlSource = "fldEmail2"
Case "Email 3"
Me.txtEmailAddress.ControlSource = "fldEmail3"
End Select
Me.txtEmailAddress.Requery
End Sub


This should fairly effectively give the results you are looking for. I may
have missed a few "cleanup" type code in there, but the majority should be
there.

The problem with using a seperate tables for emails in this case is that you
would need to loop the records related to the contact, and keep a counter
running to programmatically grab the correct email, and you would never know
exactly how many there are.

Generally speaking, in data normalization you should never really restrict
data to a certain amount of records, but this is one of the few cases where I
wouldn't crucify myself for doing it.

The above also assumes that this email combo and textbox are on the same
form as the rest of the contact info...


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)
 
Hmmm! Let me think about my table structure some more.

Believe it or not, this is one of the most difficult structures I've ever
come across to correctly build. More difficult to nail down that a BOM
structure, which there are a number of different approaches to, all with
their pro's and con's.

When I tried to do this, and tried to do it *right*, I thunk myself in
circles until I was dizzy.

In a normalized database, information should never be entered twice.
Right?? Consider...

A company can have many contacts. But who's to say that a person (contact)
can't work for more than one company? There's a few in my field that work
for this place, that place, and themselves. So now your seemingly simple 1:M
company/contact table becomes a junction (M:M) table. Not a huge deal, but a
little more work involved on the querying end.

Now throw in phone numbers fax numbers cell numbers and email addresses. I
know people that have a cell for work, as well as a regular work number (tied
to the company), and a personal cell, and then fax numbers to reach them at
if they work for more than one place, and lets not even go to email
addresses. I've got about six of those myself... some personal and some work
related, for different companies to boot.

So now there's a real mess to think through. Technically, you should never
have to update a phone number or persons name (say they get married) or email
address in more than one place. But how to coordinate all these different
possibilities to make that happen? Then there's addresses... they're almost
as bad. Physical address, mailing address, remit to address and ship-to
addresses, warehouse addresses, 911 addresses, personal address, personal
address 2 for the summer home, etc. etc. Some directly related to the
company, some directly related to the person. Some that follow a person
through the company rather than directly. What a nightmare.

So in the end, I ended up doing what every other application seems to do...
a one to many between companies and addresses (actually this is more than
most application in my line of business handle). And if a shipto address for
company A also happens to be the physical address of company C... tough.
Change it twice. Emails, phone numbers, contacts? Same thing. One company
many contacts. Double up on the contacts for people who are employed by
multiple companies. If their name changes?? tough... change it twice.

Agreed, it defies the rules of data normalization... but if you can figure
out a way to keep this mess of possibilities straight without having to
double up, by all means please let me know. I gave up... and don't give up
very easily. You're talking monster queries to handle this stuff. Beyond me.

Something to think on :-)

good luck!

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
 
Thanks! But, I am not going to go there--too much work. Yep, even the
templates use separate fields for phone numbers instead of subforms and so
on. Any extras will just go into the comment field. I have spent way too
much time thinking about normalization and stuff.

Thanks again for all your help!
 
Back
Top