Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 16th, 2007, 10:24 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating an INSERT INTO SQL statement for 2 tables

I have an Employee database, that tracks Employee information and training. I have a form set up, that captures all the Employee Information, with the Employee No being the primary key. In this form, I have a subform, that lists all the training for that Employee (rows of training can be added). This subform information is housed in a MasterMatrix, which includes the Employee No (parent/child link), and the training class, and status of the training.

What I need to do is, create a button in the form that when a new employee is added to the database, it will take the Employee No from the record, and add that and 4 training classes (which are standard classes for new employees), and add them to the MatrixMaster.

I know how to do the SQL statement by just adding taking info from 1 table to the other, but how do I get it from 2 tables? I know that I need a JOIN, but I'm not sure what type I need.

Any help would be great.

1st table - tblEmployee (need EmployeeNo from here) - This is also main form
2nd table - tblTrainingClass (need Training Class, Training Status, WHERE NewEmployeeTraining = yes)

3rd table - tblTrainingMatrixMaster (where info is going to - this is also a subform, with parent/child link to EmployeeNo): - EmployeeNo, Training Class, Training Status

Reply With Quote
  #2 (permalink)  
Old April 18th, 2007, 08:52 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Sandra,

This looks to be a little tricky...

Looks like what you are looking for, is to click the button, and then have the MatrixMaster automatically populated with 4 separate records.

Since there is not really anything common between Training Class and Employee (Nor should there be!) a join won't cut it in this case.

I'm not sure how familiar you are with VBA, but let me share what my approach would be... There is likely a better solution out there, so if anyone would like to add to this, please feel free...

Public Sub cmdNewEmployeeButton_Click()
Dim rs as ADODB.recordset
Dim cn as ADODB.connection
Dim strSQL as String
Dim empID as String

set cn = Application.CurrentProject.Connection
set rs = New ADODB.recordset

empID = Me.txtEmpID 'This should reference the field that holds your employee ID Number
strSQL = "SELECT [Training Class], [Training Status] FROM tblTrainingClass WHERE [NewEmployeeTraining]='yes'"
rs.Open strSQL

If not(rs.EOF and rs.BOF) Then
    While rs.EOF=False
     strSQL = "INSERT INTO tblTrainingMatrixMaster ([EmployeeNo], [Training Class], [Training Status]) VALUES ("
     strSQL = strSQL & "'" & empID & "', "
     strSQL = strSQL & "'" & rs.fields("Training Class") & "', "
     strSQL = strSQL & "'" & rs.fields("Training Status") & "')"

     DoCmd.RunSQL strSQL

     rs.MoveNext
    Wend
Else
    MsgBox "No New Hire Classes were found"
End If

rs.close
cn.close

set rs=Nothing
set cn=Nothing
End Sub

Hope that helps - If not let me know and we can try a different approach.

Mike



Mike
EchoVue.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
T-SQL statement causing error with insert/update saf SQL Language 0 September 7th, 2007 04:48 PM
using two-way sql adapter to insert into 2 tables? shazza Biztalk 0 May 14th, 2007 01:18 PM
Help with SQL Insert statement in VBA Code ausmoran Access VBA 1 December 28th, 2006 06:16 PM
Efficient SQL Statement for Large Tables ritag SQL Server 2000 3 April 19th, 2004 11:03 AM
SQL Insert statement with apostrophe xgbnow SQL Language 11 June 18th, 2003 08:08 AM



All times are GMT -4. The time now is 09:03 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.