What am I missing in "this code will select the checkbox on the tab for the dupl rec"

[Repent]

What am I missing in "this code will select the checkbox on the tab for the dupl rec"

I'm trying to figure out where the error in my code is or if I need to be trying to create my additional records another way. So far with the code below I can create the additional records according to the extra (Time) checkboxes selected on the form by the user.

Problem is I have a form with several tabs and on each tab are checkbox items that the user can select. All the checkboxes that are checked on the various tabs are listed in a text box on the main form for the users reference just before they commit the record.

I need each duplicate record to also have whatever checkboxes were selected in the main record to also be selected on it. The records are not exact duplicates since the record time selected by the user is different for each record.

Option Compare Database
Option Explicit
Private Function SubAddRecords()
'this function will call the Subroutine AddRecords_Click() code below so that I can use this _
function to call from the VCR control macros on the main form.
Call AddRecords_Click
End Function


Sub AddRecords_Click()
Dim varTime As String
If cb630AM.Value = True Then
    varTime = "6:30am"
    Call addentry(varTime)
    End If
If cb830AM.Value = True Then
    varTime = "8:30am"
    Call addentry(varTime)
    End If
If cb1030AM.Value = True Then
    varTime = "10:30am"
    Call addentry(varTime)
    End If
If cb1230PM.Value = True Then
    varTime = "12:30pm"
    Call addentry(varTime)
    End If
If cb230PM.Value = True Then
    varTime = "2:30pm"
    Call addentry(varTime)
    End If
If cbEndDays.Value = True Then
    varTime = "End-Days"
    Call addentry(varTime)
    End If
If cb630PM.Value = True Then
    varTime = "6:30pm"
    Call addentry(varTime)
    End If
If cb830PM.Value = True Then
    varTime = "8:30pm"
    Call addentry(varTime)
    End If
If cb1030PM.Value = True Then
    varTime = "10:30pm"
    Call addentry(varTime)
    End If
If cb1230AM.Value = True Then
    varTime = "12:30am"
    Call addentry(varTime)
    End If
If cb230AM.Value = True Then
    varTime = "2:30am"
    Call addentry(varTime)
    End If
If cbEndNights.Value = True Then
    varTime = "End-Nights"
    Call addentry(varTime)
    End If
End Sub


Private Function addentry(varTime As String)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProductionNumbers", dbOpenDynaset)
rs.AddNew
rs("ManHoursID") = Me.ManHoursID.Value
rs("ProductionDate") = Me.ProductionDate.Value
rs("TimeID") = varTime
rs("Line#ID") = LineID.Value
rs("ProductID") = Me.ProductID.Value
rs("OperatorID") = Me.OperatorID.Value
rs("TailOffID") = Me.TailOffID.Value
rs("LF Run") = Me.[LF Run].Value
rs("LF Produced") = Me.[LF Produced].Value
rs("Comments") = Me.Comments.Value
rs("UserSelectedTabItems") = Me.UserSelectedTabItems.Value 'this populates the textbox on created record
'This code will make sure the checkboxes on the various tabs are carried over to the newely created record.
rs("ImajePrinterMotorRPMTooLow") = Me.ImajePrinterMotorRPMTooLow.Value
If Me.ImajePrinterMotorRPMTooLow = -1 Then 'this code will select the checkbox on the tab for the dupl record
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"ImajePrinterMotorRPMTooLow" & vbNewLine, "") 'this code will remove the check in checkbox if item unchecked in record
    End If
'
rs("CoolingBoxesRollersCupping") = Me.CoolingBoxesRollersCupping.Value
 If Me.CoolingBoxesRollersCupping = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"CoolingBoxesRollersCupping" & vbNewLine, "")
    End If
'
rs("CoolingBoxesRollersLineBreakinginFirstCoolingBox") = Me.CoolingBoxesRollersLineBreakinginFirstCoolingBox.Value
If Me.CoolingBoxesRollersLineBreakinginFirstCoolingBox = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"CoolingBoxesRollersLineBreakinginFirstCoolingBox" & vbNewLine, "")
    End If
'
rs("CutterCutterBrainResetButtonInoperable") = Me.CutterCutterBrainResetButtonInoperable.Value
If Me.CutterCutterBrainResetButtonInoperable = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"CutterCutterBrainResetButtonInoperable" & vbNewLine, "")
    End If
'
rs("CutterProductBackedUp") = Me.CutterProductBackedUp.Value
If Me.CutterProductBackedUp = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"CutterProductBackedUp" & vbNewLine, "")
    End If
