Solved

Access 2013 - Save Data on Current Form

Posted on 2014-03-18
4
984 Views
Last Modified: 2014-03-18
I have a data entry form with a button that adds a series of records to a subform based on data in the subform

I use the code below to append records to the subform based on new data entered into the form.

The code only works correctly when I "click" off the form and then click the button that runs the code.

Is there a way to do the switch focus off and then back on the form as part of the button click or to "save" the data first when I click the button.

The parent input form is based on the CASp_ElementListTbl

--------------------------------------------------------------------

Private Sub AddWkgChecklistBtn_Click()
    Dim dbs As Database, StrSql As String, Rst As Recordset, NextElementIdNo As Double
    Set dbs = CurrentDb

'    Forms![0_masterdatafrm].CASp_Input03_ElementChecklistWorkingChecklistDetailSubFrm.CASp_Input03_ElementChecklistWorkingChecklistDetailSubFrm.SetFocus
   
    StrSql = "INSERT INTO CASp_WorkingChecklistTbl ( ElementListRecId, JobNo, ReportId, CheckListIndexRecId, StdCheckListRecId, CheckListItemSortNo, "
    StrSql = StrSql & "CheckListHeaderSort, CheckListItemSeqNo, CheckListItemDescription, CheckListItemBriefDescr, CheckListItemAcceptanceCriteria, ChekListItemActCntQtyMeasure, "
    StrSql = StrSql & "CheckListItemAcceptYN, CheckListRefCodeSectRefRecNo, CheckListFigureRef ) SELECT CASp_ElementListTbl.ElementListRecId, "
    StrSql = StrSql & "CASp_ElementListTbl.JobNo, CASp_ElementListTbl.ReportId, CASp_ElementListTbl.CheckListIndexRecId, CASp_StandardChecklistTbl.CheckListRecId, "
    StrSql = StrSql & "CASp_StandardChecklistTbl.CheckListItemSortNo, CASp_StandardChecklistTbl.CheckListHeaderSort, CASp_StandardChecklistTbl.CheckListItemSeqNo, "
    StrSql = StrSql & "CASp_StandardChecklistTbl.CheckListItemDescription, CASp_StandardChecklistTbl.CheckListItemBriefDescr, CASp_StandardChecklistTbl.CheckListItemAcceptanceCriteria, "
    StrSql = StrSql & "CASp_StandardChecklistTbl.ChekListItemActCntQtyMeasure, CASp_StandardChecklistTbl.CheckListItemAcceptYN, "
    StrSql = StrSql & "CASp_StandardChecklistTbl.CheckListRefCodeSectRefRecNo, CASp_StandardChecklistTbl.CheckListFigureRef FROM CASp_ElementListTbl "
    StrSql = StrSql & "RIGHT JOIN CASp_StandardChecklistTbl ON CASp_ElementListTbl.CheckListIndexRecId = CASp_StandardChecklistTbl.CheckListIndexRecId WHERE "
    StrSql = StrSql & "CASp_ElementListTbl.ElementListRecId="
    StrSql = StrSql & Me![ElementListRecId].Value & " AND "
    StrSql = StrSql & "CASp_ElementListTbl.ReportId = "
    StrSql = StrSql & """" & Forms![0_masterdatafrm]![HdrRptID] & """"
'    StrSql = StrSql & """" & Me![ReportId] & """"
    StrSql = StrSql & ";"


    DoCmd.SetWarnings 0
    DoCmd.RunSQL (StrSql)
   
    StrSql = "UPDATE CASp_WorkingChecklistTbl INNER JOIN CASp_StandardChecklistHeaderTbl ON (CASp_WorkingChecklistTbl.CheckListIndexRecId = CASp_StandardChecklistHeaderTbl.CheckListIndexRecId) "
    StrSql = StrSql & "AND (CASp_WorkingChecklistTbl.CheckListHeaderSort = CASp_StandardChecklistHeaderTbl.CheckListHeaderSort) SET CASp_WorkingChecklistTbl.CheckListHeaderText = "
    StrSql = StrSql & "[CASp_StandardChecklistHeaderTbl]![CheckListHeaderText], CASp_WorkingChecklistTbl.HeaderRecId = [CASp_StandardChecklistHeaderTbl]![CheckListIndexRecId] "
    StrSql = StrSql & "WHERE (((CASp_WorkingChecklistTbl.ReportId) = "
    StrSql = StrSql & """" & Forms![0_masterdatafrm]![HdrRptID] & """"
    StrSql = StrSql & ") AND ((CASp_WorkingChecklistTbl.CheckListHeaderText) Is Null)) OR (((CASp_WorkingChecklistTbl.ReportId)="
    StrSql = StrSql & """" & Forms![0_masterdatafrm]![HdrRptID] & """"
    StrSql = StrSql & ") AND ((CASp_WorkingChecklistTbl.HeaderRecId) Is Null));"

    DoCmd.RunSQL (StrSql)
    DoCmd.SetWarnings -1
   
    Me![CASp_Input03_ElementChecklistWorkingChecklistDetailSubFrm].Requery
       
End Sub
0
Comment
Question by:rogerdjr
  • 2
  • 2
4 Comments
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39937453
To save the update before running the append queries:

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Open in new window


It looks like you are copying a lot of data from one place to another.  Unless this is a change log, it would be better to modify your schema so that you don't need to do this.  In a relational database we do not duplicate data, we refer to it by using foreign keys and joins.  That eliminates the possibility of data being different in different tables because your code didn't properly synchronize it.
0
 

Author Closing Comment

by:rogerdjr
ID: 39937561
Works great thanks

I appreciate the input about database structure - will look at alternate approach when I have more time to re-configure

Thanks
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39938169
Reconfiguring is actually easier than you might imagine.  You would need to do a little conversion to get the foreign key in place and get rid of the redundant columns.  But, the rest of the problem is solved by replacing the individual table with a query that joins the two tables when you create Recordsources.
0
 

Author Comment

by:rogerdjr
ID: 39938257
thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now