Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006

    Unanswered: records overwritten issue & acess values of the fields issue

    hi folks,

    Well, in fact i am using the properties of the recordSet(rs.MoveFirst). What i want to achieve is to be able to acess the value of the fields of the table. But nt too sure as hw this can be acheived. What i knew so far are rs.Field(index), set rs = Database.OpenRecordSet("tableName"), loop through the recordSet, rs.MoveFirst.

    First encounter(Overwritten issue)

    I have a few records in my table. SerialCode is my pk in the table. My form interface is (Combo Box with predefined values, Textboxes. Once i click on the combol box, my textbox wil show the corresponding values that matches that of the combo box. By right when i inserted the values from the textboxes, combo box, the current insertion shouldnt be overwritten by the next insertion.

    2nd encounter( Acess the values of the field and compute the balance based on the selected fields)

    Next, I want to compute the balance(For instance under my table there are these few fields(SerialCode(pk) ,TotalParticpants(field1), Withdrwal(field2), Balance(field(3)). I shld be able to make use of the recordset to acess that particular values of the fields and based on the fields values to compute the balance and update the balance to my table using an sql stat(select, update). Let say under my table field(1) = 500, field(2) = 300 the sql stat shld be able to compute the balance based on the result of the selected fields and update the balance in the table.

    All this I am using MS Acess(frontend) , sql Server (backend) nt too sure whether i need to open the connection, set connection string. perhaps u can guide me along as hw recordSet can be used with some code snippets for better understanding coz i am still fresh in this environment. thks for your kind assistance.

  2. #2
    Join Date
    Dec 2006
    Here's an example from the project I'm working on:
        Dim db As DAO.Database
        Dim rs1 As DAO.Recordset
        Dim rs2 As DAO.Recordset
        Dim strSQL1 As String
        Dim strSQL2 As String
        Dim blnInvalid As Boolean
        strSQL1 = "SELECT survey.SurveyID, survey.Status FROM survey ORDER BY survey.SurveyID;"
        strSQL2 = "SELECT ImportedData.SurveyID, ImportedData.Received, ImportedData.Invalid FROM ImportedData;"
        blnInvalid = True
        Set db = CurrentDb()
        Set rs1 = db.OpenRecordset(strSQL1)
        Set rs2 = db.OpenRecordset(strSQL2)
        Do Until rs2.EOF
            Do Until rs1.EOF
                If rs1!SurveyID = rs2!SurveyID Then
                    blnInvalid = False
                    If rs1!Status > 90 Then
                        rs2!Received = True
                        Exit Do
                    End If
                End If
            If blnInvalid Then
                rs2!Invalid = True
            End If
    In this line "If rs1!SurveyID = rs2!SurveyID Then" and this line "If rs1!Status > 90 Then" you see me accessing the value of a field in the current record. In this piece of code:
    rs2!Invalid = True
    you see me updating the value of a field for the current record.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts