Form w/subform 1

[looking887]

I am relatively new to access and have a question on a form that I have been struggling with. Below you will find the SQL of the query I am using. My primary form "ExpensesTest" is using the fields YearNumber, Months, and CategoryName. What I am trying to do is set the 3 fields above, and then list by Dollar amount the expenses for that Month and Category selected. In other words, select the category and date once without repeating these entries each time I enter an expense. Then, if I want to change the category/Month, I do it once and then enter all appropriate expenses with the dollar amount.

I thought that a subform would be the way to go but when I go to a new record, my list of previously entered expenses appears rather than a clean subform. Is this the right way that I should be doing this and if so, can you perhaps give me some guidance.

QUERY for ExpensesTest Form

SELECT tblExpenses.ExpenseID, tblExpenses.ExpenseAmount, tblExpenses.CategoryID, tblExpenses.MonthID, tblExpenses.YearID, tblCategories.CategoryName, tblMonths.Months, tblYears.YearNumber
FROM tblYears RIGHT JOIN (tblMonths RIGHT JOIN (tblCategories RIGHT JOIN tblExpenses ON tblCategories.CategoryID = tblExpenses.CategoryID) ON tblMonths.MonthID = tblExpenses.MonthID) ON tblYears.YearID = tblExpenses.YearID;

SQL for subform

SELECT qryExpenses.ExpenseID, qryExpenses.ExpenseAmount, qryExpenses.CategoryID, qryExpenses.MonthID, qryExpenses.YearID
FROM qryExpenses;

 

[dhookom]

If you want to filter and automatically update fields/records in a subform, you need to use the Link Master/Child properties of the subform control.

Why do you have a MonthID and YearID? Why not just enter the Month number and Year number?

Duane
Hook'D on Access
MS Access MVP

 

[looking887]

I did change the linkage to month number, year number and category name. Upon doing this I do see now why it didn't work before. Thanks for posing that question.

Now the only question I have is this. When I open a new form and select the Year, Month and category, and then enter my expense amount on the subform, the 3 linked fields automatically appear, which is great. However, when I make a second expense entry on the same subform the 3 linked fields do not auto-populate and do not show up on the query for future reporting. why is this, or what am I missing.

 

[dhookom]

If you have link master/child the master values will populate in the child records. If the child values you see aren't what you expect then check the master values.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I agree with Duane. If you have this correct, then those values would populate. Can you post the following?

In the subform control.
LinkMasterFIelds:
linkChildField:

and ths sql for the subform.

 

[looking887]

MajP, I am sorry but apparently I don't have it set up right. When I check the properties of the subform, nothing is showing under the link master and child fields, even though I did set them up originally. As I said, this is a bit new to me and now I am confused.

Below is the Sql for my subform while the Sql for the query that I use for the main form and subform is in my initial posting above.

SELECT qryExpenses.YearNumber, qryExpenses.Months, qryExpenses.CategoryName, qryExpenses.ExpenseID, qryExpenses.ExpenseAmount
FROM qryExpenses;

 

[dhookom]

Again, why do you have tblYears and tblMonths?

What is the SQL view of qryExpenses?

Duane
Hook'D on Access
MS Access MVP

 

[looking887]

Reason for the 2 tables (tblCategories also) is so each of these fields on the main form can used as a combo box to make appropriate selection and then subsequently list all expenses for those choices on subform. If another category is to be selected, a new record is created and the combo boxes are again used with those expenses added to the subform.

SQL of qryExpenses

SELECT tblExpenses.ExpenseID, tblExpenses.ExpenseAmount, tblExpenses.CategoryID, tblExpenses.MonthID, tblExpenses.YearID, tblCategories.CategoryName, tblMonths.Months, tblYears.YearNumber
FROM tblYears RIGHT JOIN (tblMonths RIGHT JOIN (tblCategories RIGHT JOIN tblExpenses ON tblCategories.CategoryID = tblExpenses.CategoryID) ON tblMonths.MonthID = tblExpenses.MonthID) ON tblYears.YearID = tblExpenses.YearID;

 

[dhookom]

It looks like the record source of the main form and the subform are the same. This is generally wrong. If you don't intend to edit or add records in the main form, I would not bind it to any record source.

Just place unbound combo boxes in the unbound main form and use them as the Link Master property of the subform.

I would probably use a look up table for years but it would have only one field which is the actual year. The month might be the same with a single field. There would be no need to create a query that joins tblExpenses to tblYears since the one field in tblYears stores the same value as the year field in tblExpenses.

tblCategories is a little different. I would have a CategoryID and a CategoryName. I probably wouldn't include the tblCategories in the subform record source since I could display the category name in a combo box in the subform.

This would effectively remove all but tblExpenses from the record source of the subform.

Duane
Hook'D on Access
MS Access MVP

 

[looking887]

I am sorry for my continued comeback to you but my inexperience causes me to ask a couple of more questions but if I don't ask, I will never learn it right.

I do understand now why you would not use the same record source for both the main and sub-form. I have now set up an unbound main form with unbound combo boxes. Set up the subform using the tblExpenses as the record source. It is set for ExpenseID and Expense amount as visible fields for entry.

My question now comes regarding the link master/child fields. Do I do this by accessing the properties for the main and subform and type in the values? If so, I have looked at several references to figure out how to do it and have yet to find one. Is this what I need to do?

 

[dhookom]

The Link Master/Child are properties of the subform control. You will need to type in the names of your controls from the main form into the Link Master. The Link Child property would be the names of the matching fields from the subform's record source.

Duane
Hook'D on Access
MS Access MVP

 

[looking887]

That is what I had thought but I was not sure of the formatting to make it work. For example under link master would I just type - Months;Year; CategoryID with them separated by colons, inputting the same for Child? I am thinking this is not correct since it does not work. Once typed shouldn't you be able to go back to the properties later and still see what was input?

 

[dhookom]

What are the names of your 3 controls on the main form that you want to use to filter the subform? That is what goes into the Link Master property. I don't know if there names are "Months;Year; CategoryID".

If you didn't heed my concerns about your year and month lookup tables then the Link Child property would be:
MonthID;YearID;CategoryID


Duane
Hook'D on Access
MS Access MVP

 

[looking887]

I did as you had suggested and upon correcting the typo on my part it works perfectly now. I appreciate your patience and explanations while I continue to learn the magic of Access. Thanks again.