Solved

data entry form in access

Posted on 2014-10-31
6
268 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 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

820 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