Solved

data entry form in access

Posted on 2014-10-31
6
259 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 84
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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 200 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

932 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now