We help IT Professionals succeed at work.

Syncronizing 2 subforms on unbound mainform OR help with Listbox

Jr
Jr asked
on
524 Views
Last Modified: 2013-11-28
Access 2K - Want a form that displays multiple records for scrolling and editing certain fields.
The Table has many code fields so i  use a query to display descriptors of these code fields. But the query is uneditable    Attempted solutions and problems:
I tried a single mainform (unbound) containing two subforms side-by-side. Both are continuous subforms.
SubFrom1 is for editing records - bound to a table  (on Left side of mainform)
SubForm2 is to display field descriptions about the records in SubForm1 - Subform2 is bound to an uneditable query. SubForm2 has a vertical scrollbar and to the right of subForm1.
Fields common in both subForm Data sources are SSN and Name, both are text fields.
Looking for a way to syncronize the records in the two subforms so they are in sync when they scroll using the scroll bar in subForm2.
OR -my other attempt was placing a listbox (next to the editable fields) on a continuous mainform bound to a table. The listbox displays fields from the uneditable query - but can't figure out how to get rid of the record selectors(up-down buttons) so that listbox displays the data for that specific row.
Help please...  
Comment
Watch Question

Commented:
Base subform2 on your table and
In the record source for subform 1:

SELECT subform1table.eventnumber, subform1table.field1, subform1table.field2 where subform1table.eventnumber = subform2table.eventnumber

Commented:
Eventnumber would be the fields that match between the two tables or queries.
Jr

Author

Commented:
Wiswalld  apparently I'm not following your instructions properly  or not properly refrencing in the select statement.
MainForm has Sfm1 and Sfm2
Sfm2 record source is Qsfrm2  which provides the descriptors for the codes in TblAPP.  
SQL for tQsfrm2 is below:
 SELECT TblAPP.SSN, TblPerson.Name, tlkpOccCode.Description AS Occ, TlkpPayBasis.Description AS PayBas, TlkpPayPlan.Description AS PayPlan
FROM (((TblAPP LEFT JOIN tlkpOccCode ON TblAPP.OccupationCde = tlkpOccCode.Cde) LEFT JOIN TlkpPayPlan ON TblAPP.PayPlan = TlkpPayPlan.Cde) LEFT JOIN TlkpPayBasis ON TblAPP.PayBasis = TlkpPayBasis.Cde) INNER JOIN TblPerson ON TblAPP.SSN = TblPerson.SSN;

In the recordsource for  subform1 (Sfm1) I placed the following   
SELECT [TblAPP].[SSN], [TblAPP].[DutyStatus], [TblAPP].[ApplicationDate] FROM TblApp WHERE [TblAPP].[SSN]=[QSfrm2].[SSN];

When attempting to open MainForm  a popup box appears asking for the parameter value of Qsfrm2.SSN

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Jr

Author

Commented:
Wiswalld - thanks for your efforts- got this working with your help
but not what i was hoping for - this displays only the sfrm2 record that matches - i wanted all sfrm1 and sfrm2 records to scrol in sync...as a continuous form - spreadsheet like
my original query was not editable - thus the reason for side by side subforms
I decided to redesign my query to call a function that returns the code definitions - now my query is editable and contains the data needed by the form.
Seems to me Access should include an object that would be like a listbox without the selectors and could be used on forms in lieu of txtbox.  Or maybe it already has something that works that way?

Thanks again

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.