Query runs twice while only activating it once (1 Viewer)

Stefan.Kauw · Jun 22, 2018

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

Code:

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 · Jun 22, 2018

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 · Jun 22, 2018

June7 said:
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 · Jun 22, 2018

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.

Code:

Partijnummer = Keuzelijst0.Value

to

Code:

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 · Jun 22, 2018

ridders said:
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 · Jun 22, 2018

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;

Code:

        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 · Jun 22, 2018

Minty said:
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;

Code:

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.

Code:

        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:

Code:

Dim Doseerhoogte as String

Minty · Jun 22, 2018

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

Code:

Dim Doseerhoogte as String

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

Code:

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

Code:

Behandelbeurt

call the textbox

Code:

txtBehandelbeurt

Then you absolutely always know what your dealing with.

Stefan.Kauw · Jun 22, 2018

Code:

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 · Jun 22, 2018

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

Stefan.Kauw · Jun 22, 2018

June7 said:
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 · Jun 22, 2018

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 · Jun 22, 2018

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 · Jun 27, 2018

Pat Hartman said:
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 · Jun 30, 2018

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.

Query runs twice while only activating it once (1 Viewer)

Stefan.Kauw

Registered User.

Attachments

June7

AWF VIP

Stefan.Kauw

Registered User.

Attachments

isladogs

MVP / VIP

Stefan.Kauw

Registered User.

Minty

AWF VIP

Stefan.Kauw

Registered User.

Minty

AWF VIP

Stefan.Kauw

Registered User.

June7

AWF VIP

Stefan.Kauw

Registered User.

June7

AWF VIP

Pat Hartman

Super Moderator

Stefan.Kauw

Registered User.

Pat Hartman

Super Moderator

Attachments

Users who are viewing this thread