Best way for Multiple Entry on single form?

[JayRab]

Guys,

I am looking for some tips on creating a new database data entry form. I am helping safety out on an audit database.

Here is an example of the audit.


I have made the database attached to begin capturing this information but need to figure out how to capture the information.

I planned on each line in the above audit to be in a table so it will have all the header information plus the information from each section/subsection (PPE/a.Head Hair) which would result in 34 lines entered in the main table.

I have attached a sample of what I wanted to do AuditDatabase.zip

They would like the data entry form to look a lot like the audit sheet.

Is the only way to do this is to make unbound txtboxes/comboboxes then use an append query for each of the 34 entries.

Currently my main table that I want to store the data in just has 14 columns (could go with less if I keep breaking it down) that just store numbers or comments and refers back to each table to convert the number to text.

If I wanted to do it so ALL the different sunsections are in the main table it would take 46 columns to capture it all.

Anyway just wondering what you guys think on this.

[Rawb]

I would attempt to explain to them that you can't really do what they want in Access (you can, technically, but you're right that it would require adding a lot of unbound items and using code - VBAor Macros - to both display and update information - it would be a LOT of time/work and a would be a HUGE pain to debug/test and to update).

See if you can talk them into using a Form with the following for each item:

• A combo box for the "Safe", "Not Required", "Did Not Observe", "At Risk" selection
• A checkbox for each of the reasons ("A", "B", "C", etc.) - assuming there can be more than one selected? If only one is allowed, then use a combo box instead
• A text field for comments

Be sure to point out that this is a Form for data entry, not for display or printing. The Reports (if any) could easily be formatted how they want for when they print things out.

[Rawb]

I think I misunderstood your question the first time around...

No, it would not be too difficult to show the all the sections/subsections together on one Form.

It would take a little extra work to create the subsections because you would have to pre-populate each one with dummy data for it to show up in the Form. Once that's done though, you could just use a Continuous Form to display all the sections/subsections at once.

[June7]

The audit sheet image is not showing for me.

Be careful about circular references/dependency. It is not necessary to save both Section and SubSection in tblBBSOAudits. Just the ID from tblSubSections is needed. The associated Section can be retrieved in query by join with tblSubSections.

[JayRab]

Great point June7 i will pull that out since it is captured. can you see this one?

[June7]

Still no images.

[JayRab]

added the picture as an attachment maybe it will be viewable then.

[June7]

Yes, see that. The form structure is not consistent with a normalized database structure. This is a common conundrum, especially with T&A and questionnaire type dbs.

To reinforce your intial conclusion and Rawb's reply in post 2:
A data entry form to replicate the printed form and also save that data into a normalized database would mean an UNBOUND form and lots and lots of code. Then if you want to retrieve and display saved data in this non-normalized structure means more lots and lots of code.

A non-normalized db will be able to accommodate this data entry/output but analysis of the data could present its own difficulties that might call for use of UNION queries and/or VBA writing data to temp tables.

In my view, it is a balancing act between normalization and ease of data entry/output. Normalize until it hurts. I have a db that violates normalization but it suits our requirements. The few times I had to do data analysis and non-normalization got in the way, I handled with the techniques mentioned above.

[JayRab]

What about about the possibility of automatically creating all the subcodes and setting them to safe with a for loop or something like that. And only force user to select and change AT RISK Areas?

Essentially, when the user fills out the header information(observer,date,shift,auditte clock number,enviromental code) clicks new Audit Button the database automatically assigns an audit number then systematically creates 36 "transactions" tied to the header information and audit number.

Private Sub btnNewAudit_Click()
Dim rs As DAO.Recordset
Dim BBESO_v1a As DAO.Database
Dim strSubSectionID As String

Set BBESO_v1a = CurrentDb
Set rs = CurrentDb.OpenRecordset("tblSubSections")

If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
    'Grab values from header info me.txtObserver,me.txtAuditDate,... before entering this loop
    'Using those values make SQL string to add new transaction with the current subsection ID (1-36)
    strSubSectionID = rs!SubSectionID
    rs.MoveNext
Loop
Else
'Error checking
End If
End Sub

Once that is in the table it runs a query based on the AuditID and Opens the report which has Click events(36 of them ): ) tied to each Subsection ID that opens up an Edit form with which will grab and edit the correct record based on AuditNumber and Transaction ID attached to the reportTextbox?

Not sure if it is possible to get that information from the report, unless I use just the values with lookups...

just brain storming what do you guys think? Will it work and it is less code then all the unbound combo/txt boxes?

[June7]

Yes, can use code to create 36 records with or without any value in Safe field. Might be able to accomplish without a looping structure. Something like:

CurrentDb.Execute "INSERT INTO Transactions(AuditID, SubSectionID) SELECT " & Me.AuditID & " AS ID, SubSectionID FROM tblSubSections"