I wrote a macro for a button in one form that opens another form and goes to a record that shares info in a related field with the active record in the first form. It works perfectly the first time I use it, but then the process breaks down. If I come back to the first form, change records and then press the button again, it still opens the record in the second form corresponding to the first query. Additionally, if there does not exist a record in the second form that corresponds to the active record in the first form, Access gives me an error message when I try to exit the second form, telling me that I cannot add or change records because there is no record in the related table. This isn't true, but who am I to argue with Access? Any ideas?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Updating data when leaving form
- Thread starter bphillips
- Start date
Never argue with a computer!
OK, First, do you mean a Macro or a Module?
Second, Do you close the second form before returning to the first form?
Third, What are the data sources for both forms?
Fourth, If the sources are tables, are they linked in any way?
Fifth, Please post the macro (or module) so we can have a Captain Cook.
OK, First, do you mean a Macro or a Module?
Second, Do you close the second form before returning to the first form?
Third, What are the data sources for both forms?
Fourth, If the sources are tables, are they linked in any way?
Fifth, Please post the macro (or module) so we can have a Captain Cook.
- Thread starter
- #4
Table1 is related to Table2 on a 1:M basis (many records in Table2 for each record in Table1). Table1 is the data source for Form1 and Table2 is the data source for Form2. The OnClick event for Button1 on Form1 is linked to a macro that performs the action OpenForm on Form2 where [Field1]=[Forms]![Form1]![Field1]. The macro does not seem to work unless I close Form2 after each iteration and if there is no record in Form2 where [Field1]=[Forms]![Form1]![Field1], it won't let me add a new one saying that there is no record with the related data in Form1.
bphillips,
You might give this a try or at least someone may be able to expound upon it. This sounds exactly like a problem I just encountered in my db. Here is how I solved it, although rather than use a macro, I strictly stay with code due to the fear future versions of Access may not support macros. In the on-click event of the command button, before the macro is run, place this in the code:
[forms]![formname].Filter=""
I believe what you are actually doing with your macro is applying a filter of sorts. I don't think the "filter" is resetting after the first time you run your macro so by setting it to "" with the above code sets the filter to nothing just as it is when you run your macro for the first time. At least this is the way it appears with my db and the above code solved my problem.
In the event that your macro is just applying a filter, you might give the following code a try in place of your macro in the on-click event of the command button:
docmd.openform "formname1"
[forms]![formname1].Filter=""
docmd.applyfilter "filtername","filtercriteria"
The filter name is the name of a query that you've already created. I don't use both the filtername and the filtercriteria, I use one or the other. If I use a filtername, I ensure the criteria I want is already within the query. If I use the filtercriteria, then I don't use the filtername and the code looks something like this as an example:
docmd.applyfilter , "fieldname=[forms]![formname]![fieldname]
Anyway, hope this helps or maybe someone else can expound on this or let us both know that I have absolutely no clue. Good luck!
You might give this a try or at least someone may be able to expound upon it. This sounds exactly like a problem I just encountered in my db. Here is how I solved it, although rather than use a macro, I strictly stay with code due to the fear future versions of Access may not support macros. In the on-click event of the command button, before the macro is run, place this in the code:
[forms]![formname].Filter=""
I believe what you are actually doing with your macro is applying a filter of sorts. I don't think the "filter" is resetting after the first time you run your macro so by setting it to "" with the above code sets the filter to nothing just as it is when you run your macro for the first time. At least this is the way it appears with my db and the above code solved my problem.
In the event that your macro is just applying a filter, you might give the following code a try in place of your macro in the on-click event of the command button:
docmd.openform "formname1"
[forms]![formname1].Filter=""
docmd.applyfilter "filtername","filtercriteria"
The filter name is the name of a query that you've already created. I don't use both the filtername and the filtercriteria, I use one or the other. If I use a filtername, I ensure the criteria I want is already within the query. If I use the filtercriteria, then I don't use the filtername and the code looks something like this as an example:
docmd.applyfilter , "fieldname=[forms]![formname]![fieldname]
Anyway, hope this helps or maybe someone else can expound on this or let us both know that I have absolutely no clue. Good luck!
- Thread starter
- #6
I had a similar problem jumping from one to another form which are "connected". Since I'm not such an expert in programming I used simple solution to eliminate mesage you have on Form2. It is similar to advice with filter as medic133 suggested. I just added additional macro before closing Form2 with "Sendkey" command for ESC which cleared Form2 and close it. Additionaly I have used command "Requery" to refresh one form with new infromation from other form. I know that such solutions are not "elegant" and maybe this is not what you are looking for....
I had a similar question and plenty of people responded to it. Check out thread707-324177 entitled "Book needed". I haven't taken anyone's advice yet on these though. I've gotten my experience, which is pretty limited, from the help menus in Access. Although they are somewhat limited, if you get started correctly in the code, there are many dropdown lists that automatically appear as you are writing as well as control tips. A wonderful way of learning as well is spending a little time here in tek-tips. Many of the questions relate to basic questions regarding coding and many people respond with the exact code that is necessary to solve a problem. Make a practice db and put the code in and see what happens! After that, play with it a little bit and see if you can get the thing to do something else.
One thing I will tell you from my experience. If you are new to VBA, especially with no background in writing programs, avoid the Que books. I'm sure these are great references, but they seem very advanced, at least for me. Similar to an algebra student opening up a calculus book! Good luck with the coding!!!
One thing I will tell you from my experience. If you are new to VBA, especially with no background in writing programs, avoid the Que books. I'm sure these are great references, but they seem very advanced, at least for me. Similar to an algebra student opening up a calculus book! Good luck with the coding!!!
Sorry. One more recommendation if you are going to use this site in learning the basic coding. You might significantly limit your threadminder list to topics you think you might be interested in such as VBA coding, forms, reports, queries, and tables. Then, if you do a search, you can limit the results returned to just those that are in the forums in your threadminder list. This keeps you from getting hundreds of records returned that probably will only lead to confusion. Good luck!!
- Status
