This is my problem.

I have a listbox on a form which contains specific info from a table. On occasions when you choose an item from the listbox to add to the record the item isn't there so you need to add it. I can't have all the items in the list box because sometimes obscure ones do crop up which you can't plan ahead for.
As you can't add something to a listbox that isn't in the list (only combo boxes do this) i have made a button which opens up a tiny form which says "add new item" this is a form from the table which has the listbox as it's field.
It all works fine, you enter the new item description, you click another button on the new form to close it and you are presented with your original form; the table has a new item which you need so you click the list box and voila the item you just added isn't there but if you go directly to datasheet view on the table it is.
Obviously you need to refresh the form to allow for this but the only way I have managed that is to close the form and re-open it again. That's fine BUT it doesn't go back to the record you were on before you closed it.

So my question is can you make access remember what record you were on so that you can call this record up again when you re-open the table or am I completely off-track here and there is a very very simple way of refreshing a form without closing it so that a listbox shows newly added records?

Also when you are on a listbox and you begin to type it doesn't make the list drop down and auto list if you see what I mean. What i have to do is go to that listbox, click to bring the drop down list up and then start to type.

If none of this makes any sense then I appologise in advance as I've been working on this all day and I'm going insane!

EARTHY

What you need to do is put code in the On Close event of the form that you are using to add the new item.

Put this in there:

Forms!YourFormName!YourListBoxName.Requery

If you have spaces in your form name or listbox name it would be thus:

Forms![Your Form Name]![Your List Box Name].Requery

That should add the new item to the list without leaving the current record.

BL
hth
http://www.access-programmers.co.uk/ubb/smile.gif

I forgot to mention the listbox is part of the main subform.

Ok, I tried that and this is the error message:-

Microsoft Access can't find the macro 'Forms!Main subform!Oper'

/me goes insane!

Sorry about that. I haven't worked with subforms enough to know what to tell you. I thought it was on the main form.

You could try this as I searched the archives on requerying a combobox on a subform.

Forms!frmMain.Form.SubForm!cmbComboBox.Requery

or this:
Forms!frmMain.SubForm!cmbComboBox.Requery

Not sure of the exact syntax but it seems to me that the . after the Main form name instead of ! was key.

BL

Ok, did that but it's still coming up with can't find macro blah blah.

After many more hours or changing this and that I found that pressing CTRL+F9 it refreshes the listbox from the table EXCELLENT!!! that's just what I want.

Now all I need to know is how I make it CTRL+F9 on that form automatically. It does matter if I have any of the fields on the form or sub-form selected; CTRL+F9 works with either. I remember with an older version of word you could record a macro. For instance If I wanted to do what I'm trying to do now I would click record macro, selct the form, press CTRL+F9 and then stop the macro. Then it would be a simple case of assigning that macro to run when the particular form was open.

I feel now we are one step closer to solving this http://www.access-programmers.co.uk/ubb/biggrin.gif Although you haven't solved it yet your help is greatly appreciated.

EARTHY

Me.Refresh or Me.Requery, will do what you want, either of these or the code Bob suggested have to be part of an event procedure, not typed on the property sheet, which is why Access is complaining that it can't find the macro.

Ok I have now tried all combinations of that rich. Even ended up putting it in both the new form and the original at "On Activate" "On Enter" and "On Close" nothing worked.
No errors came up which is good but the listbox wasn't refreshed.

Does CTRL+F9 actually refresh or does it do something else? If I knew what that sequence of kep presses did perhaps I could assign that to that listbox.


The only thing I can think of as to why your suggestions aren't working is perhaps I haven't explained what I'm doing correctly.

I'll keep trying though...I haven't given up yet. http://www.access-programmers.co.uk/ubb/biggrin.gif

Someone else suggested this.

Private Sub Form_Activate()
Private Sub Oper_Activate()
On Error GoTo Oper_Activate_Err
DoCmd.Requery "Oper"

Oper_Activate_Exit:
Exit Sub

Oper_Activate_Err:
MsgBox Error$
Resume Oper_Activate_Exit
End Sub

Oper being the name of the listbox

But that doesn't work either. What on earth am I doing wrong?

Try on the UnloadEvent
Me.Recalc
Forms!YourFormName.Requery

Thanx. I'll try anything atm http://www.access-programmers.co.uk/ubb/biggrin.gif

Shouldnt that be
[Forms]![MainFormName].[SubFormName].[Form].[cboBoxName].Requery??

Rich - if thats not right could you tell me why as I KNOW you helped me on this very thing some time ago and it worked.
Thanks

[This message has been edited by Jerry Stoner (edited 05-09-2002).]

Try:
[Forms]![MainFormName]![SubFormName].[Form]![cboBoxName].Requery