How can I add new records to all of the tables that are bound to that query? I'm having trouble with this, cna any of you help? I thought of creating an append query, to append the new records to all of the tables, however that seems to mindless. . .does anyone have any other suggestions? Updating information in existing records is not the problem, however adding new ones is.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
In a form bound to a query(multiple tables). . .
- Thread starter SCHNYDES
- Start date
you should be able to do so by simply running your query (form) and entering the data in the appropriate fields.
this can get complicated.
in order to do this the right way you must have a relational database structure (since you are working with multiple tables).
your tables need primary keys (to uniquely identify each record) and foreign_keys (to identify what Primary Key the record pertains to from the other table).
after the tables are set up properly you need to do the following.
within the Tools - Relationships section you need to link your tables. By linking, I mean link your Primary Keys to your Foreign Keys.
Check the Enforce Referential Integrity checkbox and check the Cascading Update Related Fields checkbox.
Once you've joined your tables, make sure all primary keys and foreign keys are within the query along with other fields.
Now when you run the query you should be able to enter the data straight into the query(form) and it will automatically save in all related tables.
sorry to be so broad.
i hope this helps you.
this can get complicated.
in order to do this the right way you must have a relational database structure (since you are working with multiple tables).
your tables need primary keys (to uniquely identify each record) and foreign_keys (to identify what Primary Key the record pertains to from the other table).
after the tables are set up properly you need to do the following.
within the Tools - Relationships section you need to link your tables. By linking, I mean link your Primary Keys to your Foreign Keys.
Check the Enforce Referential Integrity checkbox and check the Cascading Update Related Fields checkbox.
Once you've joined your tables, make sure all primary keys and foreign keys are within the query along with other fields.
Now when you run the query you should be able to enter the data straight into the query(form) and it will automatically save in all related tables.
sorry to be so broad.
i hope this helps you.
- Thread starter
- #3
Actually. . .that's all in there. I'm far from the Novice Access builder, however VB Code is truly a big weekness for me. Anyway, I have the referential integrity, the primary keys are linked to each table and the query pulls the information perfectly. . .that is, for the data that is already there. However is there a way to change primary key data. Like, when I update my form, with a new primary key, can all of the tables bound to that query, update themselves with the new primary key that I entered?
SCHNYDES
I am puzzled -- updating your primary key??
Try this "from the trenches"...
Often when designing a financial application, the programmer will use the G/L account as the primary key. Occasionally, a G/L account number has to be changed. This results in writing a routine (before 4GL and relational databases were around) to go through all the transactions, balance files, etc and renumber the account number. If several G/L accounts were being renumbered, this process would take a long time. Very messy.
Although using the G/L account number as the primary key seems to make sense, my belief is that if there is a chance the primary key needs to be changed don't use it as a primary key. (Rick's rule???)
It took me two years to convince the senior programmer to use a sequential index number (or autonumber) as the primary key, and use the G/L account number as a desciption field. Worked like a charm, and renumbering accounts became a piece of cake. Change the account number in the G/L master file and all the transactions depicted the change.
Does this scenario fit??
Next, you seem well versed with Access. But I will ask the question anyway. If you are using the query design view to create and run your queries, have you changed the query to an update query? Depends on the verson of Access, but if you click on query header / title bar in design view -- select "query type", and switch to update. The criteria will change a bit. Fill in an examples for "update to" and "criteria". Now switch to the SQL view. Copy the SQL statement into the clip board.
Now go to your form. Add a command button. On the "on click" event property for the command button, go to the VB code editor. Paste your update query. (You may get syntax errors -- don't worry.)
Now look at the update query. Basically, we want to replace the "update to" and "criteria" examples with variables.
Here is where is gets a little more tricky. Quotes and variables. For simplicity, I am going to assign the query to a string.
I am assuming...
me.fld_pk_name
is the primary key for the form. You have changed the primary key from the old view to the new view, but not updated it. We are going to take advantage of the .old property used by Access to store the old value of course. (Every wonder how Access could "undo" a change by hitting the escape key?)
dim strSQL as string
strSQL = "select your_table set your_table.fld_pk_name = " & me.fld_pk_name & " where your.fld_pk_name = " & me.fld_pk_name.old
Note...
How the text of the select statemnt is within quotes, and the the field name variables are variable names are concatinated to the string using "&".
Note...
Spaces after "=" sign, and before the "where". Otherwise you may get a syntax error when executing the code.
Note...
If your primary key is a text string, we need to put quotes around the vairable. There are variations of how to do this. This is my preferred way...
dim strSQL as string, strQ as string
strQ = chr$(34)
strSQL = "select ... = " & strQ & me.fld_pk_name & strQ & " where ... = " & strQ & me.fld_pk_name.old & strQ
'Now execute the query, add this after defining the SQL string
docmd.runsql strSQL
If you have more than one query to run, repeat the process of creating strSQL and running the command.
Lastly, back up your database before doing this. It sounds like you are updating the primary key, and need to update the associate foreign keys. If this process is messed up, you will loose the relationship with between the tables for this record.
Hey, did you just write some vb code? See it is not so bad.
As an alternative solution,
I suggest you consider using an autonumber for the primary key. Piece of cake -- Access will renumber all records after you reassign the primary key. Then create a new foreign key in your other tables. Run an update SQL statement to update the new foreign key in the related tables. Since you still have the old foreign key still in the table, this should be fairly easy too. (And you have a back out plan). Modify tables and reports as required to use th enew foreign key. If confident with the results, you can then delete old foreign key.
Richard
I am puzzled -- updating your primary key??
Try this "from the trenches"...
Often when designing a financial application, the programmer will use the G/L account as the primary key. Occasionally, a G/L account number has to be changed. This results in writing a routine (before 4GL and relational databases were around) to go through all the transactions, balance files, etc and renumber the account number. If several G/L accounts were being renumbered, this process would take a long time. Very messy.
Although using the G/L account number as the primary key seems to make sense, my belief is that if there is a chance the primary key needs to be changed don't use it as a primary key. (Rick's rule???)
It took me two years to convince the senior programmer to use a sequential index number (or autonumber) as the primary key, and use the G/L account number as a desciption field. Worked like a charm, and renumbering accounts became a piece of cake. Change the account number in the G/L master file and all the transactions depicted the change.
Does this scenario fit??
Next, you seem well versed with Access. But I will ask the question anyway. If you are using the query design view to create and run your queries, have you changed the query to an update query? Depends on the verson of Access, but if you click on query header / title bar in design view -- select "query type", and switch to update. The criteria will change a bit. Fill in an examples for "update to" and "criteria". Now switch to the SQL view. Copy the SQL statement into the clip board.
Now go to your form. Add a command button. On the "on click" event property for the command button, go to the VB code editor. Paste your update query. (You may get syntax errors -- don't worry.)
Now look at the update query. Basically, we want to replace the "update to" and "criteria" examples with variables.
Here is where is gets a little more tricky. Quotes and variables. For simplicity, I am going to assign the query to a string.
I am assuming...
me.fld_pk_name
is the primary key for the form. You have changed the primary key from the old view to the new view, but not updated it. We are going to take advantage of the .old property used by Access to store the old value of course. (Every wonder how Access could "undo" a change by hitting the escape key?)
dim strSQL as string
strSQL = "select your_table set your_table.fld_pk_name = " & me.fld_pk_name & " where your.fld_pk_name = " & me.fld_pk_name.old
Note...
How the text of the select statemnt is within quotes, and the the field name variables are variable names are concatinated to the string using "&".
Note...
Spaces after "=" sign, and before the "where". Otherwise you may get a syntax error when executing the code.
Note...
If your primary key is a text string, we need to put quotes around the vairable. There are variations of how to do this. This is my preferred way...
dim strSQL as string, strQ as string
strQ = chr$(34)
strSQL = "select ... = " & strQ & me.fld_pk_name & strQ & " where ... = " & strQ & me.fld_pk_name.old & strQ
'Now execute the query, add this after defining the SQL string
docmd.runsql strSQL
If you have more than one query to run, repeat the process of creating strSQL and running the command.
Lastly, back up your database before doing this. It sounds like you are updating the primary key, and need to update the associate foreign keys. If this process is messed up, you will loose the relationship with between the tables for this record.
Hey, did you just write some vb code? See it is not so bad.
As an alternative solution,
I suggest you consider using an autonumber for the primary key. Piece of cake -- Access will renumber all records after you reassign the primary key. Then create a new foreign key in your other tables. Run an update SQL statement to update the new foreign key in the related tables. Since you still have the old foreign key still in the table, this should be fairly easy too. (And you have a back out plan). Modify tables and reports as required to use th enew foreign key. If confident with the results, you can then delete old foreign key.
Richard
- Status
Similar threads
- Locked
- Question
- Locked
- Question