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

Subform looking for other data tables to write the record...

kcdoell
100+
P: 230
Hello:

I have four tables:

tblDivision
tblWorking_Region
tblCredit_Region
tblForecast (This is the main data entry table for all forecasting records)

tblDivision has the following fields:

DivisionID > AutoNumber > Number automatically assigned to new division
DivisionName > Text > Name of the Division

tblWorking_Region has the following fields:

WrkRegID > AutoNumber> Number automatically assigned to new working region
WrkRegionName > Text > Name of the Working Region

tblCredit_Region has the following fields:
CreditRegID > AutoNumber> Number automatically assigned to new credit region.
CreditRegion > Text > Name of the credit region

tblForecast has the following fields:

ForcastinputID > AutoNumber> Number automatically assigned to new forecasting record
Product > Text
NWP > Currency
GWP > Currency
Other data entry fields for the end user…..
DivisionID > Number > Same as DivisionID in tblDivision table.
WrkRegID > Number > Same as WrkRegID in tblWorking_Region table.
CreditRegID > Number > Same as CreditRegID in tblCredit_Region table.


A Division can have multiple working regions. A working region can have multiple credit regions and a Credit region can have multiple forecasting records. So what I did was create one to many relationships between the Forecasting table and the other tables. This worked fine but now I am having a problem with my data entry screen. Via another form I have the user select the Division, Working Region and Credit Region then click on a command button which brings them to my “Dataentry forecasting”form. My Dataentry forecasting form has a subform that is tied to the forecasting table. My problem is that every time I try to input a record the forecast table is asking me for the Division & Working region. It does not ask me for the Credit Region because the wizard somehow tied it to the forecasting table. I understand why it is asking for the Division and Working region but I want to create a dataentry form in which the user does not have to continually indicate The Division and the Working region. Ultimately I would like the form to be dynamic so that the enduser could pick any combination, on the dataentry screen itself, and view, edit add to the forecasting records as needed.

Does anybody have any ideas? I have looked on the help menu and an Access 2003 book I have but can not find what I am specifically trying to accomplish.

Any help would be greatly appreciated.

Thanks,

Keith.
Feb 4 '08 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 344
Hello:

I have four tables:

tblDivision
tblWorking_Region
tblCredit_Region
tblForecast (This is the main data entry table for all forecasting records)

tblDivision has the following fields:

DivisionID > AutoNumber > Number automatically assigned to new division
DivisionName > Text > Name of the Division

tblWorking_Region has the following fields:

WrkRegID > AutoNumber> Number automatically assigned to new working region
WrkRegionName > Text > Name of the Working Region

tblCredit_Region has the following fields:
CreditRegID > AutoNumber> Number automatically assigned to new credit region.
CreditRegion > Text > Name of the credit region

tblForecast has the following fields:

ForcastinputID > AutoNumber> Number automatically assigned to new forecasting record
Product > Text
NWP > Currency
GWP > Currency
Other data entry fields for the end user…..
DivisionID > Number > Same as DivisionID in tblDivision table.
WrkRegID > Number > Same as WrkRegID in tblWorking_Region table.
CreditRegID > Number > Same as CreditRegID in tblCredit_Region table.


A Division can have multiple working regions. A working region can have multiple credit regions and a Credit region can have multiple forecasting records. So what I did was create one to many relationships between the Forecasting table and the other tables. This worked fine but now I am having a problem with my data entry screen. Via another form I have the user select the Division, Working Region and Credit Region then click on a command button which brings them to my “Dataentry forecasting”form. My Dataentry forecasting form has a subform that is tied to the forecasting table. My problem is that every time I try to input a record the forecast table is asking me for the Division & Working region. It does not ask me for the Credit Region because the wizard somehow tied it to the forecasting table. I understand why it is asking for the Division and Working region but I want to create a dataentry form in which the user does not have to continually indicate The Division and the Working region. Ultimately I would like the form to be dynamic so that the enduser could pick any combination, on the dataentry screen itself, and view, edit add to the forecasting records as needed.

Does anybody have any ideas? I have looked on the help menu and an Access 2003 book I have but can not find what I am specifically trying to accomplish.

Any help would be greatly appreciated.

Thanks,

Keith.
Assume that your first form is called 'frmSelection' then what you can do is go to the 'before update' event on the sub form and type the following code.
Expand|Select|Wrap|Line Numbers
  1. if me.newrecord then
  2.    DivisionID=forms!frmSelection!DivisionID
  3.    WrkRegID =forms!frmSelection!WrkRegID 
  4.    CreditRegID=forms!frmSelection!CreditRegID
  5. End if
  6.  
This would set the values for new records.

Alternativly, you could have 3 combo boxes in the footer of you main form, cboDivisionSelect etc and filter the sub form as you change these values, and again use the code above, but without the forms!frmSelection, for new records
Feb 5 '08 #2

kcdoell
100+
P: 230
Hello:

I applied the code adapted to my form and it works great. It has also given me other ideas as well.

Thanks a lot,

:-)

Keith.
Feb 5 '08 #3

Post your reply

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