Solved

Refresh Access Query Data

Posted on 2014-11-15
5
495 Views
Last Modified: 2014-11-17
I have a Form with a ComboBox that uses a Select Query to select data to be used to create a new record in a table[REGISTRATION].  The query uses the same table [REGISTRATION] that I am creating the new record in.  Once I select an option in the ComboBox and save the record containing it to the table [REGISTRATION], I don't want it to be available again in the ComboBox.

If I close and reopen the Form the new record in the table [REGISTRATION] no longer displays in the ComboBox which is what I want.

How do I get this to happen without closing and reopening the Form?
0
Comment
Question by:Bob_Collison
5 Comments
 
LVL 22

Assisted Solution

by:Kelvin Sparks
Kelvin Sparks earned 250 total points
ID: 40445003
Try using this code in the after update event of the form

Me.comboname.Eequery

replace comboname with the name of the combo box.

Kelvin
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 40445005
You can run this code in the AfterInsert (or AfterUpdate) event of the form:

    Me!cboYourCombobox.Requery

/gustav
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40445235
To your table, add a Yes/No field called RegisteredYN. Add this field (RegisteredYN) to your combo box rowsource. Set its criteria to false in the query feeding the combo box.

Additionally, add RegisteredYN also to your form (you can keep it visible or invisible). However when registration is complete, up on saving the competed registration have:

Me!RegisteredYN = True

This way, a registered record will not show up again in your combo box.

You also need to add the following to the OnEnter event of your combo box:

Me!cboMyComboBox.Requery

Mike
0
 

Author Closing Comment

by:Bob_Collison
ID: 40448582
I selected the two solutions because they are virtually identical (as I assume the first had a typo of eequery). and they are the simplest to implement.  I didn't try Mikes but it appears it would work but is much more complicated.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ejb wildfly example 2 30
Use after update to change dates 2 18
Access form for login using active directory 6 25
AvlTree-Node Data type 4 14
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

807 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