Let me rephrase this question
How (where) are you selecting [product-id]? Do you have a form where you
select the [product-id] and condition?
How are you determining which record ([product- id]) needs to be
updated/changed?
Do you have a form listing all [product-id]'s ,then you click on one, or a
form where you enter a [product-id], then click a button???
UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" & BookTbl.[product-id] & "AND
[SellerCondition] = 'used'")
You need to add a WHERE clause to the above line to limit the records changed.
It would look something like this:
UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" &
Forms![FormName]![ControlName] & "AND [SellerCondition] = 'used'") WHERE
[product-id] = " & Forms![FormName]![ControlName]
(Of course you would replace "[FormName]" with the name of the form and
"[ControlName]" with the name of the text box control name.)
-------
So, say you have a form named "frmUpdatePrice" and a text box control named
"txtProduct_id". You would enter the ISBN number in the text box, then click
a button to update the price.
1) If you are using an update query named "qryUpdatePrice" (you can see the
query name in the query tab of the database window), the code for the update
button would look like this
Private Sub btnUpdatePrice_Click()
DoCmd.OpenQuery "qryUpdatePrice"
End Sub
---
2) If you used code to run the query:
Private Sub btnUpdatePrice_Click()
Dim strSQL as String
strSQL = "UPDATE BookTbl SET BookTbl.LowUsedSellerPrice = " & _
DMin("SellerPrice","TempCompetitorTbl","ISBN=" &
Forms![FormName]![ControlName] & "AND [SellerCondition] = 'used'") & _
" WHERE [product-id] = " & Forms![FormName]![ControlName]
CurrentDb.Execute (strSQL), dbFailOnError
End Sub
----
3) If you used DAO recordsets:
Private Sub btnUpdatePrice_Click()
Dim rstBook as DAO.RecordSet
Dim rstComp as DAO.RecordSet
Dim strSQLBook as String
Dim strSQLComp as String
' this is all on one line
strSQLComp = ""SELECT Top 1 sellerprice FROM TempCompetitorTbl WHERE
ISBN] = " & [Forms]![frmUpdatePrice]![txtProduct_id] & " AND sellercondition]
="Used" ORDER BY TempCompetitorTbl.sellerprice;"
' this is all on one line
strSQLBook = "SELECT [LowUsedSellerPrice] FROM BookTbl WHERE
[product-Id] = " & [Forms]![frmUpdatePrice]![txtProduct_id] & " ;"
Set rstComp = CurrentDb.OpenRecordset (strSQLComp)
If rstComp.BOF and rstComp.EOF then
' error - close recordsets and exit
MsgBox "ISBN Record not found "
'do cleanup stuff
End If
Set rstBook = CurrentDb.OpenRecordset (strSQLBook)
' same type of code as above
' update price
rstBook.Edit
rstBook!LowUsedSellerPrice = rstComp!sellerprice
rstBook.Update
' close and clean up
rstBook.Close
rstComp.Close
set rstBook = Nothing
set rstComp = Nothing
End Sub
***Disclaimer: the above examples are Air Code ***
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
BLTibbs said:
To answer your questions and make a couple more:
1. product-id is a field in booktbl (BookTbl.[product-id])
2. They are not selected from a form, but from a table called
TempCompetitorTbl and the record with the lowest price needs to go into a
table called booktbl
3. currency (double) format of 'lowusedsellerprice'
4. currency (double) format of 'sellerprice'
My Q's:
I don't understand (I am a rookie) what your three options below mean. Can
you help me fix the SQL that I currently have (below in this thread).
Thanks
SteveS said:
You're right, the query as you show it would update all records to the min
price.
How (where) are you selecting [product-id]? Do you have a form where you
select the [product-id] and condition?
What is the data type of BookTbl.[LowUsedSellerPrice] and the data type of
TempCompetitorTbl.[SellerPrice]?
There are several ways to update the price/condition.
~ use a saved query.
~ use CurrentDB.Execute ("...")
~ use DAO recordsets to lookup and modify the records.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
BLTibbs said:
I am trying to update [BookTbl].[LowUsedSellerPrice] and
[BookTbl].[LowUsedSellerCondition] BookTbl has a keyID of [Product-id]. I
need to get the data from another table called [TempCompetitorTbl] where I
have a field calld [isbn] (that will match up with [BookTbl].[product-id]);
another field called [sellerprice]; and another called [sellercondition].
[TempCompetitorTbl] will have up to 6 competitors for each isbn/product-id
number, so I have to take the Min of [sellerprice] from the list of
competitors as my record to take the condition and low price to update to
booktbl. I have created a couple threads on this and so far, I have the
following SQL code for a subquery that is acting like it is going to update
all of my records in booktbl, not just the ones that match booktbl.product-id
to tempcompetitortbl.isbn. Also, when the query is done running (it is very
slow) it says it cannot update any records because the data type doesn't
match.
Please help!
UPDATE BookTbl SET BookTbl.LowUsedSellerPrice =
DMin("SellerPrice","TempCompetitorTbl","ISBN=" & BookTbl.[product-id] & "AND
[SellerCondition] = 'used'")
