Full Version: Custom Activex Control Parameter Vs Property?
UtterAccess Forums > Microsoft® Access > Access Forms
skippy116
I made a custom activex control on a form for plotting values entered in a text box. It works in Access 2003, but won't work in Access 2007 or 2010. The activex has a property called "dim1" with a default value of 0. In response to the "updated" event, it calls an update query (via DoCmd.SQL) to write the entry to the table. The SQL reads as follows:
CODE

sql = "UPDATE SPCData SET SPCData.d" & intrownum & strcolref & " = Forms!SPCEntry!SPCcolumn" & intcolref & "." & strfieldref & " " & _
"WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"

When the variables are filled in, this translates to:
CODE
sql = "UPDATE SPCData SET SPCData.d1a = Forms!SPCEntry!SPCcolumn1.dim1 WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"

Unfortunately, it now always prompts me to enter the "parameter" dim1. Something I'm confused about is when I look at the properties of the SPCcolumn1 in the VBA editor, there is no dim1 (or any other custom property of the SPCcolumn1 control), but when I look at the property sheet of the ActiveX control in form design view, it lists all the custom properties including dim1 (which has a value of zero). How do I get the code to "see" the properties of the activex control?
MikeLyons
Usually with ActiveX controls you need to refer to the control's Object property, otherwise what you're actually referring to is the ActiveX control container object on the form. The fix should be simple, if this is indeed the issue. Have your code result in:
ql = "UPDATE SPCData SET SPCData.d1a = Forms!SPCEntry!SPCcolumn1.Object.dim1 WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"
Mike
skippy116
Thanks for your response Mike, unfortunately it just prompted for the Forms!SPCEntry!SPCcolumn1.Object.dim1 parameter!
I have some more information on this problem:
I've characterized this problem a bit more. It seems to be that you can't construct a string using a control property in Access 2007 or 2010, but you can in 2003. In my example,
CODE
Dim intrownum, intcolref as integer
Dim strcolref, strfieldref as string

intrownum=1
strcolref="a"
intcolref=1
strfieldref="dim1"
sql = "UPDATE SPCData SET SPCData.d" & intrownum & strcolref & " = Forms!SPCEntry!SPCcolumn" & intcolref & "." & strfieldref & " " & _
"WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"
'becomes the following and it incorrectly prompts the user to enter a value for the "parameter" dim1 rather than retrieving it from the SPCcolumn1 property "dim1".
sql = "UPDATE SPCData SET SPCData.d1a = Forms!SPCEntry!SPCcolumn1.dim1 WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"

If, however, I enter the following line it evaluates to the value of the dim1 property in the SPCcolumn1 control, as desired
aaa = Forms!SPCEntry!SPCcolumn1.dim1
Now, if I enter:
CODE
Dim aaa As String
aaa = "Forms!SPCEntry!SPCcolumn" & intcolref & "." & strfieldref
Debug.Print Eval(aaa)

It gives the error:
runtime error '31005':
Access failed to evaluate one or more expressions because 'dim1' was referenced in an expression.
Note that in Access 2003, the original sql expression runs fine when "DoCmd.RunSQL sql" is run. In Access 2007 and 2010, it doesn't work. I've been telling people don't upgrade from 2003 to 2007 if using this database but people are increasingly unhappy. What can I do? Does Access 2007/2010 provide a way to evaluate a concatenated expression using a control property?
MikeLyons
Okay how about this approach:
If dim1 is a numeric property....
CODE
sql = "UPDATE SPCData SET SPCData.d1a = " & Forms!SPCEntry!SPCcolumn1.dim1  & " WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"

If it's a text type property...
CODE
sql = "UPDATE SPCData SET SPCData.d1a = """ & Forms!SPCEntry!SPCcolumn1.dim1  & """ WHERE (((SPCData.DatasetID)=[Forms]![SPCEntry]![DatasetID]))"

Since the issue seems to be that the query processor in Access cannot process the reference to the property on the control, then just embed the property value.
See if this works and if not, we'll have to see if we can find something else to try.
Mike
skippy116
Well, the problem is that there's 12 columns and 12 rows... so I won't really know which of SPCcolumn? or dim? will be updated until another property of the control called CurrentField is read in the same function. I really need to write this sql statement on the fly... I could write an sql statement in each of the 12 column updated events (Private Sub SPCcolumn11_Updated(Code As Integer)), but it only solves part of the problem. O... hmm. This gives me a way out... I could write a Select statement with the 12 options in each of the 12 column updated events, each select option being one sql statement! iconfused.gi
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>MikeLyons</div>
  <div class='postdate'>Oct 19 2011, 10:34 AM</div>
 </div>
 <div class='postcontent'>
  What I often do when I need to dynamically build SQL is wrap it into a function and pass the values that determine how the variable parts get built as arguments.  The function then returns the completed SQL statement ready for use as needed.<br />ike
 </div>
</div>
  </div>
  <div class='smalltext'>This is a click here.