Query runs twice while only activating it once

[Stefan.Kauw]

My query runs twice instead of once so i always add two new rows where i want only one.

Private Sub Knop59_Click()
      
        Partijnummer = Keuzelijst0.Value
        Behandeldatum = Tekst33.Value
        Behandelbeurt = Behandelbeurt.Value
        Norm = Norm.Value
        Doseertijd = Tekst44.Value
        Doseerhoogte = Tekst46.Value
        Plasticsoort = Keuzelijst57.Value
        MetingVoor = MetingVoor.Value
        MetingNa = MetingNa.Value
        Dosering = Dosering.Value
        Afwijking = Afwijking.Value
 
        SQL = "INSERT INTO `t_PlasticPartijen` (`Partijnummer`,`Behandeldatum`,`Behandelbeurt`,`Norm`,`Doseertijd`,`Doseerhoogte`,`Plasticsoort`,`Weging_voor`,`Weging_na`,`Dosering`,`Afwijking`)" & _
        "VALUES ('" & Partijnummer & "','" & Behandeldatum & "','" & Behandelbeurt & "','" & Norm & "','" & Doseertijd & "','" & Doseerhoogte & "','" & Plasticsoort & "','" & MetingVoor & "','" & MetingNa & "','" & Dosering & "','" & Afwijking & "')"
        
        CurrentDb.Execute SQL
        DoCmd.Close acForm, Me.Name
        
End Sub

I have absolutely no clue where it's going wrong. I've excluded every possible influence but the query keeps running twice.

 

[June7]

Why are you running INSERT query? Why not just a form bound to table? I suspect you are doing both and that's why you get two records, not running the query twice.

 

[Stefan.Kauw]

Why are you running INSERT query? Why not just a form bound to table? I suspect you are doing both and that's why you get two records, not running the query twice.

The form is completely independent from the table i'm writing in to.
Even the button is not linked to the table.

As you can see, it adds to rows. The first row is incomplete, the second is. I'm guessing the second is the query i'm running. I cant find any other query that inserts, triggered by the button.

 

[isladogs]

Disable the CurrentDB.Execute line & run the code.
Do you still get the first incomplete record?

Also suggest you alter each of the code lines from e.g.

Partijnummer = Keuzelijst0.Value

to

Partijnummer = Me.Keuzelijst0

Using Me. will ensure you get the values from the form.
The .Value isn't needed as that's the default option

 

[Stefan.Kauw]

Disable the CurrentDB.Execute line & run the code.
Do you still get the first incomplete record?

Thanks for your suggestion. Didn't thought about that yet.
I disabled the line and i still get the row.

 

[Minty]

By any chance did you set the forms record source to the table you are trying to update, and if so I'd bet that the following fields in red are the only ones that get a value;

        Partijnummer = Keuzelijst0.Value
        Behandeldatum = Tekst33.Value
   [COLOR="Red"]     Behandelbeurt = Behandelbeurt.Value
        Norm = Norm.Value[/COLOR]
        Doseertijd = Tekst44.Value
        Doseerhoogte = Tekst46.Value
        Plasticsoort = Keuzelijst57.Value
       [COLOR="red"] MetingVoor = MetingVoor.Value
        MetingNa = MetingNa.Value
        Dosering = Dosering.Value
        Afwijking = Afwijking.Value[/COLOR]

Because you aren't declaring those as variables Access assumes you want to update the forms (incorrectly set) recordsource fields that match with the data in the text boxes.

 

[Stefan.Kauw]

By any chance did you set the forms record source to the table you are trying to update, and if so I'd bet that the following fields in red are the only ones that get a value;

        Partijnummer = Keuzelijst0.Value
        Behandeldatum = Tekst33.Value
   [COLOR="Red"]     Behandelbeurt = Behandelbeurt.Value
        Norm = Norm.Value[/COLOR]
        Doseertijd = Tekst44.Value
        Doseerhoogte = Tekst46.Value
        Plasticsoort = Keuzelijst57.Value
       [COLOR="red"] MetingVoor = MetingVoor.Value
        MetingNa = MetingNa.Value
        Dosering = Dosering.Value
        Afwijking = Afwijking.Value[/COLOR]

