Data Entry Form - Multiple Tables?

[Fliption]

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.

 

[Pat Hartman]

Use a main form for the activity data with a subform for table 3. The subform will show two columns - a combo for RiskID and a textbox to enter the measurement data. Make sure that the master/child links are properly set so that Access will populate the ActivityID in the subform.

 

[Fliption]

Thank you Paul for your response. I believe I did do what you are suggesting. What I end up with is a single combo box with risk id and a field to input the risk measurement as you said. The problem is this: Once I select a riskID from the combobox and then enter a RiskMeasurement, the 3rd table will not capture the data until I hit the "next record" arrow down at the bottom of the subform. In this situation, the user has to know to select the 2nd RiskID from the combobox for the 2nd record and not the first one again. It seems possible that you could get 2 Riskmeasurements for the same riskID this way.

Again, I hope this makes sense.

 

[Pat Hartman]

Not if you define a multi-field primary key on the two foreign keys in the relation table. That way the combination of RiskID and ActivityID must be unique.

Access automatically saves records whenever the current record pointer changes. That happens when focus moves to another record or from a subform to a mainform or vice versa or when a form is closed among other times.

If you want to you can restrict the list of items in the combo to be only those not yet chosen but you'll need to use a continuous subform rather than one in datasheet view.

 

[Fliption]

Thanks again Pat.

I understand about the primary key not allowing duplicates but are you saying that the user will have to click on the next record button at the bottom of the form before they can input a measurement for RiskID 2?

What I would really like to do is have all 7 RiskID's listed down one side with the RiskMeasurement field right next to each of them. This way the user doesn't have to select each Risk ID from a combobox. This is what I have been trying to do and I cannot figure out a way to do it.

 

[Pat Hartman]

If you want, you can run an append query to create the risk records when the activity record is created and then all 7 will already exist just waiting for the measurement to be entered. You would size the subform to be the exact size of 7 records and you would set its properties to not allow additions or deletions.

 

[Fliption]

That's an idea. Thanks for your help. As info, I have set it up the way you described before and now when I select a RiskID from the combobox it automatically adds another combobox below it. This is much better than the user having to click the next record buttons. But I'm liking this append query idea. The only thing about that is I would want to avoid all the confirmation messages. I don't want the user to have to confirm that they want to append 7 records. But I'll look into this one.

Also, for your own information... I did see a database once that did what I'm describing using the Dlookup commands but I can't seem to get that to work.

Thanks again.

 

[wardster]

to disable the warning dialogue "you are about to append x records" add the setwarnings properties before and after your code as:

        DoCmd.SetWarnings False
        stDocName = "Your_Query_Name"
        DoCmd.OpenQuery stDocName, acNormal, acEdit
        DoCmd.SetWarnings True

Ian

 

[Pat Hartman]

Since you are running an action query, you should use the .execute method. This will not produce any warning messages.

If you create the 7 records don't forget to set the subform properties as I indicated to avoid adding new records and deleting existing records.

 

[Fliption]

If you want, you can run an append query to create the risk records when the activity record is created and then all 7 will already exist just waiting for the measurement to be entered. You would size the subform to be the exact size of 7 records and you would set its properties to not allow additions or deletions.

Pat, I am trying to accomplsih what you described in the quote above. To make this work and have the riskID field listed in the form I will need to append them into the Measurement table as you said. But the primary key for this table is the combination of ActivityID and RiskID, so I will also need to append tha activity ID into the table as well.

How in the world can this be done? I have been pulling my hair out trying to figure that out. How can I essentially create a record in the measurement table that includes the activityID (from one table) and the RiskID(from another table)? Append queries don't seem equipped to do such things.

There has to be a way to get these fields in the table so that they can be presented on the form for easy data entry. Imagine if I actually had to input a RiskMeasurement for each Activity for each of the last 5 years! That means I'm using 3 drop downs and oh what a mess. This is so much cleaner if you can lay out the input fields on the form.

Thanks for your assistance.

 

[Pat Hartman]

Here's some similar code from one of my applications.

    Dim strSQL As String
    Dim Con As ADODB.Connection
    Set Con = CurrentProject.Connection
    strSQL = "INSERT INTO tblMeasurement ( RiskID, ActivityID ) SELECT tblRisks.RiskID, "
    strSQL = strSQL & Me.ActivityID & " As Activity FROM tblRisks;"
    Con.Execute strSQL

