vba recordset loop through records wizard

#VBA RECORDSET LOOP THROUGH RECORDS WIZARD HOW TO#
#VBA RECORDSET LOOP THROUGH RECORDS WIZARD CODE#

'Now that we've found the site from the previous audit and copied its attachments into the new record we can close the old recordset and move onto the next site in the current audit. RstNewAttachments.Fields('FileType').Value = rstOldAttachments.Fields('FileType').Value RstNewAttachments.Fields('FileName').Value = rstOldAttachments.Fields('FileName').Value RstNewAttachments.Fields('FileData').Value = rstOldAttachments.Fields('FileData').Value Set rstNewAttachments = rstNew.Fields('Attachments').Value Set rstOldAttachments = rstOld.Fields('Attachments').Value 'This next block should loop through the attachments (if any) in the old record and copy them into the new record. Working with attachments is annoying though. 'Now it's just a matter of copying the attachments from the old record into the new one. If strCurrentSiteName = strOldSiteName Then 'If this is true, then we've found a record from the previous audit that matches the one from our current audit. StrOldSiteName = rstOld.Fields('SiteName').Value StrOldSQL = 'SELECT tblAuditForms.SiteName, tblAuditForms.Attachments, Year() FROM tblAuditForms WHERE Year() = ' & Me.cboPreviousDate & ' ORDER BY tblAuditForms.SiteName 'ĭo While Not rstOld.EOF 'Loop through each of the records from the previous audit until we find a record that matches the current site name. 'Now let's open a recordset that contains all records from the previous audit. We'll use this to compare with the sites in the previous audit. StrCurrentSiteName = rstNew.Fields('SiteName').Value 'Get the name of the site for the current record that we're on. StrNewSQL = 'SELECT tblAuditForms.SiteName, tblAuditForms.Attachments, tblAuditForms.AuditYear FROM tblAuditForms WHERE AuditYear = #' & Format(cboMyDate, 'mm/dd/yyyy') & '# ORDER By tblAuditForms.SiteName 'ĭo While Not rstNew.EOF 'Now we need to loop through these records. 'First let's open a recordset that contains all of the records from this year.

#VBA RECORDSET LOOP THROUGH RECORDS WIZARD CODE#

Here's my code so far: Dim db As Database After that it no longer work for any subsequent records. The weird thing is that it'll work correctly on the first first record in rstNew that it finds a match. From there I just need to copy all the attachments from the record in rstOld to the record in rstNew. The second loop will find a record in rstOld with a matching Name field. For each record in rstNew I'll gather the value of the Name field and then start a second loop. To accomplish this I start a loop through each record in rstNew. The second recordset (rstNew) contains records with a date value from this year and those records do not contain any attachments. The first recordset (rstOld) contains records with a date value from last year and those records could contain any number of attachments. Both recordsets are pulling data from the same table.

I'm attempting to copy all of the attachment files contained in records from one dao recordset into corresponding records in another dao recordset. Either too many loops or opening/closing recordsets in the wrong order. I think my problem is just a simple programming structure issue. I'm thinking I really just need another set of eyes on this. The function is called in an earlier subroutine in Access.I've almost got this working. Mat = CDate(rsCurve.Fields('MaturityDate')) VZeros = CVar(rsCurve.Fields('CurveInterpolateRecordset')) Here is what I have so far: Function EWMA(rsCurve As Recordset, InterpRate As Double, Lambda As Double) As Double

#VBA RECORDSET LOOP THROUGH RECORDS WIZARD HOW TO#

I'm not exactly sure how to convert the code over, though. The recordset has the same fields as the Excel spreadsheet. I want to re-create this in Access VBA using recordset objects. The main feature enabling the function to work is the For loop. M = Month(MaturityDate) - Month(MarkDate) The code is: Function EWMA(Zeros As Range, Lambda As Double, MarkDate As Date, MaturityDate As Date) As Doubleĭim LogRtn As Double, RtnSQ As Double, WT As Double, WtdRtn As Double

It takes as inputs a column of numbers (Zeros) and two dates. I have a simple function using Excel VBA for calculating volatility.

Vba recordset loop through records wizard