Error 3075: Syntax error in Query Expression 2

[xicana]

Hello,

I'm attempting to update a field in a table where the foreign key is equal to the value of a combo box in my form. The table could hold many records with that foreign key (if that makes a difference).

The code being used is this:

Sub UpdateMarkup()

   Dim db As dao.Database
   Dim rst As dao.Recordset
   Dim strSQL As String

    strSQL = "SELECT * " & _
    "FROM 2_PriceListDetail " & _
    "WHERE 2_PriceListDetail.PriceListID = " & _
    [Forms]![TST UPDATE COST DECREASE]![cboPriceList]
   Set db = CurrentDb
   Set rst = db.OpenRecordset(strSQL)

   With rst
      ' update Markup field
      Do While Not .EOF
         !Markup = 555
         .MoveNext
      Loop

      .Close
   End With

   db.Close

End Sub

Where PriceListID is a Number field.

the error message I get is this:

Run-Time Error '3075'
Syntax Error in Query Expression '2_PriceListDetail.PriceListID = 260'

This is the first time I attempt opening a recordset and updating a field for a set of records.

any help would be greatly appreciated!
Thanks.

Sandy

 

[PHV]

Why not simply this ?

DoCmd.RunSQL "UPDATE [2_PriceListDetail] SET Markup = 555 " & _
    "WHERE PriceListID = " & Forms![TST UPDATE COST DECREASE]!cboPriceList

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xicana]

Thanks PHV...the thing is I'm actually going to do a lot more to each record...i was testing that I could actually get to open the recordset and change the value of the field for all the records corresponding to that foreign key.

For each record, I will need to look up to 10 fields until I run into one that is not null. Then, based on that field I will run a calculation and update the value of that Markup field.

Sandy

 

[PHV]

I4d use [2_PriceListDetail] instead of 2_PriceListDetail in your code.
Furthermore, I'd have a look at the Edit and Update methods of the DAO.Recordset object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya xicana . . .

[blue]PHV[/blue] has given the [purple]perferred[/purple] solution to your problem. However, it doesn't tell you what was wrong with your recordset code. I present the following just to hilite (in [purple]purple[/purple]), what was missing:

[blue]   Dim db As dao.Database, rst As dao.Recordset, SQL As String
   
   Set db = CurrentDb
   
   SQL = "SELECT * " & _
         "FROM 2_PriceListDetail " & _
         "WHERE [PriceListID] = " & [Forms]![TST UPDATE COST DECREASE]![cboPriceList]
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)

   With rst
      If Not .BOF Then
         Do While Not .EOF
            .[purple][b]Edit[/b][/purple]
            !Markup = 555
            .[purple][b]Update[/b][/purple]
            .MoveNext
         Loop
      End If
   End With

   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[xicana]

thank you both for your help! I'll take a look at it this evening and post back results.

Sandy

 

[xicana]

this is what worked - thank you both!

Sub UpdateMarkup()

   Dim db As dao.Database
   Dim rst As dao.Recordset
   Dim strSQL As String

    strSQL = "SELECT * " & _
    "FROM 2_PriceListDetail " & _
    "WHERE [PriceListID] = " & _
    [Forms]![TST UPDATE COST DECREASE]![cboPriceList]
   Set db = CurrentDb
   Set rst = db.OpenRecordset(strSQL)

   With rst
      ' update Markup field
      Do While Not .EOF
         .Edit
         !Markup = 555
         .Update
         .MoveNext
      Loop

      .Close
   End With

   db.Close

End Sub

Sandy

 

[dhookom]

PH did attempt to point Sandy in the correct direction by mentioning

Furthermore, I'd have a look at the Edit and Update methods of the DAO.Recordset object.

I believe he was hoping that Sandy would explore and learn about these methods without providing the answer (which he clearly knew and could have provided).

Sandy,
It's appropriate to thank members who have provided help (correct answers) by clicking the link "Thank ... for this valuable post!"

Duane
Hook'D on Access
MS Access MVP

 

[xicana]

Thanks dhookum - I did thank (clicked on the link) PHV and TheAceMan for their valuable help.

I'm working on a time sensitive project and didn't have much time to explore too much. I do attempt to search for the answer first as much as i can prior to posting and asking for help...I really only do it when I'm desperate and need an answer fast.

Thanks again - love Tek-Tips!

Sandy

 

[dhookom]

Sandy,
I don't see any stars in this thread thanking PH and theAceMan. You might want to try again unless they are there and I am the only one who doesn't see them.

Duane
Hook'D on Access
MS Access MVP

 

[xicana]

I think I may not have clicked on the confirm link within the pop-up last time.


Thanks!

Sandy

 

[TheAceMan1]

Howdy dhookom . . .

My intent was to add a little more incentive in checking out the [blue]edit/update[/blue] methods, instead of just reading over them.

In any case, showing where the methods go doesn't impart understanding of how & why ... nor any knowledge of the method! I actually thought my post was an assist.

You take care ... [blue]Ya Hear![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]