Unanswered: records overwritten issue & acess values of the fields issue
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.
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
If blnInvalid Then
rs2!Invalid = True
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.