Solved

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

Posted on 2003-11-05
11
372 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:jnko
  • 5
  • 2
  • 2
  • +2
11 Comments
 
LVL 9

Expert Comment

by:Dang123
ID: 9686756
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
 
LVL 3

Expert Comment

by:SQLMaster
ID: 9686842
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
 

Author Comment

by:jnko
ID: 9686846
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
 

Author Comment

by:jnko
ID: 9686894
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
 
LVL 9

Expert Comment

by:Dang123
ID: 9687017
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jnko
ID: 9687236
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
 
LVL 3

Expert Comment

by:SQLMaster
ID: 9687716
John,

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

Dheeraj
0
 

Author Comment

by:jnko
ID: 9692559
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
 

Author Comment

by:jnko
ID: 9706941
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
 
LVL 1

Expert Comment

by:ayufans
ID: 10458378
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 10490809
PAQed, with points refunded (50)

Computer101
E-E Admin
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now