Changing query after form is built

[Amy Blankenship]

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

 

[Jeanette Cunningham]

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

 

[Amy Blankenship]

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

 

[Jeanette Cunningham]

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

 

[Jeanette Cunningham]

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 Blankenship]

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;

 

[Jeanette Cunningham]

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 Blankenship]

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.

 

[Jeanette Cunningham]

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 Blankenship]

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.

 

[Jeanette Cunningham]

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 Blankenship]

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

 

[Amy Blankenship]

I made a stored query that joined Page and Question to frmMockExamGuesses
and used that as the data source instead. Worked perfectly.

Thanks!

Amy

 

[Jeanette Cunningham]

Glad you got it working.

Jeanette Cunningham