Data Entry Form - Multiple Tables?

I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.

I have an "Activities" table that contains fields:
ActivityID
ActivityName

I have a table of 7 Risk areas which contains fields:
RiskID
RiskName

What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.

This involves the creation of a 3rd table to collect the user input which would contain fields:

MeasurementID
RiskMeasurement
ActivityID
RiskID

I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.

I hope this was clear. Any help is appreciated.


Post your answer or comment

comments powered by Disqus
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

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

I am fairly new to VB and have the following problem.

I have a data entry form that allows the user to select multiple objects from a list box, which then show up in a subform. I am doing this by setting the record source for the subform to an sql query generated in the form's code. I want to create new records in a table for each record in the subform.

The problem is this: I am using data from both the form and the subform to populate the table on the Many side of a 1:M relationship. The main form will update the 1 side. I get an error because there is not yet a record on the 1 side of the relationship. I do not know how to get the table to update with data from the main form without moving to a new record or closing the form.

Any help would be greatly appreciated!!

Here is my code so far:

Private Sub cmdOpenInvoiceInfo_Click()

Dim SubformSQL As String
Dim rstInvPieTable As Recordset
Dim rstPiecesSubform As DAO.Recordset

SubformSQL = Forms![AddNewInvoiceForm]![InvoiceSubform].[Form].RecordSource
Debug.Print SubformSQL

Set rstPiecesSubform = CurrentDb.OpenRecordset(SubformSQL)
Debug.Print rstPiecesSubform.RecordCount

Set rstInvPieTable = New Recordset
rstInvPieTable.ActiveConnection = CurrentProject.Connection
rstInvPieTable.CursorType = adOpenKeyset
rstInvPieTable.LockType = adLockOptimistic

rstInvPieTable.Open Source:="InvoicePiecesTable", Options:=adCmdTableDirect
'how many are in the recordset?
Debug.Print rstInvPieTable.RecordCount

rstPiecesSubform.MoveFirst
Do While Not rstPiecesSubform.EOF
'add records from subform to Inv Pieces table.
rstInvPieTable.AddNew
rstInvPieTable.Fields("InvoiceNumber") = Forms![AddNewInvoiceForm].txtInvoiceNumber
rstInvPieTable.Fields("PieceID") = rstPiecesSubform.Fields("PieceID")
'fill in fields...
Debug.Print rstInvPieTable.Fields("InvoiceNumber")
Debug.Print rstInvPieTable.Fields("PieceID")
Loop


Set rstPiecesSubform = Nothing
Set rstInvPieTable = Nothing
End Sub

Thanks!!

betsyr

Hi Everyone,
There may be a quite simple answer to this, but I am a novice.

I have a data entry form with multiple subforms and subsubforms. People sometimes enter their data and then realize they are on the wrong form or some other such problem and they want to undo all the data entry they have just done.

My goal is to delete the relevant records from the ultimate table that this data will end up in.

The data entry form works like this:

Mainform has this important field on the 1 side of the one to many relationship:
NameDateWeekday

The first subform is based on a query that will provide information specific to that weekday and time of day. It has its own subform which will take that information and allow for data entry for other pieces of information. NameDateWeekday is also on the subforms (the many side of the relationship).

There are 12 other subforms like this for 12 other times of day. The only data that is stored is the data on the subsubforms, which all store their data in the DataEntryTable.

I would like to delete all fields in DataEntryTable that have the same NameDateWeekday if a person clicks on "Do Not Save."

Does anyone know a way to accomplish this?

Carly

I am fairly new to access and have never created data entry Forms in Access.
But so far I have created a Form with multiple combo boxes referencing various tables, text boxes for individuals information, and toggle switches for characteristic of the individuals. The Form was created for data entry purposes however; I am having difficulty linking it to a table to store the data. Is there a way to set it so that there is a table with a column linked to each text box, combo box and toggle switch, which would add a new row of data each time I entered information.
This will save large amounts time if data entry can be reduced to filling in the few text boxes and selecting from the combo boxes and toggle switches. I also believe the combo boxes and toggle switches will increase reliability so that when we are analyzing data we can pull by each of those fields.

Does anyone know if this possible or am I on the totally wrong track?

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!!!!

Hi All,

I have a relatively elementary question for you all…

I have a database that stores information about brochures on hand. Each brochure can come in one of the following languages: English, Spanish, French, or a combination of the three.

I have a many-to-many relationship structured like this:

Products Table
- Product ID

Languages Table
- Languages (list of languages here)

ProductLanguage Table
- ProductLanguage ID (autonumber)
- Languages (linked to the languages table)
- ProductID (linked to the product table)

I need to create a data entry form that allows users to add NEW items. I am absolutely stumped on how it is I allow a user to select multiple values from a many-to-many relationship on a form.

I’ve done quite a bit of googling and this is my last resort. If any of you could point me in the direction of a good tutorial or give me a few pointers, that would be extremely appreciated.

Thanks!

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!!!!

Hello,

New to access

Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.

Any help apprecated

Okay, for simplicity's sake, I have a data entry form.

It is bound to tableData.

Inputs are:

ProductID
Customer Name


ProductID is a combo box on the form.

There is another table called tableProduct. In this table, is ProductID and ProductName.

For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.

How can I accomplish this?

Thanks in advance.

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!

I have a data entry form (based on Projects Table) used to add new projects to our database. One of the fields on the form is a combo box that looks up records in the Client table. There is a one-to-many relationshipe between the Client table and the Projects table. All data is saved to the Projects table. I want users to be able to pick from the list in the combo box, or type in a new client, if necessary. My problem is that the new client gets added to the Projects table, but not the Clients table - I can't enforce referetial integrity w/cascade updates, because the Client table is the "one" side of the relationship. How do I get the new client added to the Client table?

Hi I have a problem which I really need resolved however I don't know if this is technologically feasable and its very hard to explain so I'll try my best

I have two tables one called CustomerDatabase and CargoDatabase, the customerID attribute in CustomerDatabase is the primary key and its also specified to have no duplicates, it is linked to the cargo database in a one to many relationship

I have created a data entry form with all the sources of the attributes of the CustomerDatabase and CargoDatabase however I am aware of the no duplicates policy so this is what I want done.

For Example say there is in the CustomerDatabase and I have already stored a record these fields CustomerID: 123456 Name: Robert Smith Address: 11 Newpark Avenue

I would like this data entry form to exist in my program however this is what I want done, I would like when the customer ID is entered say 123456 I want Robert Smith to appear in the Name field of the data entry form and the address however I want to ensure that only the data entered onto the CargoDatabase is entered such as CargoID, CustomerID and all the others I don't want any of the attributes of the CustomerDatabase to be entered, I'm sorry that may seem like a mouthful, but I really need this help and I would greatly appreciate some to see if its able to be done

I know what your saying why does he want data input on a data entry form when its already there and it isn't going to be input into the system, I just want it to show so who the customer is so when I type the number it comes up, thank you.

Thanks Ted.

I'm developing an access database and seem to be having what must be a basic problem. To use an example, my database contains a Customer table and a City table. The City table is linked to the Customer table by a CityID in the Customer table. The City Id's are linked by a one-to-many relationship (the many side being in the Customer table).

I have created a query which brings the two fields - Customer Name and City together from the two tables (just to test the theory before I do the same for all the other fields). I have also created a data entry form based on this query.

My problem is when I open the data entry form, I can view existing records OK - when I add a new record, which has a new city name not already in the city table, it won't let me add the new City to the underlying table; it says it can’t add it to the ‘one’ side of the relationship. I can understand this since I have enforced referential integrity. However, when I remove the referential integrity from the one-to-many relationship, it will add the City but duplicate any cities already in the City table – which defeats the point of having a separate City table! If I then add an index with no duplicates to the City name field, then it won’t let me add new cities and existing cities are duplicates.

The purpose of the data entry form will be to choose the city, for a new customer record, from a pick-list if it’s already in the City table and if it’s a new city then to add it to the City table, via the data entry form.

I am using the City/Customer relationship as an example and will apply the theory to the rest of the relationships with my Company, Product, Orders table etc.

I would be really grateful for any help as this is the first database I have designed from scratch. I do not have any knowledge of VBA and very limited macros experience. The text books are not very helpful for this problem, so I’m thinking that what I’m doing wrong must be very obvious!

{Here is the actual Question for my assignment.}

Create a basic data entry form for each of the three tables. Each form must contain all the fields in the table. All forms must be consistent in design (using the same format layout). Set the column width of each form to five inches. The forms must have a two line title: Johnny C Foods, on line one and the form name on line 2. The title must be centered across the width of the form. You must also have the downloaded graphic “Logo” in the form header and your name and section number in the form footer. No data or labels should be truncated, including those within any subforms.