'
rs("DieRazorLaminatorBeadDropping") = Me.DieRazorLaminatorBeadDropping.Value
If Me.DieRazorLaminatorBeadDropping = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"DieRazorLaminatorBeadDropping" & vbNewLine, "")
    End If
'
rs("DieRazorLaminatorDimples") = Me.DieRazorLaminatorDimples.Value
If Me.DieRazorLaminatorDimples = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"DieRazorLaminatorDimples" & vbNewLine, "")
    End If
'
rs("DieRazorLaminatorHook") = Me.DieRazorLaminatorHook.Value
If Me.DieRazorLaminatorHook = -1 Then
    Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"DieRazorLaminatorHook" & vbNewLine, "")
    End If
rs.Update
rs.Close
db.Close
End Function


Private Function ClearCheckBoxes()
cbEndNights.Value = False
cb630AM.Value = False
cb830AM.Value = False
cb1030AM.Value = False
cb1230PM.Value = False
cb230PM.Value = False
cbEndDays.Value = False
cb630PM.Value = False
cb830PM.Value = False
cb1030PM.Value = False
cb1230AM.Value = False
cb230AM.Value = False
End Function


Private Sub cb630AM_Click()
If cboTime = "6:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb630AM.Value = False
Else
End If
End Sub
Private Sub cb830AM_Click()
If cboTime = "8:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb830AM.Value = False
Else
End If
End Sub
Private Sub cb1030AM_Click()
If cboTime = "10:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1030AM.Value = False
Else
End If
End Sub
Private Sub cb1230PM_Click()
If cboTime = "12:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1230PM.Value = False
Else
End If
End Sub
Private Sub cb230PM_Click()
If cboTime = "2:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb230PM.Value = False
Else
End If
End Sub
Private Sub cbEndDays_Click()
If cboTime = "End-Days" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cbEndDays.Value = False
Else
End If
End Sub
Private Sub cb630PM_Click()
If cboTime = "6:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb630PM.Value = False
Else
End If
End Sub
Private Sub cb830PM_Click()
If cboTime = "8:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb830PM.Value = False
Else
End If
End Sub
Private Sub cb1030PM_Click()
If cboTime = "10:30pm" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1030PM.Value = False
Else
End If
End Sub
Private Sub cb1230AM_Click()
If cboTime = "12:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb1230AM.Value = False
Else
End If
End Sub
Private Sub cb230AM_Click()
If cboTime = "2:30am" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cb230AM.Value = False
Else
End If
End Sub
Private Sub cbEndNights_Click()
If cboTime = "End-Nights" Then
DoCmd.Beep
msgbox "You cannot select the SAME TIME twice. Please try your selections again"
cbEndNights.Value = False
Else
End If
End Sub

 

[BiigJiim]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Hi Repent,

I am not sure I understand exactly what you are trying to do, but it looks to me like when you are adding each record you are using me.UserSelectedTabItems to populate rs("UserSelectedTabItems") BEFORE amending me.UserSelectedTabItems to remove any unchecked tickboxes.

Does that solve your problem?

Cheers,
Jim

 

[Repent]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Jim;

Thank you for the reply. I'm not quite sure I'm following you. I'll try to explain what is going on.
SubAddRecords looks at a checkbox for a set time of day, such as 630am, 830am etc and if that checkbox is selected it will plug that time into a newly created record and basically carry over all the other fields on the form that have been selected. A shortcut way of sorts for my users to be able to create additional records containg all the same data as the record they are filling out except that the time slot of the record is different. As a user, you create a record answering numerous questions about the product you just created. You have to complete a record every two hours, even if nothing other than the time is different. Users were making mistakes when duplicating records so the need for the code. The user fills out the record, answers questions, then is able to select "other" time frames that this record also applies to and so checks those other applicable timeframes and commits the record by using VCR controls/macros on the form to advance to the next record. When the record is saved and other time frames where checked off additional records are created containg the same data as the first record except that the time is different.

The AddEntry function creates the additional records and the code

rs("UserSelectedTabItems") = Me.UserSelectedTabItems.Value

copies over everything that has been checked and displays it in a text box. I did the text box thing so that the user could see, at a glance, everything they had checked off on the other tabs of the form.

I thought I was done there but then realized that I had to also get all the checkboxes that were checked on the original record to also be checked on the additional records. This last piece is what I'm trying to figure out.

