Empty Listbox shows listcount = 1

[moke123]

You should add option explicit to all your declarations.

try using .execute method rather than docmd.runsql. Its faster and doesn't require turning warnings on and off.

rather than using a prefix like

For Each cControl In Me.Controls
        If cControl.Name Like "m_*" Then cControl.Visible = True
        If cControl.Name Like "frame*" Then cControl.Visible = True
    Next

try using the controls tag property like

For Each cControl In Me.Controls
If InStr(1, cControl.Tag, "XYZ") Then
    cControl.Visible = True
 End If
 Next

by using instr() you can use the tag for more than one procedure.

Dim sql1, sql2, sqlMax As String
Dim TaskNo, Hold_TaskNo, i As Integer

should be

Dim sql1 As String , sql2  As String, sqlMax As String
Dim TaskNo As Integer , Hold_TaskNo  As Integer, i As Integer

The way you have it TaskNo, Hold_TaskNo, sql1 and sql2 are variants.

 

[Auntiejack56]

All good, but if I understand you correctly

When he enters another task I wipe out lst_Goals (lst_Goals.rowsource = "") so that only the goals corresponding to the new task will show.

From what you've described, when the user enters the task, the goal list is empty (lst_Goals.rowsource = ""), and when the user enters the first goal, then you would have to enter an actual rowsource (lst_Goals.rowsource = "SELECT GoalName from GOALS where TaskID = " & lst_Tasks).
Consider never setting the rowsource to "". You can put a form.control reference directly in the SQL (BI*), or dynamically create the string in VBA. Either way, requery whenever lst_Tasks changes. Then your check for listcount=0 would always work.
Jack
(*="Bad Idea")

 

[moke123]

Personally I would set up my tables so that I could use one Listbox with Tasks and Goals side by side assuming they are 1 to 1.

 

[TheSearcher]

Moke123 - I use tags for that purpose all the time. But a control only has only one tag property. What do you mean by:

by using instr() you can use the tag for more than one procedure.

My declarations are correct. You can check the value of the variable by using the vartype function.
Thanks for the db.execute tip!
Auntijack56 - I'm not sure I understand what you mean. Can you elaborate?

 

[CJ_London]

your can populate the tag with something like "Required,DoSomethingElse"

then use the instr function or the split function to select the bit you want

if instr(control.tag,"Required")>0 then ....

 

[moke123]

My declarations are correct. You can check the value of the variable by using the vartype function.

Dim sql1, sql2, sqlMax As String

The 1st 2 elements may be correctly interpreted as strings but you are declaring them as variants.

 

[Auntiejack56]

Well, I'm pretty sure that the experienced Access users here would always have a valid SQL string in the rowsource - it would never be a zls. That's why there is so much chitchat in here about why the listcount is 1 when rowsource is zls - most have not encountered the situation.
Instead, they would set the rowsource as soon as the correct filter is known - that is, as soon as the form is opened, a task is clicked, or a new task created.
You are using the existence or otherwise of the rowsource as a kind of flag, but you don't need to do it that way. IMHO it should never be set to set to zls at any point. Just make sure it always has the correct rowsource SQL, and use the listcount as you've described, because it will always be correct.
Admittedly, when you open a brand new record, it might be zls until you add a task, but that case is easily handled because on adding a new task you immediately set the rowsource.
Having said all that, though, you are in fact using the listcount in the same way you're using the rowsource - as a kind of flag. But the listcount or the existence of a rowsource only implies data, it's not actual data. When you get down among the dustbins, you really need to check the actual records for that task in the table using a DCount or similar.

 

[TheSearcher]

your can populate the tag with something like "Required,DoSomethingElse"

then use the instr function or the split function to select the bit you want

if instr(control.tag,"Required")>0 then ....

Thanks for the tip! Hadn't thought of that!