Need HELP: For Loop Issues in VBA

[sincpaul]

I am writing a VBA code for a form that places 4 records in a table that is then queried and displayed in a subform. The person would enter some initial information and then click a button which would place the records in the table to be queried. Everything was working fine on the first sheet I made but I needed to make three sheets do to different information being input. When I modified the code for the second sheet the Loop I use to place the information in the table now only places one record if I run it normally. If I run it in debug mode though it produces all 4 records. Attached below is my code. This is the first time I have programmed in VBA/Access so anything that could be done different or neater would help also.

Private Sub Update_Click()
Dim Datez As Date
Dim Shift As String
Dim Op As String
Dim Prod As String
Dim Line As String
Dim dps As DAO.Database
Dim SQLst As String

Datez = Me.Entry_date.Value
Shift = Me.Shift
Op = Me.Operator
Prod = Me.Product
Line = Me.Line
If Shift = "Day" Then
Datez2 = Datez + (7 / 24)
End If
If Shift = "Evening" Then
Datez2 = Datez + (15 / 24)
End If
If Shift = "Midnight" Then
Datez2 = Datez + (23 / 24)
End If
Me.Start_Date.Value = Datez2
Me.End_Date.Value = Datez2 + 7.5 / 24
Me.Recalc
Me.Refresh

Checkz = Me.[D_E_Line_Pressroom2h_Log_Sheet].Form.[Entry_date]
If IsNull(Checkz) = True Then
Set dps = CurrentDb
For Timez = 1 To 4
Datez1 = Datez2 + (2 * (Timez - 1)) / 24

SQLst = " INSERT INTO [D_E_Line_Pressroom2h_Log_Sheet] " _
& "(Entry_Date, Operator, Product, Line, Create_Date) VALUES " _
& "(#" & Datez1 & "#, '" & Op & "', '" & Prod & "', '" & Line & "', #" & Now & "#)"

dps.Execute SQLst

Next Timez
dps.Close
Else: GoTo SKIPZ
End If


Me.Recalc
Me.Refresh
SKIPZ:

DoCmd.GoToRecord , , acLast
DoCmd.RunCommand acCmdRemoveFilterSort
DoCmd.GoToRecord , , acLast

 

[jackd]

Does the code work? Does it give the proper result?
If so, then great.

As for neater, you could use indentation, and a Select Case

existing

If Shift = "Day" Then
Datez2 = Datez + (7 / 24)
End If
If Shift = "Evening" Then
Datez2 = Datez + (15 / 24)
End If
If Shift = "Midnight" Then
Datez2 = Datez + (23 / 24)
End If

Select Case Shift
Case "Day"
Datez2 = Datez + (7 / 24)
Case "Evening"
Datez2 = Datez + (15 / 24)
Case "Midnight"
Datez2 = Datez + (23 / 24)
End Select

See this link for more info

http://www.blueclaw-db.com/select_case.htm

 

[sincpaul]

The code did work for the first form that I made but I'm having issues with it on a second form that does a similar task but is used to input different information.

 

[sincpaul]

Thanks for the help guys figured it out. I changed the way the create_date was done from #' " NOW " '#, for some reason it didnt like that expression probably because its a set variable in VBA. Also Jackd there are some tabs the formating just did not come over with the paste but thanks for the tip with the Select Case will use that in the future.