Do While loop

[jgelpi16]

I have the below do while loop to identify the value in a table's field. Everything seems to be going well except for some reason my variable always returns the value of the last field in the table. It will loop through the correct number of times for the number of records in the table, but it always returns the same value when I step through the code. Any suggestions?

    Set db = Access.Application.CurrentDb
    Set rst = db.OpenRecordset("DenialReason")
    With rst
        '.MoveLast
        .MoveFirst
        Do While .EOF = False
            varOfferIDCheck = OfferID
            '!Rec_Group = AssignGroup(!Rec_Type)
            .MoveNext
        Loop
    End With

I am aware I have ".MoveLast" commented out. I tried it with it not commented out and the same result occured.

[jzwp11]

The data in the variable will be overwritten each time through the loop so you will only end up with the last value encountered in the loop. You have to do something with the variable before moving to the next record in the recordset.

Can you explain in words what you want to do?

[jgelpi16]

What I am looking to do is check to see if a value is in the table in the field/column "OfferID". If the value is there I want to delete it, if it is not there I want to proceed.

[jzwp11]

You want to clear the field OfferID if there is something in it (i.e. change the value to null) ? You can do that with an update query. I assume that you don't want to delete the entire record

If you want to clear the field only if there is a specific value in the field, then you can do that with an update query also.

Can you be a little more specific?

[jgelpi16]

Yes, sorry. I am storing a number (needs to be unique) and a description in a table. Whenever the user clicks the "save" button I want to check to see if that number (text box on the form) is in the table. If the value is in the table I want to delete the number and the description associated with it. I then want to insert the new value and description based upon the option box selection. The description is obtained from the option box label caption. Here is some pseudo code:

If text1.value is in the table then
delete text1.value from table and delete optionboxlabel1.caption from table
append text1.value and optionboxlabel2.caption
else
append text1.value and optionboxlabel2.caption
end if

[jzwp11]

I want to check to see if that number (text box on the form) is in the table. If the value is in the table I want to delete the number and the description associated with it.

Why remove the number in the table with the one from the form if they are the same? Just update the description field with the new value from the caption using an update query. Here is the code (not tested)

Dim mySQL as string

IF DCount("*","tablename","youruniquenumberfield=" & me.textboxnamewithuniquenumber)>0 THEN
mySQL="Update your tablename SET fieldholdingthecaptiontext='" & me.optionboxlabel2.caption & "' WHERE youruniquenumberfield=" & me.textboxnamewithuniquenumber
currentdb.execute mySQL, dbfailonerror
END IF

[jgelpi16]

Because I like to make it harder on myself....That makes perfect sense. Thank you for the help!

[jzwp11]

You're welcome. I'm guilty of making things harder for myself too!