Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Access 2003 (2000 format)
    The database is for the local Kiwanis club.

    I am searching for the best method to enter a batch of new Makeup Activity records in tblAttendance.

    The relevant fields are…
    1. MeetingDate (Date field)
    2. MemberID (Number field, long integer, joined to MemberID in tblMembers)
    3. MakeupID (Number field, long integer, joined to MakeupID in tblMakeups). This field is the type of Makeup Activity to be entered, selecting from 25 available activities)
    4. HoursSpent (Number field, long integer).

    To date, I have an unbound form, with a list box for the Members, a combo box for the Activities, text boxes for the Meeting Date and Hours Spent, and the club Secretary enters one member at a time. The reason I designed it this way is because of the MakeupID and HoursSpent fields vary for each member being entered. If these two fields weren’t individually variable by member, one could select any number of members from the list box and post.

    Even considering the variable fields, the club secretary would prefer to make batch entries. He finds it cumbersome to have to enter one member at a time.

    I can use a bound form in continuous form and data entry mode, using combo boxes for the MemberID and MakeupID fields, and text boxes for the Date and HoursSpent fields. My concern about this approach is that entries are posted as the user moves to the next row, leaving open the potential for errors, the need to delete incorrect entries etc.

    So I would prefer an Unbound form where the Secretary doesn’t post that batch of entries until he is certain all are correct.

    Any suggestions as to approach would be appreciated.

    Thanks.

    Tom

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could create an unbound form with for example 10 sets of controls for the fields that you want to fill, i.e. text boxes MeetingDate1 through MeetingDate10, combo boxes MemberID1 through MemberID10 etc.

    Put a command button cmdSave in the form header or footer that does the following:
    - Open a recordset (DAO or ADODB) on tblAttendance.
    - Perform a loop For i = 1 To 10 ... Next i
    - Within the loop, you can refer to Me.Controls("MeetingDate" & i) etc.
    - If the controls meet the requirements (MeetingDate and MemberID filled in, ...), use AddNew to add a new record to the recordset.
    - Populate the fields.
    - Use the update method of the recordset when done.
    - After the loop, close the recordset.
    Also place a command button cmdClear on the form that clears the controls, so that the user can start a new batch.

    Or you could use a completely different approach:
    - Create a table with the same structure as tblAttendance, say tblNewAttendance.
    - Create a continuous form based on tblNewAttendance.
    - Before opening the fom, delete all records from tblNewAttendance.
    - Place a command button cmdSave in the form header or footer that copies the records from tblNewAttendance to tblAttendance.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='791345' date='30-Aug-2009 09:18']You could create an unbound form with for example 10 sets of controls for the fields that you want to fill, i.e. text boxes MeetingDate1 through MeetingDate10, combo boxes MemberID1 through MemberID10 etc.

    Put a command button cmdSave in the form header or footer that does the following:
    - Open a recordset (DAO or ADODB) on tblAttendance.
    - Perform a loop For i = 1 To 10 ... Next i
    - Within the loop, you can refer to Me.Controls("MeetingDate" & i) etc.
    - If the controls meet the requirements (MeetingDate and MemberID filled in, ...), use AddNew to add a new record to the recordset.
    - Populate the fields.
    - Use the update method of the recordset when done.
    - After the loop, close the recordset.
    Also place a command button cmdClear on the form that clears the controls, so that the user can start a new batch.

    Or you could use a completely different approach:
    - Create a table with the same structure as tblAttendance, say tblNewAttendance.
    - Create a continuous form based on tblNewAttendance.
    - Before opening the fom, delete all records from tblNewAttendance.
    - Place a command button cmdSave in the form header or footer that copies the records from tblNewAttendance to tblAttendance.[/quote]
    Thanks, Hans
    I am taking a run at your "completely different approach."

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •