Making Combo Box fill in after new item is added?

[CJAlla]

Hi all,

Was hoping for a little bit of help again. For some reason I cant seem to get my head round on how to manage this or even what exactly to type so I can search for others similar question...

To put it simply...

Ive got 2 forms, first with all my data on which has a combo box. Ive got another form that opens up from a button which I can add new data for the combo box. Once ive added the new data and the second form closes, I want the newly added item in the combo box to automatically appear. So that I dont have to type it in again to bring it up.

How do I go about this?

Thanks in advance once again

Chris

 

[rainman89]

requery the combobox on the close of the other form

so do

forms!yourfrmname.comboboxname.requery

 

[MatMac]

What about refreshing the recordset when you return to your first form?

Assuming you have a CLOSE button on the first form (actually named "close"), in the VB which is run when you click to open the second form, set the focus to the CLOSE button using:

Me!close.SetFocus

Then, when the second form is closed, and the focus returns to the first form, it will return to the CLOSE button. Put this Sub in the VB of your first form to refresh it.

Private Sub Close_GotFocus()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

 

[CJAlla]

Hey

Thanks for the info. But doesnt requery just update the combo box? I want it to auto fill in to it what I just added...

Cheers

requery the combobox on the close of the other form

so do

forms!yourfrmname.comboboxname.requery

 

[rainman89]

i guess im confused. i thought u wanted the combo box to update to include the new field that you just entered in?

 

[CJAlla]

Ah, sorry, maybe I was a bit confusing.

Ive got the requery in to update the combo box. Thats fine.

But what I wanted is for the combo box to actually have the new item entered, not just in the list, but in the field and selected.

Anyway to do this?

i guess im confused. i thought u wanted the combo box to update to include the new field that you just entered in?

 

[rainman89]

oh so you want that item to be selected that you just entered?

 

[CJAlla]

Yeah thats right

Sorry if I didnt make sense.

Is it pretty simple?

oh so you want that item to be selected that you just entered?

 

[rainman89]

is the combo based on a table? if it is, you can just go to the last record.

 

[CJAlla]

Ah yes! lol.

I didnt think of that, silly me. Thank You! Your a hero again!

 

[rainman89]

Glad to help

 

[CJAlla]

Actually, lol.... Having trouble working out how to select last record on the combo..... Heeelp

Glad to help

 

[CJAlla]

Still having no luck figuring it out

Anyone?

Thnx

 

[rainman89]

can you post up a sample as to what you are working with? thanks

 

[CJAlla]

Okay,

Heres the sample.

Basically when you click to add a new supplier, you enter all the details, and when you click the + it adds it and goes back to main form and focus's on the supplier box. But I also want it to select the record in that box which was just added.

Is this possible?

Thnx again

can you post up a sample as to what you are working with? thanks

 

[boblarson]

Okay, a few things:

1. change the AddNew Button Click event to:

Private Sub AddNewButton_Click()
On Error GoTo Err_AddNewButton_Click
    If Me.Dirty Then Me.Dirty = False
    Forms("Main").SuppliersName.Requery
    Forms("Main").SuppliersName = DLookup("[Primary Key]", "SuppliersConsignees", "[Name]='" & Me!Name & "'")


    DoCmd.Close acForm, Me.Name, acSaveNo

Exit_AddNewButton_Click:
    Exit Sub

Err_AddNewButton_Click:
    MsgBox Err.Description
    Resume Exit_AddNewButton_Click
    
End Sub

But, first do these things too:

1. Change the name of the table to remove the slash (/) mark. You should NEVER use special characters in names of fields or objects. You should also not use spaces in field names or objects as it will make your life way easier when coding.

2. Change the field NAME to something else. NAME is an Access reserved word and as such will bite you in the butt if you continue to use it.

3. The same goes with naming your field PRIMARY KEY. I wouldn't do that if I were you, but if you want to identify it as such use MyFieldName_pk or something like that.

So, if you make the changes I suggest, then you will need to modify the code I gave you slightly to reference the correct names.

 

[CJAlla]

Bob,

Thank you for the tips. As I was into my project I sort of noticed the spaces in somethings were causing me trouble. I will tidy up the db with the things you said. Pretty new to this so didnt really know the best way to name and lay things out.

And thank you v much for the coding. Im not in the office working on this untill friday so will try it then and let you know. Im sure ill have no problems tho

Thank you again and again!

Chris

 

[CJAlla]

Bob,

Have gone through my database and altered all the things you said to.

Also the code works brilliantly! Thank you very much for your assistance. Its a lovely touch to the db

Many Thanks!

Chris

 

[poucedeleon]

Load Combo box data from a Form

Bob,
I have been searching the internet looking for help adding information from a form into a combo box, when I found your post. I need the same thing to happen that CjAlla needed. My database has three sub forms on a tab ctl Staff, Inmates and Special Circumstances. The form [sfrInmatesInvovled] on the Inmate tab has a combobox [InmateID] that gets it data from a table [tblInmates]. There is also a button [cmdAddNewInmate] that opens a form [AddNewInmate]. I have been trying to get this form to populate the combobox when you hit save on the [AddNewInmate] form instead of having to select the newly added inmate from the combobox list. I took the code you wrote for Cjalla and changed it to reflect my control names but there were parts of his code I was not sure about. I am hoping you will be willing to take a look at his and give me some suggestions

Private Sub cmdAddNewButton_Click()
On Error GoTo Err_cmdAddNewButton_Click
    If Me.Dirty Then Me.Dirty = False
    Forms("sfrInmatesInvolved").InmateID.Requery
    Forms("sfrInmatesInvolved").InmateID = DLookup("InmateID", "InmateLastName", "InmateFirstName", "CIN"='" & Me!InmateID & "'")

    DoCmd.Close acForm, Me.InmateID, acSaveNo

Exit_cmdAddNewButton_Click:
    Exit Sub

Err_cmdAddNewButton_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddNewButton_Click
    
End Sub

 

[pam0566]

I have a similar problem. I have a form with Vehicle makes, and Vehicle Models. The models are a drop down box. if its not on the list.. a subform opens to allow me to edit. that works great. .. BUT i would like to close that form [this is all access 2007 by the way], requery the model drop down and yes it would be nice if that model was selected. I saw the command on the requery.. but where exactly would i put this. can you send me an example? Thanks so much!!!!!!