Strange Insert...Into behavior...

[SMAirlines]

First off, a bit of background. I have a form (frm_project) that has a subform (frm_ProjectContacts) which lists all of the contacts for a particular project. On the main form, there is a button to modify the contacts for this particular project. I am using an insert...into query to add a new record, but for some reason, whenever the form is closed, it not only inserts the correct record, but also changes the value of an existing record to the same thing - in essence - removing one of the correct contacts, and duplicating the new addition. In testing, I have found that the SQL statement seems to be adding it correctly, but when the form closes, it updates the other record. I have tried using both a SQL statement, and recordset update, and both have the same effect. I am baffled????

Private Sub Add_Click()

Dim strSQL As String
Dim ProjID As Integer
Dim addID As Integer

ProjID = Forms![frm_Project].[projectID]
addID = Forms![frm_ProjectContactAdd].[contactID]

strSQL = "INSERT INTO ProjectContacts ( ContactID, ProjectID )" & _
" SELECT " & addID & ", " & ProjID & ";"

DoCmd.RunSQL (strSQL)

DoCmd.Close acForm, "frm_ProjectContactAdd", acSaveNo
DoCmd.Requery "frm_ProjectContacts"

End Sub

It seems so simple...but I am baffled. The close event has nothing associated with it, so I really have no idea why this is happenning. Any ideas would be appreciated.

Rick

 

[SMAirlines]

Success!!! I found out what the problem was. I've got the forms all set to Modal and Popup as I am hiding the Access (application) window. Basically, this form wasn't opening up to a new record, so the one that was getting changed was the existing one, in addition to the one it added (a VB function of the button itself). Anyway...just thought I'd keep everyone updated as it was driving me nuts-o.