[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

data entry form in access

Posted on 2014-10-31
6
Medium Priority
?
285 Views
Last Modified: 2014-11-05
I have 2 tables, 'contact list' & 'transactions', and every few months people in the contact list pay their fees.  What I would like to do is have a form that shows everyone in the contact list with blank fields from the transaction table so that I can enter transactions for the ones that have paid.  My tables are structured as detailed below.
Contact List - pID, First Name, Last Name
Transactions - ID, pID, Date Paid, SessionNo, ChequeNo, Amount


I attach a stripped down version of my DB.

Andy
Stripped-down.accdb
0
Comment
Question by:andymacf
  • 3
  • 2
6 Comments
 
LVL 85
ID: 40415453
I downloaded your database, and don't see the form you're referring to. While we don't mind helping you achieve your goals, we aren't here to do the work for you. Instead, we'll help you when you run into something you can't solve yourself.

I would assume you could create a Mainform/Subform sort of setup to achieve what you want. First, create two forms - one based on the ContactList table, and another based on the Transactions table. Embed the Transaction form into the ContactList form, and link them on the pID field. From there you could locate a Contact using the navigation buttons, and enter a Transaction in the Subform. If you setup the linking correct between the Mainform and Subform, Access will handle the data relationships for you, and insert the records in the correct table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40415454
create a Form/subform combination for your data entry
see this  Create Form with subform
0
 
LVL 7

Author Comment

by:andymacf
ID: 40415478
Thank you Scott/Rey

Yes, I understand your comments and I certainly do not expect you to do my work.  I should have explained that I already have a form/subform scenario, but I was wondering if it would be possible to have a situation where the existing contacts all show on the same form with blank columns for the transactions.  See my basic concept attached.

Andy
Book1.xlsx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 total points
ID: 40415497
You could create a query that includes both tables, and try using that. You would see only a single "new record" line, however, and data entry would be challenging, to say the least.

You could perhaps use a temporary table setup, where you create a table much like the layout of your Excel file and fill that table with data when the form is opened, then base a form off that table. With this sort of scenario you could add "new" lines for each record in the Contact list, and the user could interact with those. When the user closes or saves the data, you'd have to write code to move their changes back over to the live tables.

However, that sort of interface would still be somewhat "klunky", it would seem, and what happens if I need to add two transactions for a single Contact? I'd have to have some way to select the correct Contact, and then you're back to square one - how do you enforce that relationship?
0
 
LVL 7

Accepted Solution

by:
andymacf earned 0 total points
ID: 40415596
Thanks for the additional comments, Scott, I have taken them on board and agree that it will be a clunky solution, so I think I will add my data in excel, then create a linked table with an append script to add to the transactions table.
0
 
LVL 7

Author Closing Comment

by:andymacf
ID: 40423567
Scott's comments made me think about other options, so have thought about another way of achieving my goal.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question