Real-TIme Updating of Tables and Active Window Forms

  • Thread starter Thread starter John
  • Start date Start date
J

John

There is a form that I utilize in order to update a table
and another form on the screen. The question I have is
that once the change is made to the form, the table needs
to be updated and the other form needs to refreshed to
update the data based off the change in the table?

Example: Two Forms: A change is made on form #1, the table
is updated and then form #2 is updated with the
information for the change made in form #2.

The change is made in a combo box. I thought to use the
requery or repaint method, but I keep getting an error.
What do I need to use and where do I need to put the code
so that once a user chooses a new value from the combo
box, it updates the table and the form in the active
window instanteously.

Thanks,
John
 
q1: Is Form # 1's combobox a bound control?
q2: Do you know when the combobox value has updated the
database.
After you know the database has been successfully
updated, that's when you can work on refreshing
the information in Form # 2's control.
q1 applies to the control in Form # 2 (is it bound some
how to the record you just created?) But there's other
questions, like is Form # 2 a subform, or a seperate form
running simultaneously but not on top?
What's the method and syntax from Form # 1 your using
for .requery or .repaint, and the exact error you receive?

Steve
APE-UCF
 
Answer to q1: Yes the combobox is a bound control to the
table that I need to automatically updated.

Answer to q2: That is my problem, the combobox will not
update the table till the form is closed, what I need is
for the table to be updated once the combobox is changed
and then refresh all the forms in the main form. Form #2
is a subform. Like I said above the combobox in Form #1 is
bound the the table value.

I tried to put a refresh method on all the forms in the
main form and the message I receive is "The expression
after update as the event property setting produced the
following error: A problem occurred while Microsoft Access
was communicating with the OLE server or ActiveX control."

Thanks,
John
 
John,

I got this to work using OnExit event of a combobox
in a main form. After you tab out, it puts the value typed
into a textbox control of a subform.
You could also move the code to OnChange event if your
combobox happens to be a selection list.


Private Sub Form1ComboBox_Exit(Cancel As Integer)
'
'not sure where your ComboBox data comes from
'so I bound my Form1ComboBox to a table / query seperate
'from the one that's going to be updated in subForm2.
'..subForm2.RecordSource is bound to a table, with
'a text box control to accept data from Form1ComboBox.
'So if your not doing it that way, you don't need the line
'that ends in '... .addnew'.

'uncomment for debugging
'MsgBox Forms("Form1").Form1ComboBox.Value

Dim tmpStr As String

'store the value we're going to put in subForm2 textbox
tmpStr = Forms("Form1").Form1ComboBox.Value

'set up subForm2 for a new record
Forms("Form1").Controls("subForm2").Form.Recordset.AddNew

'assign the data to the empty control
Forms("Form1").Controls("subForm2").Controls _
& ("txt_TestData") = tmpStr

End Sub
 
Back
Top