I have a recordset that I display in a datasheet. One of the controls is calculated(eg "result") based on another field of the recordset(ec "City"), and when I view this calculated field in the datasheet, I may want to edit it by changing the value of the first field(ie "City") on the afterupdate event of the Calculated control (ie "result"). I can do this calculation in the SQL statement, but then I cannot edit the field (ie "result")as displayed in the datasheet. I am trying another approach, which is to do the calculation as the records are generated and displayed. I would then take first field (ie City) and then "push" the calculated field (ie "result") into an unbound text box of the datasheet. My question is ..What event can I used to do this, or is there a better way ?
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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Records dsipalyed in Datasheet view
- Thread starter lizray
- Start date
What is the SQL of the recordset?
What is the calculation?
You can't edit the result of a calculation. If you edit the values the calculation is based on, the result should be displayed after the values are updated.
Duane
Hook'D on Access
MS Access MVP
What is the calculation?
You can't edit the result of a calculation. If you edit the values the calculation is based on, the result should be displayed after the values are updated.
Duane
Hook'D on Access
MS Access MVP
You do not need any event. Usually the calculated field on a form has a function as the controlsource. Something like:
controlsource: =myFunction([city])
The function could be a user defined function or some native vb expression. But it should recalc after any change to the form fields.
- Thread starter
- #4
Thanks Duane and MajP. The reasons for the coding may seem strange, but there is a field in the recordset named "City" , which is hidden in the datasheet view, and it can contain a city name or be null. If it is null then I need to display it with a default city, eg "houston" and this is displayed in another field, the "CityName" column of the datasheet. The user can view this and may want to change it to some other city name, and so would edit the "CityName" column for that record. To achieve this I have the "City" field as hidden and then I have an unbound textbox named "CityName" and the control source is :
=IIF(isnull(city), "houston",city)
which works ok and displays correctly except that the user cannot edit that "cityName" field. If I could solve this,I would use the afterupdate event of the "CityName" to save the edited value to the "City" field. ie txtCity=txtCityName
I think I could edit the "CityName" field if it were unbound and the IIF statement above was executed from an event which occurred as the datsheet was being populated. I would appreciate any suggestions.
=IIF(isnull(city), "houston",city)
which works ok and displays correctly except that the user cannot edit that "cityName" field. If I could solve this,I would use the afterupdate event of the "CityName" to save the edited value to the "City" field. ie txtCity=txtCityName
I think I could edit the "CityName" field if it were unbound and the IIF statement above was executed from an event which occurred as the datsheet was being populated. I would appreciate any suggestions.
I doubt you can get this to work in a datasheet without displaying the city field for editing. I would probably create a double-click event that would open a form that allows editing of the city field and the refresh the datasheet form.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #6
I have followed Dhookom's suggestion and it now works ok,
Does anyone know how records are displayed in a datasheet...Do they have associated events ?? as the noraml "on current" event for the subform does not seem to refer to a row in a datasheet
Does anyone know how records are displayed in a datasheet...Do they have associated events ?? as the noraml "on current" event for the subform does not seem to refer to a row in a datasheet
A form displayed as a datasheet should have all the same events as a form in continuous view.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #8
What events are you referring to? The current event occurs for each record as you move from record to record.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #10
I dont know if there are events that relate to the populating of the Datasheet. So that as each row is populated,(in a similar way to forms)there might exist an event that would allow the modifications to a field value. ie for a textbox on the datasheet form:
Sub OnEvent ?
txtbox = " etc....."
end sub
I can see that after the form is populated events such as Current etc work as you move through the records. I appreciate your input and Thanks for help so far
Sub OnEvent ?
txtbox = " etc....."
end sub
I can see that after the form is populated events such as Current etc work as you move through the records. I appreciate your input and Thanks for help so far
You need to perform any display stuff in the SQL of the recordset. I'm old and confused and still don't understand what you are attempting to display in the datasheet.
Duane
Hook'D on Access
MS Access MVP
Duane
Hook'D on Access
MS Access MVP
- Thread starter
- #12
- Status
Similar threads
- Locked
- Question
