But I only wanted one

nhcaver

Registered User.
Local time
Today, 16:27
Joined
Sep 12, 2009
Messages
17
I have a form that runs an append query in addition to saving the record (the appended information gets archived in a separate table called tblTracking where I collect the changes that have been made over time to that record). In my main table I have in the neighbourhood of 2,800 some odd records. Every time however someone hits the save button, it appends all of the records every time (so, I started with 2,800 records, a change gets made to a record, I end up with the query adding an additional 2,800 records to the previous ones (so, the tblTracking grows 2,800 records every time). When someone hits Save and the query is run, I only want to save the new record, or the record that is being updated. Any suggestions on how to apprach this?
 
Have you got a filter condition on your append query that updates the tracking table?
 
Here is the SQL:

INSERT INTO tblTracking ( AppID, AppLName, AppFName, tblMain.criteria1, third party, ActionDate, Notes, empID, empName )
SELECT TblMain.AppID, TblMain.AppLName, TblMain.AppFName, TblMain. criteria1, TblMain.thirdparty, TblMain.ActionDate, TblMain.Notes, TblMain.empID, TblMain.empName
FROM TblMain
WHERE (((TblMain.AppID)=[Forms]![frmMain]![AppID]) AND ((TblMain.AppLName)=[Forms]![frmMain]![AppLName]) AND ((TblMain.AppFName)=[Forms]![frmMain]![AppFName]) AND ((TblMain. criteria1)=[forms]![frmMain]![ criteria1]) AND ((TblMain. thirdparty)=[forms]![frmMain]![ thirdparty]) AND ((TblMain.ActionDate)=[forms]![frmMain]![ActionDate]) AND ((TblMain.Notes)=[forms]![frmMain]![Notes]) AND ((TblMain.empID)=[forms]![frmMain]![empID]) AND ((TblMain.empName)=[forms]![frmMain]![empName]))
 
Trying a different approach...got ALOT closer...no syntax issues this time...now tis just pointing the recordset to the correct objects...
 
I finally found the answer to one of the problems, it had to be done through VBA. I learned that my form had to be unbound and then I needed to be able to create the recordset from the objects in the form, and then use .Update to get the information to go into the table that I opened. My form has to be unbound since the recordset is going into two tables, tblMain and tblTracking. Since I wanted to do these two functions in one easy click, I couldn't do it through the "automatic" queries using the query designer. So, my first step is done, generating the record from the form to put in the table. The second step is to do the same thing for tblTracking.

If anyone is interestd in seeing the code (in case someone else has the same issues) here you go. I am sure there is probably a simpler and cleaner way to do this, but this is what I did. The empName field is based off of a function that retireves the user name from logon, and the ActionDate is =Date$(), so as far as the VBA is concerned, it is read only (and doesn't need to be cleared in the end):

Private Sub Command35_Click()

Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstApplication As ADODB.Recordset

'Check that all required fields are filled in

txtAppID.SetFocus
If txtAppID.Text = "" Then
err = err + 1

MsgBox "Please enter Application Number!" & err

End If

txtAppLName.SetFocus
If txtAppLName.Text = "" Then

err = err + 1

MsgBox "Please enter Applicant's last name!"

End If

txtAppFName.SetFocus
If txtAppFName.Text = "" Then
err = err + 1
MsgBox "Please enter Applicant's first name!"

End If

txtHoldType.SetFocus
If txtCriteria1.Text = "" Then
err = err + 1

MsgBox "Please enter the Criteria Type!"

End If

'if no errors insert data

If err < 1 Then
' Open a connection to tblMain,

'this is the table where active records are stored

Set cnn1 = New ADODB.Connection
Set cnn1 = CurrentProject.Connection

' Open tblMain table

Set rstApplication = New ADODB.Recordset
rstApplication.CursorType = adOpenKeyset
rstApplication.LockType = adLockOptimistic

rstApplication.Open "tblMain", cnn1, , , adCmdTable

'Get the new record data and add it to tblMain

rstApplication.AddNew

rstApplication!AppID = txtAppID
rstApplication!AppLName = txtAppLName
rstApplication!AppFName = txtAppFName
rstApplication!Criteria1 = txtCriteria1
rstApplication!ActionDate = txtActionDate
rstApplication!ThirdParty = txtThirdParty
rstApplication!Notes = txtNotes
rstApplication!empID = txtempID
rstApplication!empName = txtempName

rstApplication.Update

' Show the newly added data.

MsgBox "New Application: " & rstApplication!AppID & " has been successfully added"





'close connections

rstApplication.Close
cnn1.Close

Else

MsgBox "An Error has occurred, please check and try again"

End If

txtAppID.SetFocus
txtAppID = ""
txtAppLName.SetFocus
txtAppLName = ""
txtAppFName.SetFocus
txtAppFName.Text = ""
txtCriteria1.SetFocus
txtCriteria1.Text = ""
txtThirdParty.SetFocus
txtThirdParty.Text = ""
txtNotes.SetFocus
txtNotes.Text = ""
txtempID.SetFocus
txtempID.Text = ""

End Sub
 

Users who are viewing this thread

Back
Top Bottom