Refresh option

[Beany]

is there anyway i can get all my forms to refresh instantly when any changes or updates are made to the table??

[George Oro]

If the forms are close then automatically it will refresh on open otherwise perhaps this will do. Put this where the last data update occurs.
[Form]![YourFormName_1].requery
[Form]![YourFormName_2].requery
[Form]![YourFormName_3].requery
etc...
if your not sure which forms are open, the you have to check and loop first which forms are open then trigger the requery.


if is unbound form then it some else.

hth,
George

is there anyway i can get all my forms to refresh instantly when any changes or updates are made to the table??

[Beany]

it is an unbound form...........

i have an address section which has a combobox and some address fields. From the combobox u select a site name and it automatically populates the address fields.....

i have a button next to this, this button takes me to a small form where i insert a new site name with the address details........ .............

ONCE ADDED, THIS SHOULD AUTOMATICALLY SHOW IN THE COMBOBOX LIST........... ......

but it only shows it when i close all the forms down and go back into it.......
is there anyway it can add to the combobox straightaway?

[George Oro]

I see, try to put this before opening your small form

me.refresh

hth,
George

it is an unbound form...........

i have an address section which has a combobox and some address fields. From the combobox u select a site name and it automatically populates the address fields.....

i have a button next to this, this button takes me to a small form where i insert a new site name with the address details........ .............

ONCE ADDED, THIS SHOULD AUTOMATICALLY SHOW IN THE COMBOBOX LIST........... ......

but it only shows it when i close all the forms down and go back into it.......
is there anyway it can add to the combobox straightaway?

[Beany]

I see, try to put this before opening your small form

me.refresh

hth,
George

me.fresh <---------- where would this code go? in the NEW ADDRESS button code?

would it not be better to refresh it once ive keyed in the new details in the small form?

im slightly confused?

[George Oro]

You said:
i have a button next to this, this button takes me to a small form where i insert a new site name with the address details........ .............

So that button for sure there's a command line something like this:
docmd.openform "YourFormNa me"

then put me.refresh on top of it so it will look like:

me.refresh
docmd.openform "YourFormNa me"

I believe this works also:
In your small form where you adding the new record put this after you save command.

[Forms]![YourFormName]![YourComboBox].requery

HTH,
George

me.fresh <---------- where would this code go? in the NEW ADDRESS button code?

would it not be better to refresh it once ive keyed in the new details in the small form?

im slightly confused?

[Beany]

cheers George,

the [Forms]![YourFormName]![YourComboBox].requery worked!

god bless

[George Oro]

My pleasure...

BTW, the me.refresh doesn't work? because it works to me. Anyway job done=)

cheers George,

the [Forms]![YourFormName]![YourComboBox].requery worked!

god bless

[NeoPa]

My pleasure...

BTW, the me.refresh doesn't work? because it works to me. Anyway job done=)

Generally, the Refresh method will replace data in records that already existed with the updated data.
The ReQuery method will redo the whole query which includes addition and removal of records.

[NeoPa]

Beany,
There is no way that I know of to recognise when data has been changed in a table unless this is done via a form.
Where do you run the code that George just provided you with?
If it's in the same form's code (as the ComboBox) then you can use the shorter :

Me![YourComboBox].ReQuery

[Beany]

im definitely confused now :)

for my combobox ive inserted the following code ( not sure if does anything):

Private Sub site_AfterUpdate()
Form_addform.Refresh
End Sub

in my small form, for the add button ive used:

Private Sub addrec_Click()
 Dim strSQL As String
 DoCmd.SetWarnings False
 Dim strmsg
 strmsg = "Are you sure you want to add " & site & " to the table?"

If MsgBox(strmsg, vbYesNo) = vbYes Then
   
 strSQL = "INSERT INTO tab_newadd ([site], [address_1], [address_2], [address_3], [postcode]) VALUES ('" & Me.site & "','" & Me.address_1 & "','" & Me.address_2 & "','" & Me.address_3 & "','" & Me.postcode & "')"
 ''Debug.Print strSQL
 [Forms]![addform]![site].Requery
 
 DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True
 
 End If
 End Sub

it seems to work thanks to George, but Neo do u think there sumat wrong...
it does everything i want it to!

[NeoPa]

This is not wrong Beany, in as much as it will not do any harm (I'm assuming this all happens in the form "addform" that you have in your code).
You do however have code which is overly complex for what you need.
The first procedure could more simply be written as :

Private Sub site_AfterUpdate()
    Me.Refresh
End Sub

Forms("addform" ) == [Forms]![addform] == Form_addform == Me (as long as you are executing code within the form itself).
So, in similar fashion, your line :

[Forms]![addform]![site].Requery

could be rewritten as :

Me![site].Requery

Don't get me wrong - George has given you good advice that works, but it's always better to understand things more deeply if possible.
My last comment :
This is quite a non-standard way to achieve the results you're after, but notwithstanding that, it's also quite a clever solution :)

[Beany]

thanx mate!!!

[NeoPa]

No Problem :)
Good luck with your project.

[George Oro]

PS.

I agreed on this:

Generally, the Refresh method will replace data in records that already existed with the updated data.
The ReQuery method will redo the whole query which includes addition and removal of records.

The reason I advised to put Me.Refresh
because its an unbound form (as he said) so it will not trigger any updates and this is what Im using too that works.

Your welcome..

cheers,
George