Hello all...This db has been working beautifully for a year. Now, when entering data in the data entry form the info is not saved. When I type the info directly in to the table it is there but it does not go through to the query which is the source for the data entry form. I have checked the usual things - data entry allowed - but to no avail. Help?

When I have a form in data entry mode, there is no way to close this form
once he started adding information to it. When I assign a macro to a button
with the command "Close" and SaveOnClose set to "No", the information added
is saved to the Table. Is there a way to get around this?
I mean, what the user enters the "data entry" form, starts adding
information and then changes his mind? Can't he close the form and
everything to be back as he never oppened it?

Hi, I need a a little help please:

I need one data entry form that has all the months and a value for each year. So you got:

January [value ]
February [ value ]
March [ value ]
Etc...

but, the problem is that the table format is flat, so each record is like this:

ID / Year / Month / Value
1 /2008 / January / 500
2 /2008 / February / 300

Then, the data entry form is supposed to write or modify different rows at the same time (the wizard entry form just let me modify months one by one)
. Your help would be greatly appreciated!

I have a data entry form called "frmAddDependant" and it is opened with a button with the VB code line:


	Code:
	DoCmd.OpenForm "frmAddDependant", , , , acFormAdd, , Me.ID

Well, "frmAddDependant" then opens and I can add the new entry and it shows up as a new record in the appropriate table. The problem is if the user doesn't input anything and just clicks the "Back" button (which takes them back to the previous form), it still creates a new record that is completely blank. For the "Back" button I am just using the code:


	Code:
	DoCmd.Close

Also I forgot to mention that in the Form_Open function of "frmAddDependant" I have the following code:


	Code:
	Private Sub Form_Open(Cancel As Integer)
    DoCmd.RunCommand acCmdRecordsGoToNew
    Me.EmployeeID = Me.OpenArgs
End Sub

So I am trying to figure out how to not save it as a new record if the user doesn't enter the proper information. I could either make some adjustments in the table design and make the fields required (currently only the ID field is required and that is an Autonumber and the primary key)... or I can create a function in the Form_Close function that would check if the form is "dirty" and if it isn't then it won't save the record, but how would I do that? Or is there some obvious solution that I am missing?

Hi there. I have build a form for data entry. It works just fine, but when I check the table to see what is new there are always lots of blank records interspersed beterrn the correctly filled ones. Im pretty sure that it isnt happening when I click the button on the splash page that sends you to the data entry form.

Here is the code for the button that adds the record.

	Code:
	Private Sub rmAddIncident_Click()
   Dim err As Integer
   Dim cnn1 As ADODB.Connection
   Dim Risk_Data As ADODB.Recordset
   Dim strCnn As String
'Check that all fields are filled in
rmENCON.SetFocus
If rmENCON.Text = "" Then
err = err + 1
MsgBox "Please fill in the ENCON number." & err
End If
rmName.SetFocus
If rmName.Text = "" Then
err = err + 1
MsgBox "Please fill in the name of the person affected by the incident."
End If
rmDate.SetFocus
If rmDate.Text = "" Then
err = err + 1
MsgBox "Please fill in the date the incident occured."
End If
rmInjury.SetFocus
If rmInjury.Text = "" Then
err = err + 1
    MsgBox "Please specify the degree of injury."
End If
rmComments.SetFocus
If rmComments.Text = "" Then
err = err + 1
MsgBox "Please briefly describe the incident in the comments box."
End If
rmResolved.SetFocus
If rmResolved.Text = "" Then
err = err + 1
MsgBox "Please briefly summarize the follow-up."
End If
rmVictimstatus.SetFocus
If rmVictimstatus.Text = "" Then
err = err + 1
MsgBox "Please fill in the Victim status."
End If
rmLocation.SetFocus
If rmLocation.Text = "" Then
err = err + 1
MsgBox "Please fill in the location the incident took place."
End If
rmType.SetFocus
If rmType.Text = "" Then
err = err + 1
MsgBox "Please incicate the type of incident that occured."
End If
rmSpecificType.SetFocus
If rmSpecificType.Text = "" Then
err = err + 1
MsgBox "Please incicate the specific type of incident that occured."
End If
'if no errors insert data
If err < 1 Then
  
  ' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "G:AdminCeoHuman ResourcesRISKMGMTInhouse dataRisk DatabaseInhouse Data.mdb"
   strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Open Risk_Data table.
    Set Risk_Data = New ADODB.Recordset
    Risk_Data.CursorType = adOpenKeyset
    Risk_Data.LockType = adLockOptimistic
    Risk_Data.Open "Risk_Data", cnn1, , , adCmdTable

