Multiple Records from single form based upon checkbox values

[jeffj803]

I've been scouring through the forums looking for a way of implementing a solution but have yet to find a good avenue of pursuit.

Here is some background on what I am trying to accomplish, hopefully that will lead to a workable solution.

Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters. In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.

Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.

Any ideas or any areas I can clarify?

Thank you for your input,
Jeff

 

[pbaldy]

If a list box is an option:

http://www.baldyweb.com/MultiselectAppend.htm

Not sure how you'd have check boxes for each, as that would hint at a normalization problem.

 

[jeffj803]

pbaldy,
If I am understanding the link you sent me correctly you would use a ctrl or shift click to select applicable items? I cant download the sample database because of firewall restrictions at my office but will have a look at it when I get on a .com network...that is definitely a workable solution if that is the way it works.

Thank you,
Jeff

 

[pbaldy]

Depending on the multi-select option selected, yes. I can post the rest of the code if you want, or you can play with the sample when you can.

 

[jeffj803]

I will download the sample db and look at the implementation and report back on how well it works.

Thank you for the advice,
Jeff

 

[pbaldy]

No problem, post back if you get stuck.

 

[jeffj803]

Paul,
That solution definitely does what I want it to do, now I have one more question. Suppose I want to select the last name from the tblEmployees as depicted but I want to write EmpID, EmpFName, EmpLName, DateOfBirth, and OtherInfo to the tblOtherTable?

Yes this definitely defeats the purpose of having relationships but here is the reason. Say that someone gets married and their last name changes, how would you preserve the integrity of the old records and allow for new records with the new last name?

I'm looking for a means to implement a mechanism like that, one thought I had was to obviously write all data from the form and create some duplication in the database (not a big concern for my purpose but definitely goes against convention). Another thought I had was to create a new field in tblEmployees as a yes/no block called deprovisioned and then use a query for the lookup box that only pulls records that are not marked as deprovisioned. Thoguhts?

Thanks,
Jeff

 

[pbaldy]

So you'd have 2 records for the married employee, one inactive? Don't like it because they'd (presumably) have different ID's. What if I want to query for all activity by a person? I'd have to find all the ID's they may have.

I'm not sure last name is something I'd care about maintaining in old records, but you know your situation better than I. I just store the ID and get their current name. If you want to duplicate fields that could change over time, go for it.

 

[jeffj803]

I was trying to stay within the confines of the example database you linked to so we are speaking the same language. What I am really dealing with is Flight Simulators that do relocate from time to time so I am primarily concerned with their location changing and that impacting previous records. I believe that any queries generated would be based upon the serial number assigned. The only time the deprovisioned field would be used is for the lookup query on the form which would populate records with the new ID that would also correctly reference the new location. I think that solution is probably the simplest.

Along these lines, how do I create a report based upon tblOtherTable but pull in first and last name from tblEmpoyees? I have joined the tables based on EmpID but unfortunately I do not play with databases enough to remember how to pull the join data together. Should I repost this in the reports section?

Thanks for all your help,
Jeff

 

[pbaldy]

Typically you'd base the report on a query that joined the two tables together, enabling it to return fields from both. If you've set up relationships, the join will probably happen automatically when you add both tables to a query.

 

[jeffj803]

That's exactly what it was Paul, here I was trying to create some fancy select statement.

Thanks again for the excellent advice.

Jeff

 

[pbaldy]

Happy to help Jeff!

 

[jeffj803]

Paul,
Im running into a minor issue with the multi-select problem you helped me with previously. The problem I have is that when records are added, it always adds a record with a blank WST_ID field that was not selected as the first record and then adds records as expected.

Here is the code that I currently have running for the procedure:
Private Sub cmdAddRecords_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

On Error GoTo ErrorHandler

Set db = CurrentDb()
Set rs = db.OpenRecordset("Certs_tbl", dbOpenDynaset, dbAppendOnly)

'make sure a selection has been made
If Me.WST_Select.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 WST"
Exit Sub
End If

Set ctl = Me.WST_Select
For Each varItem In ctl.ItemsSelected
rs.AddNew
rs!WST_ID = ctl.ItemData(varItem)
'rs!Letter_No = Me.Letter_No
rs!LETTER_DATE = Me.Letter_Date_Label
rs!TO_ID = Me.TO_Label
rs!Letter_Purpose = Me.Letter_Purpose
rs!Reasoning = Me.Reasoning
rs!Restriction = Me.Restriction
rs!DET_CC_ID = Me.DET_CC_ID
rs!A3T_CC_ID = Me.A3T_CC_ID
rs!POC_ID = Me.POC_ID
rs.Update
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
MsgBox "Records added, form will now close to the swtichboard"
DoCmd.Close
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

Its almost like the "Set ctl = Me.WST_Select" initially sets to a blank value and runs through the add and update and then loops back and selects the first item selected from the multi-select box. Any ideas on what might be causing this behavior? Attached is the form at submission and the results in the table after the records are added.

 

[jeffj803]

Problem solved, I had the controls on the form bound to the table. I removed the bindings and everything is working as expected.

 

[pbaldy]

Sorry, I'm out of the country. Yes, that was my first thought when I saw the email. Glad you got it sorted.