Using variables to acquire form data

[DamionLee]

RESOLVED - Using variables to acquire form data

Hello all,

I'm building a database system in Access 2002 and one of the functions I want to program into it has become a bit of a stumbling block. Unfortunately I have not been able to find details on doing what I want, mainly because I can't think how to word it in search engines.

In brief, the situation I have is this:

I currently have a table with VBA code which populates a variable with a string and form data:

varSubject = "Asset Information - " & CustomerName

This is passed to a function in a code module in order to generate an e-mail. The e-mail subject line then substitutes & CustomerName with the contents of the CustomerName field on the currently open form.

The code works fine at the moment, but I want to get away from having these variables hard coded. My hope is that the table I have created can be used to populate these variables in order to make changes easy for the end user.

The table has a text field which contains the same text as was added to the variable in the above code box.

The problem that I have is that by using the code:

varSubject = DLookup("SubjectLine", "gblSysLaptopSwaps", "ControlRef = 2")

I end up with with it passing the data to the function as is, so the subject line then just displays "Asset Information - " & CustomerName (rather than substituting the data in the field CustomerName.

Is there any way of having VBA recognise that I want it to use the contents of the field on the table as a command rather than copy it as a string.

Any help would be greatly appreciated.

Damion.

 

[HGMonaro]

I would investigate seperating the call to the subroutine to get the text and building the string that you want to create with the database value (CustomerName) in it.

So you'd have something like

varSubject = DLookup("SubjectLine", "gblSysLaptopSwaps", "ControlRef = 2")
varText = varSubject & db_ref!CustomerName

then use varText as the email header.

 

[DamionLee]

Hi HGMonaro,

Thank you for your input. Unfortunately this presents the same issue as hard coding the whole string.

The plan behind having the table is that the end user will be able to type in the string, including any coding, in order to change the subject line. This is also going to be done for the e-mail address, file attachment and the e-mail body.

It is important that the end user can type in the calls to the form data in order to be able to include live data within the subject and e-mail body.

Regards,

Damion

 

[gemma-the-husky]

it should work as you want

varSubject = result of dlookup

varsubject = varsubject & " Cust: " & customername

msgbox(varsubject)

 

[DamionLee]

Dear Dave,

Thank you for your reply.

Again, this code will pull text from the table, but relies on hard coding the &CustomerName section to the module.

What I am aiming for is that in the text field on the table created (lets call it txtSubject on table sysEMailConfig)

The client enters into that field:

"Asset Information - " & Customer Name

The code module connected to a button on the form (which we'll call dataAssetChange) then contains code to look up the contents of txtSubject. This is going to be done for other fields on the table sysEMailConfig.

This information is then passed to a code module which uses these variables to generate an e-mail. Because there are going to be several different forms that require e-mails with different content, each form will have a small amount of code to collect the information from config tables (in the manner above) and pass this to the code module.

However, the catch is that nothing of the string can be hard coded because this will fix the format of the text being entered and mean any changes will need to be done in code. The end user needs to be able to write the required code for calling field information into the text on the sysEMailConfig table.

 

[DamionLee]

Dear All,

Have come across a solution thanks to another source. As such, thought it may be helpful to post it on here. (To be honest I am kicking myself for not thinking of it myself).

The process is to create 'token entries' that the client can use (so the &CustomerName will work, but it is recommended to use proprietary tokens such as {CustomerName}). Then to use a module to read through the string, extract the token characters and replace them with VB code whilst building a new string.

Thus the end user could type in

Laptop Swap - {CustomerName}

The DLookup would then place this into a variable (eg. varSubject)
Coding would then read the string off into a new string (eg varSubjectPass) and replace {CustomerName} with &CustomerName.

This would then be passed to the module to generate the e-mail.

Thank you to those that read this thread, and thank you to those who have replied.

Regards,

Damion.