Batch entries with variable fields 1

[THWatson]

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, and I appreciate his concerns.

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

 

[THWatson]

I think I will take this approach:

1. Create a table tblNewAttendance with the same structure as tblAttendance
2. Create a continuous form based on tblNewAttendance.
3. Upon opening the form, delete all records from tblNewAttendance.
4. Place a command button cmdSave on the form to copy the records from tblNewAttendance to tblAttendance

Tom

 

[TheAceMan1]

How are ya THWatson . . .



If the [blue]PrimaryKey[/blue] of tblAttendance is a numeric incrementing type, then batching for a form as you prescribe could be done by adding the following criteria to a query or SQL as the forms [blue]rowsource[/blue]:

[blue]   WHERE ([AttendID]>DMax("[AttendID]","tblAttendance")[/blue]

The above would show only newly entered records and you can save, edit, delete normally! To start a new batch all you need is a button that requeries the form. To show all you simply write the SQL to the rowsource without the where clause.

[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

I really like your suggestion. Good approach!

One question about the form. I want to put a text box on the form that will show the current record count. The text box I have fashioned has this Control Source:

="Record " & [Forms]![frmNewMakeupActivities].[CurrentRecord] & " of " & Count(*)

However, the Count(*) part of the expression doesn't keep up, and is always one record behind.

Is there a way to fix this. Microsoft's built-in navigation buttons seems to have a way to do it.

Tom

 

[TheAceMan1]

THWatson . . .

[blue][purple][b]Change:[/b][/purple] Count(*)
[purple][b]To    :[/b][/purple] Forms!frmNewMakeupActivities.Recordset.recordCount[/blue]

You'll also need to add ...

[blue]   Me.ReCalc[/blue]

... to the forms [blue]OnCurrent[/blue] event.




See Ya! . . . . . .

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

 

[THWatson]

That doesn't change the result.

The thing I don't like about this is what ended up showing in the text box...

Record 1 or 0 (built in navigation button shows "1 of 1"
Record 2 of 1 (built in navigation button shows "2 of 2"

That's what I mean by "the expression not keeping up."

Tom

 

[THWatson]

Maybe I'm just better off to leave the custom counter box off the form, and use the built-in Navigation button count.

Tom

 

[TheAceMan1]

THWatson . . .

Remove the expression in the [blue]ControlSource[/blue] of the textbox. Remove the [blue]recalc line[/blue] in the [blue]OnCurrent[/blue] event. Then copy/paste the following to the [blue]OnCurrent[/blue] event:

[blue]   Dim ctl As Control, MaxCnt As Long
   
   Set ctl = Me![purple][B][I]YourTextboxName[/I][/B][/purple]
   MaxCnt = Me.Recordset.RecordCount
   
   If MaxCnt = 0 Then
      ctl = "Record 0 of 0"
   ElseIf Me.NewRecord Then
      ctl = "Record " & Me.CurrentRecord & " of " & MaxCnt + 1
   Else
      ctl = "Record " & Me.CurrentRecord & " of " & MaxCnt
   End If
   
   Set ctl = Nothing[/blue]

Perform your testing! . . .

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

See Ya! . . . . . .

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

 

[THWatson]

Hey, that's great!

Would you go for changing the line

ctl = "Record 0 of 0"
to [COLOR=blue]ctl = "Record 1 of 1"[/color]

Otherwise, the form opens showing "Record 0 of 0" and that's just fine...but when you enter the first field it would be nice to shift to "Record 1 of 1" and it doesn't do that.

Tom

 

[TheAceMan1]

[blue]Would you go for changing the line ...[/blue]

If it suites your needs ... by all means make it so!

See Ya! . . . . . .

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