SQL on Load

[Trogdor]

Okay. Not sure if whoever reads this has been seeing my other posts lately, but I am working on a database that uses dragging and dropping of labels in order to record events of a process.

I have been trying to create Unique ID's for each label, and previously I was just using the last digits after the name of the labels.

i.e. label names = lblTest1, lblTest2, lblTest3, .... lblTestn.

I then found by help here that I could assign a number to the .tag of the label and manipulate this to create unique ID's for the labels. I have 20 labels and I am recycling them, and when they are deleted I am incrementing the value by 1. This works well until the database is turned off and starting again the next day.

So on the load of the main form, when I am configuring the locations of my labals and there size and other feautes I am running an SQL statement to look back into the table where I am recording the events to see what the last ID was for that specific label is, and if it is there then I am adding 20 and continuing as normal, otherwise I am giving it the ID of the lbl. (incase that label has never been used before).

Here in lies the problem. When I run this in debug mode, stepping through it works a treat. BUT! when i run the form in normal mode it seems to skip over the SQL and just re-assigns it the value of the label. So i go back to the beggining.

I have been told there is a way to force it to finish the SQL query before continuing in the code. Can anyone tell me how?

 

[ByteMyzer]

It sounds like you are running the SQL-Select asynchronously. Could you post the code you are using for the SQL-Selection?

 

[DCrake]

VBA unlike VB especially with Access reads all the sql first committing it to memory then processes it. It does this to validate the whole sql. However VB reads the sql one line at a time and processes it as it goes along. What you can do is to place the command DoEvents at strategic places in your code, which effectively is telling Access "Don't do anything else until you have done this." It has some effect on performace if used frequently, however it may resolve your issue.

 

[ByteMyzer]

DCrake, although your answer is valid for most cases, it may be, in this case, precipitate, since it IS possible to execute an asynchronous query in VBA and not give it time to finish, and since we do not KNOW how Trogdor is actually executing the query.

The fact that Trogdor's stepping through the code in Debug works and his running it uninterrupted doesn't is what leads me to suspect an asynchronous call to the query. Because I haven't actually SEEN the code being used, I don't KNOW that, anymore than you could possibly know that that is NOT the case. Let's wait until we see the actual code, then I'm sure we can offer something better than an assumption.

 

[Trogdor]

Private Sub Form_Load()


Me.Box101.BackColor = RGB(255, 0, 0)
tglONOFFvalue = 1
Me.tglONOFF.SetFocus
Me.tglONOFF.caption = "Turn" & vbCrLf & "ON"

CurrentDb.execute "DELETE * FROM tblLabels;"
Set mcolActionLabels = Nothing

Dim rst As DAO.Recordset
'Dim albActionLabel As clsActionLabel
Dim lbl As label
Dim sinColumn As Single, sinRow As Single
Dim i As Integer
Dim result As Integer

Set rst = CurrentDb.OpenRecordset("tblLabels", dbOpenSnapshot)

Set mcolActionLabels = New Collection

For i = 1 To 15
Set albActionLabel = New clsActionLabel
Set lbl = Me("lblTest" & Trim(Str(i)))
Set albActionLabel.label = lbl
albActionLabel.OnMouseMove = "OnLabelMouseMove"
albActionLabel.OnMouseUp = "OnLabelMouseUp"

mcolActionLabels.Add albActionLabel, albActionLabel.label.Name

rst.FindFirst "LID = " & i
If rst.NoMatch Then

lbl.height = conDefaultSizeY
lbl.width = conDefaultSizeX
lbl.top = 160
lbl.BackColor = RGB(254, 159, 88)
lbl.BorderColor = RGB(0, 0, 0)
lbl.caption = ""
lbl.Tag = LastID(i)

'result = i Mod 2

If site = "SYD" Then
' If result = 0 Then
lbl.Left = 2500
' Else
' lbl.Left = 6700
' lbl.BackColor = RGB(255, 0, 255)
' End If
Else
' If result = 0 Then
lbl.Left = 6700
' Else
' lbl.Left = 2500
' lbl.BackColor = RGB(255, 0, 255)
' End If
End If
Else
lbl.height = Nz(rst!LHeight, conDefaultSizeY)
lbl.width = Nz(rst!LWidth, conDefaultSizeX)
lbl.top = Nz(rst!LTop, conDefaultGap * sinRow + conDefaultSizeY * (sinRow - 1))
lbl.Left = Nz(rst!LLeft, conDefaultGap * sinRow + conDefaultSizeX * (sinRow - 1))
End If
Next

For i = 16 To 30
Set albActionLabel = New clsActionLabel
Set lbl = Me("lblTest" & Trim(Str(i)))
Set albActionLabel.label = lbl
albActionLabel.OnMouseMove = "OnLabelMouseMove"
albActionLabel.OnMouseUp = "OnLabelMouseUp"

