Dlookup (Back Into Table)

[abbaddon223]

Hi,

I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible?

I'm declaring a variable as follows:

Dim Ref as string
Ref = [lead_id]

This is from a form.

What I'd like to be able to do is go to the table

• , reference the lead ID in the table via the variable then change the field [status] to "INCALL".
  
  Can this be done in a similar way to Dlookup?
  
  Thank you for any assistance!
  
  UPDATE - here is the code I am trying to use
  
  Dim ref As String
  ref = [lead_id]
  
  Dim MySQL As String
  
  MySQL = "UPDATE vicidial_list SET"
  MySQL = MySQL & "vicidial_list.status = 'INCALL' "
  MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))"
  
  DoCmd.RunSQL MySQL
  
  Which gives me an update clause error

 

[pr2-eugin]

abbaddon223, are you asking to UPDATE a field in the Table using a WHERE condition?

 

[abbaddon223]

Hi Paul,

Yes I suppose I am, but I need this to run from the form rather than a query. I know this is probably something really basic

 

[TJPoorman]

You're missing a space and you're WHERE clause is referencing the wrong field. Try something like this:

Dim ref As String
ref = [lead_id]

Dim MySQL As String

MySQL = "UPDATE vicidial_list SET "
MySQL = MySQL & "vicidial_list.status = 'INCALL' "
MySQL = MySQL & "WHERE vicidial_list.lead_id = " & Ref & ";"

DoCmd.RunSQL MySQL

 

[abbaddon223]

Thanks TJPoorman - you nailed it