I'm not sure what's going on. I tried search online for that error, and it seems that it usually happens to people running Access 2K. Do you have any OLE objects on your form? Is your test environment a new database?
I'm guessing the error doesn't break to a specific code line, or you would have posted it. I would try to see what in the code is triggering the error message. First comment out all the code in the On Load event. See if the form opens. From what I've read about that error, it may not even be the code at all. If so, add some of the code back in. Keep playing until you find what specific line is triggering the error. Good luck, Toby ----- Original Message ----- From: "Patty" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Tuesday, June 14, 2005 2:04 PM Subject: [AccessDevelopers] Re: Error w/ dynamic Form using Datasheet View > Hi Toby, I created a test environment without the subform design to > simplify my test. However, when I open my form in datasheet view, I > received the following error message: > > "The expression On Load you entered as the event property setting > produced the following error: A problem occurred while Microsoft > Access was communicating with the OLE server or ActiveX Control" > > My vba references are set to use DAO not ADO, not ActiveX etc. so > this message completely baffles me. Can you offer any suggestions to > trouble-shoot this problem? > > Thanks, > Patty > > --- In [email protected], "Patty" <[EMAIL PROTECTED]> > wrote: > > Thanks again Toby, you're awesome! I'll set this up now and > > hopefully it will resolve this issue so I can move on with my > > project. You've been such a great help!! : < ) > > --- In [email protected], "Toby Bierly" <[EMAIL PROTECTED]> > > wrote: > > > In your original email, you were using text boxes named txtData1, > > txtData2, > > > txtData3, . . ., txtData25. These text box names were hard > coded. > > If you > > > dynamically change the text box names so that the Datasheet > headers > > are the > > > query's field names, your hard coded txtData# names no longer > work. > > > > > > I don't see any way of determining at run time what the text box > > names are > > > from the last query. That is, they no longer follow the naming > > convention > > > that you were relying on. I believe you need to store these > names > > in a > > > table. > > > > > > So in your Form_Load event, you must change the text box names to > > match your > > > recordsource query's field names, and then store each of these in > a > > table. > > > The table should have at least two fields: > > > > > > Position Number > > > TextBoxName Text > > > > > > For initial setup, I would fill the Postion column with the > numbers > > 1-25, > > > and the TextBoxName column with txtData1 - txtData25. > > > > > > Then, instead of > > > ============================================= > > > intField = 1 > > > For Each fld In Me.Recordset.Fields > > > Me.Controls("txtData" & intField).ControlSource = fld.Name > > > Me.Controls("txtData" & intField).Visible = True > > > Me.Controls("lblData" & intField).Caption = fld.Name > > > Me.Controls("lblData" & intField).Visible = True > > > intField = intField + 1 > > > Next > > > > > > For intField = intField To 25 > > > Me.Controls("txtData" & intField).Visible = False > > > Me.Controls("lblData" & intField).Visible = False > > > Next > > > ============================================= > > > > > > you could use > > > > > > ============================================= > > > intField = 1 > > > For Each fld In Me.Recordset.Fields > > > textBoxName = DLookup > ("TextBoxName", "tbl_TextBoxNames", "Position > > = " & > > > intField) > > > Me.Controls(textBoxName).ControlSource = fld.Name > > > Me.Controls(textBoxName).Visible = True > > > Me.Controls("lblData" & intField).Caption = fld.Name > > > Me.Controls("lblData" & intField).Visible = True > > > ' Must Change The Text Box Names, but can it be done from the > Load > > event? > > > ' (since you must open the form in design view to change the > > names) > > > CurrentDb.Execute "Update tbl_TextBoxNames Set TextBoxName = '" > & > > fld.Name > > > & "' Where Position = " & intField > > > intField = intField + 1 > > > Next > > > > > > > > > For intField = intField To 25 > > > textBoxName = DLookup > ("TextBoxName", "tbl_TextBoxNames", "Position > > = " & > > > intField) > > > Me.Controls(textBoxName).Visible = False > > > Me.Controls("lblData" & intField).Visible = False > > > Next > > > ============================================= > > > > > > Now the problem is incorporating changing the the Text Box > Names. > > You can't > > > do this from the Load Event since you have to open the form > > separately in > > > design mode to change the names. I think the solution would be > to > > add > > > another field to tbl_TextBoxNames called "NewTextBoxName" that > you > > could > > > store the names to be changed to. Then, at the end of your Load > > Event, you > > > could close the form and call a module that changes the Text Box > > Names and > > > then reopens the form. When you reopen the form from the module, > > you would > > > need to skip the Load event, so we could set the OpenArgs > > argument. Here is > > > revised code: > > > > > > ============================================= > > > Private Sub Form_Load() > > > Dim fld As DAO.Field > > > Dim intField As Integer > > > Dim textBoxName As String > > > Dim numOfField As Integer > > > > > > If Me.OpenArgs = "No Load" Then Exit Sub > > > > > > intField = 1 > > > For Each fld In Me.Recordset.Fields > > > textBoxName = DLookup > ("TextBoxName", "tbl_TextBoxNames", "Position > > = " & > > > intField) > > > Me.Controls(textBoxName).ControlSource = fld.Name > > > Me.Controls(textBoxName).Visible = True > > > Me.Controls("lblData" & intField).Caption = fld.Name > > > Me.Controls("lblData" & intField).Visible = True > > > CurrentDb.Execute "Update tbl_TextBoxNames Set NewTextBoxName > = '" > > & > > > fld.Name & "' Where Position = " & intField > > > intField = intField + 1 > > > Next > > > > > > > > > numOfFields = intField - 1 > > > > > > For intField = intField To 25 > > > textBoxName = DLookup > ("TextBoxName", "tbl_TextBoxNames", "Position > > = " & > > > intField) > > > Me.Controls(textBoxName).Visible = False > > > Me.Controls("lblData" & intField).Visible = False > > > Next > > > > > > DoCmd.Close acForm, "FormName" > > > Call ChangeTextBoxNames(numOfFields) > > > > > > End Sub > > > ============================================= > > > > > > Then the following would go in a Module: > > > > > > ============================================= > > > Sub ChangeTextBoxNames(numOfFields As Integer) > > > Dim OldName, NewName > > > > > > DoCmd.SetWarnings False > > > DoCmd.OpenForm "FormName", acDesign > > > > > > For i = 1 To numOfFields > > > OldName = DLookup > ("TextBoxName", "tbl_TextBoxNames", "Position > > = " & i) > > > NewName = DLookup > > ("NewTextBoxName", "tbl_TextBoxNames", "Position = " & > > > i) > > > Forms("FormName").Controls(OldName).Name = NewName > > > CurrentDb.Execute "Update tbl_TextBoxNames Set TextBoxName > > = '" & > > > NewName & "' Where Position = " & i > > > Next i > > > > > > DoCmd.Close acForm, "FormName" > > > DoCmd.SetWarnings True > > > > > > DoCmd.OpenForm "FormName",,,,,,"No Load" > > > End Sub > > > ============================================= > > > > > > Hopefully the form's role as a subform doesn't complicate things > > more. I > > > hope this works for you. Good luck. > > > > > > HTH, > > > Toby > > > > > > ----- Original Message ----- > > > From: "Patty" <[EMAIL PROTECTED]> > > > To: <[email protected]> > > > Sent: Tuesday, June 14, 2005 6:59 AM > > > Subject: [AccessDevelopers] Re: Error w/ dynamic Form using > > Datasheet View > > > > > > > > > > Hi Toby, Thank you for this info; I'll be working with it now; > if > > you > > > > have further insights re: info in < > below, I'd very much > > appreciate > > > > it! : ) > > > > > > > > <.....Another problem here is knowing what ControlName to put > in > > that > > > > third line of code, since you are trying to do it all > dynamically. > > > > I'd have to think about it more, but I'm not sure if there is > an > > easy > > > > way to know which order the Controls are in. You may have to > > create > > > > a table to contain the Control Names from 1-25, then when you > grab > > > > your query's field names, insert these into that table in order > > and > > > > then use the table to reset all your Control Names, and also > know > > > > which controls to set to Visible = False......> > > > > > > > > --- In [email protected], "Toby Bierly" > <[EMAIL PROTECTED]> > > > > wrote: > > > > > It is not the control source or caption that provides the > name > > that > > > > shows up > > > > > in the column headers on a datasheet, but rather the Name of > the > > > > textbox > > > > > when you edit the form in Design View. > > > > > > > > > > Change the NAMES of the text boxes, and you will affect the > > columns > > > > headers. > > > > > > > > > > The problem then becomes the difficulty of changing the names > > with > > > > VBA code. > > > > > You have to be in design view to edit the text box names. > > Further, > > > > you have > > > > > to disable the warnings or you will be prompted to confirm the > > > > saving of any > > > > > changes. The following works for me in a quick little test > > > > database: > > > > > > > > > > DoCmd.SetWarnings False > > > > > DoCmd.OpenForm "FormName", acDesign > > > > > Forms("FormName").Controls("ControlName").Name > = "OtherName" > > > > > DoCmd.Close acForm, "FormName" > > > > > DoCmd.SetWarnings True > > > > > > > > > > Another problem here is knowing what ControlName to put in > that > > > > third line > > > > > of code, since you are trying to do it all dynamically. I'd > > have > > > > to think > > > > > about it more, but I'm not sure if there is an easy way to > know > > > > which order > > > > > the Controls are in. You may have to create a table to > contain > > the > > > > Control > > > > > Names from 1-25, then when you grab your query's field names, > > > > insert these > > > > > into that table in order and then use the table to reset all > > your > > > > Control > > > > > Names, and also know which controls to set to Visible = False > > > > > > > > > > HTH, > > > > > Toby > > > > > > > > > > ----- Original Message ----- > > > > > From: "Patty" <[EMAIL PROTECTED]> > > > > > To: <[email protected]> > > > > > Sent: Friday, June 10, 2005 5:23 PM > > > > > Subject: [AccessDevelopers] Error w/ dynamic Form using > > Datasheet > > > > View > > > > > > > > > > > > > > > > In my parent form, I have two child subforms as follows: 1 > is > > the > > > > > > subform employee header; it defaults to single form view. > The > > > > second > > > > > > is the subform hours; it defaults to datasheet view and > > contains > > > > > > hours-worked detail. Using datasheet view allows (1) user > to > > use > > > > > > built-in Access functionality to hide/unhide columns at > will, > > and > > > > (2) > > > > > > supposedly: when the dynamic query (qryHeadings) linked to > the > > > > > > datasheet subform changes to include new column > > headings/columns, > > > > > > then those new column headings/columns would refresh the > > linked > > > > > > subform datasheet the next time that subform was opened. I > > set up > > > > the > > > > > > design to do this as follows: SQL brings in different fields > > > > > > depending on the contents of a table. This table doesn't > > change > > > > > > often, but when it does, it can pull new fields with new > > column > > > > > > headings. Here is the VBA code that refreshes my > qryHeadings: > > > > > > > > > > > > > > > > > > Function test() > > > > > > Dim dbs As Database > > > > > > Dim qdf As QueryDef > > > > > > > > > > > > Dim rst As Recordset > > > > > > Dim strQuery As String > > > > > > Dim strSQL As String > > > > > > Dim Frst As Integer > > > > > > Set dbs = CurrentDb() > > > > > > Set qdf = dbs.QueryDefs("qryHeading") > > > > > > > > > > > > Frst = 1 > > > > > > > > > > > > strSQL = "SELECT FieldName, FieldLabel FROM tblColOrder " & > _ > > > > > > "WHERE (((tblColOrder.Active) = Yes)) ORDER BY > > tblColOrder.Seq;" > > > > > > > > > > > > Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot) > > > > > > > > > > > > rst.MoveFirst > > > > > > > > > > > > strQuery = "select " > > > > > > > > > > > > Do While Not rst.EOF > > > > > > If Frst <> 1 Then > > > > > > strQuery = strQuery & ", " > > > > > > Else > > > > > > Frst = 0 > > > > > > End If > > > > > > > > > > > > strQuery = strQuery & rst("FieldName").Value & " as [" & rst > > > > > > ("FieldLabel").Value & "] " > > > > > > rst.MoveNext > > > > > > Loop > > > > > > > > > > > > rst.Close > > > > > > strQuery = strQuery & " from tblHours;" > > > > > > > > > > > > qdf.SQL = strQuery > > > > > > > > > > > > End Function > > > > > > > > > > > > Using the above with the below code (to impact the form > > itself) > > > > works > > > > > > beautifully when I view my subform in 'continous-form' > > or 'single- > > > > > > form' form view. However, when I use that solution and view > my > > > > > > subform in 'datasheet' form view, I see the correct fields > in > > the > > > > > > correct location yet the field headings (aka column > headings), > > > > oddly > > > > > > enough, display txtData1, txtData2, etc. regardless of what > I > > > > place > > > > > > in textbox name, or label name, or label caption, or even > if I > > > > > > eliminate label name altogether. It appears that datasheet > > form > > > > view > > > > > > picks up what is placed in each textbox controlsource, > which > > in my > > > > > > case is, literally =[txtData1], =[txtData2] etc., and uses > the > > > > > > nonbracketed portion as the column/field heading for the > > > > appropriate > > > > > > field. Can someone comment on how to resolve the datasheet > > view > > > > > > problem? > > > > > > > > > > > > Private Sub Form_Load() > > > > > > Dim fld As DAO.Field > > > > > > Dim intField As Integer > > > > > > > > > > > > intField = 1 > > > > > > For Each fld In Me.Recordset.Fields > > > > > > Me.Controls("txtData" & intField).ControlSource = fld.Name > > > > > > Me.Controls("txtData" & intField).Visible = True > > > > > > Me.Controls("lblData" & intField).Caption = fld.Name > > > > > > Me.Controls("lblData" & intField).Visible = True > > > > > > intField = intField + 1 > > > > > > Next > > > > > > > > > > > > For intField = intField To 25 > > > > > > Me.Controls("txtData" & intField).Visible = False > > > > > > Me.Controls("lblData" & intField).Visible = False > > > > > > Next > > > > > > > > > > > > End Sub > > > > > > > > > > > > PLEASE NOTE: When I inserted this code as a solution > > <Me.Controls > > > > > > ("txtData" & intField).Name = fld.Name>, I received the > > following > > > > > > Visual Basic dialogue box error message: > > > > > > > > > > > > <Run-time error '2136': To set this property, open the form > or > > > > report > > > > > > in Design view.> > > > > > > > > > > > > Thanks for any assistance! Patty > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > Yahoo! Groups Links > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links > > > > > > > Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
