Use one form for data entry into multiple tables

Hello, I'm pretty new to using forms and VBA. I recently inherited a database that stored all data in a couple of tables, and used a single form for data entry.

I would still like to use a single form with multiple tabs for data entry. However, I tried to restructure the tables to make queries easier, coming up with the following relationships:


The most important structures are a Directory table, a Specs table, Project Directory table, which meet in a Prospects table. The Prospects table exists because a client could have more than one set of specs or projects, and a project could have more than one client associated.

My question is, how can I create a form that allows for easy data entry? For instance,
Tab 1 would input data about a single client into the Directory table. It would also update the Contact_Notes table (a table consisting of just a memo field and the client_id number... I split it off to prevent corruption, but conceptually it is part of the Directory table.)

Tab 2 would create a new entry in Specs with the client's id number, then the user could fill in info about the client's requirements.
Tab 3 would have the client id and specs id already set, and new dropdowns with the lists of available staff and projects, creating a new record in the Prospects table.
Tab 4 would record info about the eventual sales made based on a prospect.

I have already tried embedding a subform, which works more or less, but looks confusing to the end user, even after I removed the navigation bars and most other formatting. I also tried making a big query of all the data out of all the tables and using it as the recordsource. Though that populated the form nicely with existing data, I was unable to update it (probably because the ID numbers were not being set properly.)



Would anyone be able to help me make the embedded subforms look better? Or else there must be a way to use VBA to code an equivalent to "link master fields" for the fields of foreign tables that appear on Tabs 2, 3, and 4.
Looks matter because another person will be doing data entry and working with the database.

Thank you very much for any help you can give!
Jean


Post your answer or comment

comments powered by Disqus
Hello Everyone,
I've searched for posts regarding this issue and have only found old ones related to prior versions of MS Access.
I'm using Access 2007 and I would like to create one form for data entry into multiple tables. Is this possible?
I've attempted to add the fields from the various tables, however it will only work when the data source is set to one table and will not input information into more than one table.
When the data source is set to multiple tables, it won't allow any data entry at all.
Is my only option to make seperate entry forms for each table or is it possible to create one main form, that will input data into several different tables?
Thanks for your help!

Hello,
I'm trying to use a form for data entry with a product, then several options for that product. Tables are products, standards, and ProductsandStandards. In the form I'm trying to set it up so that I enter the product name, then several combo boxes for the standards. Trying to figure out how to hold this data, so that when I click my button for done entering data, all this info gets placed into the ProductsandStandards table. Thoughts?
TIA
cherkey

Hi folks,

I have created a data entry form with one key field (Customer ID) used to identify customers. I now want to be able to use the same form to view existing entries.

In the View mode I dont want to allow the users to change the Customer ID field. In the data entry mode they must be able to enter a Customer ID.

What is the best way to achieve this effect with a single form ?

Also, from my main switchboard form, how do I open another form directly in data entry mode ?

Thanks very much for any help,
Peter.

I am learning to use listbox in form for data entry purpose so I can select multiple values from another table. I assume once I select the multi values from the listbox, the table for storing data will be updated automatically. However, it doesn't work. Is there a code I need to put it the afterupdate event in the form? Can someone tell me the code I should use? I am new to access and do not familiar with SQL or programming. Please help!

I am using Access 2000 and have had this database working for several years. I have used a form for data entry into my database. The form is populated by a query that brings together several related tables.

A couple days ago I accidentally perfomed a sort on the form in the datasheet view and save it and now I can no longer update any of the text fields in the form. The sorted query had populated the datasheet/form view, so I'm sure that somehow this action (sorting a form field) has caused my update problem.

I am open to any suggestions. Please let me know what you can suggest that will help me over this hurdle. Thanks.

Hello,

Just wondering .... is it possible to update/data entry on multiple tables using a data access page?

If so, how?

I've been playing around and searching for examples but can't seem to get anywhere?

Any assistance is appreciated

Thank you in advance,

Arv

Morning all!
I've done some searches, but can't find this one, so:

I need to enter multiple new records, all using the same "Week Ending" date. I would like to use a form for the data entry and enter the date only once.

With this in mind, I created a Single Form for the Week Ending field. I inserted a subform (Continuous Form) for data entry of all other fields, and referenced the Week Ending field in the main form.

The problem I'm having is that the date doesn't transfer to the subform for first record I enter. After that, the date shows in all the other records as it should.

Any idea what I've done wrong.

As always, thanks for taking the time to look at this.

BeckieO

I created a query in order to create a form for data entry.

When I enter a new record on the form, I am unable to enter data into the fields connected to one of the tables.


No going off for me, which is not a surprise.

Thank you in advance for helping me out. I am seriously struggling with Access even after training and have found these forums to be my only resource for help.

What is the correct form strategy to ease numerical data entry into a table that requires multiple records?

