What's wrong?

[Yvonne]

We have a Contacts & Events database made up of three tables viz
tblContacts, tblEvents and tblAttendance with the latter linking
contacts to particular events.

The tblContacts table has a Yes/No field called 'Select".

When we want to view the Contacts for a particular event, we do so in
a continuous form driven by a query.

What we want to do is have a button that will update all the 'Select'
boxes to Yes within this event only. I've tried the following but it
doesn't work. Can someone tell me what's wrong?

Private Sub Command97_Click ()
Dim strSQL As String
Debug.Print strSQL
Set dbs = CurrentDb


strSQL = "UPDATE tblContacts SET tblContacts.Sel ect = -1 WHERE
tblContacts.Eve ntID = " & EventID

Debug.Print strSQL

dbs.Execute strSQL, dbFailOnError
Me.Refresh

End Sub

I appreciate that itwas unfortunate to have the field call 'Select' but
it's too late to change now.

[RoyVidar]

Re: What's wrong?

"Yvonne" <loyalty2uk@yah oo.comwrote in message
<1166038437.463 045.16360@n67g2 000cwd.googlegr oups.com>:

We have a Contacts & Events database made up of three tables viz
tblContacts, tblEvents and tblAttendance with the latter linking
contacts to particular events.
>
The tblContacts table has a Yes/No field called 'Select".
>
When we want to view the Contacts for a particular event, we do so
in a continuous form driven by a query.
>
What we want to do is have a button that will update all the 'Select'
boxes to Yes within this event only. I've tried the following but it
doesn't work. Can someone tell me what's wrong?
>
Private Sub Command97_Click ()
Dim strSQL As String
Debug.Print strSQL
Set dbs = CurrentDb
>
>
strSQL = "UPDATE tblContacts SET tblContacts.Sel ect = -1 WHERE
tblContacts.Eve ntID = " & EventID
>
Debug.Print strSQL
>
dbs.Execute strSQL, dbFailOnError
Me.Refresh
>
End Sub
>
I appreciate that itwas unfortunate to have the field call 'Select'
but it's too late to change now.

Can you be a bit more descriptive than "doesn't work", what happens,
what doesn't happen, what is the errormessage...

If the field name is the only problem, and if you can't change it,
try to [bracket] it.

....SET tblContacts.[Select] = -1 WHERE...

--
Roy-Vidar

[Yvonne]

Re: What's wrong?

Can you be a bit more descriptive than "doesn't work", what happens,
what doesn't happen, what is the errormessage...
>
If the field name is the only problem, and if you can't change it,
try to [bracket] it.
>
...SET tblContacts.[Select] = -1 WHERE...
>
--
Roy-Vidar

Sorry, I should have given a better picture of it. Here goes......

What happens is that the VB window pops up and advises :-

Error 3075:
Syntax error (Missing Operator) in query expression
'tblContacts.Ev entID = &EventID'.

When I click on Debug, the following is highlighted in yellow.

dbs.Execute strSQL, dbFailOnError


Does this help?


Thanks
Yvonne

[RoyVidar]

Re: What's wrong?

"Yvonne" <loyalty2uk@yah oo.comwrote in message
<1166042982.867 812.208520@16g2 000cwy.googlegr oups.com>:

>Can you be a bit more descriptive than "doesn't work", what happens,
>what doesn't happen, what is the errormessage...
>>
>If the field name is the only problem, and if you can't change it,
>try to [bracket] it.
>>
>...SET tblContacts.[Select] = -1 WHERE...
>>
>--
>Roy-Vidar

>
Sorry, I should have given a better picture of it. Here goes......
>
What happens is that the VB window pops up and advises :-
>
Error 3075:
Syntax error (Missing Operator) in query expression
'tblContacts.Ev entID = &EventID'.
>
When I click on Debug, the following is highlighted in yellow.
>
dbs.Execute strSQL, dbFailOnError
>
>
Does this help?
>
>
Thanks
Yvonne

Sounds strange. This seems to indicate the contents of whatever EventID
is, is "&EventID" - I don't understand this ;-)

What is the content of your debug.print (hit ctrl+g to find)?

--
Roy-Vidar

[Yvonne]

Re: What's wrong?

don't understand this ;-)

>
What is the content of your debug.print (hit ctrl+g to find)?
>
--
Roy-Vidar

Ok this gives me:-

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID

UPDATE tblContacts SET tblContacts.Sel ect = -1 WHERE
tblContacts.Eve ntID = 13



UPDATEtbl Contacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13

[RoyVidar]

Re: What's wrong?

"Yvonne" <loyalty2uk@yah oo.comwrote in message
<1166044188.816 010.70730@l12g2 000cwl.googlegr oups.com>:

don't understand this ;-)

>>
>What is the content of your debug.print (hit ctrl+g to find)?
>>
>--
>Roy-Vidar

>
Ok this gives me:-
>
UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID
>
UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID
>
UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = & EventID
>
UPDATE tblContacts SET tblContacts.Sel ect = -1 WHERE
tblContacts.Eve ntID = 13
>
>
>
UPDATEtbl Contacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13
>
UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13
>
UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.Eve ntID = 13

Very strange. Sometimes it will give you an unlucky number, other times
just & EventID.

What is EventID?

If it is a form control, try a fully qualified reference (my preference
would be to also name it something other than what seems to be a field
name - for instance prefix with txt) - for instance

.... WHERE tblContacts.Eve ntID = " & Me!txtEventID.V alue

--
Roy-Vidar

[Yvonne]

Re: What's wrong?

..

>
What is EventID?
>
If it is a form control, try a fully qualified reference (my preference
would be to also name it something other than what seems to be a field
name - for instance prefix with txt) - for instance
>
... WHERE tblContacts.Eve ntID = " & Me!txtEventID.V alue
>
--
Roy-Vidar

EventID is the Primary key that links the tblEvents and tblAttendance.

Many thanks for your help, I'll give your latest suggestion a try out
tomorrow, but I'm getting a bit be-draggled now, having been at it alll
day up until now.

Yvonne

[Yvonne]

Re: What's wrong?


Yvonne wrote:

.

What is EventID?

If it is a form control, try a fully qualified reference (my preference
would be to also name it something other than what seems to be a field
name - for instance prefix with txt) - for instance

... WHERE tblContacts.Eve ntID = " & Me!txtEventID.V alue

Got up early and had another go at it after I realised that the
continuous form's query involved the three tables. Here it is:-

Dim strSQL As String

Debug.Print strSQL
strSQL = "UPDATE tblEvent INNER JOIN (tblContacts INNER JOIN
tblAttendance ON tblContacts.Per sonID = tblAttendance.P ersonID) ON
tblEvent.EventI D = tblAttendance.E ventID SET tblContacts.[Select] = -1
WHERE tblAttendance.E ventID = " & EventID
'
CurrentDb.Execu te strSQL

Me.Refresh


Many thanks for your help.
Yvonne