?
Solved

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

Posted on 2003-11-05
11
Medium Priority
?
400 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 

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

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month15 days, 2 hours left to enroll

770 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