SQL call to specific record, return ID field

[MsMope]

Good Afternoon,
I need to update a record in a table (user_tblJobProp)based on a selection in an option group on a form, however I need to know how to store the current records ID or call the current record ID into a variable, Any ideas?

Private Sub frmDelivery_AfterUpdate()
Dim strSQL As String
Dim strJobID As String
Dim parentID$
[COLOR=red][i]these are two options I have tried, neither give the desired results, both return an empty string [/i]
parentID$ = is_modItem_GetParentID(ID$)
'strJobID = Me.ctlDescription[/color]
    Select Case frmDelivery.Value
      Case 1
         strSQL = "UPDATE user_tblJobProp" & _
                  "SET user_tblJobProp.[Delivery_Charge] = '0'" & _
                  "WHERE user_tblJobProp.[ID]= parentID$;"

 

[kjv1611]

A recordset?

Dim rs as DAO.Recordset
Dim db as DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tblMyTableName")

Then loop through your recordset for whatever you are doing..

Do While Not rs.EOF
  [green]'~code here[/green]
Loop

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[MsMope]

Well I don't think that is going to work, I needed to give a little more background I guess.

I have a form, with an optionSet with three different options, 0-70 70-140 and over 220. For example if the 0-70 option is chose I need to update a field (Delivery_Charge) to "0", in the tblJobProp. The ID (JOB XXXX) has already been retrieved from the SQL dB somewhere ( can't decipher where, but the current job's info is displayed on the form), I want to pull the ID and compare to make sure they match, then update the field.

I did attempt to do a recordset, but the field did not update here is the code I used:

Dim db As Database, rs As Recordset, SQL$

    Set db = CurrentDb
    SQL$ = "SELECT user_tblJobChild.Child_Class From user_tblJobchild "
    SQL$ = SQL$ & "GROUP BY user_tblJobChild.Parent_ID, "
    SQL$ = SQL$ & "user_tblJobChild.Child_Class "
    SQL$ = SQL$ & "HAVING user_tblJobChild.Parent_ID = is_modoutline_getcurrentid()"
    Set rs = db.OpenRecordset(SQL$)

 Select Case frmDelivery.Value
      Case 1
         strSQL = "UPDATE user_tblJobProp" & _
                  "SET user_tblJobProp.[Delivery_Charge]= '100'" & _
                  "WHERE user_tblJobProp.[ID]= rs [Parent_ID]"

and I did not get the desired result, is there anywhere I can test that query? becuase I know I can link the tables, but that is not what I want to do, i would like to use the current recordset field, maybe my code is just wrong.

Thx,