Form subform for data entry Results

I have 3 linked tables (SQL tables) and I want to use a single form to input data that will be used to populate the 3 tables. SQL will auto-number the PrimaryKey in each of the 3 tables, but I don't know how to construct the Append Query so that Tables 2 & 3 will get the foreignkey value to Table 1.

Table1
Patient_id (primarykey)
First_Name
Last_Name

Table2
Addr_Id (primarykey)
Patient_id (foreignkey to Table1)
Addr_1
Addr_2
...etc

Table3
Insuance_id (primarykey)
Patient_id (foreignkey to table1)
Carrier_Name
...etc

My input Form will contain all the fields necessary for the 3 tables. (NOTE: the form will NOT be providing the 3 primarykeys, as SQL assigns these.)
I assume I will need 3 separate Append Queries.
Writing the first query to populate Table1 is easy.
How, though, do I write the Append Query for Table2, so that Table2.Patient_id will pull the value of the just created Table1.Patient_id ?

I know the easiest approach would be to create a Main/Subforms setup to accomplish this. But with that I cannot figure out how to make Table 2 and Table 3 REQUIRED. (ie I do not want a Table1 entry to be created with a corresponding Table2 and Table 3 entry.)

Thanks in advance
Doug

Hi folks

I have a form that holds 'activity' data. There are about 5000 'activities' in the table.

In a separate table, I have a comment field. Bascially, it allows the user to associate a comment related to a specific activity with the activity. A unique key ties the two tables when I need to run a query.

Not every activity has a comment entry. Some do and some don't

In the activity form, I added the small comment table as a subform. I use the o_Current event to make sure that when the user navigate from one activity to the other, the subform and the activity form stay in sync.

to add a new comment related to a specifc case, the user clicks a button on the activity form, a small modal form pops up and the user enter the new comment. On close, the new comment data is written to the comment table along with the unique key. Sometime, I do append only.

Question:
After the user enters the comment in the modal form and hits the OK button, the modal form closes and the comment is written on to the comment table. To see the comment in the current Activity subform, the user must navigate one record forward or backward and then back to the record.

Is there a way to refresh the Comment subform iside the Activity form with the new comment info as soon as the user clicks the OK button on modal comment entry form?

After hours of fighting "Write Conflict Error" I settled on this schema described above. Seems to work fine, but the user would like the new comment entered appear in the main form right away.

Thanks for you help/suggestions.

Among my DB forms is one which is opened with a DoCmd.OpenForm as either Edit or ReadOnly. The form and subforms as developed have property settings of dataentry=false, Edit=true,delete=true,additions=true.
If a user with appropriate authority logs in, the form and subforms should open with Edit privileges and have the properties described above. All other users will open the main form as ReadOnly. I'm not sure what that does to the property settings in the subforms.
However, from time to time, when the DB is opened, the subform properties will all be set to false, i.e. no editing, no additions, deletions, or data entry. Once that happens, I have to open the subforms in design view and reedit and save the properties. Opening the form while signed in with authorization doesn't return the properties to the Edit mode.
I just ran a test using openForm with Edit and then with ReadOnly and there wasn't any change in the property settings above in the main form of the test. I didn't try to extend the test to subforms.

Any guesses as to what I'm missing?

Thanks for any feedback.

Hope someone can help, i have a form with three cascading combo boxes, this then goes to and additions only subform, you can enter the data ok but when you want to save the data an error message comes up " the microsoft jet database engine cannot find a record in table with key matching fields "IDPPE"".
I think i could have something to do with my relationship table, as i an new to this i am not sure.
I have included pics of the error message and relationship table also the data base.




Attached Files PPE Washing development working backup_latest.zip (206.0 KB, 1 views) Reply With Quote 05-30-2012, 05:06 AM #2 R_Badger Knows a few tricks Windows XP Access 2003 Join Date Feb 2012 Location Suffolk, UK Posts 255 I have had a look and fixed it, you had a hidden field for the ID for the garment which was blank, i set it to default to text31, and now it appears to work, if you check your table you should be able to easily find the test entries i added

The fixed file is attached below Attached Files PPE Washing development working backup_latest.zip (63.2 KB, 0 views) Hope that helps!

R Badger

If your problem is solved please use the threadtools to mark it solved

