By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,550 Members | 2,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,550 IT Pros & Developers. It's quick & easy.

ID not getting stored?

AccessIdiot
100+
P: 493
Weird.

Main Form populated by General table (with PK General_ID)

Form1 populated by Table1
Form2 populated by Table2
Form3 populated by Table3
etc.

Tables1-3 have General_ID as FK in 1:N relationship. That is, the same record in the General table can be linked to many records in any of the other tables.

Main Form has combobox that holds Form1, Form2, Form3 etc. and go button, which launches appropriate form.

All working great.

Problem is that when Form 1, 2, or 3 opens the General_ID is not carrying over, so there is nothing to tie Table1 to the appropriate record in the General table.

What am I doing wrong?
Apr 24 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
...Tables1-3 have General_ID as FK in 1:N relationship. That is, the same record in the General table can be linked to many records in any of the other tables.

Main Form has combobox that holds Form1, Form2, Form3 etc. and go button, which launches appropriate form.
...
Problem is that when Form 1, 2, or 3 opens the General_ID is not carrying over, so there is nothing to tie Table1 to the appropriate record in the General table.
Hi. The relationships will not themselves create the foreign key value for you if you open forms 1 to 3 separately in code from your main form. I think you may be confusing the opening of separate forms with what happens in a mainform containing a related subform, where setting the parent-child field links will indeed set the related field for you on new record creation.

To resolve this I would suggest that after you open the relevant form, you should set the default value property for your general_ID field in the newly-opened form to be that of the same field in your main form.

Although you could in code set the value of the general_ID field itself, this would unintentionally create a new record on opening the form, and this new record would have to be abandoned if the user closed the form without intending to add a record.

The defaultvalue property of a control is a string value (regardless of the type of the underlying field itself).

In your code add the following for each form (the DoCmd.OpenForm is shown for clarity, and Form 1 is used as a specific example - do the same for each or use variables for the form names):
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "Form 1"
  2. Forms![Form 1]![General_ID].DefaultValue = "'" & Me.[General_ID] & "'"
You should also set the General_ID field properties on forms 1 to 3 to Enabled = No and Locked = Yes to prevent users accidentally changing the foreign key value. You will need to have the general_id field present on your forms to be able to refer to it in code from the main form, but if you don't want it to be seen by your users at all set its visible property to No.

-Stewart

ps to open the form and display only the records matching your main form you also need to apply a filter to the form when you open it:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Openform "Form 1", acNormal,,"[General ID]= '" & Me![General ID] & "'"
The filter line above treats General_ID is a string - if it is a numeric you will not need the single quotes that are shown.
Apr 25 '08 #2

AccessIdiot
100+
P: 493
Ack! I can't believe I forgot about that part - I knew it was something simple, I just haven't done this in awhile.

Thanks so much for the detailed help!
Apr 28 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
Another technique you can use to pass the data across is the OpenArgs value of the form.

This would simply replace the called form needing to access the calling form directly, thereby adding flexibility. The called form would not need to assume that the calling form was any particular form but could work from a number of forms.

Rarely required I know, but fairly simple to build in anyway and doesn't hurt any.
Apr 30 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft Access / VBA Questions on Bytes