Adding mutiple records to a table when adding a new record to another table

[lee weaver]

I have a table for employees and a table that shows which containers they have access to and a table of all containers.

What i need to do is when adding a new employee i need to add a record to the "access" table for each record in the container table. that would basically default the new employee's access to each container.

I think the following code in my addemployee form_Load event should detect if they have been added. but i'm having trouble with figureing out how to proceed on what needs to done.

Dim emp As String
emp = "employee = " & Staffadd.ID
If DLookup("[Containerstring]", "access", emp) = Null Then

the above code should only detect a null if there is not an entry for the employee in the "access" table

what i need help with is:

A) I don't know how to tell how many entreis are in the containers table.

and
B) I don't know how to programitacly add records to a table.

With this information i should be able to simply loop through the containers table and add a record to the access table for the new employee each record in the containers table.

[vb5prgrmr]

Time to use your friends (yahoo, google, ask, answers, bing) and search for vb6 ado tutorial. Then once you have absorbed that you can use these for your various questions.

strSQL = "INSERT INTO tblEmp(vFName, vLName, iNumericField, dDateField) VALUES('" & Text1.Text & "','" & Text2.Text & "'," & Text3.Text & ",#" & Text4.Text & "#)"

?strSQL
INSERT INTO tblEmp(vFName, vLName, iNumericField, dDateField) VALUES('John','Doe',3,#6/10/2010#)

'Note: the date field surrounded by the pound sign (#) is an access thing while SQL Server will not accept a
'date surrounded by the pound sign but will accept a date surround by single ticks (') like the two varchar
'fields shown above ('John','Doe',...)

adoCn00.Execute strSQL

strSQL = "SELECT iEmpID FROM tblEMP WHERE vFName = '" & Text1.Text & "' AND vLName = '" & Text2.Text & "'"

adoRs00.Open strSQL, adoCn00,...

strSQL = "SELECT iConID, vConDesc FROM tblCon"

adoRs01.Open strSQL, adoCn00,...

Do While adoRs01.EOF = False
  If adoRs01.Fields("vConDesc").Value = "Low" Then
    strSQL = "INSERT INTO tblEmpConAccess(iEmpID, iConID, iAccessLevel) VALUES(" & adoRs00.Fields("iEmpID").Value & "," & adoRs01.Fields("iConID").Value & ",1)"
  Else
    strSQL = "INSERT INTO tblEmpConAccess(iEmpID, iConID, iAccessLevel) VALUES(" & adoRs00.Fields("iEmpID").Value & "," & adoRs01.Fields("iConID").Value & ",0)"
  End If
  adoCn00.Execute strSQL
  adoRs01.MoveNext
Loop

strSQL = SELECT COUNT(iEmpConAccessID) As AccCnt FROM tblEmpConAccess WHERE iEmpID = " adoRs00.Fields("iEmpID").Value

adoRs00.Close
adoRs01.Close
Set adoRs00 = New ADODB.Recordset
Set adoRs01 = New ADODB.Recordset

adoRs00.Open strSQL, adoCn00,...

strSQL = SELECT COUNT(iConID) As ConCnt FROM tblCon
adoRs01.Open strSQL, adoCn00,...

If adoRs00.Fields("AccCnt").Value <> adoRs01.Fields("ConCnt").Value Then
  'problem as employee was not added to each container
  'so need to correct somehow
End If

adoRs00.Close
adoRs01.Close
Set adoRs00 = Nothing
Set adoRs01 = Nothing

or something like that...



Good Luck

[lee weaver]

Thanks for the Information

Thanks for the Information, but apparently i posted in the incorrect area. I need information on VBA not VB6

Thanks again.

Wanders off to ask Google.