Counting Number of Selections

[Guest]

I have a database that allows People to select 4 Golfers. I have a Table
Called Pick That has a AutoNumber (Primary) field named PickNo and Name and
I
have a Table called Choice that has a AutoNumber (Primary) ChoiceNo, PickNo
(Foreign) and GolferID (Foreign) that references GolferID in the Golfer
Table. I have the Choice Table as a Sub form in the Picks form. It works
great. My problem ... I need to find a way to assign a value to the ChoiceNo
in a query other than its ChoiceNo. Since you only pick 4 golfers I would
like each Golfer Selected in a Pick to have a Value of 1 to 4 based on when
selected. This would make it easier to create a cross tab query. Right now
since the ChoiceNo is auto Incremented it is difficult to create an
organized
Cross Tab query. I would like my query to look like this. I gave the field a
name called Selected.
Name PickNo ChoiceNo Golfer Selected
Bob 3 9 Tiger Woods 1
Bob 3 10 David Duval 2
Bob 3 11 Davis Love 3
Bob 3 12 Jim Furyk 4
Joe 4 13 David Duval 1
Joe 4 14 Tom Watson 2
Joe 4 15 Tiger Woods 3
Joe 4 16 Fred Couples 4

I would probably make ChoiceNo Not visible. I just put it there to help make
what I need clearer. I tried making a counter with the Expression Builder
for
the Selected field but had no luck. I also recently was given a suggestion
to insert this code on the Before Insert of the subform to assign the
Selected valuebut had no luck.

Me.Selected = Nz(DMax("[Selected]","Choice","[PickNo]=" & Me.PickNo),0)+1

It kept telling me it could not find the macro.

Any Suggestions.

Regards,

Chris

 

[Ken Snell [MVP]]

The code that you were given does not go into the box next to the Before
Insert property in the Properties window. Instead, that box should contain
[Event Procedure], and the code step that you were given goes into the VBA
module behind the form for that event.

Go to the Before Insert box, select [Event Procedure] from the dropdown
list, click on the three-dot button at far right side of the box, and select
Code Window. You'll then see a screen with three lines (second one will be
blank):

Private Sub Form_BeforeInsert(Cancel As Integer)

End Sub


Type the code step that you were given on the blank line.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Selected = Nz(DMax("[Selected]","Choice","[PickNo]=" & Me.PickNo),0)+1
End Sub

 

[Guest]

Thanks for the help Ken but I keep getting the 3075 error saying that there
is a missing operator. I looked up the error on Microsoft and still am
confused. I also keep getting syntax can't find Macro Me. any other
suggestions. Thanks

Chris

 

[Ken Snell [MVP]]

You did not put the code in the VBE module, it appears you're still trying
to put in the box in the Properties window.

Open the form in design view. Click on the Properties icon in the toolbar.
Click on the Event tab. Go to the Before Insert box, select [Event
Procedure] from the dropdown list, click on the three-dot button at far
right side of the box, and select Code Window. You'll then see a screen with
three lines (second one will be blank):

Private Sub Form_BeforeInsert(Cancel As Integer)

End Sub


Type the code step that you were given on the blank line.

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Selected = Nz(DMax("[Selected]","Choice","[PickNo]=" & Me.PickNo),0)+1
End Sub


--

Ken Snell
<MS ACCESS MVP>

Thanks for the help Ken but I keep getting the 3075 error saying that
there
is a missing operator. I looked up the error on Microsoft and still am
confused. I also keep getting syntax can't find Macro Me. any other
suggestions. Thanks

Chris

I have a database that allows People to select 4 Golfers. I have a Table
Called Pick That has a AutoNumber (Primary) field named PickNo and Name
and
I
have a Table called Choice that has a AutoNumber (Primary) ChoiceNo,
PickNo
(Foreign) and GolferID (Foreign) that references GolferID in the Golfer
Table. I have the Choice Table as a Sub form in the Picks form. It works
great. My problem ... I need to find a way to assign a value to the
ChoiceNo
in a query other than its ChoiceNo. Since you only pick 4 golfers I
would
like each Golfer Selected in a Pick to have a Value of 1 to 4 based on
when
selected. This would make it easier to create a cross tab query. Right
now
since the ChoiceNo is auto Incremented it is difficult to create an
organized
Cross Tab query. I would like my query to look like this. I gave the
field a
name called Selected.
Name PickNo ChoiceNo Golfer Selected
Bob 3 9 Tiger Woods 1
Bob 3 10 David Duval 2
Bob 3 11 Davis Love 3
Bob 3 12 Jim Furyk 4
Joe 4 13 David Duval 1
Joe 4 14 Tom Watson 2
Joe 4 15 Tiger Woods 3
Joe 4 16 Fred Couples 4

I would probably make ChoiceNo Not visible. I just put it there to help
make
what I need clearer. I tried making a counter with the Expression
Builder
for
the Selected field but had no luck. I also recently was given a
suggestion
to insert this code on the Before Insert of the subform to assign the
Selected valuebut had no luck.

Me.Selected = Nz(DMax("[Selected]","Choice","[PickNo]=" &
Me.PickNo),0)+1

It kept telling me it could not find the macro.

Any Suggestions.

Regards,

Chris