Because you aren't declaring those as variables Access assumes you want to update the forms (incorrectly set) recordsource fields that match with the data in the text boxes.

        Partijnummer = Keuzelijst0.Value
        Behandeldatum = Tekst33.Value
   [COLOR="Red"]     Behandelbeurt = Behandelbeurt.Value (= filled by a dlookup, looking in a other table inside the standard value of the textbox)
        Norm = Norm.Value (= filled by a dlookup, lookin at a other table inside the standard value of the textbox) [/COLOR]
        Doseertijd = Tekst44.Value
        Doseerhoogte = Tekst46.Value
        Plasticsoort = Keuzelijst57.Value
       [COLOR="red"] MetingVoor = MetingVoor.Value (= manually entered with no coupling to a field. Eventuely something will be calculated)
        MetingNa = MetingNa.Value (= manually entered with no coupling to a field. Eventuely something will be calculated)
        Dosering = Dosering.Value (= Calculates the difference between MetingVoor and MetingNa)
        Afwijking = Afwijking.Value (= Calculates the difference between Norm and Afwijking[/COLOR]

So what you say is that i have to declare the variable as whatever they are:
For instance:

Dim Doseerhoogte as String

 

[Minty]

So what you say is that i have to declare the variable as whatever they are:
For instance:

Dim Doseerhoogte as String

Yes and as Ridders said please also use the Me. syntax.

Behandelbeurt = Me.Behandelbeurt

This will ensure that Access knows you are referring to the current forms object.

Lastly as a general rule rename your controls to make sure you know you are dealing with a text box control or combo and not an underlying field name, so instead of

Behandelbeurt

call the textbox

txtBehandelbeurt

Then you absolutely always know what your dealing with.

 

[Stefan.Kauw]

Private Sub Knop59_Click()
      
        Dim Partijnummer As String
        Dim Behandeldatum As String
        Dim Behandelbeurt As Integer
        Dim Norm As Integer
        Dim Doseertijd As Integer
        Dim Doseerhoogte As Integer
        Dim Plasticsoort As Integer
        Dim MetingVoor As Integer
        Dim MetingNa As Integer
        Dim Dosering As Integer
        Dim Afwijking As Integer
        
        Partijnummer = Me.Keuzelijst0.Value
        Behandeldatum = Me.Tekst33.Value
        Behandelbeurt = Me.Behandelbeurt.Value
        Norm = Me.Norm.Value
        Doseertijd = Me.Tekst44.Value
        Doseerhoogte = Me.Tekst46.Value
        Plasticsoort = Me.Keuzelijst57.Value
        MetingVoor = Me.MetingVoor.Value
        MetingNa = Me.MetingNa.Value
        Dosering = Me.Dosering.Value
        Afwijking = Me.Afwijking.Value
 
        SQL = "INSERT INTO `t_PlasticPartijen` (`Partijnummer`,`Behandeldatum`,`Behandelbeurt`,`Norm`,`Doseertijd`,`Doseerhoogte`,`Plasticsoort`,`Weging_voor`,`Weging_na`,`Dosering`,`Afwijking`)" & _
        "VALUES ('" & Partijnummer & "','" & Behandeldatum & "','" & Behandelbeurt & "','" & Norm & "','" & Doseertijd & "','" & Doseerhoogte & "','" & Plasticsoort & "','" & MetingVoor & "','" & MetingNa & "','" & Dosering & "','" & Afwijking & "')"
        
        CurrentDb.Execute SQL
        DoCmd.Close acForm, Me.Name
        
End Sub

It works!
Thanks a lot. Very valuable tips!

 

[June7]

Why use code and not bound form and bound controls? If you are setting RecordSource, why not set ControSource?

 

[Stefan.Kauw]

Why use code and not bound form and bound controls? If you are setting RecordSource, why not set ControSource?

I'm not really sure but i'll try to explain.

I'm filling a couple of boxes using dlookup which didnt work when bounding it.
And for some kind of reason i didnt get a new record everytime.

Got tired of it and tried to do it like this. Now it works

 

[June7]

Right, saving calculated data requires code. Could simply be: Me.fieldname = Me.textboxname. The real trick is figuring out what event to put that code in.

Now, why are you saving calculated data? And why do you need to use DLookup and not just join tables in query?

 

[Pat Hartman]

The real trick is figuring out what event to put that code in.

Not really a trick at all. You would use the AfterUpdate event of the source control. If that control changes, copy the value to the other control. That said - you shouldn't be doing that at all. Saving the same data in multiple tables simply is an accident waiting to happen. You always need to worry about one field or the other being out of sync and therefore having different processes working with different values.

To show lookup values, simply join to the lookup table. For example. In an Order entry application, you have a customer table and an order table. You store the CustomerID in the order table so you know what customer placed the order. However, you really want to see the customer's name rather than his ID so for the Order form's RecordSource, you use a query that joins tblOrders to tblCustomers and pick the CustomerID from tblOrders but pick CustomerName from tblCustomers. On your form, you would usually use a combo to choose CustomerID and when you do that, the CustomerName field on the form will automagically change to whatever customer you chose from the combo. It is absolutely amazing what Access just does for you.

 

[Stefan.Kauw]

Not really a trick at all. You would use the AfterUpdate event of the source control. If that control changes, copy the value to the other control. That said - you shouldn't be doing that at all. Saving the same data in multiple tables simply is an accident waiting to happen. You always need to worry about one field or the other being out of sync and therefore having different processes working with different values.

To show lookup values, simply join to the lookup table. For example. In an Order entry application, you have a customer table and an order table. You store the CustomerID in the order table so you know what customer placed the order. However, you really want to see the customer's name rather than his ID so for the Order form's RecordSource, you use a query that joins tblOrders to tblCustomers and pick the CustomerID from tblOrders but pick CustomerName from tblCustomers. On your form, you would usually use a combo to choose CustomerID and when you do that, the CustomerName field on the form will automagically change to whatever customer you chose from the combo. It is absolutely amazing what Access just does for you.

Any chance of getting a clear description of how this works? Like a example database with explanations? I'm not bad at English but English isn't my primary language which sometimes makes it hard to understand.

 

[Pat Hartman]

This was the closest thing I could find. I made it to show some of the problems caused by table level lookups. Look at the query named qLookup1 to see how to join to the lookup table to pick up the text value.