locked
How do I move RECORDS in FORMS - to a query RRS feed

  • Question

  • I have done this many years ago, and I've forgotten how.  I want to place a COMMAND button on my FORMS that will enable the USER with ONE CLICK to move the INWINDOW FORM to a QUERY - to purge it from the ACTIVE RECORDS to the INACTIVE ones.  I believe I did this with a MACRO, but somehow all that knowledge now escapes me.  Please help Thanks
    Thursday, May 26, 2011 11:29 PM

Answers

  • Actually the subform should be simple enough to set up.  Create the form as you have, making sure that when you use it as a standalone form everything works as it should.

    Place that form into the existing mainform as a subform.  Do not use the wizard.  In the subform control properties, make sure the LinkChildField and LinkMasterField properties are blank.  This "disconnects" the subform and the mainform in the sense that it will not try to keep the subform showing records pertaining to the mainform as is the case with a standard parent/child setup (in this case, even though the forms are on a parent/child setup, the data is not, being that each form has the same source table).

    On the subform,make sure AllowAdditions is set to True.  You'll probably want to include setting the status field as required in this form's BeforeUpdate event if it is not already defaulted through the repsective table or control property.

    The mainform and subform should now show two independent views of the same source data.  You will want to have the subform navigate to a new record as the mainform is open.  You'll either need a line of code in the Open event to nav the subform to a new record, or you can set the subform's DataEntry property to True, which allows only additions and not edits for a form.  The DataEntry property on the subform is probably ideal in this case.

    Depending on the type of underlying query, you may need to requery the mainform's recordset when a new record is added to the subform.  Me.Parent.Requery can be added to the subform's AfterUpdate event to accomplish this.  Also note that whenever you set the focus from the mainform to the sub, or vice versa, the form you are leaving will save it's record.  This is "by design" and uncontrollable, implemented due to parent/child data relation requirements.

    Now that we've gotten through all that, let me know if you are still experiencing the non-able-to-add-data-to-the-subform problem, and what settings are different than what I've described above (I suspect this issue may be because of the LinkChildField and LinkMasterField properties of the subform control, and am hoping that it disappears if those properties are left blank).

    As a disclaimer, I've never actually set up a form this way, but in theory there should be no reason why it shouldn't work.

    hth


    Access Wiki: http://www.utteraccess.com/wiki
    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 5:50 AM
    • Marked as answer by Bruce Song Tuesday, June 7, 2011 12:47 PM
    Sunday, May 29, 2011 3:53 AM
  • Jack, I found the problem, as simple as it seems, from the MAINFORM I set the RECORDS LOCKS to NO LOCKS and it solved the issue. I now have exactly what I wanted.  At least for the moment.

     

    Thanks for the insight, you gave me a platform to which to address and fix the issues.

    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 5:50 AM
    • Marked as answer by Bruce Song Tuesday, June 7, 2011 12:47 PM
    Sunday, May 29, 2011 10:33 PM

