By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

What's wrong?

P: n/a
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.Select = -1 WHERE
tblContacts.EventID = " & 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.

Dec 13 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
"Yvonne" <lo********@yahoo.comwrote in message
<11*********************@n67g2000cwd.googlegroups. 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.Select = -1 WHERE
tblContacts.EventID = " & 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
Dec 14 '06 #2

P: n/a
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.EventID = &EventID'.

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

dbs.Execute strSQL, dbFailOnError
Does this help?
Thanks
Yvonne

Dec 14 '06 #3

P: n/a
"Yvonne" <lo********@yahoo.comwrote in message
<11**********************@16g2000cwy.googlegroups. 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.EventID = &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
Dec 14 '06 #4

P: n/a
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.EventID = & EventID

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

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

UPDATE tblContacts SET tblContacts.Select = -1 WHERE
tblContacts.EventID = 13

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

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

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

Dec 14 '06 #5

P: n/a
"Yvonne" <lo********@yahoo.comwrote in message
<11*********************@l12g2000cwl.googlegroups. 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.EventID = & EventID

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

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

UPDATE tblContacts SET tblContacts.Select = -1 WHERE
tblContacts.EventID = 13

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

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

UPDATE tblContacts SET tblContacts.[Select] = -1 WHERE
tblContacts.EventID = 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.EventID = " & Me!txtEventID.Value

--
Roy-Vidar
Dec 14 '06 #6

P: n/a
..
>
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.EventID = " & Me!txtEventID.Value

--
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

Dec 14 '06 #7

P: n/a

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.EventID = " & Me!txtEventID.Value
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.PersonID = tblAttendance.PersonID) ON
tblEvent.EventID = tblAttendance.EventID SET tblContacts.[Select] = -1
WHERE tblAttendance.EventID = " & EventID
'
CurrentDb.Execute strSQL

Me.Refresh
Many thanks for your help.
Yvonne

Dec 14 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.