Form design issue 3

[THWatson]

Using Access 2003, Access 2000 format

An unbound form called "frmMakeupActivities". This is used to enter a Member's name, the date of the Makeup Activity, the type of Makeup Activity, and the # of hours spent.

A single select list box, lstMemberID, is on the form. Its SQL is

SELECT tblMembers.MemberID, [LastName] & ", " & [PreferredName] AS FullName, tblMembers.LastName, tblMembers.PreferredName, tblMembers.Status
FROM tblMembers
WHERE (((tblMembers.Status)="Active" Or (tblMembers.Status)="Senior"))
ORDER BY tblMembers.LastName, tblMembers.PreferredName;

The user selects a Member, a Date of Activity, a type of Activity from another list box called lstMakeupType, and the # of hours, if an Hours entry is desired (sometimes not). Then the user presses a Post command button to post the data to tblAttendance. The SQL behind the Post command button is

Private Sub cmdPost_Click()
On Error GoTo Err_cmdPost_Click

Dim sql As String

If IsNull(Me.lstMemberID) Then
    MsgBox "Please select Member from list.", vbExclamation, "Member needed"
    Me.Undo
    Me.lstMemberID.SetFocus
    Exit Sub
    'End If

ElseIf IsNull(Me.txtMeetingDate) Then
    MsgBox "Record cannot be saved without a Makeup Date!", vbExclamation, "Makeup Date required."
    Me.Undo
    Me.txtMeetingDate.SetFocus
    Exit Sub
    'End If
    
ElseIf IsNull(Me.lstMakeupType) Then
    MsgBox "Please select Makeup Meeting Type from list.", vbExclamation, "Makeup Meeting Type needed"
    Me.Undo
    Me.lstMakeupType.SetFocus
    Exit Sub
    'End If

ElseIf IsNull(Me.txtHours) Then
   Select Case MsgBox("Do you wish to enter" _
                       & vbCrLf & "         HOURS" _
                       & vbCrLf & "    for this Activity?" _
                       , vbYesNo Or vbExclamation Or vbDefaultButton1, "Check for Hours")
            Case vbYes
                Me.Undo
                Me.txtHours.SetFocus
                Exit Sub
            Case vbNo
                GoTo PostProcedure
        End Select
    Exit Sub
End If

PostProcedure:
DoCmd.SetWarnings False
    sql = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID, MakeupID, Comments, HoursSpent) VALUES(Forms!frmMakeUpActivities!lstMemberID , #" & Me.txtMeetingDate & "#, True,2,Forms!frmMakeUpActivities!lstMakeupType, Forms!frmMakeUpActivities!txtComments, Forms!frmMakeUpActivities!txtHours)"
    DoCmd.RunSQL sql
DoCmd.SetWarnings True

Dim Response As Variant
Dim Response2 As Variant
Dim MyDate As Date
  MyDate = Me.txtMeetingDate
  
    Response = MsgBox("Do you wish to Post another Makeup Meeting?", vbYesNo, "Posting check")
    If Response = vbYes Then
        Response2 = MsgBox("For the SAME Date or a DIFFERENT Date?" _
        & vbCrLf & vbCrLf & "If SAME Date select <Yes>, if DIFFERENT Date select <No>.", vbYesNo, "Same or Different Date check")
            If Response2 = vbYes Then
            
           
        Me.lstMemberID = Null
        Me.lstMakeupType = Null
        Me.txtHours = ""
        Me.lstMemberID.SetFocus
            Else
            Me.txtMeetingDate = Null
            Me.lstMemberID = Null
            Me.lstMakeupType = Null
            Me.txtHours = ""
            Me.txtMeetingDate.SetFocus
        End If
    Else
    DoCmd.Close
    DoCmd.OpenForm "frmMainMenu"
    End If


Exit_cmdPost_Click:
    Exit Sub

Err_cmdPost_Click:
    MsgBox Err.Description
    Resume Exit_cmdPost_Click
    
End Sub

Now, 2 or more Members may well involve themselves in the same Makeup Activity on the same date...however, the # of hours spent may or may not be the same. For this reason I have restricted the form to the entry of 1 member and one activity at a time.

However, it would be quicker for the user to select more than one member at the same time, and for the same date. The CLINKER is the # of Hours, which may or may not be the same...and the Hours entry may be anywhere from blank to any given number.

Any suggestion to push me in the right direction.

Tom

 

[THWatson]

Well, I went into the table and changed the Number field size to Double and the Format to Fixed.

An entry now of 1.4 produces 1, and an entry of 1.6 produces 2. Both of those should produce 1.5

As I said, when I entered the Hours by a text box, and used this code on the AfterUpdate event for the text box

Me.txtHours = Round(Hrs / 0.5, 0) * 0.5

the result in the table was accurate to the nearest .5 hour, in spite of the Number being an Integer type.

I'm confuddled.

Tom

 

[GKChesterton]

You're on the path. If the form is based a query (which generally is a good practice), it has an intermediary role in formatting which can be adjusted in "field properties" in the query design view.

But the problem could be my mistaken advice. CInt converts the string from the input box to an integer, which is precisely what we don't want. Try CSng().

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PHV]

