Solved INSERT INTO NOT WORKIG (1 Viewer)

[Sepp1945]

Private Sub Product_ID_AfterUpdate()

INSERT INTO tblPayments ([Product Value])
SELECT [AMOUNT] FROM tblScale
WHERE tbl.Payments.[Product ID] = tblScale.[ID]


End Sub

Please help with this, i have not used Access for many years.
how do i write this correctly?

Many thanks

Sepp

 

[June7]

If you want to select a specific group of records to pull from tblScale, then need to reference field in tblScale for the criteria, not tblPayments. A WHERE clause linking tblPayment.[Product ID] to tblScale.ID makes no sense for an INSERT action. You also have a period between tbl and Payments that should not be there.

 

[Sepp1945]

Thank you very much,

INSERT INTO tblPayments ([Product Value])
SELECT [AMOUNT] FROM tblScale
WHERE tblScale.[ID] = tblPayments.[Product ID]

Is this correct?

Many thanks

Sepp

 

[Sepp1945]

I just entered the above, but,

it still turns red
INSERT INTO tblPayments ([Product Value])
SELECT [AMOUNT] FROM tblScale
WHERE tblScale.[ID] = tblPayments.[Product ID]

Thank you for your help

Sepp

 

[ebs17]

You have an event procedure (VBA). In this procedure you write a SQL code. The SQL code is not executed simply by being present, you have to define what you want to do with the SQL statement.

Private Sub Product_ID_AfterUpdate()
   Dim sSQL As String
   sSQL = "INSERT INTO tblPayments ([Product Value])" & _
          " SELECT [AMOUNT] FROM tblScale WHERE tbl.Payments.[Product ID] = tblScale.[ID]"
   CurrentDb.Execute sSQL, dbFailOnError
End Sub

Now that was just the technical execution. The sense and formulation of the append query would have to be discussed separately, also why one inserts a constant statement as SQL statement in VBA instead of calling a stored query.

 

[Sepp1945]

Dear ebs17,

i thank you very much for your kind help, could you please elaborate on your last sentence?

Many thanks

Sepp

further i an error too few parameters

 

[Sepp1945]

further to above

 

[June7]

Why is tblScale and tblProducts involved in this action? Normally, records would be pulled from one table filtered by some input from form, as in:

INSERT INTO tblPayments ([Product Value]) " & _
SELECT [Amount] FROM tblScale WHERE tblScale.ID = " & Me.tbxScaleID

And surely you need to populate other fields besides Product Value, such PaymentDate, ProductID, CustomerID. What are you really trying to accomplish with this process?

Suggest you provide sample data either as tables in post or attach file.

 

[ebs17]

The code line says: Execute the SQL statement, on error reset everything back to the beginning and report the error. Only with the execution of the SQL statement it is determined that there are errors. Before that, the statement is just plain text.

Now you would have to think about the query. I could now say what all is wrong there. But first you have to talk about what the query is supposed to do, because only from the sense results a useful formulation of the query, and only with this formulation you also implement the necessary syntax.

 

[Sepp1945]

Dear June7,

i enclose a zip file (Mercury.dbs) and the form involved is frmPayments with sfrmPayments
i am selecting a Type from tblScale into tblPayments, and want to update the correct amount also,
the date is the current date, the date until is as follows
"
=IIf([Scale]="4",DateAdd("ww",1,[DATE_PAID]),IIf([Scale]="5",DateAdd("m",1,[DATE_PAID]),DateAdd("yyyy",1,[DATE_PAID])))"

I thank you for helping me, it is many years since a last used ACCESS....

kind regards

Sepp

 

[June7]

All you posted is a shortcut to your db, not the actual db.

And it sounds like you want to do an UPDATE not INSERT action.

 

[Sepp1945]

Sorry my mistake, I will send it to you asap

 

[Uncle Gizmo]

The notorious "Insert Into Statement" raises ugly head again!

You might find some useful information in my blog here:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

 

[June7]

Giz, "internet" into ?

 

[Sepp1945]

All you posted is a shortcut to your db, not the actual db.

And it sounds like you want to do an UPDATE not INSERT action.

SORRY again, here is the file

 

[June7]

So tblScale are your products?
You have a ProductID of 0 in tblPayments probably because field in table is set with DefaultValue of 0. CustomerID and ProductID should not have default values.

If you want to modify data in existing records, that is an UPDATE, not INSERT. INSERT is to add new records.

You really don't need to have price info in tblPayments. Price can be retrieved by joining tables.

Only reason to save price in tblPayments would be if price can change in future and don't want history to be changed. Price should be saved in record when record is created, not via UPDATE action later.

 

[Sepp1945]

i thank you, so how can i fix it, the person entering should see the correct price
again i thank you for helping me

Many thanks
Sepp

 

[Sepp1945]

the price could change in the future
Sepp

 

[June7]

Combobox for selecting product should be multi-column and display price. Adjust your combobox settings to add Amount field and set for 3 columns and change widths setting. Price will show in dropdown. Then code in AfterUpdate event can populate Product Value. Advise not to use spaces in naming convention. Assign more informative names to objects.

Me!ProductValue = Me.cbxProduct.Column(2)

 

[Sepp1945]

thank you very much, so easy if you know...LOL

Many thanks again

Sepp

PS will fix Naming
Sepp