Stopping a listbox on subform being requeried (1 Viewer)

[skunk711]

I have a list box that uses a hardcore vb function to populate one of the columns in it.

I don't want the list box to get requeried every time I add a new record, or change to another tabbed page an then back again.

Is there any way to stop the list from being requeried all the time, only when the user clicks a button telling the list to do so?

 

[skunk711]

The list itself does not actually refresh with new data that is entered, rather it re-populates itself with the data it retrieved when the form was first opened.

 

[Fizzio]

Under which event do you populate the listbox?

 

[skunk711]

The listbox is populated when the form is first opened, it is then repopulated each time I start entering into a new record, even though I have no event which tells it to requery.

Do you know if listboxes get repopulated each time a new record is created if the list is inside a subform?

 

[Fizzio]

You nust use an event to 'hardcore' the list with Vb. Is it only on the form open event?

 

[skunk711]

I'm probably explaining this poorly, but I'll try and give it another go.

The query the list is based on has a column in it the concatenates multiple staff name together (eg: John Doe, Jane Doe). This column gets its data from a function I wrote that opens up a recordset, and loops through each job and adds a staff member to a string for the job they are assigned to. This function takes a few seconds to loop through the jobs and returns the string of people allocated to this job.

The query works fine in that it returns each job, the person(s) allocated to it...

My problem is that each time I click Add Job, it refreshes the list as soon as I start entering in the job number. I don't want it to refresh until the user clicks another button telling the list to refresh (or they close the screen and open it up again). It takes too long to enter lots of jobs for a project if the list is constantly refreshing.

I don't use any events to populate the list. It populates when the form opens, but it is not bound to the On Open event.

What IS weird, is that normally list boxes don't refresh themselves each time you add a new record, but this one does for some reason.

If you want I can post my vb Function because I think that the problem is the query using this function to return a string.


HTH
Ben

 

[Fizzio]

I understand now. I'm not sure why it refreshes. Do you use this list box to select jobs for the selected project? Post the function. There may be something in there but I'm not sure.

 

[skunk711]

Here is the function that returns a String of employees allocated to a Section:

Public Function findPrep(st As String)
' This function returns a String of all staff members allocated to a section
' in the Responsibility Assignment Matrix.

Dim rs As Recordset
Dim qd As QueryDef
Dim qrySt, stRet, stSQL, prosID, Sect As String
Dim fld As Fields

prosID = Forms![Tender]![ProspectID] 'ProspectID
'Sect = Forms![Tender_RAM]![Sect] 'Section
Sect = st
'create an SQL string for the query



Set qd = CurrentDb.QueryDefs("prep")
qd.Parameters("Prospect") = prosID
qd.Parameters("sec") = Sect
Set rs = qd.OpenRecordset

rs.FindFirst "[Tender_RAM_Prep].[Sect] = '" & Sect & "'"

If Not rs.EOF Then
'base case
Set fld = rs.Fields
stRet = fld(2)
rs.MoveNext
While Not rs.EOF 'terminating case
stRet = stRet & ", " & fld(2) 'add a comma, then add next name
rs.MoveNext 'move to next
Wend
End If
findPrep = stRet


End Function



Here is the SQL for the query that uses the function:

SELECT Tender_RAM.ProspectID, Tender_RAM.Sect AS [Section], Tender_RAM.Desc AS Description, findPrep([Sect]) AS [Responsibility for Prep], Tender_RAM.Prep_Due AS [Prep Due], findSign([Sect]) AS [Responsibility for Signoff], Tender_RAM.Signoff_Due AS [Signoff Due]
FROM Tender_RAM
WHERE (((Tender_RAM.ProspectID)=[Forms]![Tender]![Text274]));



I only pasted one of the functions above, there is another one called findSign() which is very similiar, and just lists people allocated for signing off on a job.

Keep in mind also that this listbox is on a subform, which may also be affecting it.

Thanks for all the replies.

Ben