SQL To Insert Newly Created AutoIDNumber Into Another Table

[txfink]

I think this is going to be an easy question (and answer), but I'm not familiar with how to get it to work and I've been trying to figure it out for a little while now. Thanks in advance for any assistance - let me know if more information is needed.

So I have two tables, with relevant fields below in a 1 to many relationship. There is an input form bound to tblProperty. When a new record is added to tblProperty via the form, I need a record added to tblCurrentOwner with the PropertyID that was just created passed to tblCurrentOwner.PropertyID.

tblProperty
PK - PropertyID (autonumber)

tblCurrentOwner
PK - autonumber ID field
PropertyID (foreign key to tblProperty)
OwnerID (foreign key to another table, Owner - this field can and will be blank at this point.)

 

[TheAceMan1]

In the [blue]After Update[/blue] event of the form, all you need is the following:

[blue]   Dim db As DAO.Database, SQL As String
   
   Set db = CurrentDb
   
   SQL = "INSERT INTO tblCurrentOwner (AcctID) " & _
         "Values (" & Me!PropertyID & ");"
   db.Execute SQL, dbFailOnError
   
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[JBinQLD]

SQL = "INSERT INTO tblCurrentOwner ([red]PropertyID[/red]) " & _
"Values (" & Me!PropertyID & ");"

Just incase you missed it

JB

 

[TheAceMan1]

Howdy JBinQLD . . .

[blue]Roger That![/blue]

Its a good thing I have an eye to see with!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

txfink . . .

Be sure to include the correction by [blue]JBinQLD[/blue]!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[RoyVidar]

When a new record is added to tblProperty via the form, I need a record added to tblCurrentOwner with the PropertyID that was just created passed to tblCurrentOwner.PropertyID

Might want to consider the After Insert event of the form in stead of the After Update event of the form, else you'll be getting a new record per alteration of each record.

Roy-Vidar

 

[txfink]

Thanks everyone for the responses - I definitely would not have come up with that on my own. I'll be playing around with it here today and will let you know if I have any problems.

Much appreciation.

-smf

 

[txfink]

Finally able to get to the system I'm working on today and I tried the recommendations - it worked like a champ - thanks a bunch for the help.

Does anyone know of a good site that covers sql syntax? I understand the logic of the code we just used, but not the syntax. I'd like to get a reference to use in the future - but specifically in this case I'm wondering the functionality of the ampersand (&) in the code - (" & Me!PropertyID & ").

Thanks again everyone.

 

[NXMold]

The ampersand is just building a string, if you look at the value of your string variable SQL:

SQL= "INSERT INTO tblCurrentOwner (AcctID) " & _
"Values (" & Me!PropertyID & ");"

gives you:

"INSERT INTO tblCurrentOwner (AcctID) Values (3081543298);"

You can update records via SQL, modifying multiple fields separated by commas, such as:

strSQL = "UPDATE UserTable SET UserStatus='" & strStat & "', WorkstationID='" & strWID & "' WHERE DisplayName ='" & strName & "'"

I'm also way new to SQL and don't know whats possible, whats up with the syntax, etc. I asked in the thread below recently, which also shows methods to supress user warnings.

http://www.tek-tips.com/viewthread.cfm?qid=1491434&page=1

 

[Remou]

Here is some reading:
Intermediate Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/en-us/library/aa140015.aspx

Also available in Fundamental and Advanced (see Additional Resources).