a query that sometimes works and sometimes doesn't

[pawelch]

Hi all,

Could you please tell me why this querry sometimes works and sometimes does not ?

SQL_Text = "INSERT INTO [CompanyContacts]([CompanyId],[PersonId],[ContactTypeId]) " & _
               "VALUES (" & [Forms]![formCompany]!CompanyID & "," & [Forms]![formCompanyAllContacts]!PersonID & "," & [Forms]![formCompanyAllContacts]!ContactType.Value & ") "
'SQL_Text = "INSERT INTO CompanyContacts(CompanyId,PersonId,ContactTypeId) " & _
               "VALUES (1,2,1)"

 db.Execute (SQL_Text)
Set db = Nothing

I really do not know what happens and what goes wrong

thank you all

 

[RuralGuy]

Change this line:
db.Execute (SQL_Text)
...to...
db.Execute (SQL_Text, dbFailOnError)
...end then you will know! It sounds like something is data sensitive.

 

[vbaInet]

By the way you didn't really explain how it doesn't work. Does it throw an error sometimes or it just doesn't Insert?

Try this:

SQL_Text = "INSERT INTO [CompanyContacts] ([CompanyId],[PersonId],[ContactTypeId]) " & _
           "VALUES ([Forms]![formCompany]![[B][COLOR=Red]txt[/COLOR][/B]CompanyID], [Forms]![formCompanyAllContacts]![[COLOR=Red][B]txt[/B][/COLOR]PersonID], [Forms]![formCompanyAllContacts]![[COLOR=Red][B]txt[/B][/COLOR]ContactType])"

Note two things - the references are now enclosed in the quotes (so there's no concatenation there) and the highlights indicate that you should refer to the control instead.

 

[pawelch]

Hi all,

you are right, maybe I should have explained it a bit better. So there is a table that stores company contacts. What I want to insert into it is a row of keys that will be composed of: the company id number, then person id, and a type of contact. By "sometimes works sometimes does not" I meant that occasionally it inserts data into that table and sometimes it does not and it never reports any errors.

The values that are sent with SQL seem to be correct given what I observe in a debugging window.

I have changed the fieldnames so that there are different with the database column namse as vbaInet suggested, however the problem still prevails.

 

[pawelch]

UPDATE:

I added

db.Execute SQL_Text, dbFailOnError

as suggested by RuralGuy and it helped me to understand what really happened. So I need to give a more detailed description.

When I add a person to the database its details are stored in a table Person. Then there are options to assign it straight away to the table CompanyContacts, since the company have been created. The problem now is the following :

I don't know how to put it in words in a simple way, but sometimes the fields from Person form are not inserted into the Person table on time. By on time I mean before I try to insert data into CompanyContacts. Thus there is a clash. It is because since the person details are in the form, but have not been inserted to the table - they cannot be physically imported to the other one - because they do not exist.

I tried to insert for a person manually at first (via insert into...) but that gives me an error :

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship

Thank you for any suggestions

 

[pawelch]

I have added refresh method use for that form and it looks like it is working. I should thank RuralGuy whose post from I think 2005 helped me. I found it on the other forum

In general because of Referential Integrity - the form had had to be refreshed first, before the imputed data were sent to another table.

Thank you for your patience and help

 

[vbaInet]

Rather than a Refresh it would be better to simply explicitly save the current record before running the code:

if me.dirty then
    docmd.runcommand accmdsaverecord
end if
 
SQL_Text = "SELECT ..."
... and so on ...

 

[pawelch]

that is even a better solution.
thank you every one for your commitment and responses.