subform edit issue

[Okiguy1955]

I am new to a VBA and only a little exposure to Access 2010. I’ve been battling a problem for a few days. I have a mainform with 2 subforms. Data in subform 2 is filtered to the record selected in subform1. I have no problems with data between the mainform and subform1. I am using Access 2003 with a SQL Server database.

The particular problem I am having is when I click in subform2 to add/edit data, the last record I touched in subform1 also goes into edit mode. I’ve tried googleing this various ways but it is difficult to describe in a search.

I’ve tried setting access properties between subform1 and subform2 for 'on enter', GotFocus/Lost Focus, On Load/UnLoad, update/insert. Added VBA code setting: setfocus, doevents, and requery, in various places, but nothing seem to work. No matter what I add/change subform1 or subform2 , it goes back to edit mode in both subforms.

I have no one at work who can assist. I really need someone to point me in the right direction.

 

[MajP]

Please provide the following.
Subform 1: recordsource
subform 2: recordsouce
Any event procedures in subform2

 

[Okiguy1955]

Hello MajP: Here's what you requested.

Record source for the main form is a query (q_f_BusRoute)
SELECT DISTINCTROW t_BusRoute.*
FROM t_BusRoute
ORDER BY t_BusRoute.SchYearAbbr, t_BusRoute.BusRouteNum;

Record source for subform1 is a query (q_f_BusRoute_s1)
SELECT DISTINCTROW t_BusRun.*
FROM t_BusRun
ORDER BY t_BusRun.BusRunCode;
Link Child Fields: BusRouteid
Link Master Fields: BusRouteid

Record source for subform2 is a query (q_f_BusRoute_s2)
SELECT DISTINCTROW t_BusRunSched.*, t_BusStopLocation.BusStopLocation, t_BusStopLocation.BusStopTown
FROM t_BusStopLocation INNER JOIN t_BusRunSched ON t_BusStopLocation.BusStopLocId = t_BusRunSched.BusStopLocId
ORDER BY t_BusRunSched.BusRunId, t_BusRunSched.BusTime;
Link Child Fields: BusRunid
Link Master Fields: [BusRoute_s1].Form![BusRunid]

 

[PHV]

I'd get rid of all those DISTINCTROW ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Okiguy1955]

I removed all the DistinctRow, but same results. I also have a requery of the second subform in the Current event of the first subform, no change in the results, it goes back to edit mode in both subform1 and subform2.