Using Command Button/macro To Create Additional Records? (1 Viewer)

[Repent]

I have a form where users enter production data at set times during the day.
The set times for dayshift are: 6:30am, 8:30am, 10:30am, 12:30pm, 2:30pm and End-Days.
The set times for nightshift are: 6:30pm, 8:30pm, 10:30pm, 12;30am, 2:30am, and End-Nights
Both shifts work 12 hour shifts.
These set times for records are provided on the form via a textbox with the control source of "=DLookUp("[Time]","[tblProductionHours]","ID = " & [TimeID])"

I'm trying to cut down on the number of records a user has to manually create during their shift. Sometimes the data the user needs to enter will be the same throughout parts or the whole of their shift. The only difference would be the set time of the record. I'd like to have a checkbox on the form that listed all the users set times for creating records so that after the user filled out the form, and before going to the next (new and blank) record if they knew the rest of the records or even just some of the records would be the same then they could select the box next to the set time for records and then those records would be created for them.

So as an example, I'm a dayshift worker starting my shift a 4:30am. My first record of the day will be at 6:30am, then again at 8:30am, 10:30am, 12:30pm, 2:30pm and End-Days. When I am entering my 6:30am record I know that the rest of my records will contain the same data only that the record time will be different. I could fill out the form and use the textbox to select the 6:30am time and then click on separate checkboxes for 8:30am, 10:30am, 12:30pm, 2:30pm, and End-Days. When I commit the record to the DB the additional records would also be created and added to the DB and all the info on the form would stay the same except for the set time of the record. The set time of the record would reflect the checkbox selected.

I thought maybe I could use macros to do this but not sure if that is the right way or not?

Any help would greatly be appreciated.

 

[Repent]

Hmmm this looks interesting. How would I tie it into the form? The user typically clicks the forward VCR control to move off the current record (and thus saving it) and into a new, blank record

How would a loop work? I'm not familiar with that.

 

[Repent]

This would be in your "add entry" unbound form, you could have another form for viewing entries, or a report of some sort. Depends on your needs.

What do you mean by this? Not sure I follow....

I believe I understand your first part and am working on finishing the code based on you example. The part where you state "etc, etc".

 

[Repent]

Got your PM, don't have enough posts here to reply yet, so hopefully you'll look back here.

The msg you're getting indicates you're referring to something that either doesn't exist or has not been declared.

Use rs("LineID") = Me.LineID.Value instead.

rs("LF Produced") = "LF Produced.Value"
shoud be:
rs("LF Produced") = [LF Produced].Value or
rs("LF Produced") = me.[LF Produced].Value

Let me know how it goes.

I made the changes you stated and when I run the form, I get a debug error of Run-time error '13':
Type Mismatch

When I select "debug" there is yellow highlighting on the line for "Call addentry(varTime)" in the text block below.

If cbEndDays.Value = True Then
    varTime = "End-Days"
    Call addentry(varTime)

Depending on which check box I select on the form for the corresponding time, then I get that same error in the selected time, i.e, if I select 8:30am then I get the error in the code for 8:30am.

It is interesting to note that a new record IS being created in the DB, the parts of the record that are missing are the date, and the time. All the other parts of the record are being created so this looks to be really close.

Could this be due to the fact that the varTime is "End-Days" and so Access is trying to do math on the phrase of "End-Days" since I have a dash between those two words? Just a thought. I'm probably way off base.

I also notice that the check boxes stay checked as I cycle through records and I need everything on the form to clear (all check boxes and fields) when the user goes to a blank record or goes to a previous record.

 

[Repent]

I'm hoping someone can help me finish this where Kn0wn1 left off?

 

[Repent]

No, its not subtracting End-Days, it's taking it as a string. The message "Run time error '13': Type mismatch" may indicate an error with your references. See this article: http://support.microsoft.com/kb/181542


To clear values after running the macro add

cbEndDays.Value = False

etc, etc for each checkbox. For textboxes change False to "". Add between db.close and end sub.

OK so I've been working with this all morning and not making any headway. Still not sure where or why the Runtime error 13 is coming from or why. Below is the code I'm using along with screenshots of the checkboxes and the code behind them.

Private Sub AddRecords_Click()

Dim varTime As String

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 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()

Dim db As Database
Dim rs As Recordset
Dim varTime As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProductionNumbers", dbOpenDynaset)
rs.AddNew
rs("ManHoursID") = ManHoursID.Value
rs("ProductionDate") = ProductionDate.Value
rs("TimeID") = varTime
rs("LineID") = Me.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") = Comments.Value
rs.Update
rs.Close
db.Close

cb830am.Value = False
cb1030am.Value = False
cb1230pm.Value = False
cb230pm.Value = False
cbEndDays.Value = False
cb830pm.Value = False
cb1030pm.Value = False
cb1230am.Value = False
cb230am.Value = False
cbEndNights.Value = False

End Function

 

[Repent]

This is still driving me nuts. I continue to get the "run-time error '13': Type Mismatch when I try to run the above code. When I debug the debugger highlights the "Call addentry (varTime) for the 8:30am checkbox. I have that checkbox checked on the form. If I have the 10:30am box checked then that line is highlighted.

When I look in the recordset I can see that even though I received an error the record was still created, except that the time of 8:30am was not entered.

I looked at the support article referenced and I only have Microsoft ActiveX Data Objects 2.x Library checked. I don't have anything for DAO library checked.

Is the fact that I'm trying to add 8:30am instead of 8:30 giving this error?

"Type Mismatch" makes me think that the program is looking for a certain type of value but getting a totally different type of value so it's getting confused?

This really kills me because I'm so close, so very close.............