Using a form to display new and existing info from 2 different tables 2

[cfvcf]

I'm sorry this is rather wordy!!
I have a DB with forms that are operating in the following sequence:
Menu form:
Button to add organization - this links to form (Org_and_Accts) that displays input fields for the org (name, addr, etc), code is:
DoCmd.OpenForm "frm_Organizations_and_Accounts", acNormal, "", "", acAdd, acNormal
Works great.

Button to display existing organizations - this opens a dropdown box to select the org, once clicked it opens the same form above (Orgs_and_Accts).
DoCmd.OpenForm "frm_Organizations_and_Accounts", acNormal, "", _
"[tbl_Orgs]![OrgName]=[Forms]![frm_New_Menu]![combrgs_and_Accounts]", , acNormal
Works great.

Org_and_Accts form:
button to go back to menu
button to add accts

If the form as opened for an existing organization, it shows the org info and the subform for it’s accts – which has the fields about the org, plus has a subform that lists all the accounts for that org. If I click on any of the fields in the subform, it opens the Acct_Detail form for that acct and the org info (name, addr, etc).
Works great.

If the form was opened as a new organization, the subform does not display but they can click the add accts button which will take them to an Acct_Detail form. This is where my problem is.

The Acct_Detail form displays fields to add the acct info but I also want to display the org's info. The form was built with the form wizard with both the Org Table and the Acct Detail Table. When I open the form from the Org_and_Accts form when adding accts, I am using the following code:
DoCmd.OpenForm "frm_Accounts_Detail", acAdd, "", "((tbl_Orgs.OrgName) = Me.txt_OrgName)", acAdd, acNormal.

The Me.txt_OrgName is the field from the Org Table on the Org_and_Accts form and I was hoping to open the Acct_Detail form with the blank fields that are used to add the detail and also display the Org Table information in other fields on the form. But now that I've coded the "where" statement information the form opens totally blank except for the heading.

If this makes any sense at all to anyone, I’d appreciate it! And thanks for taking the time to at least read all of this!

 

[dhookom]

Try use code like the following which assumes OrgName is text:

DoCmd.OpenForm "frm_Organizations_and_Accounts", acNormal, "", _
        "[OrgName]=""" & [Forms]![frm_New_Menu]![combo_Orgs_and_Accounts] & """", , acNormal

and

DoCmd.OpenForm "frm_Accounts_Detail", acAdd, "", "OrgName = """ & Me.txt_OrgName """", acAdd, acNormal.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

I've coded your code and the 1st set works great - thanks!
But the frm_Accounts_Detail continues to open as a blank form except for the header. By blank, I mean there are no fields or button controls at all! What could possibly make the controls disappear except for the header image and title. Not sure where to look.... Thanks!

 

[dhookom]

You will find that the entire detail section will be missing if the record set is not editable and it is opened with no records matching the where condition.

If you are attempting to open the form and see a "new" record just created, the record might not be saved in the current form. You could add a line of code to save the current record prior to opening the new form.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

I am saving the form information that should be carried over to the next form. But I do think you are totally correct about the recordset not being editable. What I think my problem is all due to myself not understanding how to have a form that would be formatted like this:

(On the left of form)
ACCT INFO (editable)
acct #
Org Name Abrev
status
balance
date1
date2
interest
status

(On the right of the form)
ORG INFO (Display only)
Org Name
Org Name Abrev
Addr
State
Phone
Email

The form was built using both tables, so when they want to add an acct, the where is the Org Name and the form is opened as acAdd (which I was hoping it would realize that it is only for the Acct Info record). Should I instead have a subform for the Org info and not just fields for the Org on the main form? The two tables are linked by the Org Name Abrev.
I'm also experiencing the issue that the recordset is not updateable when testing a calendar control on the dates for the Acct Info side of the form, but the field visible and enabled.

I previously had a form that displayed the Org Info at the top and then had a grid subform for the accounts and the subform was used to add accts and edit them.

Think I should approach it that way?
Thanks for your help!

 

[dhookom]

Is [Org Name Abrev] the primary key of the one table? Are you familiar with relating tables based on primary and foreign keys?

I would think you would use a subform based on the child table and the main form would be based on the master table.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

I'm not too familiar with primary and secondary keys, and I know that sounds laim.

The Acct_Detail table's primary key is just the autonum ID (I did that because there will be many acct recs with the same Org_Name_Abrev which is called Org_Name in that table.
The Org table's key is OrgName. I also couldn't use acct_nbr as the key because there are duplicate acct_nbrs due to accts are really identified by different start and maturity dates.

The Acct_Detail field called Org_Name is linked to the Org table's field OrgNameAbrev (they contain the same value) and linked in the relationships.

Guess I didn't research or plan enough on that one, huh? I sure appreciate your help in the way you are making me learn and understand this.

 

[dhookom]

It would help if you would describe the significant tables and fields. Is [OrgNameAbrev] the primary key in tbl_orgs?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

Below are the tables:

Org Table:
ID
OrgName (primary key)
OrgNameAbrev (not a key only did this for a smaller column on subform)
OrgAddr
OrgCity
OrgState
OrgZip
OrgPhone
(and others…..)

Accounts_Detail_Table:
ID (primary key)
Account
Prev_Nbr
Org_Name (updated from the Org Table’s OrgNameAbrev)
Acct_Desc
Inv_Type
Curr_Bal
(and others….)

I know I didn't name things as clear as I should have when it comes to the OrgName and OrgNameAbrev (in Org_Table) and Org_Name(in Accts_Detail) and some clean up is due, but that's how they are now.
Thanks and have a good New Year!!

 

[dhookom]

I would think the ID field would be the primary key of [Org Table] and this value would be recorded in the [Accounts_Detail_Table] as the foreign key. You must store the primary key value from the main table in the related table.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

So if the primary key of the [Org Table] is now OrgName, I should change the primary key to ID and then add a field in the Account_Detail_Table that contains the value of that field? Not using the OrgName as a key in Org_Table?

 

[dhookom]

It seemed to me that you were not storing the primary key from the main table in the related child table. I rarely create a table that doesn't have an autonumber primary key. The relate child table has a numeric/long field that is used to store the primary key from its parent table.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

Thanks Duane, I now know how I need to approach this. Thanks so much for your help. If you don't mind, I will let you know my results, hopefully good ones!
Thanks again for your persistence and knowledge with this!

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cfvcf]

Thanks!

 

[cfvcf]

The relationships work great. Just decided to create a new small popup form to add the account which it does. Now when I click the close and save button and it goes back to the form with the subform that lists the accounts, I'm not sure how to refresh that form and subform so that it shows the new acct. I've tried Refresh On Got Focus and On Activate for both the form and the subform. Do I code it somewhere else? The new accts get added when I close the form and reopen it.
Thanks!

 

[dhookom]

Does the "new small popup form" open acDialog? It should. You can then add code to requery your combo box, form, or subform (where ever you expect to see the new record).

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

It does open acDialog. By chance do you have example of requery code for a dropdown that is on another form, by the way it is still open too? Thanks so much....

 

[dhookom]

This depends on what you are wanting to requery.

DoCmd.OpenForm "frmWhatever" ,,,,, acDialog
Me.sfrmOne.Form.Requery
Me.cboMyCombo.Requery

If you can't figure this out, come back with the code that you use to open the form as well as the controls you want to requery.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

Thanks, I will in the morning......My thought was that if the form was still open, I could somehow reference the form and control when I clicked the close and save button.
Thanks again, you've been so helpful and responsive.