Solved

data entry form in access

Posted on 2014-10-31
6
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

738 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