Show field changes when another field is changed 1

[kdk13]

OK, hopefully this question is easier than yesterday's which still doesn't have a solution.

I have a table, tblFundingYear. It has three fields: FundingYear (PK), StartDate, and EndDate. I have another table Request that has the FundingYear on it.

When the user is entering info on a form for the Request, I want to have the StartDate and EndDate automatically display.

When I build the form just on the Request table and link in the tblFundingYear, I can get the fields to automatically display and update when I choose a funding year or change a funding year for a Request.

The problem comes in when I start joining other tables to the source. Once I do that, the StartDate and EndDate don't 'refresh'. I can't even get them to refresh through Records>Refresh or Shift F9. I have to exit the form and go back in. I need the other tables in there so I can display the text descriptions of other id fields in the Request table. Also, when I add more tables, I find that I can't add or update the Requests without changing the Recordset Type property to "Dynaset (Inconsistent Updates)".

I'm finding that if when I have it working with just the two tables, and then change the Recordset Type to Dynaset (Inconsistent Updates), then it doesn't work, so this seems to be the problem, not the addition of the tables. But then how do I allow updates to my tables through the form queries if I don't change this property?

I have complete control of the Access database, so I'm open to any and all suggestions.

kdk13

 

[SBendBuckeye]

One thing that will automatically make a query read only is where clause joins instead of for clause joins.

Select tbl1.field, tbl2.field
From tbl1, tbl2
Where tbl.field2 = tbl2.field2 <== read only

Select tbl1.field, tbl2.field
From tbl1 Inner Join tbl2 On tbl1.field2 = tbl2.field2

If some of your controls are display only you can take them off of the query and use functions to return them.

datefield = DLookUp(&quot;tablefield&quot;, &quot;tablename&quot;, _
&quot;keyfield = &quot; & controlname.value)

Notes: If lookup is text surround it with single quotes '
&quot;keyfield = '&quot; & stringvar & &quot;'&quot

If lookup is a date surround it with pound signs #
&quot;keyfield = #&quot; & datevar & &quot;#&quot

Good Luck!

 

[kdk13]

Thanks for your help. Since I had the start and end date columns in the combo box, we were able to write vb to display the dates on change of the Funding Year.

Thank you especially for the explanation of when to use the quotes and pound signs. The pound signs were what I needed to solve a different problem involving inserting a date into a table.

kdk13