Solved

Access 2013 - Save Data on Current Form

Posted on 2014-03-18
4
989 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 35

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 35

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access: Retrieving Current Month's Orders for Invoice 6 30
Office 2016 Temp Files 3 31
Criteria for Date for DCount 4 26
Dirty form - conditional formatting 5 28
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 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