Listbox does not requery correctly after change in field

[Urco]

Hi guys,

Been working on this IT Helpdesk tool that I have to adapt since its functionality is limited at the moment. The whole issue here is about a listbox called listboxFindCall.
There is only one form here. It displays on top a listbox with all the active calls that have to be solved, which shows 5 colums (priority, callnr, name, date, IT employee). You can click on the call and it will be displayed below the listbox on the same form, this was done with the command:
Me.RecordsetClone.FindFirst "[Call_Id] = " & Me![listboxFindCall]
Me.Bookmark = Me.RecordsetClone.Bookmark

That all works fine. I have also built in a search option such that you can search the listbox as you type, on name, call number or IT employee. That also works fine.

When you go to a call there are 2 comboboxes which are relevant here.
ComboStatus (call is active or solved, in case of solved it is (hopefully) not displayed in the listbox) and ComboITEmployee.


When you make a new record (or call) everything seems to work ok. You select an IT employee and status is automaticaly set to active. When you then click in the listbox it automatically adds the record to the listbox. Both comboboxes have after update code which requeries the listbox, I'll show in a minute.

The problem really is when you want to change something from there on. When you go to a call and want to change the IT employee it doesn't change it in the listbox and when you then go back to the record it also is not changed there or it is changed in the prior record?!? Also when you close a call (put status on solved) it doesn't close it, but closes sometimes the prior record or nothing at all or jumps to a different record.

So here's the code I'm using.

For comboITemployee:

Private Sub ComboITEmployee_AfterUpdate()
Me.listboxFindCall.Requery
End Sub

For the status combobox, there is also a part that registers the date when the call is solved.:

Private Sub ComboStatus_AfterUpdate()
On Error Resume Next
Me.listboxFindCall.Requery
If Me.ComboStatus = "solved" Then
Me.Date.SetFocus
Me.Date.Locked = False
Me.Date = Now()
Me.Date.Locked = True
Me.ComboStatus.SetFocus
Else
Me.Date.SetFocus
Me.Date = ""
Me.Date.Locked = True
Me.ComboStatus.SetFocus
End If
End Sub

Hopefully you can give me some advice, as the database tool as it is now is useless as you cannot close calls and especially if it writes data to the wrong record. Your advice will be highly appreciated.

Regards, Urco

 

[missinglinq]

When you go to a call and want to change the IT employee it doesn't change it in the listbox and when you then go back to the record it also is not changed

Exactly how/where are you entering the new records and how/where are you editing these records. Are you opening a second form or what?

I think the problem is that you're requerying the listboxes each time a new selection is made from a combobox, but you haven't saved the edited record yet, so those changes aren't reflected in the record displayed in the listbox. In other words, if

In new record:

1. Call is assigned to TechA
2. Call record is saved
3. Call record appears in listbox

In record edit:

1. Call is re-assigned to TechB
2. Listbox is immediately requeried
3. Edited call record is saved

But the record showing in the listbox is the original record, still showing TechA, because the listbox was requeried before the edited record was saved! In the edited record, Steps #2 and #3 need to exchange places. The edited record needs to be saved before the requery of the listbox is done.

 

[Urco]

Hi, thanks for your quick reply.

Everything happens on one form. There is only one listbox, it is permanently in the upper part of the form, below are all the fields on the form that correspond with the necessary information of a call. If you click on the call in the listbox, the fields below load the corresponding data of the call. So everything happens on the same form.

