A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create datasheet in MDE



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 05:57 PM posted to microsoft.public.access.forms
DRedDog
external usenet poster
 
Posts: 10
Default 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  
Old May 19th, 2010, 06:17 AM posted to microsoft.public.access.forms
Tom van Stiphout[_2_]
external usenet poster
 
Posts: 1,653
Default 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  
Old May 19th, 2010, 11:35 AM posted to microsoft.public.access.forms
DRedDog
external usenet poster
 
Posts: 10
Default 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  
Old May 19th, 2010, 11:55 AM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old May 19th, 2010, 01:25 PM posted to microsoft.public.access.forms
DRedDog
external usenet poster
 
Posts: 10
Default 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  
Old May 19th, 2010, 01:52 PM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default 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  
Old May 19th, 2010, 03:40 PM posted to microsoft.public.access.forms
Linq Adams via AccessMonster.com
external usenet poster
 
Posts: 1,474
Default 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  
Old May 19th, 2010, 08:07 PM posted to microsoft.public.access.forms
DRedDog[_2_]
external usenet poster
 
Posts: 1
Default 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  
Old May 19th, 2010, 11:12 PM posted to microsoft.public.access.forms
David W. Fenton
external usenet poster
 
Posts: 3,373
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:06 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.