Creating and looping through a record set.

I haven't done any coding for two years and didn't do much of it then but 
here is my problem. I have looked though my old access books but am not 
having any luck what so ever.

I have a table called "tblInvoice" with three fields "strInvNum", 
"strGLAcct", and "dblAmount". The fields strGlAcct and dblAmount may contain 
arrays. An example of the data looks like this.

strInvNum   strGLAcct           dblAmount
556            536~436            50.00~100.15
557            466                   30.00
558            536~556~563    1.50~536.00~56.15

I need to then put the records into a table called tblInvoiceDetail as 
follows:
strInvNum   LineNum   strGlAcct   dblAmount
556             1             536              50.00
556             2             436            100.15       
557             1             446              30.00
558             1             536                1.50
558             2             556             536.00    
558             3             563               56.15

Any suggestions on how to do this? I did it years ago but no longer have the 
database to copy the code from.

0
Utf
2/4/2010 9:20:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

1 Replies
594 Views

Similar Articles

[PageSpeed] 50

On Feb 4, 3:20=A0pm, Bill <B...@discussions.microsoft.com> wrote:
> I haven't done any coding for two years and didn't do much of it then but
> here is my problem. I have looked though my old access books but am not
> having any luck what so ever.
>
> I have a table called "tblInvoice" with three fields "strInvNum",
> "strGLAcct", and "dblAmount". The fields strGlAcct and dblAmount may cont=
ain
> arrays. An example of the data looks like this.
>
> strInvNum =A0 strGLAcct =A0 =A0 =A0 =A0 =A0 dblAmount
> 556 =A0 =A0 =A0 =A0 =A0 =A0536~436 =A0 =A0 =A0 =A0 =A0 =A050.00~100.15
> 557 =A0 =A0 =A0 =A0 =A0 =A0466 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 30.00
> 558 =A0 =A0 =A0 =A0 =A0 =A0536~556~563 =A0 =A01.50~536.00~56.15
>
> I need to then put the records into a table called tblInvoiceDetail as
> follows:
> strInvNum =A0 LineNum =A0 strGlAcct =A0 dblAmount
> 556 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 536 =A0 =A0 =A0 =A0=
 =A0 =A0 =A050.00
> 556 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 436 =A0 =A0 =A0 =A0=
 =A0 =A0100.15 =A0 =A0 =A0
> 557 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 446 =A0 =A0 =A0 =A0=
 =A0 =A0 =A030.00
> 558 =A0 =A0 =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 =A0 =A0 =A0 536 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A01.50
> 558 =A0 =A0 =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 =A0 =A0 =A0 556 =A0 =A0 =A0 =A0=
 =A0 =A0 536.00 =A0 =A0
> 558 =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 =A0 =A0 =A0 563 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 56.15
>
> Any suggestions on how to do this? I did it years ago but no longer have =
the
> database to copy the code from.

Use SPLIT() to break the repeating values out.  You'll need to do it
for both GIAccount and Amount.  Since they should have the same number
of values, you could use a single counter variable to loop through
them.

dim varGIAccount as Variant, varAmount as Variant
'process outer loop here...
dim rsDest as dao.recordset
rsDest.OpenRecordset("DestTable",dbAppendOnly)
rsSrc.OpenRecordset("SrcTable",dbopenForwardOnly)
do until rsSrc.EOF
varGIAccount =3D Split(rsSrc.Fields("strGLAcct"),"~")
varAmount =3D Split(rsSrc.Fields("dblAmount"),"~")
for i =3D LBound(varGIAccount) to UBound(varGIAccount)
      With rsDest
      .addnew
      .fields("InvNum") =3D rsSrc.Fields("strInvNum")
      .fields("LineNum") =3D rsSrc.Fields("LineNum")
      .Fields("GIAccount") =3D varGIAccount(i)
      .Fields("Amount") =3D varAmount(i)
      .Update
next i

rsSrc.close
rsDest.close

....totally untested, but that should get you started.  Basically, you
split the two repeating fields into arrays, using Split() and then you
loop through the arrays, adding the values to the final table.
0
pietlinden
2/4/2010 10:27:36 PM
Reply:

Similar Artilces:

