inserting into MySQL causes a row to be updated

[galorin]

I have a form that adds a person to a company. After filling out the information, the hard-coded query shown below is run

INSERT INTO people ( Company, [First Name], [Last Name], Profession, [Direct Dial], Mobile, Email, Fax, AltAdd1, AltAdd2, AltAdd3, AltTown, AltCty, AltPcd, AltCtr )
SELECT Forms!AddPer.Cmp, Forms!AddPer.FN, Forms!AddPer.LN, Forms!AddPer.Prf, Forms!AddPer.DD, Forms!AddPer.Mobile, Forms!AddPer.Email, Forms!AddPer.Fax, Forms!AddPer.AltAdd1, Forms!AddPer.ALtAdd2, Forms!AddPer.AltAdd3, Forms!AddPer.AltTown, Forms!AddPer.AltPcd, Forms!AddPer.AltCty, Forms!AddPer.AltCtr;

In addition to adding the new contact in the right place, the very first listing in the database (as far as Access is concerned) gets updated, to have the same company. So far we're only testing, but I can't have Access willy-nilly changing the place of employment of someone every time a new company is added to the Database. I can't disallow updates on the table in case information, like phone number or email address changes.

 

[galorin]

As a follow-up the problems are within Access, not MySQL. I have duplicated the stepswithin both MySQL Query Browser and on the MySQL CLI. Both work as they should, so that just leaves Access/ODBC

 

[jordanking]

are you using an ODBC connection?

are you passing the query as a string to Mysql,

if so, what is the debug.print sqlVaraible value just before passing it to the Mysql connection.

need more info


.....
I'd rather be surfing

 

[JoeAtWork]

the very first listing in the database (as far as Access is concerned) gets updated

What is the name of the table that this "listing" belongs to?

I have a hunch that the form is bound to this table. If that is the case, I suggest you make the form unbound (by setting it's RecordSource property to nothing). Or perhaps just unbind the control that has the company name (by clearing the ControlSource property of the control).

 

[galorin]

I have yet to figure out why, but the problem has been resolved. I deleted the form, and a second, similar form and replaced it with a new form.

This new form takes the company name as a global variable, which can be set from several places within the grand scheme of things.

I have also built up a VBA script that looks like this:

Private Sub AddPerson_Click()
Dim Data(14) As String
Dim StrSQL As String
Dim X As Integer

Data(1) = Nz(Forms!AddPerson.FirstName.Value, "")
Data(2) = Nz(Forms!AddPerson.LastName.Value, "")
Data(3) = Nz(Forms!AddPerson.Prf.Value, "")
Data(4) = Nz(Forms!AddPerson.DD.Value, "")
Data(5) = Nz(Forms!AddPerson.Mobile.Value, "")
Data(6) = Nz(Forms!AddPerson.email.Value, "")
Data(7) = Nz(Forms!AddPerson.Fax.Value, "")
Data(8) = Nz(Forms!AddPerson.Add1.Value, "")
Data(9) = Nz(Forms!AddPerson.Add2.Value, "")
Data(10) = Nz(Forms!AddPerson.Add3.Value, "")
Data(11) = Nz(Forms!AddPerson.City.Value, "")
Data(12) = Nz(Forms!AddPerson.Cty.Value, "")
Data(13) = Nz(Forms!AddPerson.Pcd.Value, "")
Data(14) = Nz(Forms!AddPerson.Ctry.Value, "")

StrSQL = "INSERT INTO people (Company, `First Name`, `Last Name`, Profession, "
StrSQL = StrSQL & "`Direct Dial`, Mobile, Email, Fax, "
StrSQL = StrSQL & "AltAdd1, AltAdd2, AltAdd3, AltTown, AltCty, AltPcd, AltCtr) "
StrSQL = StrSQL & "VALUES ('" & CompName
For X = 1 To 14
 StrSQL = StrSQL & "', '" & Data(X)
Next X
StrSQL = StrSQL & "');"

DoCmd.RunSQL StrSQL
If IsLoaded("People") Then
Forms!People.CntName.Requery
End If

If IsLoaded("AddCmp") Then
DoCmd.Close acForm, "AddCmp", acSaveNo
End If

DoCmd.Close acForm, "AddPerson", acSaveNo

End Sub

 

[JoeAtWork]

Your solution is consistent with my theory that your original form was bound to the table that got updated.