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

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.