hi,
I have recently started working in Access.
In one of the forms, I am required to display data from the recordset in several controls on the form. Now the thing is, some of fields of the recordset, has data that I want to change before they are displayed on the controls (like, one of the fields have abbreviated name of states, and in controls I want to display full name of corresponding state). But when make such change in the recordset, the data in the database also gets updated. I don’t want to change the data in the database? How can I do that?

And there is one more question. If I want to attach a control (which is a combo box), with one of the fields in the existing recordset, how would I do that?

A snippet would be of great help.

regards

If you want to show the spelled out state but only want to store the abbreviation, you need to create a new table called tblStates that has

* lngStateID, type long, autonumber primary key
* strAbbrev, type string, length 2, holds the abbreviation
* strState, type string, length (say) 30, holds the spelled out state.

Then on your main form, make the abbreviation field invisible and put an unbound textbox on there. In the form's ON CURRENT event you put something like

Me.txtState = DLookUp("[strState]", "tblStates", "[strAbbrev] = '" & Me.txtAbbrev & "'")

I'm using fake names for the form controls. Use yours. To attach a combobox for a control, use the toolbox to put one there and follow the Wizard's prompts.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for replying Greg.
Actually, I wanted a general solution for the problem. As I mentioned before, one of the fields in the recordset have abbreviated names of states. What i did not mentioned is, other fields may intermittent some NULL entries, which I want to take care of. Then, I also want to do some conditional formatting, like, if the previous data in the some field has same value as the current data in that field, while looping through the recordset, then I want to display it with special charater like " -- " or omit it all together, that is, make that entry disappear in the final report/form. Thus what I want is have control over the recordset data, so that I can do conditional formatting, before displaying the data, without making change in the actual data in the database.

Please remember that I am creating the recordset through program, depending upon the selections in the combobox1 and combobox2 and some other text entry fields in the form.

Ideally, I want to have control over the recordset data in the same way, that is offered in dataset in dot net technology, if you are familiar with ADO.net. Is it possible?

regards

Well, in reports you do that by grouping the data so that one state is grouped at at time and all the data for that state is indented. On a form, you can group (so to speak) by creating a subform. The main form is based on the states table and the subform has the data. The parent and child are linked via State.

I'm not familiar with ADO.net. Sorry.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division