?
Solved

Access 2003 writing html into memo field from text fields

Posted on 2007-10-13
14
Medium Priority
?
374 Views
Last Modified: 2013-11-27
I have an Acces 2003 database that I am using to organize a web store.  The database contains various fields needed to identify a product that is uploaded to the store using a simple text querry.  I have a form that displays the details, price, availablity, pictures, etc for the product, and a large memo field where I write the html to be uploaded to the store.
I have a list of up to 15 features for each item, and my goal is to list them individually as text, then grab them, insert them between code to create a box that displays the features in a box that alternates between background colors.  (Seems as it would be efficient rather than coding that for 1500 products individually)

I have two problems,
1) assuming i can write a program to count the number of features, and then apply the correct sequence of text in front of and behind the 'feature', can that new text be written into an access memo field?

2) can the routine be written in access to accomplish this?  The first and last line of the html would differ, the middle lines would alternate the background color, and it is best if the html is on separate lines as in the template below
features template
feature 1 (first line)
1 <table width="100%" border="0">
2  <tr>
3    <td bgcolor="#cff8f9"> FEATURE 1 </td>
4  </tr>

feature 2 (repeats and alternates color)
1<tr>
2   <td> FEATURE 2 </td>
3</tr>

feature 3 (last feature)
1<tr>
2   <td>FEATURE 3</td>
3</tr>
4</table>

My access ability is intermediate (on the low end), same with html.

Any input would be appreciated.
0
Comment
Question by:Quadeeb2003
  • 7
  • 7
14 Comments
 
LVL 4

Accepted Solution

by:
seameadow earned 2000 total points
ID: 20073491
Hi Quadeeb2003

It makes more sense to me to write the HTML using the ASP script than to store it in the database. Otherwise you have to rewrite the HTML whenever a feature is added, deleted or modified. In either case however the technique is similar, with minor differences for syntax. For Access:

1. Get the feature count using SQL in either a DAO or ADO recordset
DAO Example:
Function FeatureCount(byVal ProductID as String) As Integer
  Dim SQL as String, rs as DAO.RecordSet
  SQL = "SELECT COUNT(*) From Features WHERE ProductID='" & ProductID & "'"
  Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
  FeatureCount = Nz(Rs(0),0)
  rs.Close
End Function

2. Generate the HTML
Function FeatureHTML(byVal ProductID as String) as String
  Dim rs As DAO.Recordset, Cnt as Integer, HTML as String, i as Integer
  Cnt = FeatureCount(ProductID)
  If Cnt = 0 then
    FeatureHTML = "" 
    Exit Sub
  End if
  SQL = "SELECT Feature FROM Features WHERE ProductID='" & ProductID _
         &"' ORDER BY whatever"
  Set rs = CurrentDB.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
  rs.MoveFirst
  HTML = "<table width=""100%"" border=""0"">" & vbCrLf
  Do While Not Rs.EOF 'don't use CNT in case data has changed
     HTML = HTML & "<tr> & vbCrLf
     if i Mod 2 = 1 then
        HTML = HTML & "<td bgcolor=""#cff8f9"">" & rs("Feature") & "</td>" & vbCrLf
    else
        HTML = HTML & "<td>" & rs("Feature") & "</td>" & vbCrLf
    End If
    HTML = HTML & "</tr>" & vbCrLf
    rs.MoveNext
  Loop
  rs.Close
  HTML = HTML & "</table>" & vbcrrLf
  FeatureHTML = HTML
 End Function

Write the HTML into a table

Dim HTML as String, Query as String
HTML = FeatureHTML(ProductID)
'check to see if html already exists
If Not IsNull(dLookup("ProductID","PrioductHTML","ProductID='" & ProductID & "'") ) Then
  SQL = "UPDATE ProductHTML Set HTML = '" & HTML & "' WHERE ProductID='" & ProductID & "'"
Else
   SQL = "INSERT INTO ProductHTML(ProductID, HTML) VALUES ('" & ProductID _
    & "',"'" & HTML & "'")
End if
CurrentDb.Execute (SQL)
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20081914
Would an ASP script be able to function with the database?  My goal would be to make it the easiest, cleanest possible solution.

I'm just not too familiar with ASP.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20082048
Yes, ASP uses VBScript, which is very similar to VBA, except that there are no types: everything is a variant. You would open an ADO connection to the database to read and write data. I can provide an example script if you like tomorrow morning -- I'm about to leave for the day!

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20082073
Thanks, that would be greatly beneficial.

I would appreciate looking at it.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20084792
OK here we go... a complete template for product.asp. It accepts a parameter in the query string, for example
<a href="product.asp?ID=12345">Info for product 12345</s>



<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<%
Const dbconnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\foo\bar\products.mdb;User Id=admin;Password="
Dim ObjDbConnection  'global connection object for the page