It takes time to load records in MS CRM Client
HI All, I am facing a serious problem, whenever i access CRM through Client, it takes time to load and after getting records in it, it is not shown properly. Why so??? please help me out. Client is always connected to server, Is there any way to check if it is connected or not. Thanks in Advance. Nitin Verma Nitin, I had the same issue with a client of mine and after working through all of the standard MS suggestions we found that by removing the clients local OST file and recreating it from the exchange server the issue was resolved. MS informed us of 2 items. 1) Having more then 1,000 e...

Set Checkbox.Backcolor with ColorIndex
Hi, I'm trying to create a dynamic form where I want to put 56 checkboxes, each one with one of the different "Excel colors" that there are. With that I mean the 56 colors that you can set to for instance a cell by using Interior.ColorIndex. My problem is that the BackColor should be a Hex value or the "&H8000000F" format and I don't really have an idea of how to transfer the ColorIndex values (1, 2, 3, 4 and so on) into one of these formats. Anyone have any idea? Hi, I did that (not a very casual way but it works) A Form with 56 button with the Tag parameter...

How do I restore my original excel settings to default?
EVERY SINGLE TIME that I open up Excel, it opens every single document that I have ever saved. I think this is because I changed the default settings in XLStart and now it is going bonkers on me. Recently I tried to open up a workbook that i had saved (replacing the previous work) and it just kept opening up every workbook except the one that I needed. so a days work was lost. I am very frustrated and fairly new with the inner workings of excel, so I would appreciate all the help I can get. Thanx. Version 2002 It sounds more like a setting that you changed. Tools|options|general tab Se...

Settings for viewing PPT presentation in Word Doc
One of my users has been receiving MS Word docs with embedded PPT presentations and viewing them just fine. She recently felt the need to change "something" in the latest Word doc and now she only sees the first slide - double clicking does not start the slideshow. Others in her office can access the document and the presentation works fine. It appears that she inadvertantly changed some setting in her Word options that govern how an embedded PPT file works. She is using Office 2007 and I can't find anything. Her project is going to continue at least another 10 mo...

How can I get back to first question in set up?
When asked to select which type of e-mail account to choose, I chose wrong. Now I can't get back to that screen. Can you help? Thanks >-----Original Message----- >When asked to select which type of e-mail account to choose, I chose wrong. >Now I can't get back to that screen. Can you help? Thanks >. > Yes we all use ESP here. What version? I'm going to venture a guess and tell you how to do it in MS Office Outlook 2003. Choose E-mail Accounts from the Tools drop down menu... Follow the wizard... Copy and paste this response into a Notepad document. Save ...

Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

setting notice to group (large company, recipients in different cities)
I'm sorry for the multiple groups. I don't understand Outlook well enough to narrow down my groups to just the right one or two. Here's my situation and my question -- there are certain deadlines and tasks that are due on a monthly basis, all revolving around certain business days (related to closing the corporate books). Presently these deadlines are on an excel based calendar located on the intranet, and a monthly email reminder is sent out restating the deadlines. As you can see it is somewhat awkward to force people to navigate through the intranet and then print out th...

How do i activate the Macro recorder toolbar
While recording a macro, I accidently closed the macro recorder toolbar .. Now whenever I record a macro, the recording toolbar does not appear. there fore I have to use the menu option to stop recording the macro. Also I cannot use relative and absolute reference. Can you please let me know how I can activate my macro recorder toolbar to appear the moment start recording my macro. Thanks --- Message posted from http://www.ExcelForum.com/ Tools>Customize>Toolbars. Select "Stop Recording" toolbar and "Reset". Put a check in the checkbox. Stop Recording Toolb...

IMFv2 Microsoft Update caught in "needs update" loop after January 5, 2005 Update (Exchange 2003 SP2)
We applied Exchange 2003 SP2 last year without incident, and we enabled and configured IMFv2 to pull updates down from Microsoft Update with no problems. The December 15, 2005 IMF Update (6.5.7757.0) was identified and installed correctly last year. Last night as part of the regular monthly updates, a new update to IMF was identified, January 5, 2006 IMF Update (6.5.7765.0), we applied it along with the other Critical Updates and rebooted the server. When the server came back up shortly after, Automatic Updates identified the old December Update to IMF as needing to be installed even th...

