program effiency

[ariel81]

hi,

whenever i key in some data into a form i will transfer the data into a table.
whenever i want to change some data in the form i will delete all the records in the table and re-add all the data from form into the table.

the abv method works. however, i find that it is not effiencent because it every time delete all the records and re-add in records.

how shall i code the program to make it only edit/delete the specific records only instead bulk delete and bulk add?

refer to (program.zip)

Private Sub cmdSaveFltQpi_Click()

   Dim Db As DAO.Database
   Dim rs As DAO.Recordset
   Dim sql As String
   Dim f As Form
   Dim i As Integer
   Dim k As String


   Set f = Forms!frmFltQpi
   Set Db = CurrentDb()


   Call DeleteRecords      'delete all the previous records

   sql = "SELECT * FROM [tblFltQpiMonthly] Where tblFltYear = " & f.cboFltQpiYear
  
   For i = 1 To 12          'transfer the data from form to table
   Set rs = Db.OpenRecordset(sql)
      
     If rs.RecordCount >= 0 Then
            
            If IsNull(f("txtTotalJobs" & i) Or f("txtTotalRecurring" & i) Or f("txtTotalAirWC" & i) Or f("txtTotalAF" & i)) = False Then
            
            rs.AddNew

            rs![tblFltYear] = f.cboFltQpiYear
            rs![tblFltMonth] = i

            rs![tblFltTotalJobs] = f("txtTotalJobs" & i)
            rs![tblFltTotalRecurring] = f("txtTotalRecurring" & i)
            rs![tblFltTotalAirtestWC] = f("txtTotalAirWC" & i)
            rs![tblFltTotalAirtestFailure] = f("txtTotalAF" & i)

            rs.Update
            
            End If
     End If
              
          Next i


Db.Close

End Sub

Private Sub DeleteRecords()

  Dim sql As String

    sql = "DELETE * FROM [tblFltQpiMonthly] Where tblFltYear = " & Me.cboFltQpiYear
        
    DoCmd.SetWarnings False
    DoCmd.RunSQL sql

End Sub

 

[Wiz47]

hi,

whenever i key in some data into a form i will transfer the data into a table.
whenever i want to change some data in the form i will delete all the records in the table and re-add all the data from form into the table.

the abv method works. however, i find that it is not effiencent because it every time delete all the records and re-add in records.

how shall i code the program to make it only edit/delete the specific records only instead bulk delete and bulk add?

refer to (program.zip)

From what I saw, you have a form that has every field as unbound. The design of your table shows only four or five fields, and two primary keys.

Personally, I'd start the table again from scratch and create a field for each unbound field in the form like:

Job_ID (Primary Key)
Total_jobs_Jan (Number) integer
Total_Jobs_Feb (number) integer
Total_Jobs_Mar (number) integer

and so on through December.

Then

Rcurring_Faults_Jan (number) integer
Rcurring_Faults_Mar

and so on through December

Then

Airtst_Jan (number) integer
Airtst_Feb

and so on through December

Get the drift?

Once you have defined all the fields, assign them as the data source for each matching textbox in the form. Then you can create a separate record for each year and use the combobox to recall that year of data from the table. This would also allow you to edit any year at will without having to delete the entire table each time you want to change something.