Windows 10 
Access 2007
Hi. I have been making a language learning app (basically for myself, really). It's almost finished. I have a "STATS - Rework" form that is pulling information from 4 different, unrelated tables. I stated by using the expression builder, but discovered that it doesn't seem to like more than one unrelated table, so I've resorted to DAO coding instead (and, then discovered that it's MUCH faster, lol). But I have a problem where in this STATS form, because it's basically counting the number of records in many ways, I've used a lot of Dcounts to get that information.
But the curious thing is that the DCount commands are not finding records on the first load. So if I open my form, it finds 0 records, but if I change it to design view, then back to form view, the DCount then works, pulling the correct number of records. So I know that my DCount coding is correct because it DOES work, but not the first time the form is loaded.
The code is in Form_Load. I have attached my application for you to see, in case you need more information than just the code. Please note that I am a beginner in VBA, and have recently (the last month or so) learned it to write this application, so my knowledge of programming is limited to what you can see in the app (and learning gleaned from YouTube and various sights to find out how to do stuff). I have Googled extensively trying to find someone else with this problem and found nothing! Should I try moving to another event? Form_Load is usually okay. Is Long the wrong variable? I tried several and Long was the one I found to work. I also tried moving the rst.RecordCount to after the DCounts in case it held it up, but that didn't work either. I also shortened my query to see if that helped. I'm at a loss. Thanks in advance!! :-)MyCurrentLearningWords.zip
Private Sub Form_Load()
Dim Listening As Long
Dim Reading As Long
Dim Meaning As Long
Dim Ready As Long
Set db = CurrentDb
' Set rst = db.OpenRecordset("SELECT FutureWordList.ID, FutureWordList.ListeningSuccess, FutureWordList.NumSccssList, FutureWordList.NumSccssRec FROM FutureWordList")
Set rst = db.OpenRecordset("SELECT FutureWordList.ID FROM FutureWordList")
rst.MoveLast
Me.TxtTotalFutureWords = rst.RecordCount
Listening = DCount("TxtListening", "FutureWordList", "ListeningSuccess < 20")
Reading = DCount("TxtReading", "FutureWordList", "NumSccssList < 10 and ListeningSuccess > 20")
Meaning = DCount("TxtMeaning", "FutureWordList", "NumSccssRec < 10 and NumSccssList > 10 and ListeningSuccess > 20")
Ready = DCount("TxtReady", "FutureWordList", "NumSccssRec > 10 and NumSccssList > 10 and ListeningSuccess > 20")
Me.TxtListening = Listening
Me.TxtReading = Reading
Me.TxtMeaning = Meaning
Me.TxtReady = Ready
rst.Close
Set rst = Nothing
Set db = Nothing
Dim zero As Long
Dim one As Long
Dim two As Long
Dim three As Long
Dim four As Long
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Acquired.ID, Acquired.MonthlyCount FROM Acquired")
rst.MoveLast
Me.TxtBTotalAcquired = rst.RecordCount
zero = DCount("TxtBAq0", "Acquired", "MonthlyCount = 0")
Me.TxtBAq0 = zero
one = DCount("TxtBAq1", "Acquired", "MonthlyCount = 1")
Me.TxtBAq1 = one
two = DCount("TxtBAq2", "Acquired", "MonthlyCount = 2")
Me.TxtBAq2 = two
three = DCount("TxtBAq3", "Acquired", "MonthlyCount = 3")
Me.TxtBAq3 = three
four = DCount("TxtBAq4", "Acquired", "MonthlyCount = 4")
Me.TxtBAq4 = four
rst.Close
Set rst = Nothing
Set db = Nothing
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Archive.ID FROM Archive")
' rst.MoveLast (this won't work until there is a record in the table)
' Me.TxtBArchived = rst.RecordCount
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Reply With Quote
