I have 3 tables with one-to-one relationships with the main table. These are separate tables because the 3 tables are downloaded periodically from online, so it just makes sense to not have 1 big table--especially since the tables would just get too darn big (wide with fields) for comfort. They're already pretty big. (Besides, each of the three tables have records that aren't in the main table.)
I was having trouble getting a form to work properly with several tables feeding it information, but finally, the first two problems I had are now fixed, but I just discovered that I am unable to enter new information into the form!
This really defeats the purpose of having a form in the first place. The whole idea was to give the ladies in another department an easy way to view and update information in the database I use, rather than having to use the table to update, as I prefer.
What can I do? When went wrong? They really only need to be able to update 2 or 3 fields in the main table, but they need to be able to view much more than that.
Anyway, if there are Access experts--or even novices--around who can help me, it would be much appreciated.
posted
The first thing that came to mind is the "Allow Additions" properties for the table in the form that is being updated. Or perhaps a primary key or a required field is not being updated as it should. That's about all I can think of.
Are you able to use the form to make changes to existing data?
Posts: 1256 | Registered: May 2005
| IP: Logged |
posted
camus: I went into the form properties and made sure everything was sent to yes regarding making changes, additions, deletions, etc. It didn't work. I don't know why.
Tom: I really only use SQL when I have to. I don't write it. I have been able to use it when someone has shown me a query and edit it according to what I need...but I don't "read" it, and I don't really understand it...
But there are some things I know, like making a Union Query with SQL.
Anyway... let me see if I can find SQL that the form might have written for itself... Nope. I can't find anything for the entire form.
I clicked the Relationships button to set up the relationships for the tables. I believe they are one-to-one relationships, since the fields I'm using to connect the tables are all primary keys. The join types are include all records from "Physical Holdings," my main table, and only the fields from the other tables when they are equal.
Does that answer your question?
Posts: 2880 | Registered: Jun 2004
| IP: Logged |
posted
The problem is that certain queries aren't editable.
When you say you can't add information, are you trying to add records or to change information for records that already exist?
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
Is your form built on a underlying query? If so, you can switch to SQL view in the query and copy the text shown into this thread.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
No. It's built on tables... I went to the Create form Wizard and selected fields from 3 tables. (Not 4, as I originally stated.) The wizard even had a note saying this was allowed. I had already set up the relationships on the table, too.
This form should show me all of the records for my main table, and only those records from the other tables when there is corresponding information (when the "Title Numbers" (primary keys) match up--which is how the tables are related.)
Posts: 2880 | Registered: Jun 2004
| IP: Logged |
posted
The form actually displays the right information. The relationships work fine, and it's all dandy...
The problem is I can't change anything on the form. I want some other people to use this form for data entry in two fields.
Posts: 2880 | Registered: Jun 2004
| IP: Logged |
posted
I think you need to have referential integrity turned on for that dataset to be editable. Which means at least one table must have a record for each record in any of the other tables.
Are they allowed to edit the information in all three tables? Or only the main one?
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
I only want them to be able to edit the information in one of the tables, but I'm willing to expand the permissions if I have to. They won't mess it up, I'm sure.
I hope I don't have to make a table with a record for each record in any of the other tables. That would be very difficult to update and maintain.
Each table has information from a different source--our physical holdings, and one table for each online source where we subscribe to many journals. One table with all of that would be... massive.
Okay.. I really should wait until tomorrow for the rest of this....
Posts: 2880 | Registered: Jun 2004
| IP: Logged |
posted
If the two fields to be edited are in the same table, then you can use a subform to handle this.
Base the main form only on the table to be edited. Add the fields to be edited to the form, and add fields that you will lock and disable to display the information you don't want edited but do want to appear.
Then, create another form based on a query joining all the other tables you want to display data from. Make sure you include the primary key from one table in the query, even if you don't want to display it. Simply lay out all the fields as you want them displayed. Save it and close it.
Now, drag the second form from the database container onto an empty area in the first form. If the primary keys are named the same in each table, you should be done. If not, you need to fill in the child and master fields with the primary key names for each form.
Then, the two fields will be editable. The subform will not be.
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
If I'm reading this correctly, you're really just assembling what should be (relationally) one table out of several disparate tables, which are divided pretty much for aesthetic reasons (and a bit of usability, since access is being used directly for editing). And the 'real' table is off on a server somewhere.
If so, I'd just make one large table from the 'real' table and use that to back the form. It should even be pretty simple to synchronize changes between that table and the three partial tables if you still need them.
Posts: 15770 | Registered: Dec 2001
| IP: Logged |
posted
Dagonee: That is AWESOME. I wish I could try it right now, but I can't. Have to go home. Thanks so much, and I'll have fun working on it tomorrow.
posted
Dag's instructions are spot-on. In fact, if what you're saying is correct -- that changes only need to be made to the data in one of the three tables -- you're dealing with a classic example of when to use a subform. Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
Can't check this out yet, as I'm home, but I'm pretty convinced it will work.
When I read about subforms in the Help, I got the impression that they are only useful for one-to-many relationships, so I didn't try. Well, actually, I did try once--it said that if I did my relationships right, the wizard would automatically ask me if I wanted subforms--but it didn't, so I went on to the next thing. Now with Dag's instructions, I can make my own subform. Very cool.
Fugu: There is no "real" table on a server. In fact, it's a "local" database. I download the data for the tables from the web sites of the different places that supply our access to the online journals. They come in all different formats, and require that I make them suitable for my table. My biggest table, the one that serves as the Main table for this purpose, is updated manually as information changes in our physical print subscriptions.
Posts: 2880 | Registered: Jun 2004
| IP: Logged |
posted
Subforms are dead useful. Last time I programmed in Access, it didn't have usable tab controls. With toggle button groups and subforms and just a little code, we could make our own tabbed forms.
BTW, if I were the one designing the data structure for this, I'd have one table for Access-edited data, and one table for data imported from other systems. I'd set referential integrity on with the master being the editable table.
But, it's not worth changing if this all you need. Might be worth looking into it if you need a full blown front end for it, though.
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
Most of this thread was absolute Greek to me, but one thing gave me a "small world!" feeling.
quote:Originally posted by Farmgirl: The VERY best place to ask Microsoft Access questions: Woody's Lounge
Woody was my dad's co-author. (Not that Woody actually has anything to do with that site any longer, but he started it.) And a nice guy. Posts: 32919 | Registered: Mar 2003
| IP: Logged |
posted
It's not quite Greek to me, it's more being reminded of a language I used to speak but don't know anymore. I made a decent living as a sales analyst and did a fair amount of Access programming in that position once upon a time ago.
But now, having been home for almost seven years, I couldn't do any of it now, I'm sure.
Posts: 14428 | Registered: Aug 2001
| IP: Logged |
I've been hooked on Woody's publications and his forum since I back when I was teaching Microsoft Office. Just a wealth of knowledge, and I still get their newsletters. I have referred many, many people to that site and other related sites.
I was originally pointed that direction by my cousin (who is a VB programmer in NYC) who knew Woody Leonhard personally, as well.
quote:But now, having been home for almost seven years, I couldn't do any of it now, I'm sure.
The methods have changed rather significantly in seven years. But they've gotten EASIER, so you'd probably be surprised.
Posts: 37449 | Registered: May 1999
| IP: Logged |
posted
I made the main form and subform, but when I bring them together, the information in the subform is not changing according to the information that's in the main form. The main form's primary key is called "Title Number," and that field is on the main form. The subform is based off of a query, where the multiple tables are linked together by the "Title Number" field, so that field is in the subform, too.
Since it doesn't work, what's this master/child thing you were talking about? Where can I set that up. I have attempted to find it in the help, but am having no luck in learning how to set it up. (The only thing I find says that the wizard will do it automatically.)
quote:Since it doesn't work, what's this master/child thing you were talking about? Where can I set that up. I have attempted to find it in the help, but am having no luck in learning how to set it up. (The only thing I find says that the wizard will do it automatically.)
They're called Link Child Fields and Link Master Fields under the Data tab of properties. It's worth looking at the properties to see how the wizard filled it in.
Posts: 26071 | Registered: Oct 2003
| IP: Logged |
posted
Wow. I have a meeting with our Systems department on Tuesday to discuss my database and where to put it, how to set up permissions, proper documentation, when to do backups, etc. The sysadmin even suggested that I could do a demonstration on how it works to the Library.
I feel very proud of what I have achieved in building this, and I'm very thankful to the help I've received online--here and on an access help forum.
It feels nice. But now I'm thinking about how I can add some more pizzazz if I'm going to have to present it.. Posts: 2880 | Registered: Jun 2004
| IP: Logged |
Printer-friendly view of this topic
![[Smile]](smile.gif)
