SQL problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following SQL commands run by a command button on a form. What i'm trying to do is add a user to my database in two different tables. The first table, Candidates, has personal data. The second table, Web, has CID (should be copied from Candidates table), UserName, and Password

In the Candidates table, CID is autogenerated. The first SQL command does this properly. What I'm having trouble with is inserting into the web table. The web table has a primary key of CID, but it's just a number field. When I run these commands, i get a primary key violation for the second SQL command

Ideally, the second SQL command should do this

Assume CID in Candidates is 9999, then Web should hold the values 9999, c9999, 9iO0REab (or whatever random password it comes up with

AddC just simply takes the CID and puts a "c" in front of it. RandomPassword generates a random password

Dim SQL As Strin
SQL = "Insert into candidates (firstname, middlename, lastname, suffix, street, city, state, zip, homephone, cell, email, coname, costreet, cocity, costate, cozip, cophone, fax, school1, dategraduated1, degree1, major1, school2, dategraduated2, degree2, major2, memo) values (txtFirstName, txtMiddleName, txtLastName, txtSuffix, txtStreet, txtCity, txtState, txtZIP, txtPhone, txtCell, txtEmail, txtCoName, txtCoStreet, txtCocity, txtCoState, txtCoZIP, txtCoPhone, txtFax, txtSchool1, txtGrad1, txtDegree1, txtMajor1, txtSchool2, txtGrad2, txtDegree2, txtMajor2, txtMemo);
DoCmd.RunSQL SQ

Dim sql2 As Strin
sql2 = "insert into web (CID, UserName, Password) values ([CID], AddC([CID]), RandomPassword());
DoCmd.RunSQL sql2
 
Assume CID in Candidates is 9999, then Web should hold the values 9999,
c9999, 9iO0REab (or whatever random password it comes up with)
AddC just simply takes the CID and puts a "c" in front of it.
Click to expand...
RandomPassword generates a random password.
Dim SQL As String
SQL = "Insert into candidates (firstname, middlename, lastname, suffix,
Click to expand...
street, city, state, zip, homephone, cell, email, coname, costreet, cocity,
costate, cozip, cophone, fax, school1, dategraduated1, degree1, major1,
school2, dategraduated2, degree2, major2, memo) values (txtFirstName,
txtMiddleName, txtLastName, txtSuffix, txtStreet, txtCity, txtState, txtZIP,
txtPhone, txtCell, txtEmail, txtCoName, txtCoStreet, txtCocity, txtCoState,
txtCoZIP, txtCoPhone, txtFax, txtSchool1, txtGrad1, txtDegree1, txtMajor1,
txtSchool2, txtGrad2, txtDegree2, txtMajor2, txtMemo);"
DoCmd.RunSQL SQL

Dim sql2 As String
sql2 = "insert into web (CID, UserName, Password) values ([CID],
Click to expand...
AddC([CID]), RandomPassword());"
DoCmd.RunSQL sql2
Click to expand...

I'm not sure where you take the value of [CID] from.
After you insert the row in candidates you have to read from this new row to
obtain the value generated by Access.
Stop at the last line of your code and look at the value of sql2. Does [CID]
correspond to the value in your
candidates table? I suppose, it is a default value or, if your form is bound
to candidates, the value of an old existing
row.

Hope this helps

Georg
 
Back
Top