My "INSERT INTO" code doesn't work when earlier posts exists.

[Fredrik831]

Hi!



I want to create a form where the user selects a value from a combobox, press a command button and the action would then be registered with the value (from the combobox) and the current time in a seperate table. I'm using VBA code that's triggered when i press the command button.

My target table consists of: FixturID, Tidpunkt, DatumID (autonumber and primary key).

I understand that I shall not include the autonumber field in the VBA code for my autonumber to generate a value successfully. My code works as it should, as long as the FixturID is unique. As soon as I select a value that is already registered, then Access doesn't register a new post.

Does somebody know what's wrong? FixturID is a foreign key, but that shouldn't interfere?

Here is my code:
Private Sub Commandbutton_Click()
CurrentDb.Execute "INSERT INTO Datum (FixturID, Tidpunkt) VALUES (" & Fixid.Value & ", #" & (Text5.Value) & "#)"
End Sub

So, a summarized version of my question: How can I get access to register a new post even if an earlier post with the FixturID exists?


Thanks in advance!

[orange]

I'm not sure I'm following your details, but it seems you may have a unique index on FixturId in your target table - or -
FixturId (the fk) doesn't exist in the other table.

You may need a Unique composite index on FixturId + Tidpunkt to prevent duplicates in you Target table.

You may also have to use DateValue(Text5.Value) or CDate(Text5.Value) to get the conversion from string/text to date???

As a standard practice you should put a
Debug.Print "INSERT INTO Datum (FixturID, Tidpunkt) VALUES (" & Fixid.Value & ", #" & (Text5.Value) & "#)"

before your Currentdb.execute line and comment out the Currentdb.execute to see what the SQL looks like to ensure the SQL is OK.

[Fredrik831]

Thank you, it seems Access somehow had a unique index on FixturID. I removed the referential integrity, and after that it all worked. But, then I added the referential integrity again and it still worked. I'm glad, but confused

I inserted the Debug.Print statement and this was inserted in the immediate window:
INSERT INTO Datum (FixturID, Tidpunkt) VALUES (3, #2012-12-04 07:50:32#)

Thanks again!