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]

It occurred to me that possibly 2 subreports would do the trick. Haven't tried yet...maybe there's a preferable solution.

Tom

 

[TheAceMan1]

Howdy THWatson . . .

Best I can think of is to put an [blue]Inputbox[/blue] along with your append query in a loop. Something like:

[blue]   Dim LBx As ListBox, SQL As String, Hrs
   
   Set LBx = Me![purple][B][I]YourListboxName[/I][/B][/purple]
   
   For Each idx In LBx.ItemsSelected
      Hrs = Inputbox(.....)
      Validate Hrs
      SQL = Your SQL Here
      DoCmd.RunSQL SQL
   Next[/blue]

In your append sql be sure to replace [blue]Forms!frmMakeUpActivities!txtHours[/blue] with [purple]Hrs[/blue].

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

Hi, TheAceMan1
Thanks for your suggestion. Before I get to try your solution, I am having a problem once I change the list box, lstMemberID to Multi-Select (simple).

The piece in the SQL code that is supposed to post the selections is

For ndx = 0 To Me.lstMemberID.ListCount - 1
   If Me.lstMemberID.Selected(ndx) Then
   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
   End If
Next ndx

That piece of code replaces the section in the original post that was

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

when the list box was single select.

For whatever reason, the code does NOT enter the MemberID in the table, puts everything else there but the MemberID.

I have stared at this, tried changing it, but no go.

??

Tom

 

[bcooler]

Well, it certainly works, I just thought Access could handle this a little more gracefully. Thanks so much for your idea. It really saved the rest of my hair!

 

[THWatson]

