Error Msg whenrunning "find" on combo box used to populate otherfi

G

Guest

It's me again. I can't unerstand why I repeat the same process which worked
the first time...now it doesn't.

We have several thousand client entries in our database. The users have
requested that I create a combo box, which gets it's information from our
client list to populate new records for repeat clients. Easy enough.

Below is my "On Update" action...

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!PHONE = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub

It works great. I can create new records using the combo box to populate
the fields. I can create a new record, not on the existing list. The only
problem is, when I place my cursor in the combo box field and run "find" I
get an error message "The value you entered isn't valid for this field."
This only happens if I'm searching for records that existed prior to my
creating the combo box.

After creating the combo box, I added several new records. When I run the
search for those records, no problem.

PLEASE HELP!

Rhonda

P.S. I already tried switching column 1 and 2 (file # and name) It did not
solve the problem.
 
J

J. Goddard

Hi -

I have a question - why do you "populate new records for repeat
clients.." with all the information about the client, as your code
shows? What happens when a client's information changes (e.g. moves)?

Your "...new records for repeat clients..." (in fact all records) should
contain only the Client_ID, otherwise the basic rules of normalisation
are violated.

You might want to re-think your database structure

From your comments, it appears your form can also add new clients to
the client list - when this happens, you also need to requery the combo
box to update its list.

What is the row source for the combo box?

John
 
G

Guest

Our business is unusual. We are corporate and convention event planners who
work with approx 50 different clients monthly. Conventions travel to other
cities..on occasion will return to our city, so the only information that is
repeated is the name of the company, address, phone, etc. We have 30 - 40
other fields that describe the particular event and we open an entirely new
file (record). Also, we work with 3rd party vendors who represent different
clients. We may work with the same 3rd party vendor 2 - 3 times during the
year, but all of the rest of the information is different, so we open a new
file. My staff asked if we do work with the same client, if the database
could populate several of the fields. That's all I'm trying to do.

Also, if the address changes, or our contact is different, we just make the
adjustment after the the fields are populated. Like I said, very few of the
fields are actually populated by the combo box. It's working great for us.
My problem is, why am I getting the error message?


J. Goddard said:
Hi -

I have a question - why do you "populate new records for repeat
clients.." with all the information about the client, as your code
shows? What happens when a client's information changes (e.g. moves)?

Your "...new records for repeat clients..." (in fact all records) should
contain only the Client_ID, otherwise the basic rules of normalisation
are violated.

You might want to re-think your database structure

From your comments, it appears your form can also add new clients to
the client list - when this happens, you also need to requery the combo
box to update its list.

What is the row source for the combo box?

John


R said:
It's me again. I can't unerstand why I repeat the same process which worked
the first time...now it doesn't.

We have several thousand client entries in our database. The users have
requested that I create a combo box, which gets it's information from our
client list to populate new records for repeat clients. Easy enough.

Below is my "On Update" action...

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!PHONE = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub

It works great. I can create new records using the combo box to populate
the fields. I can create a new record, not on the existing list. The only
problem is, when I place my cursor in the combo box field and run "find" I
get an error message "The value you entered isn't valid for this field."
This only happens if I'm searching for records that existed prior to my
creating the combo box.

After creating the combo box, I added several new records. When I run the
search for those records, no problem.

PLEASE HELP!

Rhonda

P.S. I already tried switching column 1 and 2 (file # and name) It did not
solve the problem.
Click to expand...
Click to expand...
 
G

Guest

Figured it out.
Works perfectly now.


R Marko said:
Our business is unusual. We are corporate and convention event planners who
work with approx 50 different clients monthly. Conventions travel to other
cities..on occasion will return to our city, so the only information that is
repeated is the name of the company, address, phone, etc. We have 30 - 40
other fields that describe the particular event and we open an entirely new
file (record). Also, we work with 3rd party vendors who represent different
clients. We may work with the same 3rd party vendor 2 - 3 times during the
year, but all of the rest of the information is different, so we open a new
file. My staff asked if we do work with the same client, if the database
could populate several of the fields. That's all I'm trying to do.

Also, if the address changes, or our contact is different, we just make the
adjustment after the the fields are populated. Like I said, very few of the
fields are actually populated by the combo box. It's working great for us.
My problem is, why am I getting the error message?


J. Goddard said:
Hi -

I have a question - why do you "populate new records for repeat
clients.." with all the information about the client, as your code
shows? What happens when a client's information changes (e.g. moves)?

Your "...new records for repeat clients..." (in fact all records) should
contain only the Client_ID, otherwise the basic rules of normalisation
are violated.

You might want to re-think your database structure

From your comments, it appears your form can also add new clients to
the client list - when this happens, you also need to requery the combo
box to update its list.

What is the row source for the combo box?

John


R said:
It's me again. I can't unerstand why I repeat the same process which worked
the first time...now it doesn't.

We have several thousand client entries in our database. The users have
requested that I create a combo box, which gets it's information from our
client list to populate new records for repeat clients. Easy enough.

Below is my "On Update" action...

Private Sub cmbGroupName_AfterUpdate()

Me!ACRONYM = Me![cmbGroupName].Column(2)
Me!SALUTATION = Me![cmbGroupName].Column(3)
Me!FIRSTNAME = Me![cmbGroupName].Column(4)
Me!MIDDLEINITIAL = Me![cmbGroupName].Column(5)
Me!CONTACT = Me![cmbGroupName].Column(6)
Me!NICKNAME = Me![cmbGroupName].Column(7)
Me!TITLE = Me![cmbGroupName].Column(8)
Me!ADDRESS1 = Me![cmbGroupName].Column(9)
Me!ADDRESS2 = Me![cmbGroupName].Column(10)
Me!CITY = Me![cmbGroupName].Column(11)
Me!STATE = Me![cmbGroupName].Column(12)
Me!ZIP = Me![cmbGroupName].Column(13)
Me!COUNTRY = Me![cmbGroupName].Column(14)
Me!PHONE = Me![cmbGroupName].Column(15)
Me!EXT = Me![cmbGroupName].Column(16)
Me!FAX = Me![cmbGroupName].Column(17)
Me!HOMEPHONE = Me![cmbGroupName].Column(18)
Me!EMAIL = Me![cmbGroupName].Column(19)

End Sub

It works great. I can create new records using the combo box to populate
the fields. I can create a new record, not on the existing list. The only
problem is, when I place my cursor in the combo box field and run "find" I
get an error message "The value you entered isn't valid for this field."
This only happens if I'm searching for records that existed prior to my
creating the combo box.

After creating the combo box, I added several new records. When I run the
search for those records, no problem.

PLEASE HELP!

Rhonda

P.S. I already tried switching column 1 and 2 (file # and name) It did not
solve the problem.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using an unbound combo box to populate fields 4
Combo Box selection / AUTO POPULATE combo box in a different form 2
POPULATE a textbox based on combo box selection -- QUESTION 2
Combo Box Help 5
Combo Box Choose Record Problem 4
Combo Box - Find Record - Multiple Field Key Problem 1
Combo Box Yes/No error 2
Need combo box to refresh when moving to new record 2017 1

Top