Combobox does no show value just added unless reopen form (1 Viewer)

Ihk

Member
Local time
Today, 21:35
Joined
Apr 7, 2020
Messages
280
Hi
I have combobox on form (Form1_withCombo),
1) Combo has record source from a Table1
2) Combo is bound to Table2, to add record there
3) Form1 which has combo, has button to add more records in combo (basically in Source Table1) via Form2 (for Adding record to Combo)
Problem
combo does not show value which I just added, unless I close current Form1 and reopen it. In that case record is fetched and appears in combo, works fine.
I tried to solve this, on close of a Form2 (adding record to combo)

Code:
Forms![Form1_WithCombo].Form!cboComboName.Requery
Requery, RefreshRecord , every thing does not work.
Neither by RefreshRecord, nor by Requering Form1

Then I tried on close of Form (FormAddingComboData)
Code:
Dim cbo As ComboBox
Dim rs As DAO.Recordset
Set cbo = Forms![Form1_WithCombo].cboComboName
Set rs = CurrentDb.OpenRecordset(cbo.RowSource)
Set cbo.Recordset = rs

By this method of open record set, the new value appears in combo but when I select it, it disappears all of a sudden and never comes back unless I close and reopen form.
NOTE:
I dont want to open and reopen form, because my form has many data entry field. User has already filled data in the fields and then this will disappear.
My form has more than 3 such combos.
 

Mike Krailo

Well-known member
Local time
Today, 16:35
Joined
Mar 28, 2020
Messages
1,030
So me.requery doesn't work? Upload sample database please.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
on the Close Event of FromAddingComboData:

Private Sub Form_Close()
Forms![Form1_WithCombo]!cboComboName.RowSource = Forms![Form1_WithCombo]!cboComboName.RowSource
end sub

or
Code:
Private Sub Form_Close()
With Forms![Form1_WithCombo]!cboComboName
    .RowSource = .RowSource
End With
end sub
 

Ihk

Member
Local time
Today, 21:35
Joined
Apr 7, 2020
Messages
280
Thank you very much @Mike Krailo and @arnelgp
When I created a sample db for this purpose, and I used open record set method (That credit also goes to @arnelgp - because it was suggested by him long time back on my post),
Magically on this sample db it is working. But on my real db, when select the Freshly added combo data, it disappears, I dont know why? form gives a jerk and freshed value in combo is not there, (older ones are there).
Sample db has no problem. let me figure out.
How to use this
order form > click pencil button > add any name in a new form > close > new value will appear in combo of OrderForm
But in my real db it shows once, but disappears when I select it.
 

Attachments

  • ComboSampleDB.accdb
    992 KB · Views: 229

Mike Krailo

Well-known member
Local time
Today, 16:35
Joined
Mar 28, 2020
Messages
1,030
So now that you know the sample works, find what is different from the sample and your actual db. There has to be a difference.

If there are no other visible differences, try importing all objects to a new database to rule out corruption.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
using code i posted on post#3
 

Attachments

  • ComboSampleDB.accdb
    992 KB · Views: 260

Ihk

Member
Local time
Today, 21:35
Joined
Apr 7, 2020
Messages
280
Thank you again @Mike Krailo & @arnelgp
I found the problem, because I have search option in combo as I type. There is module, and there is some codes on Form load and on top of Form.
This is causing the problem mentioned above.
@arnelgp ,
1) code at post#3 does not work with current version along with search
2) If I use open record set method, then it has exactly the same as above mentioned.
Database with problem is attached. Can you suggest another search method within combo record, so to avoid this problem.
 

Attachments

  • ComboSampleDB2.accdb
    1.1 MB · Views: 214

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
you are using Fart on your combobox.
 

Attachments

  • ComboSampleDB2.accdb
    1.1 MB · Views: 271
  • Love
Reactions: Ihk

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
you should look the additional code i wrote to Both forms.
 

Users who are viewing this thread

Top Bottom