Update data on subform

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Dec 30, 2003

• #1

I have a form with a listbox and a subform. The subform has to be updated when the listbox is clicked.

In the subform i have a query on opening the form.
SQL = "SELECT field FROM table WHERE id = " & listbox.value
Me.Recordsource = SQL.

I tryed Forms!FormSubToezicht.repaint in the onclick of the listbox, but that doesn't work and gives an error.

 

R

Rich

Guest

• Dec 30, 2003

• #2

Your subFormName.•Requery

 

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Dec 30, 2003

• #3

I tryed this
Forms!FormSubToezicht.Requery
but that gives error 2450 Cannot find the form......

and I also tryed this
FormSubToezicht.Requery
but that gives error 424 Object requierd

 

R

Rich

Guest

• Dec 30, 2003

• #4

the name of your subform control name is on the main forms property sheet, that is all you need

yourSubFormControlName.Requery

 

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Dec 30, 2003

• #5

The error is gone now. But the next problem is that the data isn't updated. I think that is because the sql where the data is selected is only in the "Private Sub Form_Open(Cancel As Integer)
" and has to be somewhere else, only I don't know where.

I tryed to place the sql in the field recordsource in the propertys of the form but because there is a variable in the sql that gives an error on opening the form.

 

R

Rich

Guest

• Dec 30, 2003

• #6

You should be able to set the subforms Record source to either a query or sql provided you have defined the criteria correctly, ie Forms!YourForm!YourListBox

 

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Dec 31, 2003

• #7

What I've tryed is

Private Sub listbox_Click()
SQL = "SELECT field FROM table WHERE id =" & listbox.value
Forms!FormSubToezicht.RecordSource = SQL
End Sub

But that gives me error 2450 (Cannot find the form......
) again.

 

R

Rich

Guest

• Dec 31, 2003

• #8

Here's a quick example

 

Last edited: Aug 21, 2008

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Dec 31, 2003

• #9

It works!
Thank you very very very much!!!!

 

I

ingrid

Registered User.

Local time

Today, 18:42

Joined

Apr 10, 2003

Messages

42

• Jan 29, 2004

• #10

I just found out that this also works:

Form_SubForm.RecordSource = SQL