alisonfinfrock
New member
- Local time
- Yesterday, 19:42
- Joined
- Apr 13, 2015
- Messages
- 4
Hi All! I’ve been a reader on here for a while (thanks to your helpful answers always popping up in my Google searches) but finally have a post-worthy issue of my own that has prompted me to join your ranks. Just wanted to start off by saying thanks for being so helpful in the past! I wasn't sure if I should post this in Tables or Forms, so my apologies if I put it in the wrong place!
I have a database (native, Access 2013 .accdb) that tracks call activity for marketing purposes. There are fields in the table for contact info as well as fields for other information we are required to gather during the call. These fields are primarily dropdown combo-box fields, number fields, or date fields. Our staff records the proper responses to each field in the call log/record. We also have a field called “Notes” that is a Long Text (formerly “Memo”) type field. This field gets populated with a brief narrative of the call. I understand the dangers inherent in using this field type (complications, corruptions, etc.), but due to some reporting requirements we don’t have much other choice but to use it and we need this narrative to be saved in the table with the rest of the call record.
Just a slight backtrack to better understand our environment, our database is set up with a back end/front end type setup. The staff uses forms tied to queries to pull the information they need from our table, and do all their data entry for the calls onto those forms. They do not ever access the table itself without going through a query. They also don’t do anything in “datasheet” view; all their access is through a few controlled forms.
Now for the issue: I am trying to auto-populate some or all of the narrative needed in the Notes field, based on the responses in other fields. This reduces our employee’s need to type out the info again, both wasting time and potentially causing errors in the narrative (which would then take more time to be fixed later on by our quality team). Just so you can get a feel for what I mean by the narrative, it goes something like this: “An appointment has been set for [Appt Date] at [Appt Time] to discuss gas conversion. They are currently using a [Heating System Type] system that is [Heating System Age] years old. They use [Heating System Fuel] for heat now.” Etc. The write-up is a bit longer, but I think you can get the point from this. The bracketed parts are the values from fields of those names on the call record.
I have had success doing this on past databases by using the following code (as an OnClick property for a button on my form): Forms![All Records Frm]!Notes = Eval("""An appointment has been set for "" & [Appt Date] & "" between "" & [Appt Time] & "" with "" & Forms![All Records Frm]![F Name] {truncated here, since I think you get the point}
I am trying to get this to work on a new database, and I’m not having the same luck. Does anyone know if there is a size/length limit to using this particular method? (It seems the code in VBA for this write-up is getting cut off and moving down to the next line, and that is where my problem lies.) Is there another (better, faster, smarter) way to accomplish what I need to accomplish instead of using the “Eval” that I’m using now? (That wouldn’t surprise me. I’m more or less self-taught, internet-taught, and “For Dummies”-taught in Access…)
Thank you for your time and assistance!
Alison
:banghead:
I have a database (native, Access 2013 .accdb) that tracks call activity for marketing purposes. There are fields in the table for contact info as well as fields for other information we are required to gather during the call. These fields are primarily dropdown combo-box fields, number fields, or date fields. Our staff records the proper responses to each field in the call log/record. We also have a field called “Notes” that is a Long Text (formerly “Memo”) type field. This field gets populated with a brief narrative of the call. I understand the dangers inherent in using this field type (complications, corruptions, etc.), but due to some reporting requirements we don’t have much other choice but to use it and we need this narrative to be saved in the table with the rest of the call record.
Just a slight backtrack to better understand our environment, our database is set up with a back end/front end type setup. The staff uses forms tied to queries to pull the information they need from our table, and do all their data entry for the calls onto those forms. They do not ever access the table itself without going through a query. They also don’t do anything in “datasheet” view; all their access is through a few controlled forms.
Now for the issue: I am trying to auto-populate some or all of the narrative needed in the Notes field, based on the responses in other fields. This reduces our employee’s need to type out the info again, both wasting time and potentially causing errors in the narrative (which would then take more time to be fixed later on by our quality team). Just so you can get a feel for what I mean by the narrative, it goes something like this: “An appointment has been set for [Appt Date] at [Appt Time] to discuss gas conversion. They are currently using a [Heating System Type] system that is [Heating System Age] years old. They use [Heating System Fuel] for heat now.” Etc. The write-up is a bit longer, but I think you can get the point from this. The bracketed parts are the values from fields of those names on the call record.
I have had success doing this on past databases by using the following code (as an OnClick property for a button on my form): Forms![All Records Frm]!Notes = Eval("""An appointment has been set for "" & [Appt Date] & "" between "" & [Appt Time] & "" with "" & Forms![All Records Frm]![F Name] {truncated here, since I think you get the point}
I am trying to get this to work on a new database, and I’m not having the same luck. Does anyone know if there is a size/length limit to using this particular method? (It seems the code in VBA for this write-up is getting cut off and moving down to the next line, and that is where my problem lies.) Is there another (better, faster, smarter) way to accomplish what I need to accomplish instead of using the “Eval” that I’m using now? (That wouldn’t surprise me. I’m more or less self-taught, internet-taught, and “For Dummies”-taught in Access…)
Thank you for your time and assistance!
Alison
:banghead:
