Solved

Access 2013 - Save Data on Current Form

Posted on 2014-03-18
4
999 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 36

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 36

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

685 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