Access 2013 - Save Data on Current Form

Posted on 2014-03-18
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
End Sub
Question by:rogerdjr
  • 2
  • 2
LVL 34

Accepted Solution

PatHartman earned 500 total points
Comment Utility
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.

Author Closing Comment

Comment Utility
Works great thanks

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

LVL 34

Expert Comment

Comment Utility
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.

Author Comment

Comment Utility

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

743 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

19 Experts available now in Live!

Get 1:1 Help Now