Form Ghosting (1 Viewer)

[kirkm]

I guess this can't be the right way to do this as I just get an hourglass and a "ghost" of a form until I hit Cntrl-Pause. Then the Form shows,
What i want to do is, close the Form with the mouse and have it move to the next set of data to show... and so on.
I'd be very keen to learn where I've gone wrong. Here's my code. The Form was made with the wizard with its source 'zqryShow". I may not need this query
and could set the Forms source directly the sql. I was going to try that later.

Sub ShowData()
Dim myQuery As QueryDef
Dim r As DAO.Recordset
Dim S As String
Set r = CurrentDb.OpenRecordset("qryNewSingles")
r.MoveFirst
Do
If Nz(r!Done) = "" Then 'This record has not been alrtady shown
'get all entries with matching AT

S = "Select TheDate, WeeksOn, LastWeek, ThisWeek, Artist, Title, Label, Number from qryNewSingles Where Artist = 'AAA' and Title = 'TTT' Order By TheDate;"
S = Replace(S, "'", Chr$(34))
S = Replace(S, "AAA", r!Artist)
S = Replace(S, "TTT", r!Title)

Set myQuery = CurrentDb.QueryDefs("zqryShow")
myQuery.SQL = S

DoCmd.OpenForm "frmShow", acFormDS, , , , acDialog
'TODO Set Done flag
End If

r.MoveNext
Loop Until r.EOF
End Sub

 

[kirkm]

I see on the home page am instruction to use code tags, represented by an hash char in the menu bar.
But there's no hash char showing.

 

[CJ_London]

if you mean like this - it has changed recently - highlight the code, then in the ribbon select the ... (insert) image and select code

Sub ShowData()

    Dim myQuery As QueryDef

    Dim r As DAO.Recordset

    Dim S As String

    Set r = CurrentDb.OpenRecordset("qryNewSingles")

    r.MoveFirst

        Do

            If Nz(r!Done) = "" Then  'This record has not been alrtady shown

             'get all entries with matching AT

            

                S = "Select TheDate, WeeksOn, LastWeek, ThisWeek, Artist, Title, Label, Number from qryNewSingles Where Artist = 'AAA' and Title = 'TTT' Order By TheDate;"

                S = Replace(S, "'", Chr$(34))

                S = Replace(S, "AAA", r!Artist)

                S = Replace(S, "TTT", r!Title)

  

                Set myQuery = CurrentDb.QueryDefs("zqryShow")

                myQuery.SQL = S



                DoCmd.OpenForm "frmShow", acFormDS, , , , acDialog

                'TODO Set Done flag

            End If

      

        r.MoveNext

    Loop Until r.EOF

End Sub

 

[kirkm]

Hi CJ, doesn't look as nice does it? Double spacing sort of ruins it.
Anyway pse ignore mu question as I've now changed the whole concept.

 

[Micron]

Double spacing sort of ruins it.

Might depend on where you copy it from. If Word, I'd expect hidden paragraph markers to be rendered as additional line breaks that you don't need. If directly from the vb editor, I've never experienced the extra lines. Then again, this is a new site so maybe something has changed and I haven't had the experience yet. At any rate, if it happens I'd just remove them as the formatted code is easier to read when it's rendered properly.

 

[Gasman]

Sub ShowData()
Dim myQuery As QueryDef
Dim r As DAO.Recordset
Dim S As String
Set r = CurrentDb.OpenRecordset("qryNewSingles")
r.MoveFirst
Do
If Nz(r!Done) = "" Then 'This record has not been alrtady shown
'get all entries with matching AT

S = "Select TheDate, WeeksOn, LastWeek, ThisWeek, Artist, Title, Label, Number from qryNewSingles Where Artist = 'AAA' and Title = 'TTT' Order By TheDate;"
S = Replace(S, "'", Chr$(34))
S = Replace(S, "AAA", r!Artist)
S = Replace(S, "TTT", r!Title)

Set myQuery = CurrentDb.QueryDefs("zqryShow")
myQuery.SQL = S

DoCmd.OpenForm "frmShow", acFormDS, , , , acDialog
'TODO Set Done flag
End If

r.MoveNext
Loop Until r.EOF
End Sub

 

[Gasman]

From what I can see you are running a query for every record in your recordset and as the form is open from the first time the form is opened, it is just really activating it.?

I would expect you would only get data for the last record?
How many records are there in the recordset.?

Set a breakpoint and walk through the code?