It’s been several days that I am trying to have this work. Came close several times to end up where data could not be entered in the last subform. My problem is in # 3 and my other problem is lack of experience.
The goal is to
1 Select a client in a drop down (works well)
2 Create a record (application) or amend data for that application in a subform (works well)
3 Create a record (asset) or amend data for that asset in a subform. The asset relate to the application selected prior.
Tables:
tbl_customer has [CCAN] as key field, [cust_name]
tbl_dda_app: has [app_app_no] (autonumber) as key field, [CCAN] and other data fields where the user enters information
tbl_dda_ast: has [ast_ast_no] that should equal [app_app_no] in tbl_dda_app to link the applications and asset tables, [ast_ast_no] should hold an asset number from 1 to X starting at 1 for each of the applications and [app_no] should be the merge of these 2 fields to create an unique number ([ast_app_no]&”-“&[ast_ast_no]. The table also contains user fields
Name of the main form is: frm_credit_dda_info
1) ComboBox to select client: cmb_frm_credit_dda
RowSoource: qry_frm_crd_dda_combo_select
SELECT tbl_customer.cust_name, tbl_customer.ccan,
FROM tbl_customer
ORDER BY tbl_customer.cust_name;
Private Sub cmb_frm_credit_dda_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ccan] = '" & Me![cmb_frm_credit_dda].Column(1) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
2) Subform name: frm_crd_dda_sub_dda
Master and Child field = ccan
Continuous form
RecourdSource: qry_frm_crd_dda_sub_view
SELECT tbl_dda_app.app_app_no, tbl_dda_app.ccan, tbl_dda_app.app_date, tbl_dda_app.app_exp_date, tbl_dda_app.app_cr_off, tbl_dda_app.app_status, tbl_dda_app.app_comments
FROM tbl_dda_app;
(Note: field app_app_no is an AutoNumber). This works well and the user can create new application (app) by simply entering data on the autonumber line and a record is created.
3) Subform name: frm_credit_dda_sub_view_asset
a) I need to get a drop down (cmb_frm_credit_asset_view) that list the application ([app_app_no]) listed on the subform above (frm_crd_dda_sub_dda)
b) The subform is to select the records where tbl_dda_ast. ast_app_no = [app_app_no] and be able to enter data
I can have the drop down list the app_app_no but I cannot enter data in the tbl_ast_ast
I would appreciate if you can point me in the right direction ..
Thanks in advance
If I did not say it before, I really appreciate the time and expertise of the users on this forum.
The goal is to
1 Select a client in a drop down (works well)
2 Create a record (application) or amend data for that application in a subform (works well)
3 Create a record (asset) or amend data for that asset in a subform. The asset relate to the application selected prior.
Tables:
tbl_customer has [CCAN] as key field, [cust_name]
tbl_dda_app: has [app_app_no] (autonumber) as key field, [CCAN] and other data fields where the user enters information
tbl_dda_ast: has [ast_ast_no] that should equal [app_app_no] in tbl_dda_app to link the applications and asset tables, [ast_ast_no] should hold an asset number from 1 to X starting at 1 for each of the applications and [app_no] should be the merge of these 2 fields to create an unique number ([ast_app_no]&”-“&[ast_ast_no]. The table also contains user fields
Name of the main form is: frm_credit_dda_info
1) ComboBox to select client: cmb_frm_credit_dda
RowSoource: qry_frm_crd_dda_combo_select
SELECT tbl_customer.cust_name, tbl_customer.ccan,
FROM tbl_customer
ORDER BY tbl_customer.cust_name;
Private Sub cmb_frm_credit_dda_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ccan] = '" & Me![cmb_frm_credit_dda].Column(1) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
2) Subform name: frm_crd_dda_sub_dda
Master and Child field = ccan
Continuous form
RecourdSource: qry_frm_crd_dda_sub_view
SELECT tbl_dda_app.app_app_no, tbl_dda_app.ccan, tbl_dda_app.app_date, tbl_dda_app.app_exp_date, tbl_dda_app.app_cr_off, tbl_dda_app.app_status, tbl_dda_app.app_comments
FROM tbl_dda_app;
(Note: field app_app_no is an AutoNumber). This works well and the user can create new application (app) by simply entering data on the autonumber line and a record is created.
3) Subform name: frm_credit_dda_sub_view_asset
a) I need to get a drop down (cmb_frm_credit_asset_view) that list the application ([app_app_no]) listed on the subform above (frm_crd_dda_sub_dda)
b) The subform is to select the records where tbl_dda_ast. ast_app_no = [app_app_no] and be able to enter data
I can have the drop down list the app_app_no but I cannot enter data in the tbl_ast_ast
I would appreciate if you can point me in the right direction ..
Thanks in advance
If I did not say it before, I really appreciate the time and expertise of the users on this forum.