Using ComboBox to select value, how to add a text prefix to the value stored in the field

[ConnieZ]

Hi all!



My project is a data entry form to enter Product Data for an eCommerce site. My own site.

I have a ComboBox set up to pick the State where the product is manufactured.
I would like the user to simply pick the name of the State by pressing the first letter of the State name.
When the value is stored in the table, I would like the add the prefix "The Great State of " to that State name.

I am pretty new to Access, and am totally lost on this issue. My form is looking and functioning great, but i am stumped on this.

I thought it would be done via the ComboBox, but maybe it's done in the Table itself?

Thank so much!!

... ConnieZ

[Micron]

When the value is stored in the table, I would like the add the prefix "The Great State of " to that State name.

To what or where? This would be an example of a calculation, which you should not store except in rare cases, and this isn't one of them. If you want to see this in an unbound form textbox or report, it would be a calculated control - e.g. ="The Great State of " & cmbState where cmbState is the name of your combo. If that gives the wrong result, you probably need to specify which combo column. That is a zero based value, so column(0) is the first column.

[ConnieZ]

To what or where? This would be an example of a calculation, which you should not store except in rare cases, and this isn't one of them. If you want to see this in an unbound form textbox or report, it would be a calculated control - e.g. ="The Great State of " & cmbState where cmbState is the name of your combo. If that gives the wrong result, you probably need to specify which combo column. That is a zero based value, so column(0) is the first column.

Hi Micron,

Please forgive my ignorance, but am I creating a new textbox on the form? I want the value to be written into the State field in the Products table.

I want them to pick Louisiana in the ComboBox dropdown, which pulls the States from a reference table called States. Then I want it to write The Great State of Louisiana into the State field in the Products table. Where should I be putting the "unbound form textbox?"

Thanks so much!

... ConnieZ

[moke123]

I want them to pick Louisiana in the ComboBox dropdown, which pulls the States from a reference table called States. Then I want it to write The Great State of Louisiana into the State field in the Products table. Where should I be putting the "unbound form textbox?"

What Micron is saying is that you do not store that in the table. You already have "Louisianna" stored in your states table. Tecnically, you would store the primary key of the record in the states table in your products table. Then anywhere you want to display it as "The Great State of ...", you then concatenate it (or calculate it) as needed in your source query or rowsource.

BTW, how do handle Puerto Rico and Washington DC which are not states?

[hnorgaar]

Take a look at the base attached. Open the data form and choose state there and see what happens in Data table

[Micron]

I can't imagine how State names are related to products but it's your db. States and customers or delivery destinations, sure, but products? Anyway moke123 is saying the table entries would be like this

tblProducts				tblState
ProdID	ProdName	State		StateID	StateName
1........	Widget	1...		1.....	Alabama
2........	Fidget	1...		2.....	Alaska
3........	Doodad	2...		3.....	Arizona
				4.....	Arkansas

In your forms and reports you would have a control that constructs the sentence you want.
Why would you want to do this in a table when you already have state names? You might want to check the links below to see if you're on the right track with your design.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[ConnieZ]

Take a look at the base attached. Open the data form and choose state there and see what happens in Data table

Yes!!!
Exactly what I was hoping for!!Thank you so much!! Now I will study and copy what you did. I truly appreciate the trouble you went through to create that and upload it.

[ConnieZ]

What Micron is saying is that you do not store that in the table. You already have "Louisianna" stored in your states table. Tecnically, you would store the primary key of the record in the states table in your products table. Then anywhere you want to display it as "The Great State of ...", you then concatenate it (or calculate it) as needed in your source query or rowsource.

BTW, how do handle Puerto Rico and Washington DC which are not states?

You are right! There are a number of United States Territories, and DC. But... I was going to learn how to do just the States first, then learn how to do the others later, figuring there would be some kind of "if/then" code to add. These are not States that the products will be mailed to, they are the States in which the products are manufactured. So far I don't have any manufacturers in Territories or DC, but I know I will one day.

Thank you for the info!

[ConnieZ]

I can't imagine how State names are related to products but it's your db.

Thanks so much Micron for the followup information. hnorgaar in a post reply gave me a sample database with the form field constructed, so now I will use it to learn what he did and what you are sharing, which I'll assume is the same technique.

The reason I need State, is because my eCommerce site is a niche site selling only products which are manufactured in the United States. One of the Product search filters is the State in which the product is manufactured. Plus, I feature an informative blurb about each manufacturer which includes the State in which they are located. My site is a tiny startup right now, but I'm trying to build it as efficiently as possible. I've learned that entering the products one by one manually is tedious, and my ecommerce platform has a .csv upload which is fast and really works pretty well, so I tried using Excel to create the upload files, but then realized that Access would give me a much cleaner and user-friendly user interface.

Thank you so much for your help!

[Welshgasman]

but I'm trying to build it as efficiently as possible

I would not recommend storing that string in the field. As others have mentioned, add it when needed.
Have a field in your table to indicate what each record is, State, district, whatever, then concatenate the relevant string to your 'area name' when needed.

Just because you can do it that way, does not mean you should.

[Micron]

I will use it to learn what he did

By the sounds of it, you should take that example as something that you should not do so no, it would not be the same technique. However, our dissenting opinions are based on database principles. If you need it in a table for some sort of web based data then you do what you have to do I guess.

[ConnieZ]

I would not recommend storing that string in the field. As others have mentioned, add it when needed.
Have a field in your table to indicate what each record is, State, district, whatever, then concatenate the relevant string to your 'area name' when needed.

Just because you can do it that way, does not mean you should.

Hmmm. Great idea! I will make a note of that and explore how to do it. On my product screen in the eCommerce site, I have a field called: Vendor Location that is where it displays, "The Great State of Louisiana" It can easily display "The U.S. Territory of Puerto Rico" or other.

But for now... I'm happy just to be able to concatenate the text and am working on hnorgaar's solution trying to make it work on my system.

Thanks so much!!

... Connie Z

[hnorgaar]

Let me know if you have any questions

[moke123]

But... I was going to learn how to do just the States first, then learn how to do the others later, figuring there would be some kind of "if/then" code to add.

One thing you'll hopefully learn is to think ahead of your needs and plan accordingly. Very often whats percieved as a minor little addition results in a total re-write of hundreds of lines of code and forms. Thats part of the reason I pointed it out.
As you become more familiarized with the forums you'll see it is full of posts looking for work-arounds trying to fix what should have been planned for from the get-go.

I'm assuming your constructing your CSV with a looped recordset. That is where you would add the prefix string to your csv, on demand, as needed.

If in the future you wanted to change it, you would only have to do it in one place, and not re-write the form, combobox and code, as well as updating all the records in your table.

[ConnieZ]

Update: Using hnorgaar's sample database, I was able to recreate the process using my data. I was unsuccessful at first because I didn't put in the third column in Column Count and Bound Column for StateText in the ComboBox. Now it is working perfectly. Thank you so much!!

Note: Just want to mention that this is not the perfect solution, and it temporary, and that others have given great suggestions (not that I understand them) and that I will do the best solution as soon as i learn how.