Textbox scrollbar settings
I have a userform whose main component is a TextBox. It is locked as the user is only permitted to read not edit the data there - that is done in the main spreadsheet and the userform is modeless. The TextBox has a vertical scroll bar to permit the user to navigate around its contents. The user will not normally place the focus on the Textbox contents itself but just use the scrollbar.. The data in the textbox is updated programattically (using vba). When this happens the box contents scroll back to the top - I do not want this to happen. I want the scroll position to remain as th...

this record can not be deleted
cant delete test records that ive created,error message its associated with another record. attempted to delete previous associated record, no luck. can i clean out old records? looking for options i have quotes, orders and invoices that are linked together.(test runs) i can't delete some of them via the user interface. I assume this is for history integrity. I would reinstall, except for the pain it took getting my contacts and accounts in. I have the same problem. I added some custom fields into the contact table and now I can't delete it. It tells me the data is part ...

can i create message for icon which create in DLL
Hello guys, how to call NOTIFYICONDATA and post message to the icon in MFC extension dll? I can create the icon, but i can not catch the message which point to the icon. my code in DllMain: CreateDialog((HINSTANCE)AfxGetApp(),MAKEINTRESOURCE (IDD_DIALOG1),NULL,SetDispDlgProc); dlgTimer.Create(IDD_DIALOG1); SetResolutionMenu (dlgTimer); m_hIcon = AfxGetApp()->LoadIcon(IDI_DLLSTATUS); StatusAreaIcon(NIM_ADD,1006,m_hIcon,"running"); but the callback function SetDispDlgProc() never be called except Create, why? thank you! delu "delu" <qiudelu@hotmail.com&...

Creating user after big mess up
I had an issue with license and followed a recommended procedure of removing and reinstalling the license. Now I have locked everyone out of CRM! I can't add users through Deployment manager it gives me the error: "The user has not been granted any licenses or the user has not been assigned any roles (80042f09)" I can not open CRM because there are no users installed. I am running windows server 2003 w/sp1 and SQL with latest SP. Running CRM 1.2 Any help would be appreciated! Are you the admin of CRM. can you log onto the system with that account? -- John O'Donnell ...

Creating CRM user
When attempting to create a new CRM user in the user manager wizard, I get the following error after selecting a user from the list of domain users. "You don't have sufficient security privileges to run the user manager wizard." The server is running Windows 2000 server, not 2003 like I have seen on other similar inquiries. Any direction is appreciated. Thanks, I think you need to first liscense the server in deployment manager/liscence manager. Then try to add users in settings through the web interface to verify that you are liscensed and set up properly. BK "A...

How to Copy Accounts Settings to Another Computer?
Hello: I know how to copy the PST file and export rules for putting in my laptop. But I couldn't find info. on how to copy accounts or export them. Does anyone how to do this? What else do I need in order to have my accounts in Outlook to be identical on my laptop as on my PC? Thanks so much! Spudy You used to be able to do this in Outlook 2000 (Internet Mail Only mode). In Outlook 2003, you either do it manually, or use the Office 2003 Save My Settings Wizard. http://support.microsoft.com/?id=826809 This seems to be an all or nothing thing, so I tend to just do it manually. "...

Is there a way to know when a Contact Record was created?
Is there a way or place to see when a Contact Record was created? I used to us ACT and part of the Properties or General Info automatically recorded the Creation Date for that Contact. It was very useful info to purge my Data Base of Old Inquiries that never developed. "WIM4246" <WIM4246@discussions.microsoft.com> wrote in message news:93299CA1-AD1A-421D-8371-97DF1AE6BB03@microsoft.com... > Is there a way or place to see when a Contact Record was created? > > I used to us ACT and part of the Properties or General Info automatically > recorded th...

Extender-Create a new data entry window
Is it possible to create a new extender form or window (not sure of the terminology) to enter data into Dynamics? Specifically the Sales Forecast Window in Manufacturing is terrible for entering data, everytime you move to the right, it the scrolling window jumps back to the top and you have to find the item again. Anyway, I would like a new window to enter a sales forecast, is this possible? thanks -- Doug Unfortunately Extender data is stored in Extender tables and cannot be stored in the Sales Forecast tables. I agree with you that the data entry screen is horrible. I am tal...