Chris

 

[BiigJiim]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Hi Chris,

It might be helpful if you could post an image of your form, so we can see what is going on.

At present, you have a control UserSelectedTabItems and a field in your recordset, also called UserSelectedTabItems. Your addentry code populates the UserSelectedTabItems field using the value in the UserSelectedTabItems control.

rs("UserSelectedTabItems") = Me.UserSelectedTabItems.Value 'this populates the textbox on created record

However, then it uses several Replace functions to potentially change the value in the control. This changed value will then be used for subsequent records.

Is your main form bound or unbound?

Could you not have a listbox, say lstTimes, with all the different times listed, and with Multi Select set simple or extended? You could then have a piece of code which loops through each selected item and adds a new record to the recordset that way.

e.g.
dim varItm as variant
for each varItm in lstTimes.SelectedItems
rs.addnew
rs("TimeID") = lstTimes.ItemData(varItm)
...
populate all the other fields of the record
...
rs.update
next varItm
rs.close

Hope this helps!

Jim

 

[Repent]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Jim;

I've uploaded a copy of my project. I think that will help. It's in Access 2010.

I've had to learn and create as I go :banghead:with this project and so I know in some places it may not be "pretty". I'm coming up on a deadline for this so any help would be hugely appreciated.

Chris

 

[BiigJiim]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Chris, I don't have Acc2010 installed at the moment 'cos I am working on an Acc2003 db and don't like have multiple versions on the same Windows installation and my pc with Acc2010 on it recently went tits up.

If I get a chance, I will re-install Windows and A2010 on that pc, but can't promise it soon. If you could post some screenshots it might help in the short term.

Sorry to be a pain,
Jim

 

[Repent]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Jim;

Here are some screenshot that will hopefully show what I'm trying to do. As you can see, the form is made up of several tabs. On the tabs with the checkboxes those are all issues that could come up with the product being created. The user starts on the main form and goes to any of the applicable tags to check and issue if it applies to the record being created.

The text box under the comments section is populated whenever the user selects a checkbox on any of the other tabs. Just a visual really for the user so they can know they checked all the boxes they wanted to. The time boxes at the end "Create Additional Time Records Here" basically would duplicate the record but in the Time field it would use that time instead. All users have to create a record every two hours even if nothing changes except for the time the record is being created.

I can create other screenshots if need be. Thank you SO much for helping me here.

Chris

 

[Repent]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Hi Chris,

It might be helpful if you could post an image of your form, so we can see what is going on.

At present, you have a control UserSelectedTabItems and a field in your recordset, also called UserSelectedTabItems. Your addentry code populates the UserSelectedTabItems field using the value in the UserSelectedTabItems control.

rs("UserSelectedTabItems") = Me.UserSelectedTabItems.Value 'this populates the textbox on created record

However, then it uses several Replace functions to potentially change the value in the control. This changed value will then be used for subsequent records.

Is your main form bound or unbound?

Could you not have a listbox, say lstTimes, with all the different times listed, and with Multi Select set simple or extended? You could then have a piece of code which loops through each selected item and adds a new record to the recordset that way.

e.g.
dim varItm as variant
for each varItm in lstTimes.SelectedItems
rs.addnew
rs("TimeID") = lstTimes.ItemData(varItm)
...
populate all the other fields of the record
...
rs.update
next varItm
rs.close

Hope this helps!

Jim

Jim can you help me to further develop this piece? I like the idea especially if it reduces code and makes it easier for my users. Currently there is a Time combobox on the form for the user to select the time of the record then at the bottom of the form they can select other times that this record remains the same.

Chris

 

[BiigJiim]

Re: What am I missing in "this code will select the checkbox on the tab for the dupl

Hi Chris,

It does look to me like you need to think a little about the architecture of your data. For example, you have a reasonably large dataset - 20,000 records at the moment, the majority of which are duplicates with just a different time stamp. Am I correct?
I would also have a separate table, joined to the main production card table, which stores all the problems associated with each production card.

I still don't understand why you are using all of the Replace statements to change the value in Me.UserSelectedTabItems while you are creating each new record. Should each duplicate record not have the same UserSelectedTabItems value?

If Me.DieRazorLaminatorHook = -1 Then
Else
Me.UserSelectedTabItems = Replace(Me.UserSelectedTabItems, _
"DieRazorLaminatorHook" & vbNewLine, "")
End If

I am happy to help you develop this further - I am sending you a PM.

Cheers,
Jim