Run Query if a condition is true

[Maddy.Elt]

Hi friends! I need your help please tell me anything which helps

My problem includes five queries.
Update_Query(A), Delete_Query(B), Select_Query(C), Append_Query(D) and an Update_Query(E) and one form. named Form(Start)
My problem is

1) I want to run Update_Query(A) and Delete_Query(B) whenever the Form(Start) is open.

2) After this code will check a field named [execute] in Select_Query(C)
If [execute]="yes" then code will run Append_Query(D) and Update_Query(E) otherwise if [execute]="no" then code doesn't let Append_Query(D) and Update_Query(E) run and end code.

3) And Keep executing Append_Query(D) and Update_Query(E) untill the [execute] in Select_Query(C) doen't return "no"

(*) The Update_Query(E) will make increments which will caue [execute] in Select_Query(C) to return "no" after some executions somtimes it will already be "no"

Please help me!

 

[The_Doc_Man]

This is a VBA question if ever there was one.

1) I want to run Update_Query(A) and Delete_Query(B) whenever the Form(Start) is open.

If you mean "is opened" then you put some code in the Form_Open or Form_Load event. (I prefer _Load because more of the form is available.)

Look up (in Access Help) the DoCmd.RunQuery action. This is how you run queries in code.

2) After this code will check a field named [execute] in Select_Query(C)

See the DLookup function, which can check a field in a query as well as in a table. Use an IF statement on the DLookup(....) = "desired value" and if you have the right conditions, do the other two queries inside a loop. Again, DoCmd.RunQuery does the trick.

This is a simple loop. Simple enough that a DO...WHILE or LOOP ... UNTIL construct would also work rather than the explicit IF loop. But hey, whatever floats your boat. Check the help files.

 

[tyronedjackson]

Just as The Doc Man has instructed you, all of this goes in the OnOpen or Onload event of your form...

Private Sub Form_Open(Cancel As Integer)
 
'Declare your query variables as strings
Dim Update_Query(A) As String
'...and so on for all the five queries

'then assign a value

Update_Query(A) = "" 'your sql code goes between quotes
'...and so on for all the five queries
 
'run your first two queries from jump
DoCmd.RunSQL (Update_Query(A))
DoCmd.RunSQL (Delete_Query(B))
 
'Now, create the loop for the recordset for Select_Query(C)...
Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(Select_Query(C))
 
If rs.RecordCount > 0 Then
rs.MoveFirst
Do Until rs.EOF
'while on each record of Select_Query(C)
If rs![execute]="yes" Then
DoCmd.RunSQL (Append_Query(D))
DoCmd.RunSQL (Update_Query(E))
End If
rs.MoveNext
Loop
End if

End Sub