I am having some trouble figuring this out. I have two tables, one for "Parts" and the other for "inventory". Each box in inventory has data that needs to be entered, such as Part Number, quantity, Revision, location, etc. Parts and inventory are keyed on [Part No].

I would like to enter inventory records on a form and in a subform display all inventory records already entered for that part number. Basically I would like an add button and a save button but am not understanding how to do this - work with the record data and then save. Once saved I want this record to be added in a running list of inventory for that part.

I am having trouble with getting the list to display because I am working with entry into the list but am not sure how to setup add record and save record code. How do I work with the data and then save it and have it pop into the running list of inventory for that part??

In a previous job, I used Access 97 quite regularly. I was adept at designing queries and reports. It's been a few years since I have used it however, and even longer since I actually had to design and create a database. I know the data I'm working with now is an excellent candidate for an access database - we have contract providers most with multiple staff contacts, multiple contracts, and multiple locations. However, not all providers have multiple locations or staff. Some providers are just individuals with one or more contracts.

I think I have the data divided up between appropriate related tables but I would greatly appreciate a more knowledgeable pair of eyes giving it a look-see.

Overview:

tblProviders - This table is sort of the main enchilada. Each provider is one entity, with multiple pieces of additional information. I think Address1 through Phone fields can probably be deleted, and the primary address assigned to the primary contact/location? This table populates the main form (frmProviders) for searching, data entry, etc.

tblStaff - This table populates a tabbed sub-form on the main form. Many providers (about half of 300 total) have multiple staff contacts. Right now, this table includes a lookup column for contact types, but as I understand this is the not the best way to do this, I will probably move these values to a related table.

tblContracts - Each provider has multiple contracts either at the same time or continuously expiring and renewing. This table populates a tabbed subform on the main provider form.

tblContractTypes - This table holds the short list of contract types. I imagine I'll move the contact types to an arrangment like this as well.

tblLocations - For about half the providers, there is only one address. But for the rest, there are multiple locations that either correspond to one staff person, or there is a different staff manager for each location. This table populates a third tabbed subform on the main provider form.

TIA for any advice!
Jackie Attached Files providers.zip (78.8 KB, 22 views) Reply With Quote 03-07-2012, 10:36 AM #2 mitch455 Novice Windows 7 64bit Access 2007 Join Date Mar 2012 Posts 8 I forgot to mention that the attached database has half dummy/half actual information in it. I imported an excel spreadsheet and not all the fields have been filled in or cleaned up. Record 254 is a good example of a main contractor with multiple staff and multiple locations.

I could breeze through this database that I am building if it didn't have to be
a web based database. I am having a hard time finding answers and now questioning my design do to the difficulty of finding answers specific to web based databases.

I can answer all my questions by building my data table with over 50 fields, but I was hoping to split it up a little. To split it up a little The tables should be one to one relation.

One problem I am having is the default "ID" field in one of my subforms keeps causing the "Enter Parameter Value ID" dialog to pop up. I can't get ride of it.

In Web database you don't get design view of anything, and I am having a hard time navigating to properties of anything. Are there any books written for Access 2010 exclusively teaching about web databases? I noticed that
you don't get to view relationships, but you create them through a lookup field instead.

Currently my Data Entry form contains 5 tabs
tab 1 has 7 fields
tab 2 has 8 fields
tab 3 has 11 fields
tab 4 has 15 fields (currently a subform)
tab 5 has 10 fields

Should I make it into one big table?

Hi,
I have databse with two form.
mainform+subform

main form with shopname
and sub form shop_phone,address

when data entry prevent diplicate and view all info with box massge diplay to me.
I need code for above function
---------------------------
see attched file and plz do that code in my file Attached Files shop.accdb (468.0 KB, 4 views) Reply With Quote 08-22-2011, 09:23 AM #2 Robeen VIP Windows XP Access 2010 32bit Join Date Mar 2011 Location Tulsa, Oklahoma. Posts 1,500 Miziri,
It is difficult to understand your question because your language is a little unclear.
Could you tell us your problem using different words?
Perhaps that will make it easier for us to help.

Hi, I posted something recently, but hopefully I might explain it a bit concisely this time!

I have a parent form and a subform. I choose a year end from the parent form (eg. 2008/2009).

In the subform there are four tabs, each containing a different form and each linked to the 'year end' you choose in the parent form.

