Requery information in combo-boxes on main form when new record is added in different form

[tylershannon]

PLEASE HELP!

I am new to using Access and VBA, and have spent countless hours trying to figure out this one problem. Now I've had enough and need to seek better help, so I'm here. Here's what I'm dealing with:

On my main form (frmMain) I have a button to add new records. This button opens a new form (frmSerialNumbers), and on this form I add a new serial number in the textbox (SerialNumber) for a part along with other bits of information (the serial number is the primary and most essential piece of information for identifiction), and it saves correctly to my linked tables. However, this information is not updating in my comboboxes. These comboboxes are set to narrow the search after an item is selected from another. The comboboxes are named: GTTypeSel, CombustorSel, ComponentSel, SerialSel, PartSel, JobSel.

I assume the key here is do add some sort of code for SerialSel so that this combobox recognizes the new entry. Although I may need to add code for each combobox. Any help would be greatly appreciated.

Please, I ask that you be clear with your responses. Because I am new to this, many other forums I have been through have completely lost me. Specify where the codes need to go, what type(s) of event(s), etc.

Thank you graciously in advance,



Tyler

[orange]

Me.Requery forces the entire recordset (underlying data) for the form to reload. This means ALL of the records in your current form will reload. Your current position will be lost, so if you're sitting on record 10 of 100, you'll find yourself back on the first record. Me.Requery is essentially the same as closing and reopening the form. Any new records added by other concurrent users will be available. Likewise any records that have been deleted will disappear. Requery essentially "re-runs the query" that pulled the data into the form in the first place. You can also use requery to update the data in a list box or combo box.

[tylershannon]

I follow what you're saying (for the most part), but my question is where do this code go? Does it go in with frmMain or with frmSerialNumbers? Does this need to be a private sub? Can you show me what this code would look like please? A little bit of your help would go a long way with this stranger.

~~Good Karma~~

[orange]

countless hours means what exactly? You're new to database, and access - so you need to do some tutorials or reading to get a handle on concepts.
You really need a tutorial on Forms, Controls and Events. Use Google to find something relevant.

see this site Datapig tutorials on cascading combo boxes (narrowing down possible values from one combo to another)
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

[tylershannon]

By countless hours I mean my first project as intern for the past two and a half weeks (8 hrs per day) has been to rebuild my company's access database for keeping all sorts of records. I've learned the Python language in the past but am new to VBA. Your recommendations are helpful, but I am having such a tough time because I am having to dissect all of the code to figure out what could be made better. My only problem now, as I am almost finished, is that when I add records on the frmSerialNumbers form, the comboboxes on the frmMain form do not recognize any update was made. I know its a query refreshing issue, but at this point I've exhausted all points of attack that I can think-up.

[orange]

Here are a number of tutorials from techonthenet re Comboboxes

Review the one re refresh the values

Perhaps you can post a jpg of your relationships window and/or your form(s) and controls.

I don't think Python will help with vba other than concepts -- loops, conditions etc

You are working on a copy, right?

[tylershannon]

I think that I may have identified the issue! I believe I need to add a relationship to this query builder (combobox SerialSel on frmMain) that links Serial No (in tblComponentHistory) to the primary key SerialNumber (in tblSerialNumbers). How can I add this relationship? I believe this needs to be done because all the other comboboxes have more than one relationship



The picture on the left is my comboboxes on the main form. The picture on the right is the query for the highlighted combobox SerialSel.


Thank you so much

[June7]

If you want cascading comboboxes, review the DataPig link referenced by orange.

If you want to add a new record to a lookup table 'on-the-fly' during data entry to a combobox that has LimitToList set to yes, consider the combobox NotInList event. http://www.fontstuff.com/access/acctut20.htm