dlookup and quotes?

[fedum]

I have tryed the code
DoCmd.RunSQL "UPDATE tbldimensies SET verkocht=DLookup([SomVanStuks], ""query1"", ""BarcodeProducent= '"" & Serienummer & ""'"")"
I think the code is ok but when I execute this code I get an error with fieldconversion.
somvanstuks type long, verkocht type long, barcodeproducent type text, serienummer type text. I really do not see the problem.

 

[Remou]

You are missing quotes from the first DlookUp argument.

 

[fedum]

Can you please fill it up because I do not understand. Thanks

 

[fedum]

Sorry, do you mean this:
DoCmd.RunSQL "UPDATE tbldimensies SET verkocht=DLookup(""SomVanStuks"", ""query1"", ""BarcodeProducent= '"" & Serienummer & ""'"")"
Well it still give me the same error.

 

[Remou]

How about:
[tt]strSQL = "UPDATE tbldimensies SET verkocht=DLookup(""SomVanStuks"", ""query1"", ""BarcodeProducent= '" & Serienummer & "'"")"

DoCmd.RunSQL strSQL[/tt]

I have separated the SQL from the run command because it makes it easier to debug. The line as you show it would pass the word Serienummer to the query, rather than the waluse of Serienummer, which is what you want, I think.

 

[fedum]

I still get an error 2001. Unknown to the instruction DoCmd.RunSQL strSQL
I have declared strSQL as String

 

[fedum]

Remou, can you please look at the question (a little bit futher in the list)
runsql select can I use this for my problem?
There I have explained my problem from the start.

 

[Remou]

Ok, this is all a little odd. Lets do it the conventional way:

intUpdateTo=DLookup("SomVanStuks", "query1", "BarcodeProducent= '" & Serienummer & "'")

DoCmd.RunSQL "UPDATE tbldimensies SET verkocht=" & intUpdateTo

 

[fedum]

Still get error 2001: You cancelled the previous operation

 

[Remou]

This implies that there is an error in one on the names. Test dlookup in the immediate window:

?DLookup("SomVanStuks", "query1", "BarcodeProducent= 'Fill In Serienummer Here'")

Presumable you have set Serienummer to something earlier in your code?

 

[fedum]

If I try this in the immediate window I get the same error. But the field Serienummer has to be read from de query1, compaired with the field barcodeproducent from the table tblDimensies and the value of the field somvanstuks from the query1, has to be given to the field verkocht in the table.
Total code is:
Private Sub Knop8_Enter()

Dim strSQL As String
Dim intUpdateTo As Integer
intUpdateTo = DLookup "SomVanStuks", "query1", "BarcodeProducent= '" & Serienummer & "'")
DoCmd.RunSQL "UPDATE tbldimensies SET verkocht=" & intUpdateTo

End Sub

 

[Remou]

You are saying that you wish to get the value of SomVanStuks from Query1 where a field BarcodeProducent equals another field in query1 called Serienummer? (Are you sure that this will only return one number?) If this is the case, I misread your first post in that I thought that Serienummer was a variable. You would not need quotes to compare two fields in the same query:

intUpdateTo = DLookup ("SomVanStuks", "query1", "BarcodeProducent=  Serienummer")
End Sub

 

[fedum]

Sorry Remou I think we have a misunderstanding. The problem is:
I have made a query from the tables Product and Verkoop. In this query (query1)I make the sum of the field stuks (somvanstuks) for each Barcode in Procuct (related with Barcode in table Verkoop) and ofcourse only the sold products. This works ok. But no I have made a new table Dimensions. I have copied all the products into this table but I would like to put the result from the query (somvanstuks) in the field verkocht in the table dimensions. Problem is not every product has been sold so only the products in the query must be updated.

 

[Remou]

What you need is a join between the two tables, not DlookUp, as far as I can tell. Something like:

[tt]UPDATE tbldimensies INNER JOIN query1 ON tbldimensies.Serienummer = query1.BarcodeProducent SET tbldimensies.verkocht = query1.somvanstuks;[/tt]

Where BarcodeProducent is the key field in query1 and Serienummer is the key field in tbldimensies.

You better have a look at this is query design first, to see if it is what you want.

It is not always a good idea to store calculations in tables.

 

[fedum]

Ok. I will check my query because if I run this code I get a message that it is not possible to update the query.

 

[Remou]

The query? It should be updating the table, not the query. Have you got a unique key on tbldimensies?

 

[fedum]

Nice that you are still there,
Yes I have a key on the fiels serienummer