Access 2003 Combo problem

  • Thread starter Thread starter ceb39
  • Start date Start date
C

ceb39

I have an access form I have been using for four years without any
major problems. Part of the data entry requires a user to enter a
state two letter designation in three places. I am trying to modify
the form to use a combo box which displays the two letters of the
states and then allow the user to select the state.

I have established a table with the the state name in one field (30
characters long) and the two letter combination in the other field (2
characters long).

I have placed a combo box on the form I am using and see the various
two letters display. Once selected this two letter combination is to
be displayed on the form and stored in the appropriate field the form
data goes to. Only problem is when I select the two letter
combination a dialog warning box appears with the following message:

"The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data."

The field in the table where I get the two letter combination is two
characters long, and the three fields in the table where the form data
goes are all two characters long. Any one have an idea what is
generating this error message?

The Row Source statement looks like this:

SELECT [tblState].[StateName], [tblState].[StateAbv] FROM [tblState];

Any help would be appreciated.

Thanks

Chuck Buckley
 
G'day Chuck

I am by far a long way off MVP, and I'm certain you'll get a few responses,
but here's my 2 cents worth for free.

If you have BoundColumn set @ 1 then it will try and insert the Name, not
the Abv, that's why your getting the error message "Too Long"
What's happening is that you are attempting to insert the Name, not the Abv
into the field.

Have a questions.?

1. What do you want stored, the Name or The Abv.

Change it to this:

SELECT [tblState].[StateAbv], [tblState].[StateName] FROM [tblState];

2. Why does the user have to enter in three places.

You could use something like this in the AfterUpdate()

Me.Place_1 = YourCombo.Column(0)
Me.Place_2 = YourCombo.Column(0)
Me.Place_3 = YourCombo.Column(0)

Column(0) is correct, (0) is interpreted as the first column by VBA.
(Something along those lines) this will automatically populate the areas in
1 instance.

Though I am still intrigued as to why 3 times, couldn't you just add an
index to your "Abv field", then a One-to-Many Relationship could be
established.

HTH
Mark.
 
G'day Chuck

I am by far a long way off MVP, and I'm certain you'll get a few responses,
but here's my 2 cents worth for free.

If you have BoundColumn set @ 1 then it will try and insert the Name, not
the Abv, that's why your getting the error message "Too Long"
What's happening is that you are attempting to insert the Name, not the Abv
into the field.

Have a questions.?

1. What do you want stored, the Name or The Abv.

Change it to this:

SELECT [tblState].[StateAbv], [tblState].[StateName] FROM [tblState];

2. Why does the user have to enter in three places.

You could use something like this in the AfterUpdate()

Me.Place_1 = YourCombo.Column(0)
Me.Place_2 = YourCombo.Column(0)
Me.Place_3 = YourCombo.Column(0)

Column(0) is correct, (0) is interpreted as the first column by VBA.
(Something along those lines) this will automatically populate the areas in
1 instance.

Though I am still intrigued as to why 3 times, couldn't you just add an
index to your "Abv field", then a One-to-Many Relationship could be
established.

HTH
Mark.




I have an access form I have been using for four years without any
major problems. Part of the data entry requires a user to enter a
state two letter designation in three places. I am trying to modify
the form to use a combo box which displays the two letters of the
states and then allow the user to select the state.
Click to expand...
I have established a table with the the state name in one field (30
characters long) and the two letter combination in the other field (2
characters long).
Click to expand...
I have placed a combo box on the form I am using and see the various
two letters display. Once selected this two letter combination is to
be displayed on the form and stored in the appropriate field the form
data goes to. Only problem is when I select the two letter
combination a dialog warning box appears with the following message:
Click to expand...
"The field is too small to accept the amount of data you attempted to
add. Try inserting or pasting less data."
Click to expand...
The field in the table where I get the two letter combination is two
characters long, and the three fields in the table where the form data
goes are all two characters long. Any one have an idea what is
generating this error message?
Click to expand...
The Row Source statement looks like this:
Click to expand...
SELECT [tblState].[StateName], [tblState].[StateAbv] FROM [tblState];
Click to expand...
Any help would be appreciated.

Chuck Buckley- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Mark:

Thank you for your response.

To answer your questions.

What do I want to store? I want to store the abv of the state.

Why does user have to enter in three places? The data which is being
entered into the database might represent three different locations.
In fact now that you ask that question, I think the number of times
the abv. is the same is only about 20% of the time.

Developed a couple of test databases and a test form which only
address the state abv. Your right, when I select the field which has
the abv in it, the whole name of the state is store in the field where
I thought the abv was going.

Will give your solution a try and see what happens.

Thanks a bunch.

Chuck
 
Back
Top