replaced query field by using VBA code

[ice051505]

Hi dear all, I have a query and want to write some VBA code to replace one field in this query with another one under certain situation.
Here is part of my original table:

SmallestArea SmallerArea SmallArea TotalArea count
NA NA NA A1 5
NA NA B2 A2 27
D3 C3 B3 A3 28
NA NA B4 A4 19
NA NA B5 A5 24
. . . . .
. . . . .
. . . . .
And then I have a button that let user to choose which level of area they want. If user choose SmallArea with the "count" bigger then 25, my list box will return B2 and B3, but here is the problem: when user choose SmallArea with count smaller then 20, the list box will return "NA" and B4. but what I want is under this situation, a message box would pops up ("this level of area is not avaliable, the selection will use the upper level"), then all the data would automatically query by the selection of TotalArea

I want to use Loop statment on this one, and here is my code:

'when user click the Area Zone selection, they can make the option to choose lower level area or not.
Private Sub Zone_slc_Click()
Dim QstAr As String
Dim stDocName As String

QstAr = MsgBox("Choose the SubArea", vbYesNo, "Message")
If QstAr = vbNo Then
DoCmd.CancelEvent
Else
Me.ArLevl.Visible = True 'Enable the SubArea option if choose "yes".
Me.ArLevl.Enabled = True
End If

Do While QsrAr = vbYes
stDocName = "LoopQuery"

DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallArea] = "NA" Then
[Queries]![LoopQuery]![SmallArea] = [Queries]![LoopQuery]![TotalArea]

Else: DoCmd.OpenQuery stDocName, acNormal, acEdit
If [Queries]![LoopQuery]![SmallerArea] = "NA" Then
[Queries]![LoopQuery]![SmallerArea] = [Queries]![LoopQuery]![SmallArea]
Else: DoCmd.OpenQuery stDocName, acNormal, acEdit


If [Queries]![LoopQuery]![SmallestArea] = "NA" Then
[Queries]![LoopQuery]![SmallestArea] = [Queries]![LoopQuery]![SmallerArea]
Else: Exit Do
End If
End If
End If
Loop
End Sub

please help me to change and complete this piece of code, Thanks a lot !

[June7]

You can't change values in a query with a Sub. A Sub procedure can overwrite actual data in table but not replace values in query. A SELECT query can call a Function procedure and return a value based on conditions coded in the function but this does not alter data in the table.

How would code even know which record to look at in query? Have you tested this code?

[alcapps]

you can change a query in code. However I think you are trying to update a record with the code you wrote and I don't understand what your attempting to do.

if you want a query to run in code. you would need a action query modify it's sql statement then execute it.

pseudo code
dim db as dao.database
dim qdf as dao.querydef

set db = currentdb()
set qdf = db.querydefs("yourqueryName")

qdf.sql= "Update, Delete, insert, select statement"
'you can also tell if this will return records or not..lookup code.. qdf.returnRecords = True,False
qdf.close

'now run the code
db.execute("yourqueryName",dbfailonerror)

in your sql statement you can use the controls for the query.. like
strSQL = "Update table1 set field1 = '" & me.strcontrolname & "'"
so you set the sql
qdf.sql = strSQL
db.execute strSQL, dbfailonError

Hope this helps