You may need to set a reference to ADO if you don't already have one. Notice that the query selects all the RiskIDs from tblRisks and takes the Activity from the current form, puts them together and inserts rows into tblMeasurement.

 

[Fliption]

Thanks for this. After tweaking with several errors I finally have your code working. The code will do exactly what I want. The only problem is that it still gives me this error: "The expression is typed incorrectly, or it is too complex to be evaluated". The code works perfectly by inserting the appropriate records into the table despite this error but I need to try to get rid of it. Any ideas on what might cause this?

Another thought occurs to me. There will certainly be times when users will open this form and perhaps even tweak some of the data. I don't want this code to run and try to create these records again. I can prevent it with the primary key selection but the user will likely get an error. hmmmm

Thanks again for your help,

 

[Fliption]

OK I think that error was associated with some changes I made as I worked through the coding errors and not the code itself. Also, I decided it would be best to link this code to a specific command button as oposed to some general action like opening the form. This command button will only be pushed during the initial entry of an activity.

But I have one question. The code demonstrates how you would input 2 fields like ActivityID and RiskID but what if I wanted to insert a 3rd field like "year"? How would this code be adjusted for such an addition? I tooled around with it based on intuition but I'm no coder and intuition never works!

Thanks

 

[Fliption]

Anyone else want to take a stab at this? Pat was kind enough to provide code that will insert 2 fields from 2 different tables into a single record of a third table. It works perfectly well but I now realize I need 3 fields from 3 different tables put into a single record in a 4th table. The code just needs tweaking to add an additional field. I've tried tweaking it myself but with no luck. The last line in the code throws me so I'm not sure what to do with it.

Thanks for any input.

 

[Pat Hartman]

When you have trouble with syntax, try to build the query in pieces. For example, create just the select query part of the append query that I used as an example. Add the tables to the QBE view, draw the appropriate join lines, select the columns you want and run the query to be sure that it returns the rows you want. Then substitute the new select query for the old and add the third field in the "into" field list. Be sure that the fields in the select part of the query return the columns in the exact order of the "into" list.

 

[Fliption]

I apologize for being dense but I've tried all sorts of things like this. The problem I'm having is in the 2nd to last line in your code. I can insert an additional field into the SELECT and INSERT INTO commands easily but the FROM statement in your code doesn't resemble anything that comes out of the sql when I create a select query. If I do nothing to tweak your from statement, I get an error that a parameter is missing. But I can't seem to add anything to the FROM statement without getting a syntax error. If I copy the (much longer) FROM statement from the select query, I will no longer get the syntax error but then the code no longer works. It runs and does apparently nothing.

 

[Fliption]

Wow. I just found a seemingly much easier (for me) way to do this. I saw Uncle Gizmo respond in this thread with this idea.

http://www.access-programmers.co.uk/forums/showthread.php?t=113612&highlight=append


I just need to set up an append query with each of the tables included and DO NOT connect them. Pull the field I'm interested in from each table and include a query defining the current activtyID and boom it works.

Pat, still would love to understand how the syntax of your code would have worked. After seeing the SQL of this new append query that works, I feel that I did adjust appropriately. So there's just something about that code I don't get.

 

[Pat Hartman]

Append queries add new rows to a table. If you have two columns in your table, you would not run two append queries. You would run 1 append query with two fields.

If the query I posted doesn't work, the problem is most likely that I don't have your field or table names correct.

 

[Fliption]

Hi Pat. Your query worked fine. I just wasn't able to understand how it worked well enough to be able to adjust it or add to it. I would love to know more about how it works though because it's syntax is very different from the sytax of the SQL that I see when I design a select or append query and view the SQL. I'll just mark this as one more thing that I need to learn.

Thanks for all your help.

 

[Pat Hartman]

Use Access help to help you understand the syntax. There are two formats for append queries. One format appends a single row with specified values. The format in my example appends multiple rows by selecting them from one table and appending them to another.

To effectively find help for SQL and DAO/ADO, start by opening help and selecting the table of contents. You can then drill down to the topics you need.