Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

data entry form in access

Posted on 2014-10-31
6
Medium Priority
?
283 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

772 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