Unbound combo box doesn't refresh; datagrid displaying filtered recordset doesn't show new added record

I 'm encountering the following errors in VB6 & AdoDB

1. In a datagrid that displays the data of a filtered recordset, newly added records disappear from the datagrid. Click next to the * that denotes a new record in the datagrid, add a new record, move to a different row and find out that the row disappears. If you rerun the project, the row is there. It has to do with the rs.filter command but still can't figure out why it behaves like this and how to remedy the problem.

2. The other problem has to do with how to refresh an unbound combo box. I have an unbound combo box that is filled with names from a table. It is used for selecting a name and then navigating to the selected record. When, a new record is added, or an existing record is modified, or a record is deleted, the unbound combo box is not refreshed.

Could anyone please offer his experience into any of the above? I 'm using MS-Access 2000 (Jet 4.0) with VB6. I could attach a sample project to demonstrate the above but don't know if I can in this forum.

Thanks,

John.
jnkoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dang123Commented:
John,
    On point 2 of your question, you would need to use the RemoveItem and AddItem methods to update the combo box manually. (To add a new item use AddItem, to delete an item use RemoveItem, to change an item use RemoveItem followed by AddItem) If your list is not too sizeable you may also consider using Clear and reloading the combo box when you detect a change.

Dang123

0
SQLMasterCommented:
John,

Regarding point 1 of the question, after inserting row, remove the filter from your recordset by setting the property to adFilterNone and after that again setting the filter

Cheers

Dheeraj
0
jnkoAuthor Commented:
Hi,

I clear and re-add the values to the combo box like so:

Public Sub fillInComboBox()
    cmbSelect.Clear
    cmbSelect.Refresh
    ' Fill in the combo box with the names of all the boarders.
    adoPrimaryRS.Filter = "Deleted=False"
    openRecordSet adoPrimaryRS  ' checks if rs is closed and re-opens it
    Do Until adoPrimaryRS.EOF
       cmbSelect.AddItem adoPrimaryRS("Surname") & " " & adoPrimaryRS("Firstname")
       adoPrimaryRS.MoveNext
    Loop
    adoPrimaryRS.Close
    adoPrimaryRS.Filter = adFilterNone
    DoEvents
    Set cmbSelect.DataSource = Nothing
    Set cmbSelect.DataSource = adoPrimaryRS
    cmbSelect.Refresh
End Sub

If adoPrimaryRS is modified, added, removed, or modified a record, the changes do not appear to the unbound combo box.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jnkoAuthor Commented:
Thanks for your reply regarding point 1 Dheeraj.

However the form only contains:

Private Sub Form_Load()
    adoPrimaryRS.Filter = "Deleted = False"
End Sub

The rest are configurations on the Datagrid from the vb designer.

Where exactly should I put the rs.Filter = adFilterNone?

I tried adoPrimaryRS_MoveComplete but it throws an exception.

Regards,

John.
0
Dang123Commented:
John,
    Please make the following test change to fillInComboBox and let me know if the list you get is different from the list in the combo box.

Debug.Print " ************************"
Do Until adoPrimaryRS.EOF
      cmbSelect.AddItem adoPrimaryRS("Surname") & " " & adoPrimaryRS("Firstname")
      Debug.Print adoPrimaryRS("Surname") & " " & adoPrimaryRS("Firstname")
      adoPrimaryRS.MoveNext
Loop
Debug.Print " ************************"


0
jnkoAuthor Commented:
Hi Dang123,

I have just found the problem! Because I updated the recordset in a different form, when I returned to the initial form that contained the combo box, the form_load of the initial form was not called (which in turn calls the fillInComboBox). Silly of me I didn't notice earlier. I modified fillInComboBox to public and call it from the unload method of the second form and now the combo box is updated ok. (Off course, the recordset position is lost, but I know how to deal with it - using bookmarks). :-)

What about the other problem with the datagrid, though?

To recreate the problem ('cause I don't know how to attach files in this forum), create a mdb file, put a Surname and a Firstname field and a Deleted boolean field in a table. Then create a datagrid in a form that is connected to the above table, allow add, update etc., use the code shown above and try to add a new record. when you move to another record the newly added record disappears.


Thanks anyway.

John.

0
SQLMasterCommented:
John,

I just worked on your issue. Newly added record will disppear if it does not satisfy your filter conditions

Dheeraj
0
jnkoAuthor Commented:
Hi SQLMaster,

yep. But the filter is such as to be able to show the record and I can't figure out how it turns to the other value. Let me explain.

If you 've created the table I described in Access, put default value = "False" for the Deleted field in the table design. Then, use the code:

Private Sub Form_Load()
    adoPrimaryRS.Filter = "Deleted = False"
End Sub

It should work, shouldn't it? Because this filter only displays only the records that satisfy the condition "Deleted = False", or am I wrong? But what happens when a new record is inserted in the table? Should it have the default value for the Deleted field? Because I tried to insert a new record from the datasheet view and this is exactly the value that the Deleted field takes (i.e the default one).

Regards,

John.
0
jnkoAuthor Commented:
Just to put the issue into attention again.

One of the problems solved. However the issue of the datagrid with the filtered recordset still remains. I guess the solver earns all 50 points! :-)

John.
0
ayufansCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

- PAQ'd and points refunded

Please leave any comments here within the next four days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

ayufans
Cleanup Volunteer
0
Computer101Commented:
PAQed, with points refunded (50)

Computer101
E-E Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.