Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2006
    Posts
    126

    Unanswered: Query won't accept first data

    I am doing a new database (based off an existing one that works fine). The database has a table with info on each garden, and a table with information on plants. There is a linking table that contains the link between these two, showing the quantity of plants for that garden (plus a lot of other info).

    The form that for that linking data wouldn't show the detail section controls. Following one of Allen Browne's postings, I traced it to the underlying query. Opening the query in datasheet view, there is no blank record shown. Following another of Browne's postings, I've checked the following in the query: There is no "group by"; it is a select query, not crosstab or union; there is no aggregate function in the select clause; no distinct predicate; no subquery; recordset = dynaset; not based on another query; there are no calculated fields; and all tables including the underlying table open properly in their datasheet view with a blank record shown.

    I'm stuck on what to check next to make this linking query accept data.

    The SQL version of the query is:
    SELECT tGardenData.GardenID, tGardenData.GardenName, tGardenPlantLink.ID, tGardenPlantLink.PlanYear, tGardenPlantLink.PlantID, tGardenPlantLink.QtyPlanned, tGardenPlantLink.LocationInGarden, tPlantData.ID, tPlantData.IDSuffix, tPlantData.BotanicName, tPlantData.CommonName, tPlantData.Perenial, tPlantData.SupplierID, tSupplierData.SupplierName, tPlantData.[Qty/Pack], tPlantData.PackType, tPlantData.PriceEach, *
    FROM ((tGardenData INNER JOIN tGardenPlantLink ON tGardenData.GardenID = tGardenPlantLink.GardenID) INNER JOIN tPlantData ON tGardenPlantLink.PlantID = tPlantData.PlantID) INNER JOIN tSupplierData ON tPlantData.SupplierID = tSupplierData.SupplierID
    ORDER BY tGardenData.GardenName;
    PGT

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    What is the forms name and do you have a subform? What is in the detail section? Can you post screenshots?

  3. #3
    Join Date
    Aug 2006
    Posts
    126
    Thanks for reply, just got called out of town 'til Thurs.

    We've already traced the problem, and it isn't in the form, it's in the query's (I've now got two problems). Opening the query in dataview doesn't show a blank record ready to fill in. So the form sees no data and doesn't even show the fields (a known quirk of Access). The question is what is wrong with the query? All of the underlying tables open properly with blank records.

    However to answer your questions, the form names are fPlantEval and subfPlantOrder. As you can see one is a primary form and the other a subform on fGardenData. Out of curiosity what would a formname have to do with the absense of data? The detail sections have the fields of the query.
    PGT

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    I am trying to re-create your scenario here with your table names,fields,qry,form,etc...

    I can tell you that it is a incorrect link from one or more of your tables is my hunch.
    The screenshots I asked for are AGAIN to help diagnose your problem. There is a reason why so many people have looked at your post and not responded.

  5. #5
    Join Date
    Aug 2006
    Posts
    126
    Thanks, finally back from out-of-town. Here are the screenshots you asked for. First is one of the two forms in both design and data sheet views; just to show that the detail section is not populated even with blank fields because the underlying query produces no records. Next are screenshots of the designview and datasheetview of the two queries to show that the query isn't producing a blank record.
    Pete

    Click image for larger version. 

Name:	PlantPerfFormDesignView.PNG 
Views:	8 
Size:	20.4 KB 
ID:	14798

    Click image for larger version. 

Name:	PlantPerfFormview.PNG 
Views:	7 
Size:	5.0 KB 
ID:	14799

    Click image for larger version. 

Name:	qOrderLinkDataview.PNG 
Views:	3 
Size:	4.1 KB 
ID:	14800

    Click image for larger version. 

Name:	qOrderLinkDesign.PNG 
Views:	8 
Size:	21.1 KB 
ID:	14801

    Click image for larger version. 

Name:	qPlantPerfDesign.PNG 
Views:	7 
Size:	16.4 KB 
ID:	14802

    Click image for larger version. 

Name:	qPlantPerfDataview.PNG 
Views:	3 
Size:	3.8 KB 
ID:	14803
    PGT

  6. #6
    Join Date
    Nov 2011
    Posts
    413
    This is just too much trouble for me to go to to try and help you.Can you upload a sample db with no confidential data, compiled and zipped? Explain in DETAIL what form,query is giving you the problem, where the problem is and under what circumstances it occurs?

  7. #7
    Join Date
    Aug 2006
    Posts
    126
    Burrina,
    As usual your help is appreciated. Before I upload the mdb and waste your time I want to do some more research and try some other approaches. This worked in the database I developed several years ago and copied from, and it has to be something that I'm just not seeing. As I said, a Google search turned up several hundred similar problems so the solution has to be out there.
    Pete
    PGT

  8. #8
    Join Date
    Nov 2011
    Posts
    413
    NP. As I said, it is a relationship or main form/ subform link problem.

  9. #9
    Join Date
    Aug 2006
    Posts
    126
    Just to close out this thread. Burrina was correct, it was a bad relationship in the connection between two of the tables when the query was being built. The data had an autonumber field with a name similar to a field name in the table. The link had gotten connected to the field name instead of the autonumber field.
    PGT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •