SQL Not working

[gary.newport]

I have 3 unbound fields on my form, entitled txtActivity, txtDate and txtLeader. The first and last are combo boxes and the middle one is a simple text box which allows for a date picker to be used.

The record source of the form is set to tblActivityStaffDate; the table on which I need this and all subforms to relate to.

You select the activity and the date. The SQL behind txtLeader looks at both these fields and limits the list of Leaders to those who have undertaken that activity on that date. If there are no leaders for that date then all leaders are given.

The table itself has 4 fields, three of these are directly related to the unbound fields above. The final field is the primary key and is used to relate this table to other tables in the structure.

I need a fourth field to show the primary field for the selected data. So if I choose Tennis on 24/03/2010 and it was lead by Frank it would show the value 24; it's primary key value.

I have placed this SQL as part of the VBA for the After Update on the txtLeader, txtDate and txtActivity fields.

SELECT tblActivityStaffDate.WeeklyActivity_ID FROM tblActivityStaffDate WHERE (((tblActivityStaffDate.Activity)=forms.frmNewRegister.txtActivity) And ((tblActivityStaffDate.[Staff Member])=forms.frmNewRegister.txtLeader) And ((tblActivityStaffDate.dteDate)=forms.frmNewRegister.txtDate)) GROUP BY tblActivityStaffDate.WeeklyActivity_ID;

But when I run the form it simply cycles through all of the values stored as primary fields.

Any ideas anyone?

 

[jdraw]

I have 3 unbound fields on my form, entitled txtActivity, txtDate and txtLeader. The first and last are combo boxes and the middle one is a simple text box which allows for a date picker to be used.

The record source of the form is set to tblActivityStaffDate; the table on which I need this and all subforms to relate to.

You select the activity and the date. The SQL behind txtLeader looks at both these fields and limits the list of Leaders to those who have undertaken that activity on that date. If there are no leaders for that date then all leaders are given.

The table itself has 4 fields, three of these are directly related to the unbound fields above. The final field is the primary key and is used to relate this table to other tables in the structure.

I need a fourth field to show the primary field for the selected data. So if I choose Tennis on 24/03/2010 and it was lead by Frank it would show the value 24; it's primary key value.

I have placed this SQL as part of the VBA for the After Update on the txtLeader, txtDate and txtActivity fields.

SELECT tblActivityStaffDate.WeeklyActivity_ID FROM tblActivityStaffDate WHERE (((tblActivityStaffDate.Activity)=forms.frmNewRegister.txtActivity) And ((tblActivityStaffDate.[Staff Member])=forms.frmNewRegister.txtLeader) And ((tblActivityStaffDate.dteDate)=forms.frmNewRegister.txtDate)) GROUP BY tblActivityStaffDate.WeeklyActivity_ID;

But when I run the form it simply cycles through all of the values stored as primary fields.

Any ideas anyone?

What are you trying to do in plain English?

What did you expect to happen?

 

[gary.newport]

Sorry, Swahili is so much easier to use.

Okay, I want someone to select from the 3 combo boxes (or enter new data) and, depending on the selection or data entered, I want the subforms to show any relevant data. Simples.

The data that the three unbound combo boxes draw their data from are NOT primary keys but the combination selected WILL point to an existing unique record which will have a primary key or a new record will need to be create (preferably when the form is closed).

I want the two subforms to display any relevant existing data that is related to the data selected in the combo boxes.

Any clearer?