I am able to complete all the data entry fine, it all links back, etc..but when I exit out of the parent form to the previous form and back again, an empty addition record is created for the same year end. For example for 2008/2009 I have one record that is data full and the other that is completely empty. Each as its own unique ID?

How do I stop this? so these records stop being created? Any advice greatly appreciated

Lupson2011

I am trying to create a form that will allow a user to enter sales forcasting information for each month of a given year. I have created a form that contains the following fields: Client Name, Year and Representative. I have then created a subform that is linked by the client name and year. I need to be able to have each month of the year (Jan-Dec) appear automatically on the subform so that the user can enter information for that month. Each month of data should be each own record. If I create a label for the month, I don't see how that data will transfer back to the table. I have created a drop down box; however, would rather have 12 lines appear-one for each month where the month is automatically populated. I have attached a copy of the form and subform so far.
I would greatly appreciate any help!!!! Attached Files Draft Entry Form_Forum.pdf (261.6 KB, 2 views) Reply With Quote 06-03-2012, 11:43 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,115 I think your issue is similar to thread http://www.accessforums.net/access/m...orm-25302.html and that options are the same.

Dear all,

First off let me admit that I am a novice at Access and am well aware that my question could be stupidly simple, so my apologies if this is the case! That said, I'm also at a complete loss for how to proceed..

1st, I would like to add a field to a form where I can input data to records from a second table, and then have values from the first table automatically included in the new record in the second table. I can do this manually with a subform but want to avoid extra data entry where possible.

2nd, if to replicate the information in this way is redundant, then how do I ensure that any reports I do with the records from the second table will pull the values from the first table?

A bit of background: Table1 contains info for lectures recorded. Table2 will share some of Table1's info (unique values like who gave the lecture, the language it's in, etc), but will be used to house info on specific moments in those lectures that we will want to return to later. I would just include these moments as a field in Table1 except that there may be more than one per lecture and I understand that reporting would therefore be more difficult.

Any assistance to this noob would be hugely appreciated!

Sorry if the title doesnt make any sense but im really struggling with this one! I don't have extensive knowledge of Microsoft Access and everything I have learnt is self taught so go easy with me.

I have a database with the 2 main tables; tblContracts and tblEvents. It is linked with the primary key- ID in tblContracts to ContractID in tblEvents so its one one to many relationship.

I have 3 main forms, frmContactDetails, frmEvent subform and frmEvent. the frmContactDetails is my main point of call and has to 2tabs, 1 which has all the general information of the contract and another tab that shows all the events for that contract. This comes from a subforms (frmEvent subform) and is just a basically summary of any events for that site in datasheet view. I added a button that allows you to open that record, or create a new record and opens the form frmEvent as here you can enter more details.

If an event has already been created its fine because the ContractID already links to that tblContract. However when you click new and are presented with the frmEvent, the field for ContractID is blank, meaning it doesnt link to that contracts record.

I have an open form macro with a where condition to take it to the specific record, I wonder if this is where it needs to be sorted out.

I have attached some images so if anyone has any advice that would be great.
Thanks,

Jamie Attached Thumbnails       Reply With Quote 02-17-2012, 07:13 AM #2 jzwp11 VIP Windows 7 64bit Access 2010 64bit Join Date Jun 2010 Location Dayton, OH Posts 2,892 I added a button that allows you to open that record, or create a new record and opens the form frmEvent as here you can enter more details Since viewing an existing record is different from adding a new record, I think you need separate buttons.

To add a new record, you have to open frmEvents with it's data entry property set to yes and you have to pass the value of the key field (contractID) to the corresponding control on frmEvents. You will only be able to add 1 new record because if you try to add a second, the contractID will not carry over to that second new record.

Hi everybody,

I've recently been experiencing some strange behaviour when editing data in Access forms: Sometimes changes I have made to a record wouldn't save even after going to the next record. Let me explain how my forms look like: they typically consist of a main form containing several subforms. Normally when working on a record I begin editing data in the main form and then jump to one of the subforms. In most cases, I then go back to the main form and after that switch to the next record.

Now sporadically it happens that the changes I made to the main form "jump back" to their original values after leaving the subform, i.e. I add or modify an entry in the subform and as soon as I set the focus back on the main form I can see the values I had changed just a few seconds before virtually switching back to the values that the entry had before editing it at all. Since most of my fields in the main form are combos I have only observed this weird behaviour with combos.

