Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Set db = Currentdb()
Set rst = db.OpenRecordset("SELECT * FROM DummyTargetsPside WHERE 1=2")
With rst
.AddNew
For each fld in .Fields
... build the field list
Next fld
.Update
End With
strAppend = Mid(strAppend,2) 'strip off the leading comma
strInsertSQL = Build the sql string
db.execute strSQL, dbFailOnError
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
On 2/9/2011 7:55 PM, Ben wrote:
> 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!
|
Similar Threads