A new call is created by clicking on the standard new record button in access below the form. The listbox is always visible. You just go to a different record to save the call. Normally it then shows up in the listbox as a new call. So if you go to a call, you for example just select a different technician in the combobox and hopefully the listbox also shows this (which it doesn't at the moment, nor changes the record). If you want to close a call. Go to the call via the listbox and set the combobox to solved. Click on another record and it should disappear from the listbox, but as you know it doesn't.

Perhaps this gives you a better insight in how the whole database works. Your point about when a record is edited seems quite valid. Do you think this is the source of the problem? How would you suggest designing it with this in mind?

Again, thanks a lot.

 

[missinglinq]

After the record is edited it'll have to be saved, either by moving to another record, or having a command button to explicitly save it, or even in the AfterUpdate event of the comboboxes before the listbox is requeried. You can do that by using

DoCmd.RunCommand acCmdSaveRecord

so instead of

Private Sub ComboITEmployee_AfterUpdate()
  Me.listboxFindCall.Requery
End Sub

you could do

Private Sub ComboITEmployee_AfterUpdate()
  [B]DoCmd.RunCommand acCmdSaveRecord[/B]
  Me.listboxFindCall.Requery
End Sub

You'd need to place the same line of code before the requery of the listbox in your other combobox as well.

 

[Urco]

That seems to be a good solution. I am going to give this a try first thing Monday morning when I get my hands on the database again. I will let you know if this will cure the issues, I am hopeful it will.

Really thanks a lot.

 

[Urco]

Hi,

In principle this solution works. The IT employee is changed and the call disappears when set to solved. However, after a short while it starts malfunctioning. It does not want to close the call and closes the preceding call. It also does not want to change the IT employee, but changes it from the preceding record?!? Something goes wrong with saving the data to the correct record. When it goes wrong it is always written to the preceding record. How do I solve this or what is the source of this issue?

I must say that the table contains 17000 records(calls), does this have anything to do with it?

Thanks for the help.

 

[missinglinq]

I think for anyone to help you any further we're going to have to at least see your code, and considering how complex it appears to be from what it does, we really need for you to zip it up and post it here.

If size is a problem (it may not be; 17 k records isn't really all that much) you can create a blank db and import everything into it. When you go to import the tables, you can click on Options and select "Definitions only." Then go into the new version and simply enter a handful of records for each table.

 

[Urco]

Ok, tomorrow when I have access to the database again, I will give you some more information on the code. I probably can't get a copy up here as I am not allowed to. Will see what I can do tomorrow a.s.a.p. Thanks already.

 

[Urco]

Ok, let's see. The story is the following.

We have an existing helpdesk database with which the helpdesk registers calls. However, it has very limited search features. So my aim is to add some features such that active calls can be found with ease.

To make the picture complete, here is some info on how the original database looked like. There is one form with:
- listboxFindCall: One listbox that displays active calls, which displays priority, call number, name of person who reported the call, date and IT employee that needs to solve the call)
- cmbGoToCall: A combobox where one can see all (active and solved) calls in descending order with a scrollbar, in the combobox you can type a callnumber and it will go to that record. (combo displays callnumber, person that reported the call and status)
- cmbITEmployeeActiveCalls: Furthermore, there is a combobox next to the listbox where one can select an ITemployee, this results in the listbox showing only the active calls for that particular ITemployee.

Following code was originally in place, the database works correctly like this and does not have the strange writing errors I told you previously:

Private Sub cmbGoToCall_AfterUpdate()
On Error GoTo ErrMsg
Me.RecordsetClone.FindFirst "[Call_Id] =" & Me![cmbGoToCall]
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub

ErrMsg "Submitted callnumber not recognized"

End Sub
--------------------------------------------------------------
Private Sub cmbITEmployeeActiveCalls_AfterUpdate()
If Me.cmbITEmployeeActiveCalls <> "" Then
Search = "[ITEmployee]=" & Me.cmbITEmployeeActiveCalls
DoCmd.OpenForm "FrmCalls" acNormal,,search
Else
Me.Filter = ""
End If

End Sub
--------------------------------------------------------------
Private Sub ComboITEmployee_AfterUpdate()
Me.cmbGoToCall.Requery
Me.listboxFindCall.Requery
End Sub
--------------------------------------------------------------
Private Sub ComboStatus_AfterUpdate()
On Error Resume Next
Me.cmbGoToCall.Requery
Me.listboxFindCall.Requery
If Me.ComboStatus = "solved" Then
Me.Date.SetFocus
Me.Date.Locked = False
Me.Date = Now()
Me.Date.Locked = True
Me.ComboStatus.SetFocus
Else
Me.Date.SetFocus
Me.Date = ""
Me.Date.Locked = True
Me.ComboStatus.SetFocus
End If
End Sub
--------------------------------------------------------------
Private Sub listboxFindCall_AfterUpdate()
Me.RecordsetClone.FindFirst "[Call_id]" & Me!
[listboxFindCall]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
--------------------------------------------------------------
In the query for the listbox the following was observed, you first have the 5 fields that are displayed. Then from frmCalls, field ITemployee with the following criteria: [Forms]![FrmCalls]![ITEmployee]
Then next to that there was written in the field [Forms]!FrmCalls]![cmbItEmployeeActiveCalls] and in the criteria Is Not Null or Is Null, furthermore there is a field Status with criteria <>"solved" or <>"solved"

