If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Create datasheet in MDE
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 |
#2
|
|||
|
|||
Create datasheet in MDE
On Tue, 18 May 2010 12:57:59 -0400, "DRedDog"
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 |
#3
|
|||
|
|||
Create datasheet in MDE
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" wrote in message ... On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" 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 |
#4
|
|||
|
|||
Create datasheet in MDE
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" wrote in message ... 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" wrote in message ... On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" 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 |
#5
|
|||
|
|||
Create datasheet in MDE
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" 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 |
#6
|
|||
|
|||
Create datasheet in MDE
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... On Tue, 18 May 2010 12:57:59 -0400, "DRedDog" 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 |
#7
|
|||
|
|||
Create datasheet in MDE
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/For...forms/201005/1 |
#8
|
|||
|
|||
Create datasheet in MDE
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 |
#9
|
|||
|
|||
Create datasheet in MDE
"Douglas J. Steele" wrote in
: 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/ |
Thread Tools | |
Display Modes | |
|
|