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

[kcdoell]

Hello:

I have four tables:

tblDivision
tblWorking_Regi on
tblCredit_Regio n
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_Regi on has the following fields:

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

tblCredit_Regio n 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_Regi on table.
CreditRegID > Number > Same as CreditRegID in tblCredit_Regio n 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”for m. 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.

[Lysander]

Hello:

I have four tables:

tblDivision
tblWorking_Regi on
tblCredit_Regio n
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_Regi on has the following fields:

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

tblCredit_Regio n 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_Regi on table.
CreditRegID > Number > Same as CreditRegID in tblCredit_Regio n 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”for m. 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.

if me.newrecord then
   DivisionID=forms!frmSelection!DivisionID
   WrkRegID =forms!frmSelection!WrkRegID 
   CreditRegID=forms!frmSelection!CreditRegID
End if
 

This would set the values for new records.

Alternativly, you could have 3 combo boxes in the footer of you main form, cboDivisionSele ct etc and filter the sub form as you change these values, and again use the code above, but without the forms!frmSelect ion, for new records

[kcdoell]

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.