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!

 

[cfvcf]

I coded this when they click on the close button and stored the value of the control in a public module that originally was set from the dropdown:
DoCmd.Save
DoCmd.Close
DoCmd.OpenForm "frm_Organizations_and_Accounts", , ,"OrgNameAbrev = " & str_public_Org_Abrev, , acWindowNormal

And it gives a dialog box for "enter the parameter value of t1" (which is the value of str_public_Org_Abrev) which is in a module as a public variable and the primary key for the table used for the form. When I enter t1, it brings up the form with the subform and the record that I just added is there. Not sure how to get around the 'parameter' problem. Thanks for your help, sincerely!

 

[dhookom]

You need to understand the difference between text/string values and numeric values. Since OrgNameAbrev is text, you must delimit it with quotes:

DoCmd.OpenForm "frm_Organizations_and_Accounts", , ,"OrgNameAbrev = """ & str_public_Org_Abrev & """", , acWindowNormal

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

You are absolutely right!. When you coded that code for me I was a bit confused about all those """ and """" but felt I was already asking you too much to explain that also. I have many flaws in Access and syntax is definately one of them! Thanks again!

 

[PHV]

Another way for quoting:

DoCmd.OpenForm "frm_Organizations_and_Accounts", , ,"OrgNameAbrev = '" & str_public_Org_Abrev & "'", , acWindowNormal

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

 

[cfvcf]

Great, I might remember that one, seems clearer.

 

[dhookom]

PH's syntax is a little easier to follow but could cause issues when/if the OrgNameAbrev contains single quotes like "Joe's". There is general less probability you would have a double-quote in an OrgNameAbrev or other string value.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

Got it, thanks.
I hate to ask another question, thought I was through with all my issues.
I was going to open another thread so that I could give you another star (apparently I can only do that once?), but now the issue I'm having is when I add a new organization on the form that has the Org info and the subform for the Acct Detail. I now have a button that says Save Organization and the code is:
Private Sub btn_Save_Click()
On Error GoTo Err_btn_Save_Click
DoCmd.Save
'DoCmd.GoToRecord , , acNewRec 'commented because it did't like.
str_public_Org_Abrev = Me.txt_OrgNameAbrev
MsgBox "org saved, str_public_Org_Abrev = " & str_public_Org_Abrev
Exit_btn_Save_Click:
Exit Sub

The msgbox displays the correct str_public_Org_Abrev.
Then I click on button Add Account:
Private Sub btn_Add_Account_Click()
str_public_Add_Acct = "Yes"
Me!txt_OrgNameAbrev.SetFocus
End If
str_public_Org_Name = Me.txt_OrgName
str_public_Org_Abrev = Me.txt_OrgNameAbrev
MsgBox "str_public_Org_Name = " & str_public_Org_Name
MsgBox "str_public_Org_Abrev = " & str_public_Org_Abrev
DoCmd.Save acForm, "frm_Organizations_and_Accounts"
On Error GoTo Org_and_Accounts_Save_Err
DoCmd.Echo True, ""
DoCmd.OpenForm "frm_Accounts_Detail", acAdd, "", , acFormAdd, acDialog

btn_Add_Account_Click_Exit:
Exit Sub
The msgboxes display the correct values.

Then I close and save:
Private Sub btn_Close_Click()
On Error GoTo Err_btn_Close_Click
MsgBox "str_public_Org_Abrev = " & str_public_Org_Abrev
DoCmd.Save
DoCmd.Close
str_public_Open_Type = "Existing"
DoCmd.OpenForm "frm_Organizations_and_Accounts", , , "OrgNameAbrev = """ & str_public_Org_Abrev & """", , acWindowNormal
Exit_btn_Close_Click:
Exit Sub

But when I go back to the form, the subform is empty and the tbl_Account_Detail does not contain the record.
The "Existing" is used in this event:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
If str_public_Open_Type = "New" Then
Form_subform_Accounts_Detail.Visible = False
ElseIf str_public_Open_Type = "Existing" Then
Form_subform_Accounts_Detail.Visible = True
Else
MsgBox "Not sure what the variable str_public_Open_Type is"
GoTo Form_Open_Err
End If
Form_Open_Exit:

I can go through the process again and select the Org on the mainmenu to view the org, the subform with accts is blank, then click on add account button again, enter the info, save and it brings up the org form with the subform contain the new acct.

What do I need to do to refresh the contents of the subform when adding a new org? That is the only time it doesn't. Make sense? Sorry about my confusion. Thanks!

 

[dhookom]

DoCmd.Save has no purpose in your code. If you have a record in a bound form that you want to save, use something like
DoCmd.RunCmd acCmdSaveRecord 'I think
You should also be able to use
Me.Dirty = False

This line also does nothing unless the form is in design view:
DoCmd.Save acForm, "frm_Organizations_and_Accounts"


Beyond that, I don't know what you are attempting to do. Are all your forms bound to tables/queries?

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[cfvcf]

I''ll give those a try. Yes, all the forms are bound to tables. I thought that in order to save the input entered by the user, I needed to save the form, which in turn would save the table fields. Thanks!

 

[cfvcf]

Ok, let me ask you this. I did the "DoCmd.RunCommand acCmdSaveRecord" on the Org Form which is bound to the Organization table and it saves the organization. Then when I click the Add Account btn it opens the New Account form. I enter the data, click Save and Close Btn and code the "DoCmd.RunCommand acCmdSaveRecord". That form is bound to the Account_Detail table. Then the Account form closes and it goes back to the Org Form (which is still open) which has the Org information (bound to the Org Table) and the subform (bound to the Accounts Detail table). The subform is blank but the record gets added to the Accounts Detail table.

I added to the subform load event:
Private Sub Form_Load()
Me.Dirty = False
MsgBox "subform load"
End Sub
And I get that message. The data is in the accounts detail table which the subform is bound to. Just not sure how to make sure the subform displays the latest table data.

If I go back to the main menu and click a btn that views the Orgs, I get a combobox and the org is there. When I click on the org, it opens the Org form and the subform has the new acct. I'm sure I'm missing something....

 

[PHV]

Have a look at the Requery method.

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

 

[cfvcf]

What about this code when closing acct detail?
Private Sub btn_Close_Click()
On Error GoTo Err_btn_Close_Click
MsgBox "str_public_Org_Abrev = " & str_public_Org_Abrev
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
str_public_Open_Type = "Existing"
MsgBox "str_public_Open_Type = " & str_public_Open_Type
(MsgBoxs show str_public_Org_Abrev = correct str_public_Org_Abrev & correct str_public_Open_type = Existing)
DoCmd.OpenForm "frm_Organizations_and_Accounts", , , "OrgNameAbrev = """ & str_public_Org_Abrev & """", , acWindowNormal
With Forms!frm_Organizations_and_Accounts
.SetFocus
.Requery
End With
Exit_btn_Close_Click:
Exit Sub

Then this when Org and Accts opens:
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
Me.Dirty = False
MsgBox "orgs and accts open"
If str_public_Open_Type = "New" Then
Form_subform_Accounts_Detail.Visible = False
ElseIf str_public_Open_Type = "Existing" Then
Form_subform_Accounts_Detail.Visible = True
Else
MsgBox "Not sure what the variable str_public_Open_Type is"
GoTo Form_Open_Err
End If

But the same thing happens. Any ideas? - thanks?

 

[dhookom]

To be honest, I am confused regarding your strategy. I don't think you have clearly stated something like "I have a form bound to ... the a subform of child records... I would like to open to... Here is my code with comments about the purpose of the lines of code... My problem is ...."

BTW: this question is not related to your original posting and should have started a new thread. TT'ers see long threads with stars and probably avoid reading them.

Duane MS Access MVP
Now help me support United Cerebral Palsy