mcolActionLabels.Add albActionLabel, albActionLabel.label.Name

rst.FindFirst "LID = " & i
If rst.NoMatch Then

lbl.height = conDefaultSizeY
lbl.width = conDefaultSizeX
lbl.top = 160
lbl.BackColor = RGB(254, 159, 88)
lbl.BorderColor = RGB(0, 0, 0)
lbl.caption = ""
lbl.Tag = LastID(i)

' WTMD is always ODD, and PAX is always even

' result = i Mod 2

If site = "SYD" Then
' If result = 0 Then
' lbl.Left = 2500
' Else
lbl.Left = 6700
lbl.BackColor = RGB(255, 0, 255)
' End If
Else
' If result = 0 Then
' lbl.Left = 6700
' Else
lbl.Left = 2500
lbl.BackColor = RGB(255, 0, 255)
' End If
End If
Else
lbl.height = Nz(rst!LHeight, conDefaultSizeY)
lbl.width = Nz(rst!LWidth, conDefaultSizeX)
lbl.top = Nz(rst!LTop, conDefaultGap * sinRow + conDefaultSizeY * (sinRow - 1))
lbl.Left = Nz(rst!LLeft, conDefaultGap * sinRow + conDefaultSizeX * (sinRow - 1))
End If
Next

End Sub


Function LastID(i)

Dim rst As DAO.Recordset
Dim SQLcmd As String
Dim dbs As Database

Set dbs = CurrentDb

SQLcmd = "SELECT tblCapturedData.event_id, tblCapturedData.squareID, Last(tblCapturedData.LabelID) AS LastOfLabelID " & _
"FROM tblCapturedData " & _
"GROUP BY tblCapturedData.event_id, tblCapturedData.squareID " & _
"HAVING (((tblCapturedData.event_id)=2) AND ((tblCapturedData.squareID)=" & i & "));"


Set rst = dbs.OpenRecordset(SQLcmd)

If Not (rst.EOF And rst.BOF) Then
rst.MoveFirst
LastID = rst![LastOfLabelID] + 30
Else
LastID = i
End If

rst.Close
Set rst = Nothing

End Function

 

[Trogdor]

Sorry about the code if it looks sloopy.. Fairly new to this type of work and I didn't know how to put it into one of the code boxes like I see in some of the forums.

 

[ByteMyzer]

In your LastID function, instead of:

If Not (rst.EOF And rst.BOF) Then

...why not simply use:

If Not rst.EOF Then

 

[MSAccessRookie]

Sorry about the code if it looks sloopy.. Fairly new to this type of work and I didn't know how to put it into one of the code boxes like I see in some of the forums.

To Format the code, surround it as follows (remember to eliminate the spaces first):

[LIST]
[*][ code ]
[*]        Your Code
[*][ /code ]
[/LIST]

 

[Trogdor]

ByteMyzer..

That is just the way I have seen people do it to make sure there is something returned in the query. But do you think this is what is causing me grief?

How do you eliminate the asynchronous SQL?

 

[ByteMyzer]

I said that it SOUNDED like you were executing the query asynchronously. Now that I've actually SEEN the code, that does not appear to be your problem.

Try this as a test to see what the VBA environment thinks the conditions are:

If Not (rst.EOF And rst.BOF) Then
    [b][i]MsgBox "True" ' add this line[/i][/b]
    rst.MoveFirst
    LastID = rst![LastOfLabelID] + 30
Else
    [b][i]MsgBox "False" ' add this line[/i][/b]
    LastID = i
End If

...then load the form without using Debug. If the popup says "False", but you KNOW that there is a matching entry for the Label, then you know that the Not (rst.EOF And rst.BOF) expression isn't working.

Afterwards, humor me and try it after replacing:

Not (rst.EOF And rst.BOF)

...with

Not rst.EOF

If, after making THIS change, the popup shows "True", then you'll know that that was the problem.

 

[Trogdor]

Excellent! Well I put in the msgbox's then I cleared out my test data set and thought I should to a manage fix and repair, and it worked a treat. I didn't need to change the EOF and BOF. Must have had an access Gremlin in there. Thanks for your comments though.

ByteMyzer just wodnering though, when would you use an asynchronous SQL query, and how would you incorporate one into your code?

 

[ByteMyzer]

The option to run a query asynchronously exists for querys on an ODBCDirect type connection/recordset. Because the initial connection to the ODBC data source can take time to instantiate and retrieve the data, the dbRunAsync option allows the code to continue with other tasks, then to check the recordset's StillExecuting attribute to see whether the query has finished.