New Textbox control won't accept input

[sprtrmp]

I have a form that is based on a query. I am in the process of defining exactly what data the form/query will be used to collect. in other words, I started with some basic data collection, and am getting input from others as to what they want this particular form to do. As these requests are coming in, I am trying to go back and add fields to the existing query, then add controls (textboxes, combo boxes, etc,) to the existing form. none of the controls I add to the form for the newly added fields will allow me to input anything from the keyboard: when I add the fields and set the control source (to a NEWLY added field/control), the control will not accept anything that I attempt to type. Like I said, I have set the control source correctly, and I have checked to make sure that the form is in "data entry mode", and that the controls that I am adding are unlocked and enabled.

when I run the query, the newly added fields show the data I am looking to collect/add from the respective tables, but any related controls I add to the form don't work.

Anyone ever run into anything like this before, or have any idea what is going on?

Thanks for the help

[John_G]

It sounds like the form's underlying query is not updatable. If you run the query on its own, outside the form, can you update any information in it?

Some queries can be made updatable by putting indexes on the tables, but many cannot. What does your query look like?

[sprtrmp]

Trial&Error - Copy.zipI can update the one existing record in the query itself, but I can not add a new record. I am posting the DB, just because it is easier than explaining what is going on.
I am having trouble with the Query called "qryAddAFTP" and the "frmAddAFTP"
any help would be invaluable.

[John_G]

Your form frmAddAFTP, has the query qryAddAFTP as its record source, but the text box on it isn't bound to a field in the query. So even though you can type text into the box, it doesn't go anywhere. Because there are no bound controls on the form, when you click "Submit", you don't get a prompt to save because there is no data TO save.

[sprtrmp]

I know, that is a result of my trying to see what the issue is. IF you look at qryAddAFTP, the first 2 fields (CrewID_PK, and SupportType) where initially added when the query was created. if you set the control source to either of those 2 fields, you can add data to the query/tables.

the last field (FlightTime) was added later, after the query was created, and if you set the control source to that field, the textbox will not accept any input.
I know it is something I am doing wrong, because I just created a completely new DB with essentially the same setup ,and I successfully recreated the same problem. I just can't figure out what it is I am screwing up.

[sprtrmp]

I just proved myself wrong: it is NOT the case that it is because the fields are added after the query is created as I had assumed. I created a new query, with the same 3 fields, and only the first 2 (from the "AFTPDetails" table) are accepting input. the 3rd field (from the "AFTPTime" table) is the only one that is NOT. I think that tells me that I am goofing something up with regards to Primary/Foreign key behavior... but not sure what, exactly

[pbaldy]

Try adding a record directly in the query and note the error you get.

[sprtrmp]

I don't get an error: the query-field ("FlightTime") is not active. I can tab into the field, but it doesn't allow me to enter anything.

[pbaldy]

Are you trying in the query itself? I get an error relating to the join key not being included in the recordset. Adding it allows that field to be edited.

[sprtrmp]

Yessir, trying to add it to the query. I went back and made sure I had the primary key field ("AFTPID_PK") from the "main" table ("AFTPDetails"), which I admit I didn't have in there initially, and I still get nothing, and still can't input anything in the field

[John_G]

First, table tblAFTPDetails has its PK as AFTPID_PK, but it has NO FK back to tblAFTPFlight, which it should have. Your relationships diagram shows a 1-to-many relationship tblAFTPDetails to tblAFTPFlight, which makes no sense at all. One detail can't have many flights.

I'm confused about your table names and the PK field names within them.

tblAFTPFlight has as its PK AFTPFlightDetails_PK which doesn't appear to make sense, at least not by the name. Do you have a good understanding of how PK's and FK's work, and the terminology used?

I think you need to take a look at the tables you have, and the relationships between them, and re-do the relationship diagram.

The problems you are experiencing with your form are due to the fact that the query is not updatable when it comes to adding new records, and it is not updatable because it needs a related record somewhere, and it wants that related record because the relationships are not correct.

Because most of your data is related by one-to-many, your form should reflect that with a form-subform arrangement.

But get your entities (tables) and relationships sorted out first. Decide which is the "Main" table. I think it is tblAFTPFlight, which has many details.

You may have too many tables - all the data in tblAFTPTimes relate to one particular flight (I think), so those fields can be included in tblAFTPFlight.

[pbaldy]

You mentioned frmAddAFTP, which only has an unbound textbox. If I add the other fields from the query, I can add records.

[sprtrmp]

I went back and downloaded the exact same version that I uploaded, added the Primary key, added more textboxes, set the control sources for them, and I STILL can't add records... what did you do to get it to add records, because I am having no luck?!?!?!?
(it is still the "flightTime" field that is messing the whole thing up, as far as I can tell (are you just messing with me?)

[sprtrmp]

John G:
now that I went back and looked at it, I can definitely see what you are saying about the one-to-many relationships between the tables, that is obviously a mistake on my part and those need to be re-visited.
the PK-FK relationships between the tables however make sense to me, at least, but I agree that they should be one-to-one due to the fact that all of the information being input refers to one and only one flight... and I will admit, I could have done a better job at coming up with field names, but I am still in the conceptual stage with all of this.
(this setup has changed 3 or 4 times since I originally started this, and the one-to-many relationships are a hold-over from an earlier attempt at a form-subform setup that I decided not to go with at least for inputting flights, and I may go back to it when it comes to displaying all the flights for a single day, or for a single person, etc)

[John_G]

Oops - sorry sprtrmp - I posted this before I saw your post above. It's a bit repetitious I guess, but does explain what is happening (I hope). Working with relationships can drive you nuts sometimes. (outside of Access, too - but that's another story )

Yes, it is the flight time that is causing the problems. What happens is that as soon you enter data in to that field (flight time), it is trying to create a new record in tblAFTPFlight, which it can't do. The reason is that tblAFTPFlight is related to tblAFTPDetails on field AFTPID, with Enforce Referential Integrity turned on. When a new record is created in tblAFTPFlight, the default value for AFTPID is 0. There is no related record in tblATFPDetails, so the record cannot be added to tblAFTPFlight. And because AFTPID is not included in the query, you can't give it a valid value. As pbaldy has pointed out, if you add AFTPID to the query, and give it a valid value, the new record can be added.