tblHourlySales tracks the sales per hour of our store. Each record has 4 fields:
pk
CalendarDate
Hour
Sales

At the end of the day I want to be able to enter sales using a 10 key pad to make entry quick i.e.
105
210
358
etc

So by typing a column of sales numbers I get,
1 7/26/2010 11:00am $105
2 7/26/2010 12:00pm $210
3 7/26/2010 1:00pm $358
etc
What's the best strategy to make this easy? I have come across several potential solutions, but want to get it right the first time.

Thanks!!!!

This is my first attempt at making a relational database to fit my needs, so sorry if this is a basic question. I am attempting to make a direct marketing database with multiple tables that will accommodate the import of large data sets (csv.)

I have developed the table structures, made various queries and forms, and have modified tables with various action queries. My problem is that I don't understand how to import a large csv data set to more than one relational table in the database (Ie. splitting the csv into multiple tables that are relationally joined.)

As an example, I have a table for "Agencies" that contains all agency contact information, I have a second table for "Demographics" that charts the demographic data for each agency. I also have a "Contacts" table for charting any contact that I may have with the agency.

The problem is that the external csv data set (import data) may contain both the "Agency" data and "Demographic" data in the same table. I need to import it to the separate tables in a way that keeps the data integrity.

I thought that using a query joining the tables would work with an append function, but it seems that append only works for one table at a time.

Any help or suggestions would be appreciated.

Good morning. I wonder if anyone can help me understand this. I have now read most of the 1600 page Access Inside Out, all of Access for Dummies, All of Access Plain and Simple, and have read a good part of this forum, and relationships still confuse me (just ask my wife).

I am building a database with multiple many-to-many relationships. I have a main table, linked with junction tables to three other tables.
I need to link all of the tables (conditionally, as explained a little below) in a form for data entry (whether tab control, form/subforms, or however I am unsure). Simply stated, I envision the form having document info entered, then a command box opening people form if clicked to add one or many related people (the author, an expert discussing it, etc.) , then if there are other "exhibits" to the documents (one or many 'sub-documents') that Exhibits form can be opened to enter information about that/those.

I can get two or three tables together onto a form, but the junction tables are not being updated/populated as I enter new data into the forms... I don't know if the junction tables just have to exist back at the ranch, or have to be brought into the form in some manner...and how specifically to enforce the links between tables to update and populate as each new document with details is entered.

I am attaching the database as it currently stands. Any suggestions on this would be GREATLY appreciated.

I keep track of 12 servers' updates and maintenance manually in notebooks. This is very tedious, and I would like to create a database for this. I would like to set up a form for data entry only, and because I do the same thing to multiple servers at the same time, I would like to create new records for each action taken without having to select each server one at a time.

I've looked into both multiselect list boxes and option groups with check boxes, but I can't figure out how to populate the table with all of the selections. The table and data entry form are very simple - date, server, action. So, I would like to select X number of servers, enter the date and action, resulting in X amount of new records.

Would someone please point me in the right direction? Thanks!

Ok...... I got my tables setup as dicussed in here .

Relationships seem to be fine and dandy. But no matter what methods i try, i can not figure out how to make a form for data input that works. At times i think "I got it", but then Aceess complains about one field or other not being able to update.

Can someone point me in the right direction?

I have:
tblItemID ItemID (pk)

tblPartID PartID (pk)

tblBOM [bill of matreials]
ItemID (pk1)
PartID (pk2)
Quantity

What i would like to do is have a form that would allow entry something like this:
Enter Item# *********
Enter Part# *********
Enter Qty per Item ********

I was close at one point. I was using a form with a subform, and it "seemed" to be working. However only for the first entry. I entered Item# and Part# and both values showed in tblBOM as did the Qty. However, when i went to enter additional Part#'s per Item, the existing values were overwritten as opposed to being "added" to the table.

For years, I've used a setup where data is imported into an Access table, and users could use forms to query the data any way they liked. After they select from a series of drop down boxes, they can run a report and print out the pages and begin collecting data from the field. Members of the data collection team knock on doors and ask them predefined questions and record the answers via check boxes on the printed report.

The data collection team brings back the completed pages and then people manually enter the information via Access (desktop) or through the Access web page using sharepoint (Accesshosters). This process is becoming slower and slower and more prone to mistakes fromi those entering the data.

Does anyone have any experience or alternative idea on a better way for data entry into Access? I wouldnt be opposed to something that could scan into another format (Excel maybe?) and I manually import that information back into Access which would be much faster than watching a stack of papers pile up.

Hi all,

I have searched but it has all been fruitless! We have developed a lovely database ;-) which I am now trying to tidy up! Currently we have (for example) a form for users to add new people, and a form for admin's to delete people, make changes to data etc. Essentially these are the same form, one with an extra button (the admin one).

What I am trying to do, is just use one form for this by hiding/displaying the delete button depending on where the form was opened from. Any ideas on the code to use? (Access 2000.... we haven't moved into modern technology yet!) For example, three forms - A, B, C. If I open form C from form A, is shows button A. If I open form C from form B, it doesn't show button A.

Any suggestions welcome :-)

Many Thanks!

Hello -
I have a form that is used for data entry into a table. I would like to have the the text boxes and combo boxes clear once the user clicks on the "Submit" button. What I have now doesn't work. When a user goes into the form, the last entry is displayed. Can anyone help me in figuring this out. Thanks in advance.

Hello -

I am new to forums and not sure how to start this thread - so please be patient. I have however, up to this point, thought myself to be fairly proficient with Access, that is until this issue.

I found a thread (Add Multiple Records on One Form 8/13/10) from William McKinely that is similar to my problem. He was able to get an answer from PBaldy - but it was a little short on details.

I am looking for the details of PBaldy's solution to create / run an append query to pre-populate a table in order to create / show a form for data entry with multiple new records for easy update.

Since I did not know if was appropriate to contact them directly, I thought I would post my request for more information in a new thread.

thanks

I have been through many examples of cascading combo boxes, and have succeeded in creating a form in my database that works, but I don't know how to use this form for data entry instead of just looking values up.

My database contains:
tblCategories
-CategoryID
-CategoryName

tblSubCategories
-SubCategoryID
-SubCategoryName
-CategoryID

tblVendors
-VendorID
-VendorName
....

tblVendorCategories
-VendorCategoryID
-VendorID
-CategoryID
-SubCategoryID

I need to be able to enter data to the VendorsCategories table using the cascading combo boxes I made in a form. The wizard creates a subform on it's own that does not narrow down choices for subcategories based on category choice.

I'm sure this is an easy problem for most, but I'm stumped and my boss is getting a little sick of waiting on me!

I have checked the forum for security posts and havent quite found what I am looking for...so, here is my question.

I have a database that allows users to input info, supervisers to run queries and reports, etc. Works fine!!! But, the terminal was moved to another place that is out of the way, and we are finding that some users (savvy "wanna be's") are getting into the tables and changing info to give them better percentages (therefore, they will get bonuses!). The problem that I dont know how to get around is that the supervisors are not savvy enough to get around without using the switchboard (that allows them to query the info they need).

So, how do I set up my db that will allow the users to only be allowed to use one form for their input and all the rest of the db is secured with a password, even the switchboard?

I will check back on Monday since it is the end of the day...Thanks in advance for your help.

Noreene

On my switchboard I have a place for people to enter a new client and it opens a intake form but it opens it to a existing client how can I make it so it only opens to a blank form for data entry???

I have come across a boat load of posts in different forums where people tout the advantages of using unbound forms for data entry. There seem to be a lot of advantages to using unbound data entry form, particulary for multi-user systems. Among the advantages is having complete control over the data being added to the database. The major drawback to using unbound forms is, of course, the development time in coding all of the things to fully mimick a bound form. I understand that some people utilize append queries to add records (usually done explicitly with a cmd button).

I have several bound data entry forms in my database that I would like convert to unbound forms to have more control over the data. While I have heard a load of people praise their advantages, I have not seen anyone post an example of how they accomplish this themselves. Searching this and other forums, I have found only vague hints about how to go about it. If anyone has had success with using unbound forms as data entry forms, I would greatly appreciate some tips or an example of how to go about it.

Thanks in advance.

Hello,

Please help!!
I created a split form from a table for data entry, on my form I have a dynamic combo box that get its value from another drop drown combo box. Here is the problem I have, for the first record entry it works fine but for the second entry my dynamic combo box still retain the old value. Please show me how to fix this.

I have made a one table database and use a form for data entry into

that database. I have made a simple search form for searching by some

important fields and use the following like criteria in query designer.

Like [Forms]![queryform].[qname] & "*"

Here query form is the name given to the search form and qname is the

name given to the Name field. Similar criteria is given to rest of the

searchable fields. When I put any criteria like for example a name Ali

in the name search field and click search it gives me the database of

all records conatining Ali. But in other fields where there is more

longer text like memo boxes, which contains full sentences, in such

search boxes even if it contains the words like counseled, referred and I

type these words in their respective search boxes, running the query

doesnt return any results. It only yeilds results if I type the very

first word written in these memo boxes but searching for the rest of

the words doesnt yield any result.

So how can I correct this by writing proper SQL language for such

purposes.

i am a newbie, i use split forms for data insertion in tables in a relational db

i chose the table then click "split form" so that textboxes are binded to the table. however i am facing a problem of the error messages that keep to come up because of a primary key was not inserted or because of the relations between tables or because of a field "Required" property set to yes.

this messages don't go away no matter i did

how to deal with it?
and what is the best way for adding, editting, deleting, or searching for data in such situation?


Not finding an answer? Try a Google search.