All replies

  • Hi,

    I'm not quite sure I'm following:  a query is a view of records from any given table(s) that may be filtered and sorted as you wish.  Form's display the data from a query, therefore you can't really "move" records from a Form to a Query.

    You seem to be indicating that you want to mark the current record(s) shown in the Form as inactive, so they no longer appear in the Form.  Do you have a status field that indicates which records are Active vs. Inactive?  A standard setup is to include such a field, and update the status to Inactive by changing the record's value and requerying the form.

    Can you give some more detail behind the underlying table structure and Form recordset?


    Access Wiki: http://www.utteraccess.com/wiki
    Friday, May 27, 2011 1:35 AM
  •  

    Indeed it is sending the Inactive records to a query, by placing a COMMAND button for the USER to send it (move it) to the query.

     

    I have a data-set table of 15 columns, the Form contains the same fields.  What I am looking to do is that once the USER determines the record is no longer active they can click the COMMAND control or CHECKBOX so that the record is no longer active on the form but insteads sends it to a query.  The query is labeled DUMP HISTORY.

    Like I mentioned before I did this a number of years ago for another DB, but I've forgotten the details, upon reading your text I now remember placing a COLUMN in my QUERY labeling it INACTIVE and perhaps setting the criteria to "1"  -- I am a bit fuzzy on the details but once I get the expression down, I know I can place it either in the CHECKBOX or COMMAND.

    Thank you in advance for your help.

     

    Maribella

     

     

    Friday, May 27, 2011 4:57 PM
  • Can you please post the SQL statement for the query DUMP HISTORY?

    Thanks,


    Access Wiki: http://www.utteraccess.com/wiki
    Friday, May 27, 2011 5:01 PM
  • I don't have an SQL statement, that is what I am working on.
    Friday, May 27, 2011 5:12 PM
  • Ok, sorry...  here's what you need, from the bottom up (in my opinion anyway):

    One table.  Call it tblWhatevers.  This will hold ALL of your data, active and inactive.  In this table should be an indexed field that can be used to identify the Active or Inactive status.  I prefer an Integer field with it's default set to 0 (let 0 = Inactive, 1 = Active), but for your case maybe make the default 1 (active) instead.

    You'll need two SELECT querys.  Call one qryActiveWhatevers, which will display all your Active records.  It's SQL will be this:

    "SELECT * FROM tblWhatevers WHERE fldStatus = 1"

    The second SELECT query will be called qryInactiveWhatevers.  It's SQL will be this:

    "SELECT * FROM tblWhatevers WHERE fldStatus = 0"

    Now, take you Form, and set it's recordsource to qryActiveWhatevers.  This gives you all of the records in tblWhatevers, EXCEPT those that are inactive.

    From your command button, you will set the record status field to 0 (Inactive).  This does not "move the records to the inactive query" technically, but instead "sets the value of the table's record status to inactive" so it therefore shows up in the Inactive query instead of the Active query (sorry, there's a bit of a terminology discrepancy there - when you inquire about "moving records to a query" I wasn't sure what you meant exactly).

    After the command is run, the effect records still exist in tblWhatevers, but now will show in qryInactiveWhatevers instead of qryActiveWhatevers, and therefore your form as well will not show them anymore.

     

    As for the command, it is certainly possible to run an SQL UPDATE statement to toggle the status field from Active to Inactive, but in most cases you shouldn't have to.  This record that will be affected, it is a record in the form's recordsource, correct?  If so, simply set the value of fldStatus (or whatever field you name it) to Inactive and save the record - seeing that the form is already bound, it will handle the rest for you.  This can be done using VBA (Code Builder) with the following Click event of the command:

    Private Sub cmdDisableRecord_Click()
      Me!fldStatus = 0
    End Sub

    If, by chance, the form is unbound (doesn't have a recordset) or otherwise not applicable to setting the desired record's status, you can use an update query like so (include the correct criteria for it, obviously - this is just a generic example):

    CurrentDb.Execute "UPDATE tblWhatevers SET fldStatus = 0 WHERE fldID = " & Me!fldID, dbFailOnError

     

    Is that making any sense?  Let me know what else you need to get it working

    hth


    Access Wiki: http://www.utteraccess.com/wiki
    • Proposed as answer by Bruce Song Wednesday, June 1, 2011 3:23 AM
    Friday, May 27, 2011 5:52 PM
  • It makes sense, thanks--I will try it and let you know.  I never used SQL with the last DB but then again that was probably Access 97 and the commands were drop-down, this SQL is all new to me.

     

    Regards,

    Mari

    Friday, May 27, 2011 6:10 PM
  • If it helps at all, SQL and queries are the same thing.  SQL is the "code" behind the query - Access gives us a nice designer for it though so we don't necessarily need to know the actual SQL (this was true in Ac97 as well)

    Essentially all you need to do is change the value of the status field, which can be done with a single line of VBA code, or even directly from the Expression Builder.

    Good luck,


    Access Wiki: http://www.utteraccess.com/wiki
    Friday, May 27, 2011 6:14 PM
  • Jack,

     

    Thank you so much it worked, my problem was not having the 2ND query, once I assigned the default settings it all worked well. I added a CHECKBOX and assigned it the INACTIVE field and that solved my problem. All that SQL frightened me, wouldn't know where to assign it, glad it all worked out.  Funny how some knowledge escaped me, but you pointed me in the right path, very much appreciate that. 

    Sunday, May 29, 2011 12:03 AM
  • On another note, I have another issue, wonder if you could shed some light.

     

    Based on 'TBLwhatever' displaying all my records in 'FORMwhatever,'  I created a  'SUB-DATA FORM1' using 'QUERYwhateverACTIVE' the relationship is the PRIMARY 'ID'KEY.  Upon clicking the stand-alone SUBDATAFORM it works very well, no problems --all my relationships are good--double checked with 'TBLwhatever' and double checked with 'QuerywhateverACTIVE'--- my problem is the following when I dragged the 'SUB-DATAFROM1' --saved and exited, and returned back to my 'FORMwhatever' to add a new record and subsequently to add a new entry in the SUBDATAFORM the 'SUBDATAFORM1' does not allow me to type into the fields.  I can type into the fields when I access the 'SUBDATAFORM1' in its stand-alone mode--any clues as to what I'm missing?

     Thanks again,

    Mari

    Sunday, May 29, 2011 12:08 AM
  • If you have a query based off and single table - qryWhatever pulling data from tblWhatever, there are no relationships for it.  Relationships are used to tie more than one table together (or more than one query together) under a specified behavior.  Therefore, creating a subform whose recordsource is qryWhatever (essentially a filtered and ordered view of tblWhatever's records), there is no Relationship.

    Can you explain the final goal?  What data do you intend to display on the main form, and what data do you intend to display on the subform?  In most cases, the subform's data shows child records in a related table to the main form, but you have not described such a scenario here.

    The first thing to look at is to make sure the underlying tables are properly normalized - this is key to everything else.  Once this is confirmed we can work towards troubleshooting the respective form/subform problems.

    Cheers,


    Access Wiki: http://www.utteraccess.com/wiki
    Sunday, May 29, 2011 1:14 AM
  • My final goal is to use the FORM as a NEWRECORD ENTRY only and the embedded SUBFORM to add to the NEWRECORD.  Not all NEWRECORDS will require or use the same fields from the SUBFORM.  The SUBFORM will pull out specific information to create three types of REPORTS, materialREPORT, ordersREPORT, vendorREPORT.

    Or could you show me an easier way to obtain my goal?

     

    Thanks,

    Mari

     

    Sunday, May 29, 2011 1:22 AM
  • Personally I find that a popup form is better than a subform for this scenario.  For one, you're not wasting valuable screen real-estate (subform) with a form that's only used during new record entry, for two you can open the add record popup in Dialog mode, meaning the user won't be able to do anything else in the app until they close the form (good making sure they explicitly add a record or cancel the addition), and for three you don't have to worry much about any of these relationships you're inquiring about in the popup scenario.  I tend to use this often for editing detail data that isn't otherwise shown on the main form as well.

    That said, it certainly should be possible to as you ask as well - having a mainform for viewing/editing existing which contains an "off to the side" subform which would allow for entry of a new record.

    Let me know which you'd like to persue and we'll see what it takes to get either or up and running.

    Cheers,


    Access Wiki: http://www.utteraccess.com/wiki
    Sunday, May 29, 2011 2:46 AM
  • Although the pop-up would seem simple and resolve my issue the fastest, I like the idea of the mainform for viewing/editing with the subform that allows the new entry.

     

    Regards,

    Mari

    Sunday, May 29, 2011 3:39 AM
  • Actually the subform should be simple enough to set up.  Create the form as you have, making sure that when you use it as a standalone form everything works as it should.

    Place that form into the existing mainform as a subform.  Do not use the wizard.  In the subform control properties, make sure the LinkChildField and LinkMasterField properties are blank.  This "disconnects" the subform and the mainform in the sense that it will not try to keep the subform showing records pertaining to the mainform as is the case with a standard parent/child setup (in this case, even though the forms are on a parent/child setup, the data is not, being that each form has the same source table).

    On the subform,make sure AllowAdditions is set to True.  You'll probably want to include setting the status field as required in this form's BeforeUpdate event if it is not already defaulted through the repsective table or control property.

    The mainform and subform should now show two independent views of the same source data.  You will want to have the subform navigate to a new record as the mainform is open.  You'll either need a line of code in the Open event to nav the subform to a new record, or you can set the subform's DataEntry property to True, which allows only additions and not edits for a form.  The DataEntry property on the subform is probably ideal in this case.

    Depending on the type of underlying query, you may need to requery the mainform's recordset when a new record is added to the subform.  Me.Parent.Requery can be added to the subform's AfterUpdate event to accomplish this.  Also note that whenever you set the focus from the mainform to the sub, or vice versa, the form you are leaving will save it's record.  This is "by design" and uncontrollable, implemented due to parent/child data relation requirements.

    Now that we've gotten through all that, let me know if you are still experiencing the non-able-to-add-data-to-the-subform problem, and what settings are different than what I've described above (I suspect this issue may be because of the LinkChildField and LinkMasterField properties of the subform control, and am hoping that it disappears if those properties are left blank).

    As a disclaimer, I've never actually set up a form this way, but in theory there should be no reason why it shouldn't work.

    hth


    Access Wiki: http://www.utteraccess.com/wiki
    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 5:50 AM
    • Marked as answer by Bruce Song Tuesday, June 7, 2011 12:47 PM
    Sunday, May 29, 2011 3:53 AM
  • After all that, I still cannot type into the MAINFORM's Subform.  I followed your instructions and then gave up. 

     

    I have managed to get the currentSubfrom to coincide with the MAINFORMview, which it wasn't doing before, by placing the ID in the HEADER only.  The SUBFORM works in standalone mode, and it also matches both the TBL AND THE QUERY, when data is entered--so that is OK.  The SUBFORMs source a QUERY, however I cannot manage to type into it no matter if I set both the MAINFORM and SUBFORM to DATAENTRY (removing all ALLOWS) or vice versa, setting all the ALLOW ADDITIONS, EDITS, DELETIONS and setting the DATA ENTRY to NO.

    Any clues?

     

    Sunday, May 29, 2011 10:27 PM
  • Jack, I found the problem, as simple as it seems, from the MAINFORM I set the RECORDS LOCKS to NO LOCKS and it solved the issue. I now have exactly what I wanted.  At least for the moment.

     

    Thanks for the insight, you gave me a platform to which to address and fix the issues.

    • Proposed as answer by Bruce Song Tuesday, May 31, 2011 5:50 AM
    • Marked as answer by Bruce Song Tuesday, June 7, 2011 12:47 PM
    Sunday, May 29, 2011 10:33 PM