Subform query error (1 Viewer)

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
I have a master form with a listbox and a subform. The listbox is unbound. The form has a recordsource of peoples data. Each row of the listbox contains a unique TelefID (Autonumber), name, telephone etc of each person. The subform is on the master form and contains detailed data about every person including TelefID. Thus when the user selects a person from the list box the subform should display the details of that person. I have defined a query to select the person from the listbox and link it to the right detail record. The code is below:

Code:
Dim QryName As String
    Dim qdf As DAO.QueryDef
    Dim J1 As Long
    ParName = "ListNumbersFm"
    ForName = "EnterRecordFmSub"
    QryName = "SelQy"
    J1 = Nz(Me.People, 1)
    
    RowSource = "SELECT * from TelephoneAAA " & _
        " WHERE " & _
        "[Forms]![ListNumbersFm].[EnterRecordFmSub].[Form].TelefID = " & J1 & ";"
    
    SQLStatement = RowSource
    If QueryExists(QryName) = True Then CurrentDb.QueryDefs.Delete (QryName)
    Set qdf = CurrentDb.CreateQueryDef(QryName, SQLStatement)
    
    With Forms!ListNumbersFm!EnterRecordFmSub
        .SourceObject = "Query." & QryName
        .LinkMasterFields = "TelefID"
        .LinkChildFields = "TelefID"
        .Requery
    End With

There is something wrong with the query. It asks for the value of the parameters as if the value J1 does not exist. Help please.
 

namliam

The Mailman - AWF VIP
Local time
Today, 13:19
Joined
Aug 11, 2003
Messages
11,696
Code:
RowSource = "SELECT * from TelephoneAAA " & _
        " WHERE " & _
        "[Forms]![ListNumbersFm].[EnterRecordFmSub].[Form].TelefID = " & J1 & ";"
Your rowsource shouldnt refer to the form in the where clause, instead I believe it should refer to your table...
Code:
RowSource = "SELECT * from TelephoneAAA " & _
        " WHERE " & _
        "TelephoneAAA.TelefID = " & J1 & ";"
 

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
Thanks it worked. But now I have another problem. Although the subform is based on a single form and is designed as such it shows up as a tabular form. It has probably to do with the query. How do I fix that so that the subform displays only one person at a time.
 

Keith Tedbury

Registered User.
Local time
Today, 12:19
Joined
Mar 18, 2013
Messages
26
You probably have checked but is default view set as single form in properties
 

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
You probably have checked but is default view set as single form in properties
Yes I did. There is only one form and that is single. THe listbox acts as single form showing all the records, but the subfom takes the format columnar.
 

pr2-eugin

Super Moderator
Local time
Today, 12:19
Joined
Nov 30, 2011
Messages
8,494
Okay Listbox <> Single Form. I don't even know what the other part about subform you mean by. Read the MSDN article on Forms.
 

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
Okay Listbox <> Single Form. I don't even know what the other part about subform you mean by. Read the MSDN article on Forms.
Well to know what I mean by that you have to read my first post carefully. There is one main form consisting of a list box and a subform. The listbox gets a list of people to display and each time you click on row of the listbox the subform displays the details about that row. This subform does not stick to the single format but goes into columnar format. This is what we are trying to resolve. By the way I have read that article. It does not help.
 

pr2-eugin

Super Moderator
Local time
Today, 12:19
Joined
Nov 30, 2011
Messages
8,494
Well to know what I mean by that you have to read my first post carefully.
Oh really ?!?
Yes I did. There is only one form and that is single. THe listbox acts as single form showing all the records, but the subfom takes the format columnar.
For anyone jumping into this post; considering the fact you had solved the problem in the third post. One could only make that you have not understood what you are doing !
This subform does not stick to the single format but goes into columnar format.
If you really did understand what it meant, then the reply by Keith would have made sense to you !
You probably have checked but is default view set as single form in properties
This is what we are trying to resolve.
Your thread title suggest something else !
By the way I have read that article. It does not help.
Ah ! Bless ! :rolleyes:
 

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
PR2, The title of the thread and the problem we are trying to solve may not coincide. This is because the issue came up immediately after the matter was sorted out and I thought that instead of starting a new thread I should just mention it to the person helping with the first issue. As to the issues about not understanding articles and remarks I find your tone rather aggressive.
Code:
If you really did understand what it meant, then the reply by Keith would have made sense to you !
Are we writing here to help each other or to blame one another for stupidity in a sarcastic tone. When it comes to being aggressive any one of the forum members can become one.
Enough on that we have got work to do.
 

pr2-eugin

Super Moderator
Local time
Today, 12:19
Joined
Nov 30, 2011
Messages
8,494
I find your tone rather aggressive.
Yes ! I accept that I meant to be aggressive there.
Are we writing here to help each other or to blame one another for stupidity in a sarcastic tone. When it comes to being aggressive any one of the forum members can become one.
I did jump in to help, but was rather unhappy of the way it went around.
....read my first post carefully.
....This is what we are trying to resolve
......By the way I have read that article. It does not help.
So :eek:
Enough on that we have got work to do.
Okay, go to the Subform design view, then go to the properties sheet, check the Format tab, under which you will find the Default view property, see if it says anything other than "Single form". If it does not say single form, then there is your problem. Change it to single form.
 

exaccess

Registered User.
Local time
Today, 13:19
Joined
Apr 21, 2013
Messages
287
Code:
Okay, go to the Subform design view, then go to the properties sheet, check the Format tab, under which you will find the Default view property, see if it says anything other than "Single form". If it does not say single form, then there is your problem. Change it to single form.
Now back to business. I went to Main Form design view in format tab the default view is "single". The Subform design view does not have any Default view. The subform is sitting in the main form. The subform does not have any separate default view and yet it adopts a columnar view by itself.
 

Users who are viewing this thread

Top Bottom