'get the new record data
      Risk_Data.AddNew
        Risk_Data!ENCON = rmENCON
        Risk_Data!Name = rmName
        Risk_Data!Date = rmDate
        Risk_Data!Injury = rmInjury
        Risk_Data![Incident Comments] = rmComments
        Risk_Data![Medication Risk] = Med_Risk
        Risk_Data![Medication/Equipment] = rmMedication
        Risk_Data![Victim Status] = rmVictimstatus
        Risk_Data!Location = rmLocation
        Risk_Data!Type = rmType
        Risk_Data![Specific Type] = rmSpecificType
        Risk_Data![Phys/Empl/Pt Involved] = rmPersonInvolved
        Risk_Data![Follow-up Summary] = rmResolved
        Risk_Data![Resolved or Pending] = ResolvedOrPending
        Risk_Data![Date Resolved] = rmDateResolved
      Risk_Data.Update
'Show the newly added data.
   MsgBox "Incident #: " & Risk_Data!ENCON & " has been successfully added"
'close connections to DB.
  Risk_Data.Close
    cnn1.Close
'clear all objects
Me.rmENCON.Value = ""
Me.rmDate.Value = ""
Me.rmName.Value = ""
Me.rmInjury.Value = ""
Me.rmComments.Value = ""
Me.Med_Risk.Visible = False
Me.rmMedication.Value = ""
Me.rmLocation.Value = ""
Me.rmVictimstatus.Value = ""
Me.rmType.Value = ""
Me.rmSpecificType.Value = ""
Me.rmPersonInvolved.Value = ""
Me.rmResolved.Value = ""
Me.ResolvedOrPending.Value = ""
Me.rmDateResolved.Value = ""
'send back to main page.
 DoCmd.OpenForm "Main_Page"

Else
MsgBox "An Error has occurred, please check and try again"
End If
DoCmd.Close acForm, "Enter_New_Incident"

End Sub

Anyone see anything in there that would be causing this issue. Its not a huge deal, but a pain in the butt to get rid of the blanks.

-Z

Hi, I am new to access... I just want to prepare a simple data entry form. I have table "Activity", where all team names, function names, and cycle times are stored.

And I have another table "Volumes" with columns Date,Team,Function,Volumes.

I need a form with one combo box with distinct team names and if we select team name, it shud show all the functions for that team from Activity table. we need to update volumes for that. after entering all data, if we click "upload to volumes button", all those records shud be saved in volumes table with current date.


Please help me out.. its really urgent.

Thanks in advance

I have a search form (continue) contain basic information which allow people to filter to a specific record. I have another data entry form (single form) has all the detailed information. These two forms have the same data source. the PK for the table is ParticipantID

I add a macro (I do not much VBA codes) to participantID for the search form and hopefully it can open the data entry form for that participant if I click the ParticipantID.

the macro are: open form and I use where condition :
Forms![FrmSearch]![ParticipantID] = forms![FrmDataEntry]![ParticipantID]. however, it open a new record in the data entry form?

Can somebody tell me what's wrong with it?
Or write an code for me?
Thanks.

how do i make my data entry form close without saving whatever i typed in it. i have a save button and a "close without saving" button. the second button seems not to work. the data i didnt want to save is in the table when i close the form by clicking on "close without saving" button. any ideas from anyone?

I am new to Access and VBA, and i am trying to make one database using Office XP, Access 2000 and VBA. it is almost complete but i am stuck at two three places like1. Sir, I have a data entry form say 'D' which is linked to a form 'C' when i click on a button on 'C' form The 'D' Form is getting open and i can fill all the text box available for data entry and if i will press SAVE button on 'D' all the data will be saved in my linked table. upto this every thing is normal but when i link 'C' to anotther form like'B' then the problem starts. i mean to say, i have button on form 'B' to open form'C' and again on form 'C' i have a button to open data entry form 'D'. but if i open the form 'D' from the form 'C' its fine but if i open the form , from the form 'B' to 'C' and further from 'C' to 'D' then data is not getting saved it is fix on the text box of form 'D'. while all other button is working perfectly on form 'D' like Report, Clear, Close etc.


Not finding an answer? Try a Google search.