Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Show field changes when another field is changed 1

Status
Not open for further replies.

kdk13

Programmer
Jul 3, 2002
23
US
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
 
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!
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top