Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2013 - Save Data on Current Form

Posted on 2014-03-18
4
Medium Priority
?
1,025 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 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 39

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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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