Results 1 to 6 of 6
  1. #1
    akrylik is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    47

    writing data from recordset to table


    I have a database where we take records from a table and create two recordsets. The purpose of this is to compare records from the table where the ID field is equal to the ID we are looking at. Using the two recordsets, we use one of the record sets and go through it, one by one, and compare it to all the records in the second recordset. We have the comparisons working, but we change values in the first record set. My question is, how do we change the values in the actual table based on the changes made in the recordset? Do we need to create a loop to go through the table and change the values where the ID field = the ones in the recordset or is there an easier way to do it?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show the related code (SQL or vba).

  3. #3
    akrylik is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    47
    here is the entire code for the on click event for a button:

    'creates a new recordset w/ all records from EmployeeData table
    Set rs1 = CurrentDb.OpenRecordset("EmployeeData", dbOpenDynaset)
    If Not rs1.BOF And Not rs1.EOF Then
    Do While Not rs1.EOF 'D0 runs the code below until it reaches the end of the recordset
    'creates a new recordset w/ records from ResourceAllocation table
    'where HRID(RA) = HRID(ED) and project lies within the month
    query = "SELECT ResourceAllocation.* FROM ResourceAllocation WHERE (((ResourceAllocation.[HRID]) = '" & rs1.Fields("HRID") & "') AND ((ResourceAllocation.[Resource Start Date])<#1/31/2012#) AND ((ResourceAllocation.[Resource End Date])>#1/1/2012#))"
    counter = 0
    Set rs2 = CurrentDb.OpenRecordset(query, dbOpenDynaset)
    Do Until rs2.EOF
    counter = counter + 1
    rs2.MoveNext
    Loop
    Debug.Print "Counter = "; counter
    If counter = 1 Then 'If(A)
    rs2.MoveFirst
    With rs2
    .Edit
    !Headcount = "True"
    End With
    Debug.Print "Headcount = "; rs2(12).Value
    rs1.MoveNext
    ElseIf counter > 1 Then 'ElseIf(A)
    rs2.MoveFirst
    If Not rs2.BOF And Not rs2.EOF Then 'If(B)
    Do While Not rs2.EOF 'Loop(A)
    Debug.Print "Counter is greater than 1"
    Set rs3 = rs2 'uses the 2nd records set to create a 3rd recordset to be used to compare each record to determine headcount
    If Not rs3.BOF And Not rs3.EOF Then 'If(C)
    Do While Not rs3.EOF 'D2 run the following code until it reaches the end of the 3rd record set
    Debug.Print "Loop D2 is running"
    'creates null if statement to skip an equal record
    If rs2(0) = rs3(0) Then 'If2
    rs3.MoveNext 'moves to the next recordset if both records from the 2nd and 3rd recordset are the same record
    Debug.Print "rs2 = rs3"
    'if a record has already been compared, skip it
    ElseIf rs3(0) < rs2(0) Then 'ElseIf2 - 1
    rs3.MoveNext
    ElseIf rs3(0) >= rs2(0) Then 'ElseIf2 - 2
    'calculate headcount
    If rs2(8) = rs3(8) Then 'If3 if the percentages are equal, move to next if statement
    If rs2(6) = rs3(6) Then 'If4 if the start dates for both records are the same, compare the end dates
    If rs2(7) = rs3(7) Then 'If5 if the end dates are equal, set headcount to undefined
    rs2(12) = "Undefined"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    rs3.MoveNext
    ElseIf rs2(7) > rs3(7) Then 'ElseIf5-1 if the end date of rs2 is after the end date of rs3, set headcount to true
    rs2(12) = "True"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    ElseIf rs2(7) < rs3(7) Then 'ElseIf5-2 if the end date of rs2 is before the end date of rs3, set headcount to false
    rs2(12) = "False"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    rs3.MoveNext
    End If 'EndIf5
    ElseIf rs2(6) > rs3(6) Then 'ElseIf4-1 if the end date of rs2 is after the end date of rs3, set headcount to true
    rs2(12) = "True"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    ElseIf rs2(6) < rs3(6) Then 'ElseIf4-2 if the start date of rs2 is before the end date of rs3, set headcount to false
    rs2(12) = "False"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    rs3.MoveNext
    End If 'EndIf4
    ElseIf rs2(8) > rs3(8) Then 'ElseIf3-1
    rs2(12) = "True"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    ElseIf rs2(8) < rs3(8) Then 'ElseIf3-2
    rs2(12) = "False"
    Debug.Print rs2(12).Value
    'Debug.Print "Headcount for "; rs2(5).Value; " is "; rs2(12).Value
    rs3.MoveNext
    End If 'end if 3
    End If ' end if 2
    Loop 'D2 loop
    End If 'EndIf(C)
    Debug.Print "Loop A still running"
    If rs3.EOF Then
    rs3.Close
    Exit Do
    End If
    rs2.MoveNext
    If rs2.EOF Then
    rs2.Close
    Exit Do
    End If
    Loop 'EndLoop(A)
    End If 'EndIf(B)
    rs1.MoveNext
    Else
    rs1.MoveNext
    End If 'EndIf(A)
    Debug.Print "End of D0"
    If rs1.EOF Then Exit Do
    Loop 'Do loop
    End If
    rs1.Close

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The code hasn't really cleared things for me. You should use tags when posting vba, since it will preserve any indenting and structure.
    I'm not clear on what is happening in the loops and the If/else statements,nor the third recordset.

    Perhaps you could tell us in plain English how you determine HeadCount.

    It seems generally you have an EmployeeData table and a ResourceAllocation table and are doing manipulations to determine if the HRID has been available for the month of Jan 2012. It would appear, but not knowing your table structures, that it might be possible to do this with some SQL.

  5. #5
    akrylik is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    47
    We got it working. Sorry for not updating the thread. We just did a with statement and use the current record and did a record.update and it worked beautifully.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Writing ASP into a Data Access Page
    By nellb13 in forum Programming
    Replies: 0
    Last Post: 07-19-2010, 12:23 PM
  2. Replies: 2
    Last Post: 06-20-2010, 06:54 PM
  3. Writing data from textbox to table
    By mll in forum Forms
    Replies: 4
    Last Post: 03-10-2010, 05:10 PM
  4. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  5. Automatically Writing SQL From A Table
    By smitstev in forum Programming
    Replies: 1
    Last Post: 06-05-2009, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums