how to link query with subform in VB

  • Thread starter Thread starter ye
  • Start date Start date
Y

ye

Hi,

I build a subform in a main form. This subform will show
all records according to query. This query is created in
VB because it is a dynamic query (that is, query is
created according to one of field that user inputs in the
main form).



For example,
There is a text control called "textFirstName" and a
subform called "child".

VB code:
dim FirstName As String
FirstName = Me![textFirstName]
qry = "SELECT * FROM CorporateTable WHERE " & SearchBy
& " = """ & FirstName & """"
Me![child].SourceObject = qry

The last sentence doesn't work.



I don't know how to link query with subform in VB. Please
help me if anybody knows how to do it.


thanks.
 
ye said:
Hi,

I build a subform in a main form. This subform will show
all records according to query. This query is created in
VB because it is a dynamic query (that is, query is
created according to one of field that user inputs in the
main form).



For example,
There is a text control called "textFirstName" and a
subform called "child".

VB code:
dim FirstName As String
FirstName = Me![textFirstName]
qry = "SELECT * FROM CorporateTable WHERE " & SearchBy
& " = """ & FirstName & """"
Me![child].SourceObject = qry

The last sentence doesn't work.



I don't know how to link query with subform in VB. Please
help me if anybody knows how to do it.


thanks.
Click to expand...

You don't want to change the subform control's SourceObject -- that
would be changing the form that is to be displayed, by the control, so
it would have to be set to a form name, not to a SQL statement. I
believe what you want to do is change the RecordSource of the subform,
which you would do like this:

Me![child].Form.RecordSource = qry

That presumes that "child" is the name of the subform control on the
main form, and not just the name of the form being displayed by that
control.
 
Thank you for your help

I don't understand the meaning of this sentence
"That presumes that "child" is the name of the subform
control on the main form, and not just the name of the
form being displayed by that control."

In addition, I tried "Me![child].Form.RecordSource =
qry". It says "Object doesn't support this property or
method".


Ye Li



-----Original Message-----
Hi,

I build a subform in a main form. This subform will show
all records according to query. This query is created in
VB because it is a dynamic query (that is, query is
created according to one of field that user inputs in the
main form).



For example,
There is a text control called "textFirstName" and a
subform called "child".

VB code:
dim FirstName As String
FirstName = Me![textFirstName]
qry = "SELECT * FROM CorporateTable WHERE " & SearchBy
& " = """ & FirstName & """"
Me![child].SourceObject = qry

The last sentence doesn't work.



I don't know how to link query with subform in VB. Please
help me if anybody knows how to do it.


thanks.
Click to expand...

You don't want to change the subform control's SourceObject -- that
would be changing the form that is to be displayed, by the control, so
it would have to be set to a form name, not to a SQL statement. I
believe what you want to do is change the RecordSource of the subform,
which you would do like this:

Me![child].Form.RecordSource = qry

That presumes that "child" is the name of the subform control on the
main form, and not just the name of the form being displayed by that
control.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Hi,

Now I understand you.

I want the records displayed in the subform
name "child". "child" is a name of the subform control of
the main form.


Ye Li


-----Original Message-----
Hi,

I build a subform in a main form. This subform will show
all records according to query. This query is created in
VB because it is a dynamic query (that is, query is
created according to one of field that user inputs in the
main form).



For example,
There is a text control called "textFirstName" and a
subform called "child".

VB code:
dim FirstName As String
FirstName = Me![textFirstName]
qry = "SELECT * FROM CorporateTable WHERE " & SearchBy
& " = """ & FirstName & """"
Me![child].SourceObject = qry

The last sentence doesn't work.



I don't know how to link query with subform in VB. Please
help me if anybody knows how to do it.


thanks.
Click to expand...

You don't want to change the subform control's SourceObject -- that
would be changing the form that is to be displayed, by the control, so
it would have to be set to a form name, not to a SQL statement. I
believe what you want to do is change the RecordSource of the subform,
which you would do like this:

Me![child].Form.RecordSource = qry

That presumes that "child" is the name of the subform control on the
main form, and not just the name of the form being displayed by that
control.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
ye said:
Hi,

Now I understand you.

I want the records displayed in the subform
name "child". "child" is a name of the subform control of
the main form.
Click to expand...

Are you sure it's "child", and not "Child1" or something like that? The
message you report getting, "Object doesn't support this property or
method", suggests that "child" is not the name of a subform control.
Please click on the subform control and open its property sheet. Make
sure that the property sheet shows that you're looking at a
Subform/Subreport's properties, not those of the SourceObject form.
From the property sheet, report the Name property (from the Other tab)
and the Source Object property (from the Data tab).
 
Hi Dirk,

Sorry, I made mistake. I wrote
me![child].RecordSource = qry, instead of
me![child].Form.RecordSource = qry.

But when I wrote me![child].Form.RecordSource = qry,
it says "The expression you entered referers to an object
that is closed or doesn't exist".

Do I need to link something to SourceObject? I don't know
what is SourceObject for? And what is Object mentioned
here?


Ye Li
 
Ye said:
Hi Dirk,

Sorry, I made mistake. I wrote
me![child].RecordSource = qry, instead of
me![child].Form.RecordSource = qry.

But when I wrote me![child].Form.RecordSource = qry,
it says "The expression you entered referers to an object
that is closed or doesn't exist".

Do I need to link something to SourceObject? I don't know
what is SourceObject for? And what is Object mentioned
here?
Click to expand...

Yes, you need to put something in SourceObject: the name of the form
object that is to be displayed by the subform control. You can't bind a
subform control directly to a table or query. You must design a form
that is bound to your table or query, and then set that form as the
SourceObject of the subform control. That's the way subforms work --
the subform control acts as a window in which some other form is
displayed.

Now, the fact that you're trying to assign your SQL statement to the
subform's recordsource on the fly suggests that you may want to take one
of two alternative approaches:

Approach 1.
If all the queries you might want to swap in will have the same fields,
then create a simple form in continuous or datasheet view, initially
based on any of the possible queries. Then set that form as the
subform's SourceObject, and use the code I gave you to change the
RecordSource property on the fly.

Approach 2.
If the queries you might want to swap in will have different fields,
then create a separate form for each of these queries, and just change
the SourceObject property of the subform control to the form you want it
to display.
 
I have a table called "CorporateTable". It includes one
field called "FirstName". The main form is used to search
records according to FirstName user inputs in a text
control and display records in the subform.

I did according to what you said:
1. I build a form that displays all the records in the
table called "CorporateTable". Link this form as
SourceObject in the subform called "child".
2. And then in Vb code, I make a string:
qry = "SELECT * FROM CorporateTable WHERE FirstName = "
& """ & Me![textFirstName] & """"
This textFirstName is a text control I made in the main
form. It allows user to input the FirstName to search
records in the table "CorporateTable".
3.I wrote Vb code as:
Me![Child].Form.[RecordSource] = qry

But, I got "Object is required".







-----Original Message-----
Hi Dirk,

Sorry, I made mistake. I wrote
me![child].RecordSource = qry, instead of
me![child].Form.RecordSource = qry.

But when I wrote me![child].Form.RecordSource = qry,
it says "The expression you entered referers to an object
that is closed or doesn't exist".

Do I need to link something to SourceObject? I don't know
what is SourceObject for? And what is Object mentioned
here?
Click to expand...

Yes, you need to put something in SourceObject: the name of the form
object that is to be displayed by the subform control. You can't bind a
subform control directly to a table or query. You must design a form
that is bound to your table or query, and then set that form as the
SourceObject of the subform control. That's the way subforms work --
the subform control acts as a window in which some other form is
displayed.

Now, the fact that you're trying to assign your SQL statement to the
subform's recordsource on the fly suggests that you may want to take one
of two alternative approaches:

Approach 1.
If all the queries you might want to swap in will have the same fields,
then create a simple form in continuous or datasheet view, initially
based on any of the possible queries. Then set that form as the
subform's SourceObject, and use the code I gave you to change the
RecordSource property on the fly.

Approach 2.
If the queries you might want to swap in will have different fields,
then create a separate form for each of these queries, and just change
the SourceObject property of the subform control to the form you want it
to display.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Ye said:
I have a table called "CorporateTable". It includes one
field called "FirstName". The main form is used to search
records according to FirstName user inputs in a text
control and display records in the subform.

I did according to what you said:
1. I build a form that displays all the records in the
table called "CorporateTable". Link this form as
SourceObject in the subform called "child".
2. And then in Vb code, I make a string:
qry = "SELECT * FROM CorporateTable WHERE FirstName = "
& """ & Me![textFirstName] & """"
This textFirstName is a text control I made in the main
form. It allows user to input the FirstName to search
records in the table "CorporateTable".
3.I wrote Vb code as:
Me![Child].Form.[RecordSource] = qry

But, I got "Object is required".
Click to expand...

Your assignment to qry doesn't look right to me, though I kind of doubt
that's the source of the error you're getting. I think it should be

qry = "SELECT * FROM CorporateTable " & _
"WHERE FirstName = """ & Me![textFirstName] & """"

But, as I said, I don't think that's where your error message is coming
from. Where are you putting the statement
Me![Child].Form.[RecordSource] = qry
Click to expand...

?
 
I put
Me![Child].Form.[RecordSource] = qry
right after
qry = "SELECT * FROM CorporateTable " & "WHERE FirstName
= """ & Me![textFirstName] & """"
in vb code where the command button named "Search" in the
main form is clicked (cmdSearch_click).

Thanks






-----Original Message-----
I have a table called "CorporateTable". It includes one
field called "FirstName". The main form is used to search
records according to FirstName user inputs in a text
control and display records in the subform.

I did according to what you said:
1. I build a form that displays all the records in the
table called "CorporateTable". Link this form as
SourceObject in the subform called "child".
2. And then in Vb code, I make a string:
qry = "SELECT * FROM CorporateTable WHERE FirstName = "
& """ & Me![textFirstName] & """"
This textFirstName is a text control I made in the main
form. It allows user to input the FirstName to search
records in the table "CorporateTable".
3.I wrote Vb code as:
Me![Child].Form.[RecordSource] = qry

But, I got "Object is required".
Click to expand...

Your assignment to qry doesn't look right to me, though I kind of doubt
that's the source of the error you're getting. I think it should be

qry = "SELECT * FROM CorporateTable " & _
"WHERE FirstName = """ & Me![textFirstName] & """"

But, as I said, I don't think that's where your error message is coming
from. Where are you putting the statement
Me![Child].Form.[RecordSource] = qry
Click to expand...

?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
ye said:
I put
Me![Child].Form.[RecordSource] = qry
right after
qry = "SELECT * FROM CorporateTable " & "WHERE FirstName
= """ & Me![textFirstName] & """"
in vb code where the command button named "Search" in the
main form is clicked (cmdSearch_click).
Click to expand...

That doesn't sound wrong. Would you mind posting (by copy/paste) the
complete code of the cmdSearch_Click subroutine? Also, if possible can
you point out the exact line of that routine on which the error is
raised?
 
The code is:

Private Sub cmdOK_Click()
Dim qry As String
Dim SearchBy As String

If Me![groupSearchBy] = 1 Then
SearchBy = "FirstName"
qry = "SELECT * FROM CorporateTable WHERE " &
SearchBy & " = """ & Me![ListSelect] & """"
ElseIf Me![groupSearchBy] = 2 Then
SearchBy = "LastName"
qry = "SELECT * FROM CorporateTable WHERE " &
SearchBy & " = """ & Me![ListSelect] & """"
End If
Me![SearchContent].Form.[RecordSource] = qry
End Sub

"groupSearchBy" is the name of Option Group control to
tell if the record is searched by FirstName or LastName.
"ListSelect" is the name of List Box control to show all
the list of FirstName/LastName in the table.
"SearchContent" is the name of subform.

Please look at it. Thanks.




-----Original Message-----
I put
Me![Child].Form.[RecordSource] = qry
right after
qry = "SELECT * FROM CorporateTable " & "WHERE FirstName
= """ & Me![textFirstName] & """"
in vb code where the command button named "Search" in the
main form is clicked (cmdSearch_click).
Click to expand...

That doesn't sound wrong. Would you mind posting (by copy/paste) the
complete code of the cmdSearch_Click subroutine? Also, if possible can
you point out the exact line of that routine on which the error is
raised?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Ye said:
The code is:

Private Sub cmdOK_Click()
Dim qry As String
Dim SearchBy As String

If Me![groupSearchBy] = 1 Then
SearchBy = "FirstName"
qry = "SELECT * FROM CorporateTable WHERE " &
SearchBy & " = """ & Me![ListSelect] & """"
ElseIf Me![groupSearchBy] = 2 Then
SearchBy = "LastName"
qry = "SELECT * FROM CorporateTable WHERE " &
SearchBy & " = """ & Me![ListSelect] & """"
End If
Me![SearchContent].Form.[RecordSource] = qry
End Sub

"groupSearchBy" is the name of Option Group control to
tell if the record is searched by FirstName or LastName.
"ListSelect" is the name of List Box control to show all
the list of FirstName/LastName in the table.
"SearchContent" is the name of subform.

Please look at it. Thanks.
Click to expand...

Hmm. While your code could be simplified to ...

'------ start of revised code ------
Private Sub cmdOK_Click()

Dim SearchBy As String

Select Case Me!groupSearchBy.Value
Case 1
SearchBy = "FirstName"
Case 2
SearchBy = "LastName"
Case Else
MsgBox "Please choose a search option."
Exit Sub
End Select

Me!SearchContent.Form.RecordSource =
"SELECT * FROM CorporateTable WHERE " &
SearchBy & _
" = """ & Me![ListSelect] & """"

End Sub
'------ end of revised code ------

.... I don't see any reason inherent in the code itself that would cause
it to fail, especially not with the error you report.

Here are the things to check. I've already suggested you check them in
earlier messages, but I have to believe the problem lies in here
somewhere, so please double-check:

1. Make sure that "SearchContent" is the name of the subform control,
on the main form, that will display the subform. Be aware that the
subform control doesn't necessarily have the same name as the form that
is its SourceObject.

2. Make sure that the subform control has the name of an existing form
in its SourceObject property.

3. Make sure that that form (the one that is SourceObject for the
subform control) either has CorporateTable as its RecordSource property,
or is compatible with having that table as its RecordSource.

That's about all I can think of.
 
Back
Top