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;
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.
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.
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.
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?
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.
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.