Subforms: select and create/edit data

[DajTwo]

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.

 

[TheAceMan1]

How are ya DajTwo . . .

Just wanted to point out the following:

[blue]tbl_dda_ast: has [red][ast_ast_no] that should equal [app_app_no][/red] in tbl_dda_app to link the applications and asset tables, [red][ast_ast_no] should hold an asset number from 1 to X[/red] . . .[/blue]

There are other ambiguous statments in your post. Apparently your [blue]naming convention[/blue] is causing even you ... trouble!

Out of curiosity is [blue]frm_credit_dda_sub_view_asset[/blue] setup for [blue]read only?[/blue] Also ... have a look at the [blue]Add New Button[/blue] of the access navigation buttons at the bottom of the subform. If its disabled then the recordsource of the subform is [blue]read only![/blue]

It may be best for you to post the db some place like 4Shared.com ([green]its free![/green]). If you decide to upload and your using 2007, save the db as an earlier version first.

Good Luck!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Hi Aceman1.

Been a while. Yes I agree that my naming convention got out of hand..

The actual quote is correct but I see with the naming convention it may cause issues.

I cannot access 4free from work, so I am sending the file home, then will upload and post when done.

Thanks a lot


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Again: It may be best for you to post the db some place like 4Shared.com (its free!). If you decide to upload and your using 2007, save the db as an earlier version first ([blue]A2K would be perfect![/blue]).

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

I am having problems uploading the file as firewalls at the office prevent me to do so AND I have a mac at home and the mdb copy on the CD is not readable.

Please bare with me.. I will try again.

Thanks

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

I have uploaded the database (2003) in zip format as you recommended

http://www.4shared.com/file/104507485/4571ef11/WCM-F-test.html

Any help is as always very appreciated



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Downloaded just fine but there's a format (version) problem. [blue]Try converting[/blue] a copy of the db to a prior Version. From the db window its [blue]Tools - Database - Utilities - Convert Database[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Will have to do this tomorrow.. Thanks for looking at it..

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

Here is the link to the file saved as 2000

http://www.4shared.com/file/104589109/527ad72b/WCM-F-2000.html

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

[green]Success![/green]

I don't have much time tonight so look for me tomorrow evening ... possibly earlier.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Take your time.. Again, I apologize for the naming convention but this is a spall part of a larger database and I get lost if the names do not group all the relevalnt queries / forms together..

Thanks

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

I've given it by best shot and can't get thru the naming [blue]convention[/blue] as well. I always loose the logical path I was on. Its alot of work just to read whats going on.

There are no [blue]table relationships[/blue] in the relationships window, and I see relations in queries are used instead. Although relationships can be done this way, its unconventional and [blue]can easily cause problems[/blue] ... just by adding a single table in the query grid so you can make that relationship.

Between the two subjects above, I see you getting deeper and deeper into trouble ... and all this you say comes from a larger db! You have alot of work and long hours ahead if you continue as is!

My best advice is stop! ... don't go any further until you take care of naming convention and relationships. Do go over the link below. Its the best I can leave you at this point.

Fundamentals of Relational Database Design

SPEED Ferret

[blue]Good Luck![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Thank you for the information and the time you spend looking at this 'mess'. I will take care of this.



If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[DajTwo]

Aceman1

Thank you again.. I re-did this portion of the database and it looks a lot 'simpler'.

As such, I will re-post my problem in another thread.

Thanks again

Daj

If I did not say it before, I really appreciate the time and expertise of the users on this forum.