Hi Klatuu,
Thanks for the note of reassurance and sorry for taking so long to get
back to you. Another reason for creating the one record temporary
table is so that I can make a cartesian join with the permanent table
and thus compare values. At this point maybe it will be better if I
show you the code. Please be gentle as I have only been coding vba for
a few weeks. Note that Parent_Ele() is a function that returns the
'code' for the parent element given a child element that is inserted
into an unbound textbox. What I am essentially creating is a
hierarchical tree using a nested set model whereby each element is
assigned a left and right id. The 'code' refers to a work breakdown
structure number. So this is what I have as an example for the root
element in a tree given no child elements:
1.0 = work breakdown structure 'code' as string = [ElementID]
1 = integer representing left position =
2 = integer representing right position =
False = boolean representing whether the element is a work package =
[WorkPack]
I don't think that it is necessary to understand how the nested set
model for hierarchical trees works, other than it relies on
and
as a position in the tree. You might be asking yourself why I
didn't work with the work breakdown structure code instead. For
instance, I might have created a function that recognizes that 1.0.1 is
a child to 1.0. Fact is I have no idea how to code for that. Also I
would really appreciate it if you had any pointers.
Private Sub Add_Element()
On Error GoTo ErrHandler
Dim ws As DAO.Workspace
Dim Db As DAO.Database
Dim bInTrans As Boolean
Dim strSql As String
Set ws = DBEngine(0)
ws.BeginTrans
bInTrans = True
Set Db = ws(0)
'Identify parent element by creating temporary table
strSql = "SELECT ElementID, " & _
"
,
, WorkPack " & _
"INTO tblIsolateElementAdd " & _
"FROM tblElements " & _
"WHERE ElementID = '" & Parent_Ele() & "'"
Db.Execute strSql, dbFailOnError
'Update left counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.Left = tblElements.Left+2 " & _
"WHERE tblElements.Left>tblIsolateElementAdd.Right"
Db.Execute strSql, dbFailOnError
'Update right counter permanent table
strSql = "UPDATE tblElements, tblIsolateElementAdd " & _
"SET tblElements.Right = tblElements.Right+2 " & _
"WHERE tblElements.Right >= tblIsolateElementAdd.Right"
Db.Execute strSql, dbFailOnError
'Update temporary table for child element
strSql = "UPDATE tblIsolateElementAdd " & _
"SET ElementID ='" & Me.Add_Text1 & "', " & _
"
=
, " & _
"
=
+ 1, " & _
"WorkPack =" & Me.Add_Check
Db.Execute strSql, dbFailOnError
'Append permanent table with temporary table for new child elements
strSql = "INSERT INTO tblElements " & _
"(ElementID,
,
, WorkPack) " & _
"SELECT ElementID,
,
, " & _
"[WorkPack] " & _
"FROM tblIsolateElementAdd"
Db.Execute strSql, dbFailOnError
'Drop temporary table
strSql = "DROP TABLE tblIsolateElementAdd"
Db.Execute strSql, dbFailOnError
'Commit transaction
ws.CommitTrans
bInTrans = False
Exit_AddElement:
'Clean up
On Error Resume Next
Set Db = Nothing
If bInTrans Then 'Rollback if the transaction is active.
ws.Rollback
End If
Set ws = Nothing
Exit Sub
ErrHandler:
MsgBox "Error in AddElement()." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
Resume Exit_AddElement
End Sub
Thanks!
Paolo
P.S. I was stupid enough to include my current e-mail address in
previous post. Ugh. Note that I don't check (e-mail address removed), only
(e-mail address removed) (remove NOSPAM).
Then the way you are doing it is not all that bad. The main objection I
would have is the make table query. I have a particular aversion to them.
An alternative would be to capture the values in memory variables you want
to put in the new appended record. Then after you have done the updates,
create an SQL statment that will append a new record to your table:
strSQL = "INSERT INTO MyTable (Field1, Field2, Field3) VALUES (" & _
strOne & ", " & strTwo & ", " & dtmSomeDay & ",);"
CurrentDb.Execute(strSQL), dbFailOnError
The above is untested "air code" and may contain syntax errors, but
hopefully, you will get the idea.
:
Hi Klatuu,
To answer you questions:
1. Yes. But also, not only has the original record been modified, all
of the records on the table have been modified in the same way. The
new record which has been added to the table, as you have noted, has
been modified in a different way.
2. I am doing this on a pop-up unbound form that pops up from a bound
form(so I can see what I am doing). Only some of the fields in the
table are bound to controls on the bound form. The user enters the
necessary information in the unbound pop-up form. This information is
used to determine which record(s) get manipulated in what fashion. On
the click event for a button in this unbound form, a series of action
queries are executed.
3. See my response on 2.
Thanks for the interest!
Paolo
Klatuu wrote:
A couple of questions:
1. Are you saying the net result is you now have added a record to the
permanent table where the original record has been modified and the new
record added is also a modification of the original, but different changes?
2. Are you doing this from a form, If not, how?
3. If it is from a form, are all the fields in the table bould to controls
on the form?
:
Hi all,
I have a table that is constantly appended to. It appends in the
following way: I create another table (temporary) to house one record
found on the permanent table. Then I update the permanent table and
temporary table in different ways using action queries (update). Then
I append the permanent table with the record from the temporary table.
Then I drop the temporary table.
Instead of creating a temporary table housing one record. Should I
create a recordset instead?
Come to think of it, should I do this all in DAO/SQL, instead of
strictly SQL? I am getting somewhat proficient in DAO, but don't know
if it is worth it.
Thanks in advance,
Paolo
