0

I'm trying to realise a simple sign up structure, and I am really new in VBA and Access. At the instruction INSERT TO it should add a record to the table USER. but it adds 2 identical records that have just a different userID, generated by the Automatic Numeration. Then I want to copy the last generated UserID in global variable valID, so that I can insert this UserID in a another table. What am I doing wrong?

DoCmd.RunSQL ("INSERT INTO User ([Type], [FiscalCode], [Name], [Surname], [Birthdate], [Gender], [phoneNumber], [email], [country], [city], [postalCode], [street], [houseNumber])VALUES ('" & valtype & "', '" & valfis & "', '" & valname & "', '" & valsur & "', '" & valdata & "', '" & valgen & "', '" & valphone & "', '" & valemail & "','" & valcountry & "', '" & valcity & "', '" & valcode & "', '" & valstreet & "', " & valnum & ")")

valID = DLookup("[UserID]", "User", "[FiscalCode] = '" & valfis & "'")

after this code I want to do another instruction of the type insert to in a different table to insert in this new table as primary key the UserId I just obtained. this is the only other INSERT INTO inside the code.

katy
  • 1
  • 2
  • 1
    Are you sure one of the records didnt already exist? There is nothing about that SQL that could create more than one record. – braX May 22 '20 at 13:56
  • Also: https://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – braX May 22 '20 at 13:57
  • This statement adds only 1 row.If you see 2 new rows then either the one already existed or you executed the statement twice. – forpas May 22 '20 at 13:57
  • I'm doing this instruction on a void table, so no record already exists. I really can't understand what's the problem – katy May 22 '20 at 13:57
  • 2
    How is this code being executed? You might literally be calling the `DoCmd.RunSQL ("INSERT...` statement twice somehow. – jamheadart May 22 '20 at 13:58
  • There's not enough detail included here to sort out the record duplication issue. As noted by others, that code won't insert multiple rows by itself. – Eric Brandt May 22 '20 at 13:59
  • In other words, please include the code around it in your question as well. – braX May 22 '20 at 13:59
  • I added some more details, but the code is really simple and should be perform a simple task. – katy May 22 '20 at 14:05
  • Why would UserID be a primary key in another table? This then is a one-to-one relationship. Why not just have one table? – June7 May 22 '20 at 17:09

1 Answers1

1

For this to happen, most likely form is bound to table and controls are bound to fields so user is inputting data into controls and then code runs INSERT as well thereby creating two records of same data.

A record is committed to table when one of the following happens:

1) table or query or bound form is closed

2) move to another record

3) run code to save

Remove the code.

Use form/subform arrangement for inputting parent record as well as dependent record in 'another' table.

June7
  • 19,874
  • 8
  • 24
  • 34