469,150 Members | 1,951 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

Crosstab Query

Hi all,

Does anyone know of a way to do this please?

I have crosstab query with Team names in the rows, and Training in the columns. Teams that have completed the training have a number 1 in the field.

I also have an entry form where I can pick the above teams from a dropdown, and then the training they have completed from another dropdown.

What I am looking for is if a team has completed the training, a message box (or a warning) would appear advising the team has already completed the training when I select these from the dropdowns.

I'm not even sure if this is possible but would be grateful of any suggestions.

Thanks
Gareth
May 10 '11 #1
9 1224
Rabbit
12,516 Expert Mod 8TB
You could use the on change event of the drop down to run a DLookup to see if they have completed the training.
May 10 '11 #2
Would it be something like this

Dlookup("Team" , "Training_Query_Crosstab" , "Training = 1")
May 10 '11 #3
Rabbit
12,516 Expert Mod 8TB
Yeah, although now that I think about it, you would probably want to use DCount instead. One modification you will need to make though is to include the Team in the where parameter so you only count the ones for that team.
May 10 '11 #4
No problem, like this?

DCount("Training" , "Training_Query_Crosstab" , "Team = 1")
May 13 '11 #5
Rabbit
12,516 Expert Mod 8TB
It would be better to count *. And you probably want the training in the where parameter as well.

DCount("*" , "Training_Query_Crosstab" , "Team = 1 And Training=1")

Also, you may not want to go against the crosstab. It would be better to go against the table itself.
May 13 '11 #6
For some reason, this is not working :(

I have tried every combination I can think of but either get Error 13 type mismatch or Error 3075 Missing Operator in Query Expression

I have tried DCount and DLookup... this is my current code


Expand|Select|Wrap|Line Numbers
  1. Private Sub Training_Course_Change()
  2.  
  3. If (DLookup(Me.Training_Course, "Training_Query", [Team])) Then
  4. MsgBox ("Warning: The selected Team has already completed this Training")
  5.    Else
  6.        Me.Refresh
  7.    End If
  8.  
  9. End Sub
  10.  
May 14 '11 #7
I am getting closer with this one. I have combined the 2 fields [Training Course] and [Team] to make the 1 field [Count]. All I need to do is find if the text value for [Count] has occurred before. If so, fire a message box.

My current code in the Before Update event.
Expand|Select|Wrap|Line Numbers
  1. If DLookup("Count", "Training_Query", "Count = '" & Forms!trainingentry!Count & "'") Then
  2. MsgBox ("Warning: The selected Team has already completed this Training") 
  3.    Else 
  4.        Me.Refresh 
  5.    End If 
  6.  
I now only get an error when there is a duplicate which is Run-time error 13. Type Mismatch. When there is no duplicate, there is no error.

I have tried all day but cant get it to work.
May 14 '11 #8
NeoPa
32,165 Expert Mod 16PB
Hang fire Gareth. Someone may be able to step in here but Rabbit's away for about a week. I expect he'll drop in again when he's back, and someone else may find they can help, but you may need to be patient :-)
May 15 '11 #9
Got it!!! :)

Expand|Select|Wrap|Line Numbers
  1. Private Sub TrainingCourse_BeforeUpdate(Cancel As Integer)
  2.  
  3. If IsNull(DLookup("Count", "Training_Query", "Count = '" & Forms!TrainingEntry!Count & "'")) = False Then
  4. MsgBox Me.Team & " has already completed " & Me.TrainingCourse & "  ", vbSystemModal, ""
  5. End If
  6. End Sub
  7.  

Works perfect. Thanks Rabbit for your help.

Gareth
May 15 '11 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Judy | last post: by
6 posts views Thread by tizmagik | last post: by
2 posts views Thread by Jim Devenish | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.