Update Subform

[Guest]

Good morning,

I have subforms within forms which are used simply to add a new reference to
a table which then can be used to update a field on the main form. Example:
On my expense form, I have a field business. Rather than closing out the
expense form, opening up the table business, and adding a new business there,
reopening, etc. I put a subform linked only to table on the main form (the
expense form). I have add a new business, pressed enter, or add a new
record. That new record should be an option on the expense form without
having to close out the expense form. For some reason, the subform is
updating the table, but the new business does not appear on the drop down
list of businesses, therefore, I am not given the option of choosing that new
business on the expense form. I am still having to close out the expense form
and open it up before the updated business table options show in the combo
box. Any suggestions as to what I should do?

 

[Guest]

Tried the "after update" event or requerying?

 

[Guest]

Good morning scubadiver,

Thanks for your suggestion. I tried writing a macro for "after update" on
the main form (frm_expense) . In the Macro, I put “Open Table,†“Name of
Table.†Then closed and saved Macro_Business. I inserted the macro in the
“After Update†on the main form, frm_espense. When I clicked the Combo Box,
the new entry appeared. When I deleted it, a blank space was left when I
again clicked to open the combo box on the main form. Does that have to do
with the fact that the control source for my combo box is the query for the
control source?

Main Form: “frm_expenseâ€
Control Source: “qry_expenseâ€
After Update: “macro_expenseâ€
Combo Box: “Enter New Businessâ€
Control Source: “qry_businessâ€
Subform: “subform_businessâ€
Control Source: “tbl_businessâ€

Hopefully that will explain what I have and what I’ve done. I like the fact
that my new business shows up immediately in my Combo Box. However, if I
delete a business, I do not like the “whole†it leaves in the list. What
should I do that I did not do?

 

[John W. Vinson]

Hopefully that will explain what I have and what I’ve done. I like the fact
that my new business shows up immediately in my Combo Box. However, if I
delete a business, I do not like the “whole” it leaves in the list. What
should I do that I did not do?

Requery the combo in both the AfterUpdate and the AfterDeleteConfirm events.

John W. Vinson [MVP]

 

[Guest]

Thanks for your suggestion. I tried writing a macro for "after update" on the
main form (frm_expense) . In the Macro, I put “Open Table,†“Name of Table.â€
Then closed and saved Macro_Business. I inserted the macro in the “After
Update†on the main form, frm_espense. When I clicked the Combo Box, the new
entry appeared. When I deleted it, a blank space was left when I again
clicked to open the combo box on the main form. Does that have to do with the
fact that the control source for my combo box is the query for the control
source?

Main Form: “frm_expenseâ€
Control Source: “qry_expenseâ€
After Update: “macro_expenseâ€
Combo Box: “Enter New Businessâ€
Control Source: “qry_businessâ€
Subform: “subform_businessâ€
Control Source: “tbl_businessâ€

Hopefully that will explain what I have and what I’ve done. I like the fact
that my new business shows up immediately in my Combo Box. However, if I
delete a business, I do not like the “whole†it leaves in the list. What
should I do that I did not do?

 

[Guest]

Good evening John,

Hope you are doing well and you will be happy to know that my forms are
working great, thanks to your intructions about the AutoNumber FieldID. (Did
you think I was out of your hair?--Afraid not, I have so very much left to
learn--I think I've only touched the tip of the iceburg!) Anyway, down to the
business at hand:

I wrote a new Macro to cover the Requery. It is quite simple.
Open Query
Query Name: Name of my query
View: DataSheet
Data Mode: Edit
Requery
Control Name: __________(I left it blank)

However, I could not find the controls After Update and After Deletion
confirm on the Combo Box. I found the After Update there and put the
Macro_OpenQry_Name.
On the main form, frm_expense, I found the controls After Update and After
Del Confirm. I put Macro_OpenBusiness on the After Update control and
Macro_RequeryBusiness on the After Delete Confirm.

Initally, a whole is left, however, when I close out the frm_expense and
reopen it, all the blanks are at the top of the qry_Businesses. Is this as it
should be? I would at assume at some point, I should go into the qry_Business
and delete those blank records or add new businesses in them.

Here is another questions, How do I go about making a report using more than
one query. I want to show CashDonators, In-KindDonators, and
MonthEndTotalSales and Cash Donations on the same report. I have used the
same tblDonators for In-KindDonators and Cash Donators (might be my problem)
and when I run the query, I get the correct amount of CashDonators, but a
repetion of the In-KindDonators to the number of CashDonators, even if there
is only one In-KindDonator for the month.
The time-frame for both CD and IKD is the same.

On the lower half of that report page, I would like to list the totalsales
and total cashdonations for the month. I would like for the totals of each of
those to be to the right, ie sales+cashdonations=totalmonthlyincome.

I tried clicking the Report Record Source icon at the upper left of the
report in design view. However, when I try to get fieldnames from different
queries or tables, I am not successful.

I am still searching for the correct way to make a subreport, which I think
is what I needon the lower half of the report page to show the monthly total
incomes.
As always, thank you so very much and God Bless, k