Creating a PDF
How can I publish my company newsletter to a pdf so that I may send out the newsletter via e-mail? Here's how I do it. Create the newsletter with Publisher (or other program you like, i.e., Word). Convert it to a pdf file with a pdf producing program, i.e., Primopdf, PDF-XChange, Acrobat etc. Send newsletter out as an attachment to the e-mail. Primopdf is free and can be found at: www.Primopdf.com PDF-XChange is a modestly priced program which produces excellent results and beats all pdf programs for creating the smallest size files. It can be found at: http://www.docu-track.c...

I am looking for a template to create bank deposit slips.
I am so tired of lilling out bank deepost slips and I am looking for a template to create my own. Thanks. Unless your printer has a magnetic ink cartridge, you will not be able to do this. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "kkmoore" <kkmoore@discussions.microsoft.com> wrote in message news:571C5524-B368-492A-89CD-34CB94E7EF99@microsoft.com... >I am so tired of lilling out bank deepost slips and I am looking for a > template to create my own. > Thanks. > On Jan 7, 10:51...

Some Visio drawings lose text when creating PDF from Word
I have a number of Visio drawings that I've inserted into a Word document, using Edit>>Copy Drawing in Visio then Paste Special (selecting Microsoft Visio Drawing Object and Paste Link). When I compile a PDF using Adobe Acrobat Professional 7, some of the Visio drawings lose their text (all of it). <arggh> Anyone got any ideas or cures?? Thanks Mike ...

Exchange System Manager error
Hi, I created some Public folders in OL 2003 before Xmas no problems as I am the administrator for the local domains server & Exchange admin. Today I tried to create additonal folders form my LAN XP machine but continually receive the error of: Unable to create the folder. You do not have sufficient permission to perform this operation on this object. See the folder contact or your system administrator. When I look at the folder properties in OL I see the folder contact is NT USER:S-1-5-21-606747145-1682526488-682003330-1109 but I have no idea who that is...or was more likely! If I then g...

Need help creating a database for a shopping cart website.
Being a total newbie to Excel I need some help building a database for my first shopping cart website. Other than the SKU numbers, what other information do I need to include? For the website I have selected SecureNetShop as my shopping cart service and will be using PayPal as my payment gateway. Any and all help is more than greatly appreciated. Thank you sooooo much! :o) Here's what I would do... they appear to have an export function. So manually enter a product, and export it. You should now have a "template" for yourself. ************ Hope it helps! Anne Troy www....

Cumulative totals from individual records
I have labor data for employees given by hours worked on a given day. Eg. Tom, 5-hrs, 5-Jan-2001 What I need is cumulative hours worked by month: E.g. Tom, 100-hours-to-date, Jan-2001 How can I use Access to generate cumulative-hours-to-date by month from hours-worked-on-given-days? Note: It’s something I could get from Excel Pivot table, but I have well over 60,000 records (in the millions). -- Richard Use a totals query to get the totals for a period (e.g. a month.) Then use a subquery to get the progressive total. The Year-to-date example should illustrate how: ...

Step out of loop when a condition is met
I have the following code as part of validatation in a form's Before Update event: Dim ctl As Control, blnNoVal as Boolean For Each ctl In Me.Controls If IsNull(ctl) And ctl.Tag = "R" Then blnNoVal = True Else blnNoVal = False End If Next ctl If blnNoVal = True Then Exit Sub Else ' perform validation End If Five controls in one section of the form have "R" as the tag. If these controls are also null, it means that the section of the form was not started, which is OK. In that case the rest of the validation c...

Where are Rules stored created in 'Organise'?
I'm using Outlook 2000. Whilst in the Inbox, having received an email I want to filter out, I use the Organise/Using Folders and send the umwanted email on to the delete Items folder. This seems to be working fine - however how would I delete this 'rule' that I have created with organise? I have checked the Rules wizrd but the rules created with organise does not reside there. Are they located somewhere else? Thanks in advance for any help. ...