B
Ben
WHAT: I'm trying to copy all the fields of a record from a table named
"DummyTargets" to a new line, from within a form not bound to this
table. The primary key is autonumber.
WHY: I have a form with a datasource consisting of a query combing 2
tables: "DummyTargets" and "tblTests"
The 2 tables have a 1-to-many relationship where 1 "DummyTargets" can
have many "tblTests" associated. The form is a way for the user to
apply a set of Crashtest Dummy Setup target numbers (DummyTargets) for
a specific crash test (tblTests). If the user wants to tweak the setup
numbers for a single test, a new record in "DummyTargets" needs to be
copied and pasted and applied to that test in "tblTests". Otherwise,
previous tests will be affected by a changing dummy target in
"DummyTargets".
Anyways, my initial approach was to use
accmdselect
accmdcopy
accmdpasteappend
This works fine if I just make the datasource for the form the table
"DummyTargets" and then use Dlookup to fill the fields associated with
"tblTests". However, then I lose the ability to modify those "tblTest"
fields from this form...
My idea was, all from within VBA, go to the "DummyTargets" table,
selectrecord/copy/pasteappend the record of choice. Then relink the
record in "tblTests" to the new line in "DummyTargets". Then re-filter
or refresh the form.
My other idea was to run an APPEND query that copies all but the
primary key to a new line in the "DummyTargets" table. My start to
this is below (commented out).
SOME CODE:
'ONE APPROACH
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
'Set db = OpenDatabase(CurrentProject.path & "\" &
CurrentProject.Name)
'Set tbl = db.TableDefs("DummyTargetsPside")
''Set rst = db.Recordsets("DummyTargetsPside") 'doesn't work
'Dim strAppend As String
'Dim strInsertSQL As String
'With tbl
' For Each fld In .Fields
' 'skip the primary key field
' If fld.Name <> "PDummyTargetID" Then
' strAppend = strAppend & ", [" & fld.Name & "]"
' End If
' Next fld
'End With
'Set fld = Nothing
'strAppend = Right(strAppend, Len(strAppend) - 2)
'strInsertSQL = "INSERT INTO DummyTargetsPside SELECT " & strAppend &
" FROM DummyTargetsPside WHERE DummyTargetsPside.PDummyTargetID = 48"
'DoCmd.RunSQL (strInsertSQL)
''ABOVE CODE CAUSES ERROR#3103: Circular reference caused by alias
[FirstField of strAppend] in query definition's SELECT list
'ANOTHER APPROACH
Call EnableDisable(False, "NotBound") 'disables unbound textboxes so
there are no paste errors
'in this area I'd like to make VB think it's got a form sourced to
ONLY "DummyTargets" open to the record of interest, then...
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
Me.DataEntry = True
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.RunCommand acCmdPasteAppend
Me.Refresh 'causes error: Index or Primary Key cannot have a null
value
'If the entire row of the QUERY is copied, the "tblTests" primary key
is duplicated...
Call EnableDisable(True, "NotBound") 'enables unbound textboxes
'Update field on "tblTests" that points to the record in
"DummyTargetsPside"
DoCmd.RunSQL "UPDATE tblTests SET tblTests.PdummyTargetRecord = '" &
strTargetIDNew & "' WHERE (((tblTests.TestNum_)='" & strTestNum &
"'))"
Me.Filter = "TestNum_ = '" & strTestNum & "'" 'TestNum_ is PK for
tblTests, ....strTestNum is public, set in onOpen
Me.FilterOn = True
THANK IN ADVANCE!
"DummyTargets" to a new line, from within a form not bound to this
table. The primary key is autonumber.
WHY: I have a form with a datasource consisting of a query combing 2
tables: "DummyTargets" and "tblTests"
The 2 tables have a 1-to-many relationship where 1 "DummyTargets" can
have many "tblTests" associated. The form is a way for the user to
apply a set of Crashtest Dummy Setup target numbers (DummyTargets) for
a specific crash test (tblTests). If the user wants to tweak the setup
numbers for a single test, a new record in "DummyTargets" needs to be
copied and pasted and applied to that test in "tblTests". Otherwise,
previous tests will be affected by a changing dummy target in
"DummyTargets".
Anyways, my initial approach was to use
accmdselect
accmdcopy
accmdpasteappend
This works fine if I just make the datasource for the form the table
"DummyTargets" and then use Dlookup to fill the fields associated with
"tblTests". However, then I lose the ability to modify those "tblTest"
fields from this form...
My idea was, all from within VBA, go to the "DummyTargets" table,
selectrecord/copy/pasteappend the record of choice. Then relink the
record in "tblTests" to the new line in "DummyTargets". Then re-filter
or refresh the form.
My other idea was to run an APPEND query that copies all but the
primary key to a new line in the "DummyTargets" table. My start to
this is below (commented out).
SOME CODE:
'ONE APPROACH
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim tbl As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
'Set db = OpenDatabase(CurrentProject.path & "\" &
CurrentProject.Name)
'Set tbl = db.TableDefs("DummyTargetsPside")
''Set rst = db.Recordsets("DummyTargetsPside") 'doesn't work
'Dim strAppend As String
'Dim strInsertSQL As String
'With tbl
' For Each fld In .Fields
' 'skip the primary key field
' If fld.Name <> "PDummyTargetID" Then
' strAppend = strAppend & ", [" & fld.Name & "]"
' End If
' Next fld
'End With
'Set fld = Nothing
'strAppend = Right(strAppend, Len(strAppend) - 2)
'strInsertSQL = "INSERT INTO DummyTargetsPside SELECT " & strAppend &
" FROM DummyTargetsPside WHERE DummyTargetsPside.PDummyTargetID = 48"
'DoCmd.RunSQL (strInsertSQL)
''ABOVE CODE CAUSES ERROR#3103: Circular reference caused by alias
[FirstField of strAppend] in query definition's SELECT list
'ANOTHER APPROACH
Call EnableDisable(False, "NotBound") 'disables unbound textboxes so
there are no paste errors
'in this area I'd like to make VB think it's got a form sourced to
ONLY "DummyTargets" open to the record of interest, then...
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
Me.DataEntry = True
Me.AllowAdditions = True
Me.AllowEdits = True
DoCmd.RunCommand acCmdPasteAppend
Me.Refresh 'causes error: Index or Primary Key cannot have a null
value
'If the entire row of the QUERY is copied, the "tblTests" primary key
is duplicated...
Call EnableDisable(True, "NotBound") 'enables unbound textboxes
'Update field on "tblTests" that points to the record in
"DummyTargetsPside"
DoCmd.RunSQL "UPDATE tblTests SET tblTests.PdummyTargetRecord = '" &
strTargetIDNew & "' WHERE (((tblTests.TestNum_)='" & strTestNum &
"'))"
Me.Filter = "TestNum_ = '" & strTestNum & "'" 'TestNum_ is PK for
tblTests, ....strTestNum is public, set in onOpen
Me.FilterOn = True
THANK IN ADVANCE!