Refresh when in a mainfiorm/subform, what is the VB code?

  • Thread starter Thread starter P Mitchell
  • Start date Start date
P

P Mitchell

Hello

I would like someone to be able to help with the updating of data in a
lookup list in a subform. In short, is their a VB instruction that is the
equivalent of performing the action of going to the menu and selecting
Records and then Refresh?

or

To explain, based on a three table system where cars participate in rallies,
which is a many to many relationship, between the Cars and Rallies tables,
which is resolved by having a Races table.

I have a menu/switchboard which contains a button that opens a
mainform/subform.

On the mainform/subform is a button to open a form to allow the entry of a
new car into the Cars table.

I realise this button could be placed on the Menu form, but for better or
worse, if it is placed on the Mainform/Subform

and one then uses the New Car form to enter the details of a new car

and one then returns to the Mainform/Subform the new car appears in the
lookup list (which is the foreign key field in the Races table, i.e.
[Races.Engine #]), but if one repeats this process a 2nd time (or more),
without closing the Mainform/Subform, the 2nd car does not appear in the
lookup list. Selecting Records and then Refresh from the menu system updates
the lookup list. But I have tried numerous instructions in VB when returning
to the form, but I cannot find an instruction that matches Records and then
Refresh. And where does one place the instruction? On Activate, On Got
Focus, or ?????



I can certainly forward a zipped version of a sample database file.



Thank you in anticipation for any assistance.



Peter
 
Hello Peter:

There are a handful of simple things you can do.

If I understand your example properly, I would say go the
the OnCurrent event of the form which has the combo box
you want updated and type this code:

Me."field name of combo box".Requery

What happens is, every time you view a record in your
form, the OnCurrent event runs. For example, if you
toggled through your records one at a time, every time
you arrive at a different record the OnCurrent event
would run.

If you want to have your combo box updated immediately at
the close of your New Car form you could place this code
in the OnClose event of your New Car form:

Forms!"your main form name"!"your combo box name".Requery

However, if you can open this form from other places in
your application and not just from this form you are
having trouble with, I would advise this process:

Add a Sub procedure to Modules and name is 'IsLoaded' and
make it Public. Paste this code into the module:

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form
view or Datasheet view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm,
strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <>
conDesignView Then
IsLoaded = True
End If
End If

End Function


Then modify your last code snippet to this:

If IsLoaded ("your main form name here") Then
Forms!"your main form name"!"your combo box
name".Requery
End If

This will cause the combo update code to run only if the
main form is open. If you can open the New Car form from
more than one location and you don't add the Is Loaded
code, you will be bombarded with error messages because
Access won't be able to find the form and field you are
referencing with the Requery code.

Seth


-----Original Message-----
Hello

I would like someone to be able to help with the updating of data in a
lookup list in a subform. In short, is their a VB instruction that is the
equivalent of performing the action of going to the menu and selecting
Records and then Refresh?

or

To explain, based on a three table system where cars participate in rallies,
which is a many to many relationship, between the Cars and Rallies tables,
which is resolved by having a Races table.

I have a menu/switchboard which contains a button that opens a
mainform/subform.

On the mainform/subform is a button to open a form to allow the entry of a
new car into the Cars table.

I realise this button could be placed on the Menu form, but for better or
worse, if it is placed on the Mainform/Subform

and one then uses the New Car form to enter the details of a new car

and one then returns to the Mainform/Subform the new car appears in the
lookup list (which is the foreign key field in the Races table, i.e.
[Races.Engine #]), but if one repeats this process a 2nd time (or more),
without closing the Mainform/Subform, the 2nd car does not appear in the
lookup list. Selecting Records and then Refresh from the menu system updates
the lookup list. But I have tried numerous instructions in VB when returning
to the form, but I cannot find an instruction that matches Records and then
Refresh. And where does one place the instruction? On Activate, On Got
Focus, or ?????



I can certainly forward a zipped version of a sample database file.



Thank you in anticipation for any assistance.



Peter


.
Click to expand...
 
Back
Top