We help IT Professionals succeed at work.

Update a field based on supplied variable

Fordraiders
Fordraiders asked
on
I have a table called:
Rebate_Qualifier
3 fields:
Rebate_ID      Entity_Value      Qualifier_ID

"Entity_Value" will always have a value in the field.

I have 2 variables    rb   and   ent

rb = SLS-000118-MAIN-A1-5-R1
ent = 5

The Rebate_Id is null
Qualifier_ID is null

Table example below:
Rebate_ID	Entity_Value	Qualifier_ID
	                AABAA	
	                AABAA	
	                AABAA	
	                AABAA

Open in new window


What I need:
I need to update the Rebate_Id field with  variable  rb
I need to update the Qualifier_ID field with  variable  ent


Thanks
fordraiders
Comment
Watch Question

View Solution Only

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
UPDATE Rebate_Qualifier SET Rebate_Id = rb, Qualifier_ID =ent WHERE SomeCriteria

MVP
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Use DAO for that: 

Dim Records As DAO.Recordset

Dim Sql     As String

Sql = "Select * From Rebate_Qualifier Where The Rebate_Id Is Null And Qualifier_ID Is Null"

Set Records = CurrentDb.OpenRecordset(Sql)

While Not Records.EOF
    Records.Edit
        Records!Rebate_Id.Value = rb
        Records!Qualifier_Id.Value = ent
    Records.Update
    Records.MoveNext
Wend
Records.Close
therealmongoose
CERTIFIED EXPERT

Commented:
dim dbs as database
dim rst as recordset
dim strsql as string

dim strPKValue as string

strPKValue = "AABAA" '<- the entity value you for the record you want updating

'This assumes entity value is unique in the table..

strsql = "Select * from Rebate_Qualifier where Entity_Value      = '" & strPKvalue & "';"

set dbs = currentdb
set rst = dbs.openrecordset(strsql)

rst.movefirst

rst.edit
rst!Rebate_ID      = RD
rst!Qualifier_ID = ent
rst.update
rst.close
set rst = nothing
dbs.close
set dbs = nothing

or you could code an update query with variables / functions and call that....
Fordraiders

Author

Commented:
All good examples and thank you very much !!