Even after leaving the record and loading it again I won't be able to make cahnges to it. Only a restart of the complete Access application will enable me to modify the record. Oddly enough, changes I made to the subforms within the same record ARE saved.

This is really getting problem for me as I can never be sure whether it has saved the changes I made or not. What is even more confusing is that this behaviour seems to occur only sporadically with no obvious reason triggering this "mode".

Something that may be unrelated but also occurs sporadically is that it doesn't prompt to save changes I made to a form when closing it. On other occasions, though, it won't allow NOT saving a query even if want to close it without saving changes.

This is really confusing. Has anyone witnessed a similarly strange behaviour before? Is there maybe a known issue related to that?

Thank you in advance for your help!

Hi All,

I have a database to create for a friend and been some years since I worked with MS Access so realising how much I have forgotten. I have made a number of attempts with the aid of google but cannot get the database to work as I need it. So I was wondering if anyone could make some suggestions on tackling my problem?

First I have to create a data entry form to capture Client details which I have built no problem for phone number, address etc. The problems start when I need to associate this client record with a partner or Children. I have create a Partner details table and Depandents details table but cannot get the relationships right with my Client ID primary Key?

I have tried subforms which works fine for the partner but some folk have more than one child and creating these records do not associate with the Client records. I have also tried creating a button to "add partner" but the form that loads does not import the Client ID and thus not assocaited.

Any advice on initial structure?

Once I crack this I will add that service they have in my friends line of business but have hit a brick wall straight out of the gate.

Thanks for reading.

Hi ALl,

My friend asked me to take on a project and build a database t replace out his paper based file system but I am hitting a brick wall . . .have taken many runs at it.

The basic is that the database is to hold contact details of clients and their realated services they have taken up. So the first step is to normalize my database and have a good think about the design - engaging what i rember from 10 years ago. So:

I have 3 tables relating to the clients
- Client details
- Partner details
- Dependants details

For services
- Pension
- Mortgages
- Life assurance
- and so on

Each table has a primary key of their name then ID. However I cannot build or edit a template so that when I have Client details form open, the related partner/dependats details enter do not tally up. I have tried the client ID as foreign key and each tables primary in the client details table, don't work. HELP.

The closest I got was to create a form with 3 different tabs and tab contained each data entry form (client/partner/dependants) but agin they went in unrealted.
I tried putting a button on the client details table to open the partner details table and enter details if they had a partner. Didn't work.
Tried a subform but 2 subforms looks terrible and the dependants would never create properly.

I am off to read the tutorials on this form as other I have used on Youtube or website still has me struggling to find the gap in my logic.

Hoping someone can just point is out.

Thanks

I'm having problems working through the logic of the tables and relationship set ups on this database.

What I am trying to do:
I have Buildings that may have many posts attached to it.
For each post there will be many inspections

I'll have a data entry form where the inspection will be inputted but I need all the underlying connections to run reports. I'd like to have the building number and address show on the form to confirm the post number is being entered correctly, which I thought I could do a subform to show.

It seems I'm making this more complicated than it needs to be.

See the attached. Attached Files Doc7.doc (374.0 KB, 2 views) Reply With Quote 01-25-2011, 02:06 PM #2 orange VIP Windows XP Access 2003 Join Date Sep 2009 Location Ottawa, Ontario, Canada Posts 4,207 There are a number of models here
http://www.databaseanswers.org/data_models/index.htm

Take a look at the business rules user requirements from a couple of models and create some rules/terms that represent your situation.


What is a Post? Can you tell more details of your requirement.

Ok, this is a little complicated, so here goes:

Customer Info Form = customer information

Work Order Sub-Form = a subform within the customer info form that lists all work orders associated with that customer

Details form = a separate form that should show a detailed list of tasked completed for a given work order number. There is no primary key for this table and form as I need to be able to list several records related to a single record from the "Work Order Sub-form".

What I want is to be able to double-click on a work order on the "work order sub-form" and have the "Details form" appear with all entries associated with that particular work order number.

I'm using a macro with a "where" clause to accomplish this (which usually works) but for some reason this set up is causing a pop-up window to appear asking for a work order number (which should already be selected when I click on the work order record, right?), then opens up the form with no information. I have the macro set to filter out all records except the ones that match the work order number of the currently selected record on the "Work Order Sub-Form". Even if I enter the record I want on the pop-up window, the "Details Form" comes up blank, like it's stuck in data entry mode.