Well, I solved my problem with the MemberID not sticking in the table. The piece of code in the SQL is

 For ndx = 0 To Me.lstMemberID.ListCount - 1
   If Me.lstMemberID.Selected(ndx) Then
       SQL = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID, MakeupID, Comments, HoursSpent) VALUES (" & _
           Me.lstMemberID.ItemData(ndx) & ", #" & Me.txtMeetingDate & "#,True,2, Forms!frmMakeupActivities!lstMakeupType, Forms!frmMakeupActivities!txtComments, txtHours)"
       DoCmd.RunSQL SQL
   End If
Next ndx

which to me looks suspiciously like the piece of code I was already using.

Now to apply the Input box code piece.

Tom

 

[THWatson]

I'm glad that bcooler has saved his hair! Mine is going quickly. I hate to say it but I can't quite get how to meld TheAceMan1's code

Dim LBx As ListBox, SQL As String, Hrs
   
   Set LBx = Me!YourListboxName
   
   For Each idx In LBx.ItemsSelected
      Hrs = Inputbox(.....)
      Validate Hrs
      SQL = Your SQL Here
      DoCmd.RunSQL SQL
   Next

in with my piece

For ndx = 0 To Me.lstMemberID.ListCount - 1
   If Me.lstMemberID.Selected(ndx) Then
       SQL = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID, MakeupID, Comments, HoursSpent) VALUES (" & _
           Me.lstMemberID.ItemData(ndx) & ", #" & Me.txtMeetingDate & "#,True,2, Forms!frmMakeupActivities!lstMakeupType, Forms!frmMakeupActivities!txtComments, txtHours)"
       DoCmd.RunSQL SQL
   End If
Next ndx

I get errors on idx such as "needs to be object or variant".

I'm puzzled.

Tom

 

[THWatson]

Okay, here is what I have working, but not as well as I would like...

For ndx = 0 To Me.lstMemberID.ListCount - 1
   If Me.lstMemberID.Selected(ndx) Then
    
    Hrs = InputBox("Enter a number greater than 0 or leave blank", "")
       SQL = "INSERT INTO tblAttendance(MemberID, MeetingDate, Present, MeetingTypeID, MakeupID, Comments, HoursSpent) VALUES (" & _
           Me.lstMemberID.ItemData(ndx) & ", #" & Me.txtMeetingDate & "#,True,2, Forms!frmMakeupActivities!lstMakeupType, Forms!frmMakeupActivities!txtComments, Hrs)"
       DoCmd.RunSQL SQL
   End If
Next ndx

I get 2 prompts, one Input Box prompt, and then one Hrs prompt. Doesn't matter what you put in the Input Box, as it doesn't seem to count. The value you put in the Hrs prompt is what goes in the table.

Two questions:
1. How do I get fix this so I only get one prompt?
2. If I have selected 10 members from the list, how do I keep track of which member I am entering Hours for, given that the # of hours could be different for each member?

Tom

 

[THWatson]

There is a text box, txtHours, on the form that I put there when the form was used for single entry only.

I see that I can get rid of the Hrs prompt by changing the one line of the code to...

Me.txtHours = InputBox("Enter a number greater than 0 or leave blank", "")

The value in each case gets entered into the txtHours text box, but this doesn't change the question about "If I have selected 10 members from the list, how do I keep track of which member I am entering Hours for, given that the # of hours could be different for each member?"

Tom

 

[TheAceMan1]

THWatson . . .

What I provided was simply a [blue]logic flow[/blue], not actual code. Also ... its [blue]hard to follow[/blue] the thread if your going to change the code as soon as you get it! As an example, I gave you ...

[blue]For Each idx In LBx.ItemsSelected[/blue]

... which returns only the selected records in the listbox. You changed it to ...

[blue]For ndx = 0 To Me.lstMemberID.ListCount - 1[/blue]

... [red]which scans all records[/red] forcing you to detect the selected property I also added a listbox object:

[blue]   Dim LBx As ListBox ...
   Set LBx = Me!YourListboxName[/blue]

... which you declined to use. I can't fathom what you might have against objects.

Now since were handling Hours in code (via the Inputbox) you need to [blue]rem[/blue] out the following code:

[green]'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[/green]

This should take care of the extra messages. Then new code becomes:

[blue]   Dim LBx As ListBox, Hrs
   
   Set LBx = Me!lstMemberID
   
   For Each idx In LBx.ItemsSelected
      Hrs = InputBox("Enter a number greater than 0 or leave blank", LBx.Column(1, idx))
      SQL = [purple][B][I]Your SQL Here[/I][/B][/purple]
      DoCmd.RunSQL SQL
   Next[/blue]

Note: The members fullname appears in the inputbox titlebar.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[THWatson]

TheAceMan1
Sorry to be so long in replying. Long day...

Also, sorry for changing the code piece

For Each idx In LBx.ItemsSelected

to

For ndx = 0 To Me.lstMemberID.ListCount - 1

That was done simply because I couldn't make the other work. And I appreciate the understanding you have given me as to ndx scanning all the records.

As for the two other matters, I had included

 Dim LBx As ListBox ...
   Set LBx = Me!YourListboxName

For the sake of brevity of posting, I hadn't shown it because I had put it in up at the top of the code. I don't have anything against objects, and apologize for misleading you into thinking that I hadn't used that declaration.

I had also remmed out the section

ElseIf IsNull(Me.txtHours) Then ...

Now, to use

For Each idx In LBx.ItemsSelected
Hrs = InputBox("Enter a number greater than 0 or leave blank", LBx.Column(1, idx))
SQL = Your SQL Here
DoCmd.RunSQL SQL
Next

encounters problems. It results in "Variable not defined" error. So then I included it in the line

Dim LBx As ListBox, idx, Hrs

thus making the full piece of code

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

This results in a double prompt again. The first is the Input Box but the value entered is meaningless as it doesn't go into the table. Only the value you enter in the second prompt, Hrs, goes into the table...and by that time the Input Box which contained the name of the member for whom data was being entered is gone.

After some tinkering around, here is what I found does work.

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

I'm sure that I don't fully comprehend why the process you posted ends up in a double prompt with the Input Box not producing a value that sticks, and why once I change the code to have the Hrs value feed from the Input Box feed into the text box "txtHours" the second prompt for Hrs goes away.

Thanks for sticking with me on this.

Tom

 

[PHV]

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

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

 

[THWatson]

That does it. Thanks, PHV.

Tom

 

[THWatson]

By the way, is there any reason I shouldn't eliminate ndx completely, and change that to idx in the sql?

SQL = "INSERT INTO tblAttendance(MemberID,MeetingDate,Present,MeetingTypeID,MakeupID,Comments,HoursSpent) VALUES (" & _
   Me.lstMemberID.ItemData[b][COLOR=blue](idx)[/color][/b] & ",#" & Me.txtMeetingDate & "#,True,2,Forms!frmMakeupActivities!lstMakeupType,Forms!frmMakeupActivities!txtComments," & Hrs & ")"

Tom

 

[THWatson]

Hmmm. Just discovered that you can't leave the Input Box blank (hit OK rather than enter Hrs in it) or a Syntax error in INSERT INTO statement results. Works fine if you enter a value in the Input Box.

I checked the table, and entry in "HoursSpent" is not required.

Tom

 

[PHV]

Hrs = InputBox("Enter a number greater than 0 or leave blank", LBx.Column(1, idx), [!]0[/!])

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

 

[THWatson]

One additional thing I see I need to fix, and we're good to go.

I need to round the Hrs to the nearest .5 hour.

When I entered Hours in the text box, I had the following on the AfterUpdate event of txtHours.

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

I have tried inserting the Round function following the InputBox line, as in

Hrs = InputBox("Enter a number or leave blank", LBx.Column(1, idx), 0)
      Hrs = Round(Hrs / 0.5, 0) * 0.5
      SQL...

but that doesn't do it.

Tom

 

[GKChesterton]

Hrs = CInt(Hrs) 
Hrs = Round( Hrs / 0.5, 0) * 0.5

Input boxes return a string!

[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]

GKChesterton
Nope, that doesn't do it.

For example, if I input 1.4 hours, I would expect 1.5, but I get 1.

If I input 1.6 hours, I would expect 1.5, but I get 2.

In the table, the field setting for HoursSpent is Long Integer, 1 decimal place. I have also tried changing the number setting to Standard, Fixed, Single and Double.

Tom

 

[GKChesterton]

Long Integer would explain it by itself; certainly you need one of alternatives you cited. If those produce the same result, then the problem is the underlying source. If a query, the field properties may be set to 0 decimals or integer. I apologize for not reviewing the whole thread.

[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]​