That's how the situation was originally.

Well this functionality just is not good enough for a helpdesk tool. So my idea was to add features such that one can search better in the listbox for active calls. A search as you type function would seem ideal. The same function as in the following topic:
http://www.access-programmers.co.uk/forums/showthread.php?t=116253&highlight=listbox
I used exactly the same code and names as in that example.
I deleted cmbITEmployeeActiveCalls from the form, the related code and also the two fields from the query. So I put in the criteria area of the query of the fields that I want to search: Like "*" & [Forms]![frmCalls]![Search2] & "*"

And the corresponding code for the field where you search is the following:

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString
Me.listboxFindCall.Requery

End Sub

The search function appears to work well.

As you advised previously I added DoCmd.RunCommdSaveRecord to Private Sub ComboITEmployee_AfterUpdate() and Private Sub ComboStatus_AfterUpdate().
Important is that in principle everything works. However, after some time (can be 20 seconds or 3 minutes or a bit longer) it starts to write to the preceding record and does not close the correct call anymore.

I hope this information is of any help to you. A pity I am not allowed to attach the database itself.

 

[NigelShaw]

Hi,

i might have missed something in the reading but is the list box on the main form or on a subform to that main form?

have you tried forms!yourform!yourlistbox.requery approach?

does the listbox get its data from a table or query? if query, is it filtered?


nigel

 

[Urco]

The listbox is on the mainform. It gets the data from a query.

The approach you mention is unknown to me, could you explain?

 

[NigelShaw]

Hi,

my method is very common. it gives a full path per se to the control rather than a local path-

forms!yourForm!YourListbox.requery rather than me!Listbox.requery

i would also requery the mainform too as this listbox might be data dependant on the mainform.


hth

nigel

 

[Urco]

Ok, I am going to give that a try.

Hmm, it does not appear to be working. This is quite an irritating issue. I'm not sure what the source of the problem is, is it the code or something else. Especially since it always starts messing about with the preceding record.

What I have also tried today is to delete everything from the form that is not relevant here and also delete all the related code. Even then when I make the listbox it starts malfunctioning. I think it might have to do something with the query in the listbox. I will come back on this tomorrow.

By the way is it possible that the issue might arise from the fact that the listbox is on the form itself? Perhaps it causes problems when you change something in the record that it then must display this change above in the listbox on the same form. I am going to try to take the listbox of the main form.

 

[NigelShaw]

Can you post a sample or copy your db, remove any data so people can take a deeper look for you?

NS

 

[Urco]

I'll see what I can do a.s.a.p.

 

[Urco]

I am now seriously doubting whether the issue stems from the code I use. In principle it all works, only for a short while. Is it possible that the indexing of the table is corrupt somewhere. Apparently the database did crash a while ago with the following error: The database .... needs to be repaired or isn't a Microsoft Access database file. It was fixed with the access built in repair tool. Any suggestions whether this might be the source of the problems?

 

[Urco]

I have tried to import everything into a new database. Deleted the indexes and moved the data via an appeend query into a new table. Still the issues remain that after a (short) while it starts writing in the preceding record. I will open a new topic about this as this issue is not directly related to the problem discussed here.