Solved

VBA Code to transfer ParentID when New record Inserted into Hierarchical Lookup Table

Posted on 2013-01-26
7
712 Views
Last Modified: 2013-02-02
I am trying to transfer a ParentID value to the DMP_Lookup table in my sample ASP database application.   The "Town" lookup values are stored in a self-referencing hierarchy table. They are provided to the application using a query "qryTown".

The application gives you the option to "Add" new values while editing a data table form.  When I add a new town name (ie Lookup), it does NOT transfer the ParentID that is present in the query. In this case the ParentID = 1, but could be any integer.

The form generator application I am using is AspMaker 10.1.  The project is in the attached file (no its not because EE is munted).  The program is 17mb and is free to demo. The AspMaker project file is "LookupHierarchyTest.agp".  This project is setup with the folder and database path on the root of the c drive.

The support staff sent me the following code to include on the "Row_Inserting" server event. It works if you place in the Parent ID value in, but I want that to be parsed without the need for user intervention.  The user ONLY needs to add the Lookup name.

    
Dim ar, ParentName, ParentID
ar = Split(rsnew("Name"), " ") ' Split the name to get table name
ParentName = ar(0)
If ParentName <> rsnew("Name") Then
ParentID = ew_ExecuteScalar("SELECT ID FROM <Table> WHERE Name = '" & ew_AdjustSql(ParentName) & "'")
Else
ParentID = 0
End If
rsnew("ParentID") = ParentID

Open in new window


I would have added the project as a zip or rar but this site is so backwards you cant use file types (eg rar, js, agp etc etc) that would actually be used in a professional programming support website. It would be too bad if I needed help with a javascript file. maybe the site should be "Almost EE"....
LookupHierarchy.mdb
AddLookupSample1.gif
AddLookupSample2.gif
0
Comment
Question by:XGIS
  • 3
  • 3
7 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38823522
I would have added the project as a zip or rar but this site is so backwards you cant use file types (eg rar, js, agp etc etc) that would actually be used in a professional programming support website.
A professional programer or somebody trying to learn to be professional would:
Understand the reason for not allowing the upload of certain files
Would be able to formulate a more concise question that would not require downloading of your entire project to answer your question  
Write your own code and try and understand the logic rather then using a tool to generate code to compensate for your lack of skills  
It would be too bad if I needed help with a javascript file. maybe the site should be....
If you need help with javascript, you would place a code snipit
window.displaymessage= function ()
{
alert("Hello World!");
}

Open in new window


Or you could place your files in jsbin or jsfiddle http://jsfiddle.net/praveen_prasad/XNJxT/14/
Or you could have a working sample on your own site and link to that.

As far as your question, it is confusing, "The support staff sent me the following code to include on the "Row_Inserting" server event. It works if you place in the Parent ID value in, but I want that to be parsed without the need for user intervention.  The user ONLY needs to add the Lookup name."
What exactly does this script do?  It looks like it is meant to add look up items on the admin end?  How can it do something without user intervention?   If I enter the lookup name, what is supposed to happen?

From your 2nd image, it looks like you click on the link, "Add Look Up ID".  You then get a pop up to add the lookup field and ID it is supposed to link to.  Are you asking to have the link ID auto populated?  If that is the case, you simply need to add <%=theID%> to the form field like, <input name="somename" type="text" value="<%=theID%>">.  You will grab the id code I assume from the same place you see the Site ID in the light purple area (the number 1).

You will probably have to do some hand coding for this.  That is one of the problems with code generators.  They can only do so much without becoming more overly complex then they already are.
0
 
LVL 7

Author Comment

by:XGIS
ID: 38823958
the sql needs to do this
NewRecord.gif
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38824152
There are multiple ways to do this.  The way I am showing you is the easiest to show but not really the most efficient.  If your database is small and you don't have a lot of users, it will not make much difference.  

<%
'create rsLookup LookUpID, Lookup, LookUpParentID
sql="select * from myTable"


%>
<table>
<%
if not rsLookup.bof or not rsLookup.eof then
do until rsLookup.eof
%>
<tr>
<td><a href="#">View</a></td>
<td><a href="#">Edit</a></td>
<td><a href="#">Copy</a></td>
<td><a href="#">Delete</a></td>
<td><%=LookupID%></td>
<td><%=Lookup%></td>
<td><%=LookupParentID%></td>
</tr>
<%
rsLookup.movenext
Loop
end if
%>
</table>

Open in new window


The more efficient way is to use getrows() and create an array.
' create a query/view with only the fields you will use

if not rs.eof or not rs.bof then
  myArray=rs.getrows()
end if
' in this case you have 3 fields and arrays are zero based
for x = 0 to ubound(MyArray,2)
 theRow="<tr>"
 theRow=Row&"<td><a href=""#"">View</a></td>"
 theRow=Row&"<td><a href=""#"">Edit</a></td>"
 theRow=Row&"<td><a href=""#"">Copy</a></td>"
 theRow=Row&"<td><a href=""#"">Delete</a></td>"
 theRow=Row&"<td>"&MyArray(0,x)&"</td>"
 theRow=Row&"<td>"&MyArray(1,x)&"</td>"
 theRow=Row&"<td>"&MyArray(2,x)&"</td>"
 theRow=Row&"</tr>"
  response.write theRow  'just one response.write per row.  With proper logic, you can built the entire table this way and have just one response.write for the entire table.
next

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 38824598
XGIS,

You might find this helps you to formulate better questions and make it easier for Experts to help you.


Cd&
0
 
LVL 7

Accepted Solution

by:
XGIS earned 0 total points
ID: 38826555
Solved without code. Thankyou for your assistance. The software supplier support overlooked this solution. The form generator creates queries with controls that become active when you edit and update values in the data table. These values are filtered by parentID values. The access lookup table has 100% referential integrity and even comes with a null value in the root record for hierarchical .NET databound menus or treeviews. Attached is the MS Access 2003 database with sample infrastructure. Only 2 tables are required. The real world database data table contains 200+ fields, 500+ rows and 50 unique virtual tables with unknown total lookup values. It is fast and reliable. That is why crud is out and auto generate is in, even if it is only an access database. No point wasting a week when a machine does it in 60 seconds. Someone may find this of use.
AspMaker10-ParentIDQuestionSolve.gif
AspMaker10-ParentIDQuestionSolve.gif
AspMaker10-ParentIDQuestionSolve.gif
LookupHierarchy.zip
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38826579
Good luck with that
0
 
LVL 7

Author Closing Comment

by:XGIS
ID: 38846363
Solved by XGIS
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now