Accessing fields via variable

Hi

In a table I have fields like field1, field2, field3 and so on. Is there any 
way for me to access these fields via variable like;

I =1 to 10
  "field"&str(I) = "some value"
next

Thanks

Regards 


0
John
3/22/2008 2:30:17 AM
access 16762 articles. 3 followers. Follow

4 Replies
895 Views

Similar Articles

[PageSpeed] 48

See:

http://www.mvps.org/access/forms/frm0003.htm



and add a bookmark to:


http://www.mvps.org/access


HTH
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"John" wrote:

> Hi
> 
> In a table I have fields like field1, field2, field3 and so on. Is there any 
> way for me to access these fields via variable like;
> 
> I =1 to 10
>   "field"&str(I) = "some value"
> next
> 
> Thanks
> 
> Regards 
> 
> 
> 
0
Utf
3/22/2008 3:12:00 AM
John,

Yes, it would be like this:

  For i = 1 to 10
     Me("field" & i) = "some value"
  Next i

Your need to do so, however, probably indicates that your table design 
could be improved.  Let us know if you are interested in exploring that 
aspect.

-- 
Steve Schapel, Microsoft Access MVP

John wrote:
> Hi
> 
> In a table I have fields like field1, field2, field3 and so on. Is there any 
> way for me to access these fields via variable like;
> 
> I =1 to 10
>   "field"&str(I) = "some value"
> next
> 
0
Steve
3/22/2008 3:14:17 AM
On Sat, 22 Mar 2008 02:30:17 -0000, "John" <info@nospam.infovis.co.uk> wrote:

>Hi
>
>In a table I have fields like field1, field2, field3 and so on. Is there any 
>way for me to access these fields via variable like;
>
>I =1 to 10
>  "field"&str(I) = "some value"
>next

You can do so, using a Recordset in VBA code:

Dim i As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)
 '   You can replace "tablename" by a string containing a valid SQL query
returning records from the table 
' use some appropriate code to find the record that you want to edit, e.g.
FindFirst
rs.Edit
For i 1 to 10
   rs.Fields("Field" & i) = "some value"
Next i
rs.Update ' commit the changes to disk


The need to do this makes me REALLY REALLY suspicious of your data structure,
however! I suspect that this table could and should be normalized into a
one-to-many relationship between two tables, or more.
-- 

             John W. Vinson [MVP]
0
John
3/22/2008 3:15:32 AM
Oops,  Hit the enter key trying to paste


So your code would look like:


Dim i As Integer

    For i = 1 To 10
        Me("field" & i) = i*10  ' or some value
    Next i



HTH
-- 
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Steve Sanford" wrote:

> See:
> 
> http://www.mvps.org/access/forms/frm0003.htm
> 
> 
> 
> and add a bookmark to:
> 
> 
> http://www.mvps.org/access
> 
> 
> HTH
> -- 
> Steve S
> --------------------------------
> "Veni, Vidi, Velcro"
> (I came; I saw; I stuck around.)
> 
> 
> "John" wrote:
> 
> > Hi
> > 
> > In a table I have fields like field1, field2, field3 and so on. Is there any 
> > way for me to access these fields via variable like;
> > 
> > I =1 to 10
> >   "field"&str(I) = "some value"
> > next
> > 
> > Thanks
> > 
> > Regards 
> > 
> > 
> > 
0
Utf
3/22/2008 3:16:00 AM
Reply:

Similar Artilces:

Outlook Web Access #17
I am trying to have Outlook Web access run in IIS 6 on a Windows 2003 server. Half of the mailboxes in the company are on the message store on this Windows 2003 server and the other half are on another message store on another server. We have a web site where a use clicks on a link to go to http://192.168.42.7/exchange where 192.168.42.7 is the location of the Windows 2003 server. If a user's mailbox is on this server then the link works fine and they can see their mail. If the user is on the other server they get the error message page not found, dns error or server error. How...

How do I add a grouping level to an existing report in Access?
How do I add a grouping level to an existing report in Access? In report design view, show the Sorting and Grouping dialog (View menu.) Click the "record selector" at the left of the field where you want a row above. Tap the Insert key. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "latwood" <latwood@discussions.microsoft.com> wrote in message news:D49F7C44-8E44-4547-811C-ABC492917A75@microsoft.com... > How do I add a grouping level ...

Programmatic Access Security Pop up with Wordeditor
Outlook 2007 SP2 Exchange 2007 SP1 I want to create a task with an formated hyperlink in the body. At Set objDoc = objInsp.WordEditor always security boxes pop up !!!!! Why???? This is the Code: Private Sub CreateNewTaskWithHyperlink() Dim appOutLook As Outlook.Application Dim taskOutLook As Outlook.TaskItem Dim objInsp As Outlook.Inspector Dim objSel As Word.Selection Dim strLink As String Dim strLinkText As String Dim olAnw As Object Dim docangebot As Word.Document Set docangebot = ActiveDocument 'Only For test fix Hyperli...

