VB code to create a form that enters multiple records in a single field

[JFo]

Essentially I have a database where I have a number of check-lists that each have different responses for inspections of different pieces of equipment and instead of making different tables for different check-lists I have an tblQuestion table and a tblAnswer table. This is all very well in datasheet view however I would like to have a different form that opens for each different equipment type and allows the user to just answer the questions for that check-list.



The difficulty that I have never encountered before and I am having trouble finding how to do is how to create a form that essentially
'can enter multiple records into a single field'

What would happen ideally is the entire system would be input via a series of forms... I have worked out how to do this all the way to the point where ideally a user chooses a certain piece of equipment which in turn opens the specific form that has the equipment check-list.

As all the answers are actually going into the same field (as different records) I need vb code that links the questionid to each different text box (on the form) and inserts it into a different record (answerid).

I hope this is not too vague and someone may understand what I am asking for

For a start though maybe just some code that allows for example 10 text boxes on a single form to populate 10 different records of the same field in the same table would be a good starting point.

Thanks

[Robeen]

Hi,

If I understand you correctly - you want to take the [10?] different values from the text boxes on your Form and put them into 10 different records in your Table. Is that right?

When you insert the different records into your table, are you going to be inserting just the one value from the TextBox into one field in the Table? I mean - does the table consist of only one field?

[JFo]

Yes this is exactly correct Robeen, I was just trying to start with 10 separate records updating a single field however this was just for ease of understanding.

Ideally I would like to update 2 fields for each record with a text box and a combo box for each record.

Eventually there may be 100's of check-list questions on a single form separated by tabs where each check-list question has a text field (comment/ response) and a combo (yes, no or N/A) answer for each record. As the table only contains these 2 fields as well as PK and FK I need a way to populate each question as a separate record

I hope this makes sense

Cheers

[Robeen]

Can you tell me what fields are in the Table that you are going to add the records to?
I'll use that information to create a mock-up here and send you the code.

[JFo]

Hi Robeen here is pretty much the current design of the database so a user enters or chooses client, site and contact details in a main form and then chooses an inspection or starts a new inspection. Within this singular inspection the user inspects a number of different pieces of equipment. For each piece of equipment a different form will open as per which check-list is associated with this piece of equipment.

For example if you look at tblChecklist it contains a number of questions for 3 types of equipment. The corresponding form should open displaying those questions and as a result the user must answer each question with a response/ comment and also a yes, no or n/a option from a combo box (values 1, 2 and 3). This is represented in tblChecklistAnswer.

I hope you can then understand the design and what I am trying to do from this stripped down design version of the db (notably this does not contain my forms)

Attachment 4552

Thank you

[Robeen]

I think the best way for me to help you right now would be if I gave you an example of how to insert a few rows of data from a TextBox on a Form into one of your tables.

Give me some data [10 records ? - or two or three records] with the data that you would like to insert - and I'll send you the code.

I will need to know the Data for each row and the Table into which the data for each row will be inserted.

What I would attempt in your scenario would be to write code to get values from each of the TextBoxes on a Form and 'INSERT' it into a new Row in the Table.

[JFo]

Not quite sure I totally understand I thought this was attached above.

As per example db I posted

If a certain piece of equipment is selected as part of an inspection ie In an inspection the user needs to inspect Example 1 equipment type, then the specific frmExample1 is opened and on this form as per tblChecklist questions on the form could be

CheckListQuestion
1. Local Isolator Identification present
2. Registration Number posted on beam
3. SWL sign posted on beam
4. Direction signs posted on beam
5. Warning signs in correct locations
6. Inspect, pendant control – support wire
7. Inspect, pendant control – functions
8. Inspect, pendant control – decals
9. Test, over-hoist limit switch
10. Test, low limit switch
11. Inspect, rope remaining on drum (how many turns)
12. Inspect, travel limits and stops
13. Inspect, parking limits and stops
14. Inspect, derailment plates
15. Inspect, hoist/trolley connections
16. Inspect, hoist rope and hook
17. Inspect, hoist chain and hook
18. Inspect, dead chain bucket
19. Inspect, dead chain anchor
20. Support structure


and the corresponding answers that need to be entered and stored into tblChecklistAnswer include;
1. ID present, shielded 1
2. Posted in top left 1
3. Not Sighted 3
4. Remnants of decay 2
5. 2 out of 3 in correct location 2
6. Support wire loose 2
7. Inspected 1
8. Not Sighted 3
9. Tested 1
10. Failed 2
11. Inspected 7 turns remaining 1
12. Travel limit Sign newly painted 1
13. Sighted 1
14. Sighted 1
15. Trolley Connection not visible 3
16. Inspected 1
17. hook requires replacement 2
18. No bucket found 2
19. Sighted 1
20. Stable 1

Note that for each piece of equipment that is inspected for a given inspection then a different form is opened. All data that is collected is stored in tblChecklistAnswer

Note that for every question answered there is 2 parts the comment (response) and the yes, no, N/A combo selection (1, 2 or, 3)

Additionally for every question, ideally I will want to code some form of validation and present the user with the default value for that answer (however this will come later)

FYI there can be many upwards of 20 different pieces of equipment inspected for every inspection and for each piece of equipment the check-list may have more than 100 questions

Please let me know if this is still not clear?

Thanks

[Robeen]

JFo,

I thought it was a simple matter of inserting rows into one table.
That is simple enough.

But you have the relationships set up in such a way that when I try and enter this:

ID present, shielded, 1

into Answer and Answer2 fields directly in your table

I geta message saying:
'You cannot add or change a record because a related record is required in the table 'tblCheckList'.'

??

[JFo]

Yes this is correct design I think, the related record in tblChecklistAnswer is the foreign key from tblChecklist for each question in any given checklist

I think if you can show me how to enter multiple records in one table using one form I should be able to figure out how to enter the foreign key for each question quite easily.

You will notice that for each checklist every question should also have foreign key from equipment table in tblChecklist.

[Robeen]

Hi JFo,

Sorry it's been a while [weekend!! ].

Here's some code to insert two Values from a Form into a new row in a Table:

 
Private Sub cmdUpdate_Click()
 
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
 
db.Execute " INSERT INTO [TableName]" & "([TableField1], [TableField2]) VALUES " _
       & "('" & Me.FieldOnForm1 & "'," & "'" & Me.FieldOnForm2 & "'" & ");"
 
End Sub

I'm assuming a command button on your Form named cmdUpdate.

You could use multiple db.Execute statements to insert rows of data into your Table using data from different text boxes on your Form.

Let me know if this helps - or if you need clarification.

[JFo]

Awesome thanks Robeen that does exactly what I want it to do

Cheers

[Robeen]

You're welcome JFo!

Happy trails!!