Create datasheet in MDE
in [Form]
|
Prev: Microsoft Word Table design in Access 2007
Next: Filter by form search field showing non-distinct values in pull do
From: Douglas J. Steele on 19 May 2010 08:52 There is no way to do it. CreateControl (and presumably DeleteControl) will not work in an MDE. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "DRedDog" <dreddog69(a)hotmail.com> wrote in message news:O$1jS%2309KHA.1892(a)TK2MSFTNGP05.phx.gbl... > Thank you, Douglas J. Steele. I will probably use the method you have > suggested if there is really no way to create/delete controls in the MDE. > But I am still hoping to find a way to do this. > > DT > > "Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in message > news:eSo1IH09KHA.420(a)TK2MSFTNGP02.phx.gbl... >> Create a form that has the maximum number of text boxes you think you'll >> need, and simply set the Visible property to False for the unneeded ones. >> >> -- >> Doug Steele, Microsoft Access MVP >> http://www.AccessMVP.com/DJSteele >> (no e-mails, please!) >> >> "DRedDog" <dreddog69(a)hotmail.com> wrote in message >> news:OgBYVA09KHA.4316(a)TK2MSFTNGP04.phx.gbl... >>> Thanks for your response. Assigning the SQL to the form's recordsource >>> property is not the problem - it is adding/deleting controls that the >>> MDE database objects to. >>> Troubleshooting the MDE is a little awkward but I think the offending >>> line might be -: >>> >>> DeleteControl strForm, Forms(strForm).Controls(0).name >>> >>> This is the sub that creates the datasheet form in the mdb file. >>> >>> Sub MakeRptDataForm(strForm As String, strQuery As String) >>> Dim ctl As Control >>> Dim fld As Field >>> Dim qdf As DAO.QueryDef >>> Dim db As DAO.Database >>> Dim sngTop As Single >>> >>> On Error GoTo Err_MakeRptDataForm >>> DoCmd.OpenForm strForm, acDesign, , , , acHidden >>> >>> Do Until Forms(strForm).Controls.Count = 0 >>> DeleteControl strForm, Forms(strForm).Controls(0).name >>> Loop >>> >>> Forms(strForm).RecordSource = strQuery >>> Set db = CurrentDb >>> Set qdf = db.QueryDefs(strQuery) >>> sngTop = 0 >>> For Each fld In qdf.Fields >>> CreateControl strForm, acTextBox, acDetail, , fld.name, 0, sngTop >>> sngTop = sngTop + 0.725 * 567 >>> Next fld >>> >>> For Each ctl In Forms(strForm).Controls >>> If ctl.ControlType = acTextBox Then >>> ctl.name = ctl.ControlSource >>> End If >>> Next ctl >>> >>> Exit_MakeRptDataForm: >>> DoCmd.SetWarnings False >>> DoCmd.Close acForm, Forms(strForm).name >>> DoCmd.SetWarnings True >>> Exit Sub >>> >>> Err_MakeRptDataForm: >>> Select Case Err >>> Case Else >>> MsgBox Err.Description >>> 'Stop >>> Resume Exit_MakeRptDataForm >>> >>> End Select >>> End Sub >>> >>> "Tom van Stiphout" <tom7744.no.spam(a)cox.net> wrote in message >>> news:d1t6v5hjlf6lh528uk6vh2nmirlnl5b977(a)4ax.com... >>>> On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" <dreddog69(a)hotmail.com> >>>> wrote: >>>> >>>> What command? >>>> Surely MDE would allow you to create a sql statement and assign that >>>> to the RecordSource of a form? >>>> >>>> -Tom. >>>> Microsoft Access MVP >>>> >>>> >>>>>My application creates a datasheet on the fly. Users make a selection >>>>>from a >>>>>set parameters including column names, sort order, etc and the code >>>>>populates a querydef with the set of columns selected by the user. >>>>> >>>>>This works fine in an MDB. The equivalent MDE crashes - "That command >>>>>isn't >>>>>available in an MDE database" >>>>> >>>>>Please tell me how to add/delete columns from a querydef in an MDE >>>>>file? >>>>> >>>>>DT >>> >> >>
From: Linq Adams via AccessMonster.com on 19 May 2010 10:40 The whole point of an MDE file is to prevent users from making design changes, which is exactly what you were attempting to do. You'll have to follow Doug's suggestion. -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201005/1
From: DRedDog on 19 May 2010 15:07 Linq Adams wrote: >The whole point of an MDE file is to prevent users from making design changes, >which is exactly what you were attempting to do. You'll have to follow Doug's >suggestion. > Hmmm..... I heard somewhere that there is always more than one way to skin a cat. Sorry! DT
From: David W. Fenton on 19 May 2010 18:12
"Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in news:OKzvsI19KHA.5476(a)TK2MSFTNGP06.phx.gbl: > There is no way to do it. CreateControl (and presumably > DeleteControl) will not work in an MDE. And, of course, even if it *did* work, after using it a few times, the form would no longer be changeable, as the maximum number of controls (700-something) has been used up. In creating the Access replication conflict resolver for Access 2000, Michael Kaplan used the method of adding all the controls and then hiding/showing them as needed. If that's good enough for a genius like MichKa, it's good enough for me! -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |