 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / March 2006set Form properties if Form is not open
I did a few searches, but couldn't come up with a clear answer to my question. So here it is: If I am in an open form (MyForm1), is it possible to set the RecordSource of an un-opened form (MyForm2). I want to use the OnClose Event of MyForm1. But I do not want to have the MyForm2 open when this happens. My wish it to have the RecordSource of MyForm2 permanently changed by the OnClose Event of MyForm1. Can this be done ? Thanks for any suggestions, Scott > I did a few searches, but couldn't come up with a clear answer to my > question. So here it is: If I am in an open form (MyForm1), is it [quoted text clipped - 5 lines] > > Thanks for any suggestions, Scott A couple of options. Docmd.Openform "FormName" Forms!FormName.Form.Recordsource = whatever You could also open the form in design mode, hidden. Modify the recordsource, and close/save it. Then open it. Wow ! That was quick. Thanks, I will try that. ~Scott >I did a few searches, but couldn't come up with a clear answer to my > question. So here it is: If I am in an open form (MyForm1), is it [quoted text clipped - 5 lines] > > Thanks for any suggestions, Scott I would not do this by altering the design view. I would store this string as some form of setting - perhaps in a settings table or perhaps as a database property or even a registry entry. Then each time the form opened, it would look up it's value and set the recordsource accordingly. To the end user, this would look like the design had changed each time the form was closed, it would just be implemented differently. No, you can't do what you want. Salad has given you some alternatives. Another would be to write the RecordSource to a Table, and have MyForm2 open the Table from code in its Open event and reset its own RecordSource. That may not work so well, depending on the details of your application, if you have multiple users (though there are probably workarounds for most of those potential problems, too.) Another approach would be to use the name of a Query as the Record Source, and instead of changing the RecordSource, replace the SQL property of that Query. That, effectively, permanently changes the RecordSource without ever opening the Form, either in Form or Design View. What kind of changes are you making to the RecordSource? Generally the only kind that make sense are those selecting or llimiting which Records are to be processed. Certainly, for a bound Form to work, both the old and new RecordSources would have to have Fields with the same names. The only way to permanently change the RecordSource is to do it with the Form open in Design View (but could certainly be hidden) then close the Form. Larry Linson Microsoft Access MVP >I did a few searches, but couldn't come up with a clear answer to my > question. So here it is: If I am in an open form (MyForm1), is it [quoted text clipped - 5 lines] > > Thanks for any suggestions, Scott I like the option of opening the form in Design view something like this: DoCmd.OpenForm MyForm1, , , , , acHidden Forms!MyForms.Form.RecordSource = "newQry" DoCmd.Close acForm, MyForm1, acSaveYes Would that work ? The recordsource is a Query that limits a users view of previous year's data. I suppose I could change the SQL property for the query, but not sure how to make that change permanent either. ~Scott >I like the option of opening the form in Design view something like > this: [quoted text clipped - 7 lines] > > ~Scott If you used the function below, you could write something like: If Not DefineQuery("MyQuery","SELECT * FROM MyTable WHERE MyYear<2006") Then Beep End If So you always have the same query, named MyQuery, you just change its definition. Public Function DefineQuery(strQuery As String, _ strSQL As String) As Boolean On Error GoTo Err_Handler Dim dbs As DAO.Database Dim qdf As DAO.QueryDef Set dbs = CurrentDb Set qdf = dbs.QueryDefs(strQuery) qdf.SQL = strSQL DefineQuery = True Exit_Handler: If Not qdf Is Nothing Then Set qdf = Nothing End If If Not dbs Is Nothing Then Set dbs = Nothing End If Exit Function Err_Handler: MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number Resume Exit_Handler End Function I don't recall ever using acHidden, but I have, at times used DoCmd.Echo False before my code to freeze the display, run the code, and DoCmd.Echo True after the code to put things back to normal. If you do that, be very, very sure that the code runs without error. Otherwise the screen won't be responding and you will have the Devil of a time getting back to normal. When I use it, I run and test and test and test without setting Echo off -- and still, sometimes, I worry. Larry Linson Microsoft Access MVP Larry Linson Microsoft Access MVP >I like the option of opening the form in Design view something like > this: [quoted text clipped - 7 lines] > > ~Scott > I don't recall ever using acHidden, but I have, at times used > DoCmd.Echo False before my code to freeze the display, run the code, [quoted text clipped - 5 lines] > test and test without setting Echo off -- and still, sometimes, I > worry. No need to worry. Just add Application.Echo True into the error handling. > Larry Linson > Microsoft Access MVP [quoted text clipped - 12 lines] >> >> ~Scott > No need to worry. Just add Application.Echo True > into the error handling. Yes, that is the solution, but as I sit there and the code takes longer than I remembered it taking, I wonder, "Hmm. Did I add it?" And, even more to the point, "Did something happen so my code is in an unending loop?" Larry Linson Microsoft Access MVP >I like the option of opening the form in Design view something like > this: [quoted text clipped - 7 lines] > > ~Scott If you change the SQL property of a Query at runtime, it stays changed (unlike changes to the design of a Form). Just to make certain my memory wasn't playing any tricks on me, I just tried and verified that. What, particularly, determines the year you want displayed? Is it always the year prior to the current year, or is it always the current year, or do you allow the user to specify it somehow? Larry Linson Microsoft Access MVP Larry Linson Microsoft Access MVP > I like the option of opening the form in Design view something like > this: [quoted text clipped - 7 lines] > > ~Scott I normally don't change the recordsource except in specific situations. Here is what I do. I open the form and pass an argument. DoCmd.OpenForm "SomeForm", , , , , , "Test" In the OnOpen event for the form you can check the argument. Let's say 50%+ of the time you are going to use the recordset used when the form was designed. Then you could check for arguments and update the recordsource. Let's say the recordsource is currently Query1. You could do something like this if "Test" is passed. If Me.OpenArgs = "Test" Then Me.Recordsource = Query2 This changes the recordsource only if the argument is Test. For whatever reason...I prefer setting/changing my recordsource in the form. Thanks for all the suggestions. I wish my Access knowledge was sufficient to figure out how to make this easy. But I am still struggling. I have the following code behind a button that I can't get to work. It hangs up on the Forms! argument, giving me this error: "Type declaration character does not match declared data type." '>>> Code behind On-Click button of Form1 Dim MyForm As String, MySource As String Dim MyEdit As Boolean, MyDelete As Boolean MyTarget = chr(34) & Me.FormName & chr(34) MySource = Me.DataSource MyEdit = Me.EditOption MyDelete = Me.Add_Delete DoCmd.OpenForm MyForm, , , , , acHidden Forms!(MyTarget).Form.RecordSource = MySource Forms!(MyTarget).Form.AllowEdits = MyEdit Forms!(MyTarget).Form.AllowAdditions = MyDelete Forms!(MyTarget).Form.AllowDeletions = MyDelete DoCmd.Close acForm, MyForm, acSaveYes My fear is that I am way off base with this approach. Any thoughts ? Should I be declaring MyTarget as a Form object? -Scott kokostik@yahoo.com wrote in news:1143486125.365016.79760 @u72g2000cwu.googlegroups.com: > Thanks for all the suggestions. I wish my Access knowledge was > sufficient to figure out how to make this easy. But I am still [quoted text clipped - 21 lines] > Should I be declaring MyTarget as a Form object? > -Scott I haven't used this really clumsy Forms!(SomeFormName) for many years and have no idea why it's recommended by seemingly all. Regardless, if MyTarget is a string, as it seems to be, then the chr(34)'s in MyTarget = chr(34) & Me.FormName & chr(34) are redundant.  SignatureLyle Fairfield I have tried it with and without the forced quotation marks, but I get the same error. -Scott Any other thoughts on this ? I have the following code behind a button that I can't get to work. It hangs up on the Forms! argument, giving me this error: "Type declaration character does not match declared data type." '>>> Code behind On-Click button of Form1 Dim MyForm As String, MySource As String Dim MyEdit As Boolean, MyDelete As Boolean MyTarget = Me.FormName MySource = Me.DataSource MyEdit = Me.EditOption MyDelete = Me.Add_Delete DoCmd.OpenForm MyForm, , , , , acHidden Forms!(MyTarget).Form.RecordSource = MySource Forms!(MyTarget).Form.AllowEdits = MyEdit Forms!(MyTarget).Form.AllowAdditions = MyDelete Forms!(MyTarget).Form.AllowDeletions = MyDelete DoCmd.Close acForm, MyForm, acSaveYes Should I be declaring MyTarget as a Form object? Is there a more elegant way to get this to work ? -Scott |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.