Sub OpenDB()
  Set objDbConnection = Server.CreateObject("ADODB.Connection")
  objDbConnection.ConnectionString = dbconnect
  objDbConnection.Mode = 1  'readonly
  objDbConnection.Open
End Sub

Function FeatureHTML(byVal ProductID)
  Dim rs, HTML, i, SQL  
  SQL = "SELECT Feature FROM Features WHERE ProductID='" & ProductID _
         &"' ORDER BY whatever"
  set rs = Server.CreateObject("ADODB.Recordset")
  rst.Open SQL, objDbConnection, 3, 1 'static readonly cursor

  HTML = "<table width=""100%"" border=""0"">" & vbCrLf
  Do While Not Rs.EOF
     HTML = HTML & "<tr> & vbCrLf
     if i Mod 2 = 1 then
        HTML = HTML & "<td bgcolor=""#cff8f9"">" & rs("Feature") & "</td>" & vbCrLf
    else
        HTML = HTML & "<td>" & rs("Feature") & "</td>" & vbCrLf
    End If
    HTML = HTML & "</tr>" & vbCrLf
    rs.MoveNext
  Loop
  rs.Close
  HTML = HTML & "</table>" & vbCrLf
  FeatureHTML = HTML
End Function

Function ProductInfo(ByVal ProductID)
  Dim rs, HTML, i, SQL  
  '...
  ' some code here similar to above to read product info and generate HTML
  '...
  ProductInfo = HTML
End Function

%>

<%
  'main processing code here (VBscript not in a function or sub)
  Dim PID 'a global variable
  PID = Request.QueryString("id")
  Call OpenDB

%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
<HEAD>
  <TITLE>Example</TITLE>
</HEAD>
<BODY>
  <p>Product Example</p>
  <%=ProductInfo(PID)%>
  <p>Product Features</p>
  <%=FeatureHTML(PID)%>
  <% Reponse.Write("<hr><p>This is VBScript executing inside the HTML</p>")
</BODY>
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20087731
Wow, looks pretty cool.
What would I need to do to get it operational?
I would execute it from Access right?
Put is behind a button on a form, add the correct table name, field names?
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20088060
The first example is Access code -- that can be executed from a button on a form. It does require a DAO reference -- from the VB Editor, select Tools > References, scroll down and check the latest version of Microsoft DAO Object Library that you see in the list.

The second example is an ASP page to be executed by a web server (Microsoft IIS). For this use a text editor such as notepad, fix up the code with correct names, etc. and execute by opening the page in a browser; e.g.,
http://localhost/products/product.asp?ID=12345
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20103337
Thanks a bunch seameadow:

I'm going to try to get that Access code working, at least I've got my feet a lttle wet there.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20103779
You're quite welcome. I spotted at least two syntax errors in the Access code:
  HTML = HTML & "<tr> & vbCrLf
should be
  HTML = HTML & "<tr>" & vbCrLf
and
  HTML = HTML & "</table>" & vbcrrLf
should be
  HTML = HTML & "</table>" & vbCrLf

Good luck, and have fun !
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20104290
Thanks, I am having a bigger problem.
I have not been able to get the first part going, as, my table has each feature running across each CusId.
I came up with a way to get a FeatureCount (probably poorly written), but i cant get it to execute in a module, to even get to the next part of the puzzle.
the sql in query form is:
SELECT tbl_Inventory.CusID, [F1]+[F2]+[F3]+[F4]+[F5] AS FeatureCount, IIf(IsNull([Feature 1]),0,1) AS F1, IIf(IsNull([Feature 2]),0,1) AS F2, IIf(IsNull([Feature 3]),0,1) AS F3, IIf(IsNull([Feature 4]),0,1) AS F4, IIf(IsNull([Feature 5]),0,1) AS F5
FROM tbl_Inventory;
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20104690
What you really need is a new table, with three fields: CusID, FeatureNo, Feature. The primary key is CusID + FeatureNo. It is related to tblInventory by CusID. This way you're not restricted to five features. You will continue to run into headaches with SQL and whatever unless you adopt this structure.

That said, with the structure you have, I'd do it in code. To get the feature count for one CusID

  SQL = "SELECT * FROM tblInventory WHERE CusID='" & CusID  & "'"
   Set rs = CurrentDB.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
  rs.MoveFirst
  FeatureCount = IIf(IsNull([Feature 1]),0,1) + ...+ IIf(IsNull([Feature 5]),0,1)
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20104770
You are absolutely right, i already ran into a problem with the next part of the code, writing the html.

I'll create a table as you suggested and apply the old suggested solution.
I was just creating a new question as we speak.
0
 
LVL 1

Author Comment

by:Quadeeb2003
ID: 20110276
Is there a forum i can post this as a job too?  My limited skills have diminished in time, and i can't get it done.
0
 
LVL 4

Expert Comment

by:seameadow
ID: 20122568
Try googling rent-a-coder
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question