How is dimmed Hrs ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

Aha! I changed the Number field size to Single, and in the code I changed CInt to CSng. Works! Now with 1.74 I get 1.5 and with 1.76 I get 2.

I guess what I don't understand is why the Round function would work to insert the correct value from a text box, but not once it is being inserted from an InputBox. Interesting.

However, the big thing is that it works.

Thanks a lot for your help.

(by the way, are you related to the late Gilbert Keith Chesterton?)

Tom

 

[THWatson]

PHV
Hrs is declared

Dim LBx As ListBox, idx, Hrs

It is my understanding that this would declare Hrs as Variant. Correct?

I had left it like that, to allow for the possibility of leaving the InputBox blank, which is a moot point now that I added, at your suggestion, the default value of 0.

Tom

 

[GKChesterton]

The response from an input box is always a string. That's why the behavior is different.

If left blank, the response is an empty string ("").

Correct that your syntax for dim makes Hrs a variant. However you can see that gives no particular advantage, given the above.

PS: In spirit, I hope, but not otherwise.

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[THWatson]

Thanks. Good spirit to be in sync with. It's been some time...perhaps as long as 30 years...since I read any of his works, but he's still occasionally quoted in other material.

Tom

 

[PHV]

The response from an input box is always a string
A workaround:
myVar = Val(InputBox(...))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[THWatson]

PHV
Bearing in mind that I need to be able to round to the nearest .5 hour, which method is better, or preferable?
1. Have the table field value at Long Integer, and do the myVal workaround
2. Have the table field value as Single, and use CSng

Tom

 

[GKChesterton]

Err ... if you want the table to store n.5, an integer is by definition not viable!

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​

 

[PHV]

Have the table field value at Long Integer
As already stated you can't store any decimal value in an integral variable.

 

[THWatson]

Ok, here's my code piece now...

For Each idx In LBx.ItemsSelected
      Hrs = Val(InputBox("Enter a number or leave blank", LBx.Column(1, idx), 0))
      Hrs = Round(Hrs / 0.5, 0) * 0.5
      SQL = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID, MakeupID, Comments, HoursSpent) VALUES (" & _
           Me.lstMemberID.ItemData(idx) & ", #" & Me.txtMeetingDate & "#,True,2, Forms!frmMakeupActivities!lstMakeupType, Forms!frmMakeupActivities!txtComments, " & Hrs & ")"
      DoCmd.RunSQL SQL
   Next

That does the appropriate rounding to the nearest 1/2 Hour.

I'm hopeful I can soon put this puppy to bed. Thanks again for all of your helpful insights.

Tom