update query problem

[jacks]

I have an update query :

vs3 = "UPDATE (wedstrijd INNER JOIN club AS a ON wedstrijd.[club a] = a.[club-id]) INNER JOIN club AS b ON wedstrijd.[club b] = b.[club-id], [schema] INNER JOIN regel ON schema.[schema-id] = regel.[schema-id] SET wedstrijd.[voorspel a] = regel.[goals-home], wedstrijd.[voorspel b] = regel.[goals-away] WHERE (((regel.[home klasse])=[a].[klasse-id]) AND ((regel.[away klasse])=.[klasse-id]) AND ((regel.[schema-id])=[schema].[schema-id]) AND ((wedstrijd.[club a])=[a].[club-id]) AND ((wedstrijd.[club b])=.[club-id]) AND ((schema.naam)=[Forms]![schema]![naam]))"

which is not working.

The bold part doesn't seem to work.. How can I make sure that the query is executed with the current opened form ? (which the bold part shoud do). I think it's a syntactic thing..

 

[stopher]

If schema.naam (is that like a Keema Naan?) is a string then:

vs3 = "UPDATE (wedstrijd INNER JOIN club AS a ON wedstrijd.[club a] = a.[club-id]) INNER JOIN club AS b ON wedstrijd.[club b] = b.[club-id], [schema] INNER JOIN regel ON schema.[schema-id] = regel.[schema-id] SET wedstrijd.[voorspel a] = regel.[goals-home], wedstrijd.[voorspel b] = regel.[goals-away] WHERE (((regel.[home klasse])=[a].[klasse-id]) AND ((regel.[away klasse])=.[klasse-id]) AND ((regel.[schema-id])=[schema].[schema-id]) AND ((wedstrijd.[club a])=[a].[club-id]) AND ((wedstrijd.[club b])=.[club-id]) AND ((schema.naam)='" & [Forms]![schema]![naam] & "'))"

If schema.naam is a number then:

vs3 = "UPDATE (wedstrijd INNER JOIN club AS a ON wedstrijd.[club a] = a.[club-id]) INNER JOIN club AS b ON wedstrijd.[club b] = b.[club-id], [schema] INNER JOIN regel ON schema.[schema-id] = regel.[schema-id] SET wedstrijd.[voorspel a] = regel.[goals-home], wedstrijd.[voorspel b] = regel.[goals-away] WHERE (((regel.[home klasse])=[a].[klasse-id]) AND ((regel.[away klasse])=.[klasse-id]) AND ((regel.[schema-id])=[schema].[schema-id]) AND ((wedstrijd.[club a])=[a].[club-id]) AND ((wedstrijd.[club b])=.[club-id]) AND ((schema.naam)=" & [Forms]![schema]![naam] & "))"

Note if you look carefully you'll see single quotes used in the string version.
hth
Chris

 

[jacks]

If schema.naam (is that like a Keema Naan?) is a string then:

vs3 = "UPDATE (wedstrijd INNER JOIN club AS a ON wedstrijd.[club a] = a.[club-id]) INNER JOIN club AS b ON wedstrijd.[club b] = b.[club-id], [schema] INNER JOIN regel ON schema.[schema-id] = regel.[schema-id] SET wedstrijd.[voorspel a] = regel.[goals-home], wedstrijd.[voorspel b] = regel.[goals-away] WHERE (((regel.[home klasse])=[a].[klasse-id]) AND ((regel.[away klasse])=.[klasse-id]) AND ((regel.[schema-id])=[schema].[schema-id]) AND ((wedstrijd.[club a])=[a].[club-id]) AND ((wedstrijd.[club b])=.[club-id]) AND ((schema.naam)='" & [Forms]![schema]![naam] & "'))"

If schema.naam is a number then:

vs3 = "UPDATE (wedstrijd INNER JOIN club AS a ON wedstrijd.[club a] = a.[club-id]) INNER JOIN club AS b ON wedstrijd.[club b] = b.[club-id], [schema] INNER JOIN regel ON schema.[schema-id] = regel.[schema-id] SET wedstrijd.[voorspel a] = regel.[goals-home], wedstrijd.[voorspel b] = regel.[goals-away] WHERE (((regel.[home klasse])=[a].[klasse-id]) AND ((regel.[away klasse])=.[klasse-id]) AND ((regel.[schema-id])=[schema].[schema-id]) AND ((wedstrijd.[club a])=[a].[club-id]) AND ((wedstrijd.[club b])=.[club-id]) AND ((schema.naam)=" & [Forms]![schema]![naam] & "))"

Note if you look carefully you'll see single quotes used in the string version.
hth
Chris

eh.. schema.naam is a string. I'll try it again.. thanks.

 

[stopher]

Sorry, it was a play on words. Keema Naan is bread sold in Indian restaurants (at least in the UK). It rhymed with your field.

If schema.naam is a string then use the first version I gave. The on that looks like this at the end:
((schema.naam)='" & [Forms]![schema]![naam] & "'))"

Note that because you are dealing with strings then you have to present the value from your form to SQL by enclosing it in quotes. 'myFormValue'

We use single quotes so that the SQL engine doesn't get confused with this string and the other double quotes being used.

hth
Chris

 

[jacks]

THAT DID IT !!!

Thanks man !! I'v been struggling for hours.. and your help worked !!

GREAT. THANKS !!