Order in which VBA is processed

[moorsey]

Hi there, im doing some stuff with SQL in VBA for uni, I thought what I wanted to do would be simple, but apparantly now. Alls I want to happen is have a box on a form change to "processing" when an SQL statement is executed and then change back to "done" when it has been processed

The trouble is, the sql is processed and then after that, the box changes which is no good

Is there a way to define what should be processed before the SQL?

code as follows

Private Sub cmdNewDB_Click()

    Dim db As Database
    Set db = DBEngine.OpenDatabase(Application.CodeProject.path & "\server.mdb")
    Dim qdf As QueryDef
    
    Forms!switchboard!txtProcessing.BackColor = "255"
    Forms!switchboard!txtProcessing.Caption = "Processing"
    
    Set qdf = db.CreateQueryDef("", "CREATE TABLE tblCustomers ([CustomerNo] " & _
    "INTEGER, [LastName] TEXT (15), [FirstName] TEXT (10), [Address] TEXT (25), " & _
    "[City] TEXT (15), [Telephone] TEXT (13), [DiscountRate] REAL, " & _
    "CONSTRAINT PrimaryKey PRIMARY KEY (CustomerNo))")
    
    qdf.Execute
    
    Set qdf = db.CreateQueryDef("", "CREATE TABLE tblSalesmen ([SalesmanNo] INTEGER, " & _
    "[SalesmanName] TEXT (50), [MonthlyBasicSalary] SMALLINT, [CommissionRate] REAL, " & _
    "CONSTRAINT PrimaryKey PRIMARY KEY (SalesmanNo))")
    
    qdf.Execute
    
    Set qdf = db.CreateQueryDef("", "CREATE TABLE tblInvoices ([InvoiceNo] INTEGER, " & _
    "[Date] DATETIME, [CustomerNo] INTEGER, [SalesmanNo] INTEGER, CONSTRAINT PrimaryKey " & _
    "PRIMARY KEY (InvoiceNo), CONSTRAINT CustomerNOFK FOREIGN KEY (CustomerNo) " & _
    "REFERENCES tblCustomers (CustomerNo), CONSTRAINT SalesmanNoFK FOREIGN KEY (SalesmanNo) " & _
    "REFERENCES tblSalesmen (SalesmanNo))")
    
    qdf.Execute
    
    Set qdf = db.CreateQueryDef("", "CREATE TABLE tblParts ([PartNo] INTEGER, " & _
    "[Description] TEXT (30), [SellingPrice] MONEY, [CostPrice] MONEY, " & _
    "CONSTRAINT PrimaryKey PRIMARY KEY (PartNo))")
    
    qdf.Execute
    
    Set qdf = db.CreateQueryDef("", "CREATE TABLE tblInvoiceLines ([InvoiceNo] INTEGER, " & _
    "[PartNo] INTEGER, [Quantity] INTEGER, CONSTRAINT InvoiceNoFK FOREIGN KEY (InvoiceNo) " & _
    "REFERENCES tblInvoices (InvoiceNo), CONSTRAINT PartNoFK FOREIGN KEY (PartNo) " & _
    "REFERENCES tblParts (PartNo))")
    
    qdf.Execute
    
    Forms!switchboard!txtProcessing.BackColor = "65408"
    Forms!switchboard!txtProcessing.Caption = "Done"
    
End Sub

 

[RoyVidar]

Sometimes issuing a Me.Repaint might do, but you'll probably need a DoEvents just after you assign the values to the label.

Note that when doing a DoEvents, the application will receive and process/start processing - keystrokes/mouseclicks received, so your app will probably need to handle that. For instance to avoid restarting the same process while still executing it.

 

[moorsey]

thats great, thanks for the reply. The doevents worked a treat, very useful to know for the future as well

cheers!!