Form to Query Problem

[Terminus]

I have built an form in which users select are able to find which contacts have attended 2 or more events. The underlying table holding the info looks like:

ContactID EventID
1 1
1 3
2 3
3 1
3 2
4 1

The query i use in query builder works, it is:

INSERT INTO tblMSContactID ( contactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID)
IN (SELECT ContactID
FROM tbllinkEvents
WHERE EventsID = 15))
AND ((tbllinkEvents.EventsID)=31));

However when i use enter the query into the back of the form using vb it builds the statement

INSERT INTO tblMSContactIDa ( ContactID )
SELECT DISTINCT tbllinkEvents.ContactID
FROM tbllinkEvents
WHERE (((tbllinkEvents.ContactID) In (SELECT ContactID FROM tbllinkevents WHERE EventsID = 31 AND EventsID = 15)));

and for some reason this query wont work. Ihave tried changing the brackets, but when it builds the query it keeps changing them back 2 the SQL statement that cant work.

Is there any way of getting Vb to build the query correctly? Or is there another statement i can use to select a contacts ID?

Really neeed help!!!

Ross

 

[MarkWright]

Hello Ross,
have you tried:
WHERE (((tbllinkEvents.ContactID) In (SELECT ContactID FROM tbllinkevents WHERE (EventsID = 31) AND (EventsID = 5) )));

have a nice day
Mark

 

[Terminus]

Tried it, same as before. I get no error message but it doesnt like the SQL and does not contacts are not inserted into the table

Ross

 

[MarkWright]

Good Morning Ross,
are you using access 2000 with a SQL Server by chance ? If so the Insert into does not work there.

Have you tried making a query its self and seeing if it works outside a form.

And are you sure there is a contact that has attended Event 31 and 15. If not then the sql works and nothing will be shown.(just a thought)

Have a nice day
Mark

 

[Terminus]

Hi Mark

Thanks for the reply,

No its just an access 2000 db with no links no any sql servers or anything. And unfortunately there are contacts who have attended both 31 and 15.

Ive also built the query in the query builder and that works, however when its built in the vb environment and run all the brackets get moved.

Is there any chance i could send u a mini version on the db to take a look at just incase i am making a simple error somewhere?

Ross

 

[MarkWright]

Hello Ross,
sure no problem. I'll take a look at it when I get in the office tomorrow.
Send it to mark.wright@pbs-software.com

I'm living in Germany so I'm 6 hrs ahead of eastern standard time. Otherwise I'ed look at it today.

Have a nice day
Mark