Endless form always jumps to first recordset II 2

[waldemar]

In regard to one of my last problems, I have additional information.

The issue was a continous subform that displays data entries to provide the user some overview while clicking on one of the entries synchronizes the main form with some detail data of that entry.

The problem seems to be of some SQL type. If you scroll down that continous list and click on an entry, some queries (like the following one) cause the subform to move to the very first entry (top of the list, scrolling all the way up automatically). This is quite stupid since now you have to scroll down again to be able to select the next entry etc. The following query is one example where this strange behavior is present:

SELECT *
FROM ((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID =
tblStudentsMedical.strStudentID) INNER JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID
WHERE (((tblStudents.dtmLeavingDate)<=[Forms]![frmToDo]![gdtmVirtualDate]) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]));

In the following query everything works fine. The subform stays where it is like it is supposed to.

SELECT *
FROM ((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID =
tblStudentsMedical.strStudentID) INNER JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID
WHERE (((tblUserStudentsSelection.strUserID)=[application].[currentuser]));

Any errors in the vba code behind is impossible (in fact there is only minimal code like GoToRecord, no requery nothing.... it MUST be in the query since this is only envirnomental difference.

Some background to the queries:
These will display Students joined together with extra bus and medical data plus an extra table containing a simple boolean for this entry to be or not be selected. Since every Access user can have his/her own selection this is linked to [application].[currentuser]. The only difference between both queries is the last displaying ALL students and the first displaying Students who left the school already (tblStudents.dtmLeavingDate <= [forms]![frmToDo]![gdtmVirtualDate] - an always present (current or virtual) date).

Unfortunately I cannot provide the database itself since it contains 12 Megs of confidential data already, but maybe someone has an idea where this Jumpy-behavior is hidden the query??

Thanks a lot in advance
waldemar

 

[cmmrfrds]

What you describe has to do with the Form not the query. Check the GoToRecord, which is the most likely cause if there are no requeries on the form. Also, check for any other action on the Form that would cause a requery.

 

[waldemar]

As I said both queries use the same environment, there is no change whatsoever in the form code but the different query in the recordsource. (I simply use &quot;DoCmd.GoToRecord acDataForm, Me.Parent.Name, acGoTo, Me.CurrentRecord&quot. It seems that the form requeries some queries and some not (more complicated ones?) ... like there is some special query property &quot;autorequery true&quot; ...

 

[btaber]

The only thing I can think of is that when the query is built in VBA off the values in the form, there may be a character causing the query to return no results, and when the GoToRecord runs, there is no value for it, so it goes to the first record. I would suggest you do a debug.print of the query string with all the values, and the gotorecord value

I hope this didn't confuse you more

 

[waldemar]

Good point. Unfortunately I use real Queries() that are not modified by vb. It is really exactly that query from above's post that causes that bevior.

Thanks
waldemar

(Access: &quot;Hmmm this query seems quite 'complex' to me, I just decide to requery it everytime somebody accesses to it - thats fun...&quot

 

[cmmrfrds]

<=[Forms]![frmToDo]![gdtmVirtualDate])

Probably what is happening is that the Jet Engine notices the first query has a variable from the Form and does a requery to satisfy the variable. You could test out by hard coding the virtual date.

 

[waldemar]

cmmrfrds! You are my hero (puzzling on this for weeks); indeed with the hard coded value the form did NOT requery yipieh! But now for the tricky part: How do I get the value [Forms]![frmToDo]![gdtmVirtualDate] anyway (frmToDo is a different form somewhere in the background)?

Thanks
waldemar

 

[LLudden]

It sounds to me like switching the subform and the form around is what you really want to do. The data in the main form should be deciding what is shown in the subform, not vice versa.

 

[waldemar]

Hmmm. I uploaded a screenshot to help visualize the situation. Just check out

http://www.rixcafe.de/frontend.gif

Here you see the students list in the subform to the left and the details of a single student in the main form. Once you click on a student in the list the main form jumps there via GoToRecord (they both use the same queries). At the bottom of the screen there is a controlbar that allows switching between a selection of students (all students, former students, future students, etc....) - this is where the different queries apply.

Thanks
waldemar

 

[LLudden]

I would still make one big form containing the left pane and the bottom pane, then put the student data (upper right pane) in a subform. As pointed out by Cmmrfrds, since a value used in the query potentially changes with each new name clicked, the form based on that query has to be refreshed, causing the name to bounce back to the top. If the student ID numbers are unique, you could just save the current student in a variable and do a find and goto after the refresh, but that would cause a flicker on the screen. The better method is just to do as I said above.

 

[waldemar]

just let me get this clear for me: The subform jumps to it's first recordset because the query of the parent form requeries because it contains a variable of another form.

So when I put all the parent form stuff into another sub form, the parent form (having no recordsource at all now) will never be requeried so the list-subform will stay where it is...!?

I'm asking since this causes a lot of work (having Parents, Classes, Students, etc.).... Many of the elements you see are subforms already...

Regards
waldemar

 

[LLudden]

Before you do alot of recoding, try this:

Put a hidden field in the subform with the student list for holding the date value that is on the main form. Make it an unbound field. Have the field on the main form compare itself with the value in the hidden field each time the record is updated, and if they are different, then force a requery of the student list form. Base the query off the data in that unbound form.

This should solve the problem of automatic requeries without alot of extra work.

 

[waldemar]

Is it certain that there no other easier way? The main problem here is the refer to

Forms![frmToDo]![gdtmVirtualDate]

I already tried to set up a global vb variable to get around this form-field-reference. The problem is the integration in the sql query... (inserting a Public gdtmVirtualDate causes some sort of 'I don't know the name of that variable' error). If there would be a way to get the date into the query differently I would only need to update the queries. Some other object reference (DBEngine? Workspace? Application?)

Regards
waldemar

 

[cmmrfrds]

You can reference a function in a query.

Set up a function that returns the global variable from the standard module. Something like:

Public mydatevar as date
Function ReturnDateVar() as Date
ReturnDateVar = mydatevar
End Function

'- in some event
mydatevar = Me.AfieldfromtheForm
Dim sql as string
sql = &quot; Select * from table where mydate = &quot;
sql = sql & &quot;#&quot; & ReturnDateVar() & &quot;#&quot;

 

[waldemar]

Will replace this soon with somewith like

function replaceDate(strSQL as String) as String
do while instr(1,strSQL,&quot;#&quot>0
etc...
loop
end function

BUT *********** §)(!(/$&quot&quot;§&quot;=$&&quot&($&quot;$

The funny part doesnt stop yet. When I was so euphoric about the hard coded date having worked instead of the Forms![frmToDo]![gdtmVirtualDate] Value I did not replace and try it out immedately in ALL 30 queries but some key queries.

Result: Replacing the hard coded date works in 50% of the queries as we discovered (indeed all of the parents queries, but none of the students'). It does not work yet in the above Students query:

SELECT *
FROM ((tblStudents LEFT JOIN tblStudentsBus ON tblStudents.strStudentID = tblStudentsBus.strStudentID) LEFT JOIN tblStudentsMedical ON tblStudents.strStudentID = tblStudentsMedical.strStudentID) INNER JOIN tblUserStudentsSelection ON tblStudents.strStudentID = tblUserStudentsSelection.strStudentID
WHERE (((tblStudents.dtmLeavingDate)<=#06/20/02#) AND ((tblUserStudentsSelection.strUserID)=[application].[currentuser]));

Am I blind here? Whats the problem with this one, that causes a requery?

Regards
waldemar

 

[cmmrfrds]

What is in the link properties between the subform and the main form? If the value in the link changes that could cause a requery on the subform. If you right click on the upper left hand corner of the subform, it should show the link properties.

 

[waldemar]

They are not linked, otherwise the (continuous) subform would display only one entry.

 

[cmmrfrds]

Okay, how do you requery the subform when going to the next record on the main form. A typical mainform to subform would have the primary key of the one side of the relationship linked to the foreign key in the many side of the relationship, which is the subform. Do you have this set up differently?

 

[waldemar]

In this case there is no subform requery. The subform's purpose is only to display all entries:

http://www.rixcafe.de/frontend.gif

Regards
waldemar