Add something to "To:" message field
Hi is there any way how to add something to sender's name field? I need to add organisation name before the sender's name (to distinguish different organisations)? The only way I found so far is changing users name in Active Directory and this is (for me :-) not preferred way as I have to change all 500 mailboxes' names :-( Any hints? On Wed, 8 Dec 2004 07:49:10 -0800, Jiri Kopacek <JiriKopacek@discussions.microsoft.com> wrote: >Hi is there any way how to add something to sender's name field? I need to >add organisation name before the sender's name (to d...

Access data transfer
Hello, I have been looking for a data integration / transfer program able to help with Access and also Mysql. I have had some advice but have not found the right software yet. The main feature is being able to migrate important quantities of contacts and data on a daily basis. The migration would be done manually. Thank you for your help! Access has pretty robuse import and export capabilities. Describe in greater detail what you need to transfer and what file structure is required. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrage...

excel calculations in Access
Hi, I have a complex query written in Excel which i want to replicate in Access, however when i do it flags a error message saying undefined functions. So i'm looking for the eqivualents in Access. The ones it can't find are: SQRT - Which i believe is just SQR in access CHIINV NORMSINV So if anyone can help with this i'd be most grateful. If needed the full access query as it stands at the minute is as follows; Expr1: [T03 - Workings with Calcs]![DSR Rate]/100000+SQRT([T03 - Workings with Calcs]![Calc2 Total]/[T03 - Workings with Calcs]![SumOfStaTOT]^2/[T03 - ...

WriteFile access error on UMDF USB driver
I am creating a driver based on the UMDF FX2_driver sample. My device differs in that it has one input and one output interrupt endpoint. Also am providing a symbolic name. I have a test application I would like to use with this driver/device that uses WriteFile to send data to the driver. I am able to verify thru tracing and a line trace that the data from the WriteFile is being received by the driver and passed on to the usb device. However, the WriteFile fails with a GetLastError() of 1117 (ERROR_IO_DEVICE). Need to determine why I am getting this error. ricobandito &l...

where can i store my personal templates and have access from excel
I have office 2010 beta installed and am having trouble finding a location to store my personal templates to and have access from all my office 2010 programs ...

upgrade for access 97
I'm still running access 97 and am buying a new pc 64 bit windows 7. Is there any way I can upgrade or make it possible to use this program? Thanks -- Faith Hi Faith, I'm not sure if Access 97 includes any 16-bit code. If it does, then it simply will not run on a 64-bit system. You could always purchase and install VMWare Workstation, which allows you to create virtual machines. You will need a licensed copy of the operating system, and any software that you install into a virtual machine. What identified need do you have for running a 64-bit operating system...

Pivot Table Field Header
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Hello, <br> I'd like to find out if there's a way to HIDE the FIELD HEADERS from Pivot Tables. <br> I can't find it in the Pivot Table Options which seems like the place it would be found. <br><br>Thanks in advance. ...

Access 2007: Escape Out of Compacting Corrupts Database
Access 2007 We have a rather complicated application that has the front end set to compact on close. During the QA process our testers were able to corrupt the front end by hitting the escape key a few times while the front end was compacting. Lots of nasty popup messages when the application was restarted. We created a simple database with one table and one form (see attached). The form has three buttons: Populate Data -- This empties the table and then adds a little over one million records. View Data -- This opens the table in normal mode. DoCmd.Quit -- This issues ...

vb variables
have a series of check boxes on a form such as check box 1 labelled bhn, check box 2 labelled dme, check box 3 labelled amb when any of these boxes are checked, want to store the value in a vb variable. for example, if the first one is checked, the value in the variable would be bhn. then i want to take that variable and use in a query. how can i do this? i was trying to use case in vb code and then when the box is checked, take a numeric value and give the text value in the variable. but then i could not get the query to see the variable. thanks for your help jnewl wrote: >hav...

Using Access 2000 packaging and deployment wizard
Hi In Access 2000 when I open a module in my app and from Addins menu run the Access 2000 packaging and distribution wizard, it cerates the distribution package with the original MDB. How can I make it instead include the MDE of the app instead? Thanks Regards ...

Customize field in My Task
Dear all, In My task view in PWA, is it possible to customize the name of a Field. For example, i want field "Text1" to be called "Test". Thanks for your help Yann -- When you create the custom enterprise field, you need to give it a new name, which you apparently did not do. Log into PWA with administrator permissions and then click Server Settings - Enterprise Custom Field Definition. Click the name of the field in question to open it for editing. Give the field a new name and then click the Save button. From your description of the problem, it so...