Any ideas? Need clarification?

Hi, i'm creating a search form giving the end user a range of controls to use when filtering/searching data. See the image.

But, i think my range search (using the textbox) to put in a lower and upper limit...is preventing this from working. In fact, when i put data into all the controls, no data pops up in my subform. How can i get around this?

My query data source can also be seen...showing you how i've handled teh null entries. (Hmmm..perhaps i need to put in a null 'handler' for the two textboxes??)

Please advise where you can...

Many thanks
tim Attached Thumbnails     Reply With Quote 11-19-2012, 03:35 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,108 LIKE operator is only useful with wildcards: LIKE [Forms]![frmSearch]![txtSurnameSearch] & "*"

Is [Member ID] a text or number?

Why are the controls under the [Member ID] field named with 'StartDate' and 'EndDate'?

Is data in each of these fields required?

Hello All,

I managed to establish a relationship how I wanted it. For example when I am typing a record in table 1, I get the ID pulldown PK, where I can type subrecords there. When I refresh I see all the records I typed in Table2 for that record 1 in my original table. However in Table1 when I am typing in new record for example record 2 and want to pulldown again to write my subrecords in to reflect Table2, I get this error " This expression is typed incorrectly or it is too complex to be evaluated " #Name?

Can anyone see, why its not letting me record subrecords in Table1 on line 2. But it worked on line 1, since I see 3 subrecords in my second table.

Any help is appreciated,

Thank you
Nicholas Attached Thumbnails       Reply With Quote 05-24-2012, 11:34 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,108 Don't know, I have never done data entry of child records directly with tables. I use forms. This situation calls for a form/subform arrangement.

Want to provide project for analysis? Follow instructions at bottom of my post.

Another question that is probably a bit basic for old hands. Sorry... Could you give me a bit more guidance?

This follows from another question (a little guidance...) that I have marked as solved. A bit premature, as it turns out.

I'm trying to build a database to collect and process information about model aircraft contests. I've attached a snip of the relationships, following the advice I got earlier. Also attached is a snip of a sample screen with completed flight details. And a snip of the resulting entry in the table "tblflighttime" (it's the bottom record).

My aim is to build a form to use during the contest, for an operator to select in succession: contestant, model, event (the three combo boxes, choosing data from tables already entered) then click a radio button to identify the specific flight that the data is being recorded for, and enter the flight details (time and landing yes/no). The output is then a record in tblflighttime with fkeventsenteredid coming from the bound field on the bottom combo box, and fkflighttype coming from the option group with the radio buttons.

In the QBE the form has its record source as tblflighttime, with criteria for the two foreign keys set to the relevant combo boxes. Yet another snip attached.

The picture shows text fields to the right of the cascading combo boxes showing the actual bound values from the user's selection: they are all the expected keys to the data that is in the tables. So far so good.

The resulting record however records only the flight details, and not the foreign keys to complete the relationships.

I am sure this must be pretty basic, but I am still struggling with where to look to make those two foreign keys appear in the record.

I've tried setting the control source for the combo box and option group to their relevant table fields, but Access then won't let me make selections on the screen. OK, you knew that would happen.... I've still got a lot to learn

I'll be really pleased to get any pointers to how to link the form properly to the table! And of course, let me know if I need to provide more information.

Thanks in advance Attached Thumbnails         Reply With Quote 11-17-2011, 05:54 AM #2 jzwp11 VIP Windows 7 64bit Access 2010 64bit Join Date Jun 2010 Location Dayton, OH Posts 2,892 I would typically use a subform in the form you show since the flight info is on the many side of the relationship. The form-subform should link via the key fields (and the flight time table would populate), without that link the key fields will not populate as you indicated. Now, if you want to keep the form as is rather than using a subform design, you will most likely have to use an unbound form and thus would need code behind your save button that in essence runs an append query. That query would pull the values from the appropriate form controls and store the info in the flight time table. The drawback of this is that you would not be able to use the form to see previously entered data.

Any chance you can post your actual database? Screenshots only tell part of the story. I would recommend running the compact and repair utility on the database, zip the database and post the zip file. (this will keep the size of the file manageable)


Not finding an answer? Try a Google search.