Trouble With Combo Box - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Trouble With Combo Box

Richard Van Wagoner

2 years ago

I have a table with state names and abbreviations. On my customer form I have a combo box that I can pick a state with. This puts the stateID number into my Customer Table which is fine. But now to concatenate the City State and Zip together Ineed the text of the stste. I have tried nested if statements but they are too long, I have tried the switch Function also too long, the same with choose function. Is there a way in my combo box to put the state text in the Customer table instead of the stateID?

	Sami Shamma @Reply 2 years ago
You Don't want to do that. The way you have it set up is correct. If you are concatenating in the form, then the state name is the second column of the combo box. Remember, combo boxes are zero-based So your code will look something like this: CSZ= City & ',' & StateCombo.column(1) & ' ' & Zip

	Richard Van Wagoner OP @Reply 2 years ago
Thank you, But this is in another form CustomerListF. The Combo box is in CustomerF. I need to reference the Combo box from another form. How is this done?

	Kevin Robertson @Reply 2 years ago
Value From a Form If this isn't what you need please post some screenshots.

	Richard Van Wagoner OP @Reply 2 years ago
Here are the screenshots. As you can see when concatenating the fields CITY STATE and ZIP I get a number for the state. I need the text instead of the Number

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Sami Shamma @Reply 2 years ago
Richard What is the row source for your State combo box?

	Kevin Robertson @Reply 2 years ago
I would also like to see screenshots of the forms (Design View AND Form View) since you mentioned TWO different forms. The video I pointed you to should be what you need. Did you watch it?

	Richard Van Wagoner OP @Reply 2 years ago
Here are the screenshots of the query and the two forms that are in question. I have taken the CSZ Field out of the CustomerListF. But the Concatenation is CSZ:[City]&", "&[State]&" "&[Zip].This is a field in my FullNameQ.

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
Where are you trying to concatenate City, State, ZIP? You mentioned CustomerListF but that only has the customers name in it. In the Customer Table you have a field called State. Is that Short Text or should it be StateID (from another Table)?

	Richard Van Wagoner OP @Reply 2 years ago
I am trying in the query to concatenate the fields. and then use that in the customerlistF. As I have stated before I have taken that feild (CSZ) out of the customerlistF because it doesn't work. Also If I cannot get It to work in the query I am not going to put it in the form until it works. As for the field called state I have tried it both ways as Short text and also as Number.

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Richard Van Wagoner OP @Reply 2 years ago

	Sami Shamma @Reply 2 years ago
Ok. You have couple of problems 1) your query needs the state table as will linked between State and StateID. Then you concatenate using StatAbb. 2) you row source, flip the first two fields. Start with StateID then StateAbb. Then hide the key field

	Richard Van Wagoner OP @Reply 2 years ago
I just figured this out before I check the notifications. I did just what you suggested, adding StateT to my Query and using StateAbb in the query. As for the row source it is already in that order. I don't know why simple things like this always give me trouble. But after a couple days it comes to me how to fix them. I just is frustrating that I can't remember things as I need them.

	Sami Shamma @Reply 2 years ago
What I found helpful, is rewatching the Videos a second and a third time.

	Kevin Robertson @Reply 2 years ago
And also a whole lot of practice. Do it over and over it becomes second nature.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View