Instant Access not enabled.
At start-up of Excel I get a pop-up which states: "Unable to Find Quit on the File Menu. Instant Access not enabled." I can then choose to press OK on the pop-up, after which everything works OK and Instant Access does work. I believe Quit is on the File Menu: it's the X mark to quit Excel completely. I also have an add-on icon which I can't get rid of (couldn't get rid of it in Excel 2000 either), as it keeps coming back, even though it says it can't come back and will be irretrievable. This add-on is for an old scanner that I no longer use and have deleted ...

Microsoft CRM 3.0 with Microsoft Outlook Web Access
Is it existing un plugin for Microsoft Outlook Web Access. I installed the client for Outlook. Is somebody can help me Thanks ...

Remarks field character size
I have been looking at changing the caracter size of the Remarks field on the PO. I have been getting some feed back that I shouldn't do that because its going to make the database unstable. The current size of the Remarks field is set to 30 characters and it just don't cut it for the PO. If we have to add a special instruction we can't. Does any one know anything about the stability issue or how to increase the character size? Regards, -- Microsoft Partner Don't they explain this stuff when you become a "Microsoft Partner"? "Yaniv Zahavi" &l...

How to manipulate Outlook data from MS Access
When the Calendar folder is used as a linked table in MS Access, only some fields are shown. Is there a way to have them all? The linked table method has major limitations, including the inability = to show even all the reasonably important fields. While articles have = been written on how to expand on this method's obvious features, I've = never been able to duplicate the results. See = http://www.outlookcode.com/d/database.htm#linkedtables .=20 That page will also give you other ideas on how to work with databases = and Outlook together. You'll need to write code or use a t...

Showing the actual database, not access's idea of it...
I've been working with MS Access for the past few weeks. A friend has a side job working for a summer camp and he needed a database to store all the kids information. Using VBA is not going to happen, I thought I could do what he needed much much easier in visual studios.net 2008 and I probably could have if that department of Microsoft didn't do what appears as drastic as disown the MS Access department. After much work I am able to read/replace and edit the data, but through the whole process I started noticing this. First, in Access the ID(auto number) is listed co...

how can i make a in-process report that can be accessed by severa.
i need to set up an in-process report that can be accessed by several users at once, in which every user can input data without shutting down the workbooks on the remaining computer so that the one user can do his inputting. Hi you could use 'Tools - share workbook' BUT: - several functions are then no longer available (see Excêl's help for details' - IMHO Excel is NOT designed for multi-user access. Use a database application instead -- Regards Frank Kabel Frankfurt, Germany "tim" <tim@discussions.microsoft.com> schrieb im Newsbeitrag news:E5B2A371-4...

Outside programs accessing wrong Outlook account when sending
A program designed by one of our technicians attempts to send out an emergency email to multiple email addresses, using Microsoft Outlook 2002. However, the mail message attempts to send thru our Exchange server, instead of the SMTP it is supposed to be using. The Exchange is not set up to send mail out of our university for security reasons, yet Outlook will not allow me to redirect those mails thru our SMTP server. The SMTP is set up as the default on every user's machine here, but these emails skip right past it. Any suggestions would be greatly appreciated. Is the Outlook pr...

How do I change the action of button in Access 2007 contact list?
I need to change the email address manually when I click the "send email" button to create new email directly from Contact List of Access 2007 as the default email address is including the receiver name (name + emal address was recognized as wrong email address by Windows Live Mail). How to change the default setting of Access 2007? -- THANKS & BEST REGARDS, Nguyen Tung Director of Sales and Marketing --------------------------------------- THIENTHAONGUYEN TRANSPORTATION CO., No181 Dai Tu Str,Hoang Mai Dist,Hanoi,Vietnam Tel :(84.4) 36410 577 - 107 /...

access denied
cannot open completed worksheet from folder that is in My documents. Cannot do anything with it. ...

Access Bulletin Board? Acess/Excel Jobs?
Just wondering if anyone might know of online resources providing info pertaining to Access or Excel software positions. How does a person go about finding such positions? CareerBuilder, Monster, HotJobs? Is there someplace more specific? Is this the kind of thing that generally requires referral? "David Portwood" <Mondrogan@yahoo.com> wrote > Just wondering if anyone might know of online > resources providing info pertaining to Access or > Excel software positions. How does a person go about > finding such positions? There seem to be fewer jobs, these ...

question regarding access
So here is the situation.. Dual booting win vista business and win 7 ultimate on 1 partitioned drive and all games and docs on another drive. Games I have added are accessible from vista as they have always been but I cannot seem to get them to play from win 7. In vista when I click on the shortcut for 1 particular game it starts with no problem however even after copying the shortcut from the vista side to win 7 it asks me to buy/unlock the game even though I own it and have for 3 years. Is there something simple I am missing here? I do not really want to buy it again or sta...