 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2008Changing query after form is built
Hi, all; I started out with a query something like: SELECT PageID, otherFields FROM Page INNER JOIN Question ON Page.QuestionID = Question.QuestionID I built my form, and it worked great. Then, I added another table left joined to Page on PageID. Now, when anything in my code refers to Me.PageID, I get the error that Access can't find the field or control PageID. So I tried aliasing PageID in the query that is on the right of the join. That gives me an error that the field "myAlias" does not exist in Page. Yes, I checked, and I didn't alias the wrong one. I then tried removing PageID from the query results, but I get an error that the joined field does not exist in the recordset. After that, I tried renaming the PageID control to Page_PageID like a wizard would have done if it had built the form from the same data source. I get the error that it can't find field or control Page_PageID. I am at my wit's end on what to do here. I don't want to start the form from scratch, since there is a lot of code that I've built behind it. Any ideas? Thanks; Amy Amy, try referring to PageID by the name of the table it comes from each time you use it. Example: Page.PageID when it comes from Page table, Table1.PageID when it comes from Table1. That way access will know which PageID you are referring to. Jeanette Cunningham > Hi, all; > [quoted text clipped - 28 lines] > > Amy > Amy, > try referring to PageID by the name of the table it comes from each time > you use it. > Example: Page.PageID when it comes from Page table, > Table1.PageID when it comes from Table1. > That way access will know which PageID you are referring to. I tried that, too, and it says it couldn't find the reference or something like that. The code hinting showed it as a good thing to type in, but when I ran the form it didn't work. Thanks; Amy Would you paste the query and post here? Do you have the correct PageID in the query? or maybe you have both of them, one from each table involved in the outer join? Is there a problem with the outer join - are null records not displayed? Another approach is to do a quick test with an autoform created from your query. See if the autoform can find the PageID - you may feel more free to experiment with the autoform. Jeanette Cunningham >> Amy, >> try referring to PageID by the name of the table it comes from each time [quoted text clipped - 10 lines] > > Amy Amy, Would you paste the query and post here. Do you have the correct PageID in the query? or maybe you have both of them, one from each table involved in the outer join? Is there a problem with the outer join - are null records not displayed? Jeanette Cunningham >> Amy, >> try referring to PageID by the name of the table it comes from each time [quoted text clipped - 10 lines] > > Amy > Amy, > Would you paste the query and post here. SELECT Page.PageID, Page.ParentLevelID, Page.ParentID, Page.QuestionID AS Page_QuestionID, Page.IsActive, Page.PageOrder, Question.QuestionID AS Question_QuestionID, Question.DistCorrect, frmMockExamGuesses.UserID, frmMockExamGuesses.StudentPageMarkType, frmMockExamGuesses.PageID FROM (Question INNER JOIN Page ON Question.QuestionID = Page.QuestionID) LEFT JOIN frmMockExamGuesses ON Page.PageID = frmMockExamGuesses.PageID; > Do you have the correct PageID in the query? or maybe you have both of > them, It _insists_ I have both of them. If I don't, it gives me a message that the joined field is not in the recordset. > one from each table involved in the outer join? Yes. > Is there a problem with the outer join - are null records not displayed? Records are displayed even if there is a null on the right hand side of the join, if that is what you are asking. I have code that handles unbound controls on the form based on PageID, but it can no longer figure out what the PageID is since I changed the underlying query. Thanks; Amy, I'm not very familiar with using unbound forms to manipulate data, I was assuming you were using a bound form. Maybe someone else can help you with this. Jeanette Cunningham >> Amy, >> Would you paste the query and post here. [quoted text clipped - 26 lines] > > Thanks; > Amy, > I'm not very familiar with using unbound forms to manipulate data, I was > assuming you were using a bound form. > Maybe someone else can help you with this. The form is bound, but some of the controls on it are not, since they are manipulating denormalized data. Amy, Access can get confused if a control has the same name as the field. Suggest you rename the control for PageID to txtPageID and check that its control source is the field PageID from the Page table. I don't know if you have a control for the PageID from frmMockExamGuesses, but if you do have I suggest you name it like txtPageIDMEG (for MockExamGuesses). See if that helps Access to find the particular control/value it needs. If it is an unbound control maybe this is confusing access as well. Jeanette Cunningham >> Amy, >> I'm not very familiar with using unbound forms to manipulate data, I was [quoted text clipped - 3 lines] > The form is bound, but some of the controls on it are not, since they are > manipulating denormalized data. > Amy, > Access can get confused if a control has the same name as the field. > Suggest you rename the control for PageID to txtPageID and check that its > control source is the field PageID from the Page table. I did, but when I use that name, even with context hinting, Access errors out on that line and says it can't find the control. > I don't know if you have a control for the PageID from frmMockExamGuesses, > but if you do have I suggest you name it like txtPageIDMEG (for > MockExamGuesses). No, I don't. A bit more information might help. What is your code trying to do when the error appears. What events work OK for the form - eg Open, Load, Current etc. If you make the textbox for PageID visible while you are debugging the form, can you see a value in it at any stage. Add any other info that might be helpful. Jeanette Cunningham >> Amy, >> Access can get confused if a control has the same name as the field. [quoted text clipped - 9 lines] > > No, I don't. >A bit more information might help. > What is your code trying to do when the error appears. What events work OK > for the form - eg Open, Load, Current etc. If you make the textbox for > PageID visible while you are debugging the form, can you see a value in it > at any stage. Add any other info that might be helpful. The code errors at pID = Nz(Page.PageID, 0) or pID = Nz(Me.Page_PageID, 0) or pID = Nz(Me.PageID, 0) Depending on what the control is called at the moment and what code I'm trying to access it. This code is about the second line after the declarations in the Form_Current event. Thanks; Amy I made a stored query that joined Page and Question to frmMockExamGuesses and used that as the data source instead. Worked perfectly. Thanks! Amy Glad you got it working. Jeanette Cunningham >I made a stored query that joined Page and Question to frmMockExamGuesses >and used that as the data source instead. Worked perfectly. > > Thanks! > > Amy |
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.