Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 2nd, 2005, 03:06 AM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default Question regarding recordset

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



 
Old March 2nd, 2005, 09:02 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old March 2nd, 2005, 05:35 PM
Registered User
 
Join Date: Jan 2005
Location: , , .
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old March 3rd, 2005, 08:52 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Quote:
quote:Originally posted by emachines
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.
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.

Quote:
quote: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?
I'm not familiar with ADO.net. Sorry.

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




Similar Threads
Thread Thread Starter Forum Replies Last Post
Select and Recordset question iacon Beginning VB 6 4 June 24th, 2006 09:37 AM
Recordset Update question smarks Classic ASP Databases 2 March 14th, 2005 03:57 AM
Recordset delete question/problem bleutiger Classic ASP Databases 1 February 25th, 2005 07:50 AM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
Chapter 13; Using Recordset question ou812 BOOK: Beginning ASP 3.0 2 November 7th, 2003 12:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.