INSERT Problem

[mych]

I am trying to insert some data into a table and am not qetting very far!!!

I have two table TblReqirements and TblChecklist

TblRequirements has the following structure

ReqNo (AutoNo)
TeamRefNo (Number)
ReqDetail (Text)
Redundant (Yes/No)

Basically this table holds the Requirements for various teams
Typical data would be
[tt]
ReqNo TeamRefNo ReqDetail Redundant
1 1 Create Backup Stratergy False
2 1 Aquire enough tapes True
3 2 Procure Hardware False
4 2 Procure Software False
5 3 Build Workstations False
6 4 Install Software False
[/tt]
The second table TblChecklist has the following structure

CheckID (AutoNo)
ProjectID (Text)
ReqNo (Number)

In my application a project is created and the user selects teams that are required for the project. I then need to create a checklist which will consist of a list of (current) requirements for the teams selected. Current are denoted as not being redundant

So for example, if a user selects teams 1 and 3 for project Z1234 the following needs to be inserted into TblChecklist
[tt]
CheckID ProjectID ReqNo
Z1234 1
Z1234 5 (The CheckID would be AutoNumbered)
[/tt]
Using the SQL Statement

INSERT INTO TbleChecklist (ReqNo)
SELECT TblRequirements.ReqNo
FROM TblRequirements
WHERE (((TblRequirements.TeamRefNo)=[TeamNo]) AND ((TblRequirements.Redundant)=False));

will do half the job ..... I get
[tt]
CheckID ProjectID ReqNo
1
5 (The CheckID would be AutoNumbered)
[/tt]
How do I get it to insert the ProjectID which is taken from a field in a form?

Any help appreciated!!!!

 

[formerTexan]

The syntax is different if you are inserting variables or values from a control.

INSERT INTO tblTable (field1, field2) VALUES (variable1, variable2);

You will have to handle this seperately from your INSERT ... SELECT statement.

Cheers,
Bill

 

[KaiserSouza]

Hi Mych,

Try an INNER JOIN statement in your SQL query
I think it goes something like this:
INSERT INTO TbleChecklist (ReqNo, projectID )
SELECT TblRequirements.ReqNo, tblRequirements.projectID
FROM TblRequirements INNER JOIN tbleChecklist ON tbleChecklist.Reqno = tbleRequirements.Reqno
WHERE (((TblRequirements.TeamRefNo)=[TeamNo]) AND ((TblRequirements.Redundant)=False));

This only works if you have projectId field in the tblRequiements table.

you need to do an Update statement AFTER your insert statement where you update the last record you inserted with the the value of the projectid = to reqno.


Hope I'm making sense

Kaiser Souza

 

[mych]

Bill, Kaiser,

Thanks for your input.

I have solved my problem but my solution is not pretty....

I used a sledgehammer to crack a nut. No doubt someone can think of a more elegant way.

My code now is

DoCmd.SetWarnings False
Dim TeamNo As Integer

Y = DCount("*", "TblChecklist") 'Find out how many record in the table

For X = 1 To C    
    TeamNo = DLookup("[TeamRefNo]", "TblTeamNames", "[TeamFullName] = '" & TeamFullArray(X) & "'")
    strSQL = "INSERT INTO TblChecklist ([ReqNo]) "
    strSQL = strSQL & "SELECT ReqNo "
    strSQL = strSQL & "FROM TblRequirements "
    strSQL = strSQL & "WHERE  (((TblRequirements.TeamRefNo)= " & TeamNo & ") AND ((TblRequirements.Redundant)=False));"
    DoCmd.RunSQL strSQL
Next X

Z = DCount("*", "TblChecklist") 'Do a recount to see how many new rows were added Z-Y

'Next bit of code goes though the TblChecklist and puts the ProjectID in the newly added rows.

Set myset = MyDB.OpenRecordset("TblChecklist", DB_OPEN_DYNASET)
    myset.MoveFirst
    For X = 1 To Y
    myset.MoveNext
    Next X
    For X = Y + 1 To Z
    myset.Edit
    myset("ProjectID") = [Forms]![frmCreateChecklist].[ProjectID]
    myset.Update
    myset.MoveNext
    Next X
    myset.Close

DoCmd.SetWarnings True

Now there's a challenge!

 

[PHV]

And what about this ?
strSQL = "INSERT INTO TbleChecklist (ProjectID,ReqNo)" _
& " SELECT " & [ProjectID] & " As ProjID,ReqNo FROM TblRequirements" _
& " WHERE TeamRefNo=" & [TeamNo] & " And Redundant=False;"
DoCmd.RunSQL strSQL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244