Header footer in datasheet view...

[hzgt9b]

I am linking tables into my mdb and need to programatically open them for my
user for review of the data...

I was planning on using the DoCmd.OpenTable "TableName"... method to perform
this... the problem is, that how do I determine when the table is closed?
(After closing the table, I need acknowledgement from the user about the
state of the data)...

Any suggestions?

 

[Duane Hookom]

Use your table as the record source for a form. You can open the form to
display the records. Forms have event code that you can use.

 

[Marshall Barton]

[email protected]

I am linking tables into my mdb and need to programatically open them for my
user for review of the data...

I was planning on using the DoCmd.OpenTable "TableName"... method to perform
this... the problem is, that how do I determine when the table is closed?
(After closing the table, I need acknowledgement from the user about the
state of the data)...

Don't display a table's sheet to user's. Create a form in
continuous view that looks like a datasheet. Then you can
use the form header/footer section's for command button's
for users to indicate what they intend for you to do. If
you don't display the form's X (close) button in the title
bar, then you can provide your own confirm button and
perform whatever actions you want.

 

[hzgt9b]

Duane,
Thanks for the quik reply... yes, I know how to use the table as the
recordsource for the form... but I need to present the data in datasheet
view... and with that no or header or footer controls are displayed (that's
where I had hoped to put some acknowledgement buttons/controls)...

Any other thoughts?

 

[Joshua A. Booker]

Forms have a datasheet view if you wan to use it. But Marshall's idea of
making a continuous form look like datasheet is the only way to have a
header and footer.

Alternatively, you could make a custom toolbar, but the code doesn't travel
neatly with the form that way.

HTH,
Josh

 

[hzgt9b]

Good idea:
I want to make this very generic - so that I don't have create a form for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are displayed
as you suggest?

 

[Joshua A. Booker]

Have you tried the form wizard?

HTH,
Josh

Good idea:
I want to make this very generic - so that I don't have create a form for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are displayed
as you suggest?

 

[hzgt9b]

Joshua,
I'm trying to avoid manually creating and maintaining these forms... any
suggestions on building these forms programmatically?

something like:

set myForm's recordsoure = myTable
for each myField in myTable
add myField to myForm... ??? how do I do this?
next myField

of course then next problem I'll have is detecting events in the form...
hmm...

 

[Marshall Barton]

You do not want to create a form programmatically at run
time. This would have all kinds of negative ramifications.

It is much better to pre create a form with the required
buttons and (their event procedures) in the form footer.
Add more invisible text boxes in the detail and labels in
the header than you will need for any table. Use a combo
box in the form's header section to selected the table for
the form's RecordSource.

You can then set the needed text box's and label's Visible
property, ControlSource or Caption and their Left and Width
properties. This is actually easier than creating the form
and all its controls on the fly.

If you name the text boxes and labels with a common prefix
and a sequential numeric suffix (eg txt0, txt1, txt2,... and
lbl0, lbl1, lbl2,...), the whole thing can be done in a
loop.

Here's some code to demonstrate the kind of logic involved:

Private Sub cboTables_AfterUpdate()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim k As Long
Dim lngWidth As Long
Dim lngTotalWidth As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(Me.cboTables)
Me.RecordSource = Me.cboTables
Me.cboTables.SetFocus

For k = 0 To Me.Section(0).Controls.Count - 1
Me("txt" & k).Visible = False
Me("lbl" & k).Visible = False
Me("txt" & k).Left = 0
Me("lbl" & k).Left = 0
Me("txt" & k).Width = 0
Me("lbl" & k).Width = 0
Next k

k = 0
For Each fld In tdf.Fields
Me("txt" & k).Visible = True
Me("lbl" & k).Visible = True
Me("txt" & k).ControlSource = fld.Name
Me("lbl" & k).Caption = fld.Name

lngWidth = fld.Properties("ColumnWidth")
If lngWidth > 4000 Then lngWidth = 4000
If lngWidth = -1 Then lngWidth = 1000
Me.InsideWidth = lngTotalWidth + lngWidth
Me("txt" & k).Left = lngTotalWidth
Me("lbl" & k).Left = lngTotalWidth
Me("txt" & k).Width = lngWidth
Me("lbl" & k).Width = lngWidth
lngTotalWidth = lngTotalWidth + lngWidth
k = k + 1
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub

The combo box, cboTables, in the form's header that's used
to select the table has the RowSource query:

SELECT Name
FROM MSysObjects
WHERE Type = 1 And Left(Name ,4) <> "MSys"
ORDER BY Name

 

[Duane Hookom]

There is a simple solution to displaying either a table or a query as a
"subform" on a form. There is a simple demo of how to do this with a saved
query in the DynamicSubfrm at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I just modified this (on my PC) to include tables in the cboQueryName combo
box by setting the
Column Count: 2
Bound Column: 1
Row Source:
SELECT msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type) In (1,5)) AND ((Left([Name],2)) Not In
("~s","ms")));

Then modify the After Update event of the combo box to:
Private Sub cboQueryName_AfterUpdate()
If Me.cboQueryName = "qxtbMthly" Then
Call BuildXTabSQL
End If
Select Case Me.cboQueryName.Column(1)
Case 5 'query
Me.subformctrl.SourceObject = "Query." & Me.cboQueryName
Case 1 'table
Me.subformctrl.SourceObject = "Table." & Me.cboQueryName
End Select
End Sub

This will display virtually any table in the subform control with very
little code.

--
Duane Hookom
MS Access MVP
--

You do not want to create a form programmatically at run
time. This would have all kinds of negative ramifications.

It is much better to pre create a form with the required
buttons and (their event procedures) in the form footer.
Add more invisible text boxes in the detail and labels in
the header than you will need for any table. Use a combo
box in the form's header section to selected the table for
the form's RecordSource.

You can then set the needed text box's and label's Visible
property, ControlSource or Caption and their Left and Width
properties. This is actually easier than creating the form
and all its controls on the fly.

If you name the text boxes and labels with a common prefix
and a sequential numeric suffix (eg txt0, txt1, txt2,... and
lbl0, lbl1, lbl2,...), the whole thing can be done in a
loop.

Here's some code to demonstrate the kind of logic involved:

Private Sub cboTables_AfterUpdate()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim k As Long
Dim lngWidth As Long
Dim lngTotalWidth As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(Me.cboTables)
Me.RecordSource = Me.cboTables
Me.cboTables.SetFocus

For k = 0 To Me.Section(0).Controls.Count - 1
Me("txt" & k).Visible = False
Me("lbl" & k).Visible = False
Me("txt" & k).Left = 0
Me("lbl" & k).Left = 0
Me("txt" & k).Width = 0
Me("lbl" & k).Width = 0
Next k

k = 0
For Each fld In tdf.Fields
Me("txt" & k).Visible = True
Me("lbl" & k).Visible = True
Me("txt" & k).ControlSource = fld.Name
Me("lbl" & k).Caption = fld.Name

lngWidth = fld.Properties("ColumnWidth")
If lngWidth > 4000 Then lngWidth = 4000
If lngWidth = -1 Then lngWidth = 1000
Me.InsideWidth = lngTotalWidth + lngWidth
Me("txt" & k).Left = lngTotalWidth
Me("lbl" & k).Left = lngTotalWidth
Me("txt" & k).Width = lngWidth
Me("lbl" & k).Width = lngWidth
lngTotalWidth = lngTotalWidth + lngWidth
k = k + 1
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub

The combo box, cboTables, in the form's header that's used
to select the table has the RowSource query:

SELECT Name
FROM MSysObjects
WHERE Type = 1 And Left(Name ,4) <> "MSys"
ORDER BY Name
--
Marsh
MVP [MS Access]

I want to make this very generic - so that I don't have create a form for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are
displayed
as you suggest?

 

[hzgt9b]

Wow that's great - exactly what I was looking for... thanks for the help!

There is a simple solution to displaying either a table or a query as a
"subform" on a form. There is a simple demo of how to do this with a saved
query in the DynamicSubfrm at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I just modified this (on my PC) to include tables in the cboQueryName combo
box by setting the
Column Count: 2
Bound Column: 1
Row Source:
SELECT msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type) In (1,5)) AND ((Left([Name],2)) Not In
("~s","ms")));

Then modify the After Update event of the combo box to:
Private Sub cboQueryName_AfterUpdate()
If Me.cboQueryName = "qxtbMthly" Then
Call BuildXTabSQL
End If
Select Case Me.cboQueryName.Column(1)
Case 5 'query
Me.subformctrl.SourceObject = "Query." & Me.cboQueryName
Case 1 'table
Me.subformctrl.SourceObject = "Table." & Me.cboQueryName
End Select
End Sub

This will display virtually any table in the subform control with very
little code.

--
Duane Hookom
MS Access MVP
--

You do not want to create a form programmatically at run
time. This would have all kinds of negative ramifications.

It is much better to pre create a form with the required
buttons and (their event procedures) in the form footer.
Add more invisible text boxes in the detail and labels in
the header than you will need for any table. Use a combo
box in the form's header section to selected the table for
the form's RecordSource.

You can then set the needed text box's and label's Visible
property, ControlSource or Caption and their Left and Width
properties. This is actually easier than creating the form
and all its controls on the fly.

If you name the text boxes and labels with a common prefix
and a sequential numeric suffix (eg txt0, txt1, txt2,... and
lbl0, lbl1, lbl2,...), the whole thing can be done in a
loop.

Here's some code to demonstrate the kind of logic involved:

Private Sub cboTables_AfterUpdate()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim k As Long
Dim lngWidth As Long
Dim lngTotalWidth As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(Me.cboTables)
Me.RecordSource = Me.cboTables
Me.cboTables.SetFocus

For k = 0 To Me.Section(0).Controls.Count - 1
Me("txt" & k).Visible = False
Me("lbl" & k).Visible = False
Me("txt" & k).Left = 0
Me("lbl" & k).Left = 0
Me("txt" & k).Width = 0
Me("lbl" & k).Width = 0
Next k

k = 0
For Each fld In tdf.Fields
Me("txt" & k).Visible = True
Me("lbl" & k).Visible = True
Me("txt" & k).ControlSource = fld.Name
Me("lbl" & k).Caption = fld.Name

lngWidth = fld.Properties("ColumnWidth")
If lngWidth > 4000 Then lngWidth = 4000
If lngWidth = -1 Then lngWidth = 1000
Me.InsideWidth = lngTotalWidth + lngWidth
Me("txt" & k).Left = lngTotalWidth
Me("lbl" & k).Left = lngTotalWidth
Me("txt" & k).Width = lngWidth
Me("lbl" & k).Width = lngWidth
lngTotalWidth = lngTotalWidth + lngWidth
k = k + 1
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub

The combo box, cboTables, in the form's header that's used
to select the table has the RowSource query:

SELECT Name
FROM MSysObjects
WHERE Type = 1 And Left(Name ,4) <> "MSys"
ORDER BY Name
--
Marsh
MVP [MS Access]

I want to make this very generic - so that I don't have create a form for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are
displayed
as you suggest?


[email protected] wrote:
I am linking tables into my mdb and need to programatically open them
for my
user for review of the data...

I was planning on using the DoCmd.OpenTable "TableName"... method to
perform
this... the problem is, that how do I determine when the table is
closed?
(After closing the table, I need acknowledgement from the user about
the
state of the data)...


Don't display a table's sheet to user's. Create a form in
continuous view that looks like a datasheet. Then you can
use the form header/footer section's for command button's
for users to indicate what they intend for you to do. If
you don't display the form's X (close) button in the title
bar, then you can provide your own confirm button and
perform whatever actions you want.

 

[hzgt9b]

Duane,
I downloaded a couple your samples at rogersaccesslibrary... very nice. One
question that I acn't figure out... in the "DynamSubFrm.mdb" the "frmXTab"
form opens on open of the mdb... how are you doing this? The only way I knew
how to accomplish this was throught the autorun macro and I don't see that...

Thanks again for your help

There is a simple solution to displaying either a table or a query as a
"subform" on a form. There is a simple demo of how to do this with a saved
query in the DynamicSubfrm at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I just modified this (on my PC) to include tables in the cboQueryName combo
box by setting the
Column Count: 2
Bound Column: 1
Row Source:
SELECT msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type) In (1,5)) AND ((Left([Name],2)) Not In
("~s","ms")));

Then modify the After Update event of the combo box to:
Private Sub cboQueryName_AfterUpdate()
If Me.cboQueryName = "qxtbMthly" Then
Call BuildXTabSQL
End If
Select Case Me.cboQueryName.Column(1)
Case 5 'query
Me.subformctrl.SourceObject = "Query." & Me.cboQueryName
Case 1 'table
Me.subformctrl.SourceObject = "Table." & Me.cboQueryName
End Select
End Sub

This will display virtually any table in the subform control with very
little code.

--
Duane Hookom
MS Access MVP
--

You do not want to create a form programmatically at run
time. This would have all kinds of negative ramifications.

It is much better to pre create a form with the required
buttons and (their event procedures) in the form footer.
Add more invisible text boxes in the detail and labels in
the header than you will need for any table. Use a combo
box in the form's header section to selected the table for
the form's RecordSource.

You can then set the needed text box's and label's Visible
property, ControlSource or Caption and their Left and Width
properties. This is actually easier than creating the form
and all its controls on the fly.

If you name the text boxes and labels with a common prefix
and a sequential numeric suffix (eg txt0, txt1, txt2,... and
lbl0, lbl1, lbl2,...), the whole thing can be done in a
loop.

Here's some code to demonstrate the kind of logic involved:

Private Sub cboTables_AfterUpdate()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim k As Long
Dim lngWidth As Long
Dim lngTotalWidth As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(Me.cboTables)
Me.RecordSource = Me.cboTables
Me.cboTables.SetFocus

For k = 0 To Me.Section(0).Controls.Count - 1
Me("txt" & k).Visible = False
Me("lbl" & k).Visible = False
Me("txt" & k).Left = 0
Me("lbl" & k).Left = 0
Me("txt" & k).Width = 0
Me("lbl" & k).Width = 0
Next k

k = 0
For Each fld In tdf.Fields
Me("txt" & k).Visible = True
Me("lbl" & k).Visible = True
Me("txt" & k).ControlSource = fld.Name
Me("lbl" & k).Caption = fld.Name

lngWidth = fld.Properties("ColumnWidth")
If lngWidth > 4000 Then lngWidth = 4000
If lngWidth = -1 Then lngWidth = 1000
Me.InsideWidth = lngTotalWidth + lngWidth
Me("txt" & k).Left = lngTotalWidth
Me("lbl" & k).Left = lngTotalWidth
Me("txt" & k).Width = lngWidth
Me("lbl" & k).Width = lngWidth
lngTotalWidth = lngTotalWidth + lngWidth
k = k + 1
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub

The combo box, cboTables, in the form's header that's used
to select the table has the RowSource query:

SELECT Name
FROM MSysObjects
WHERE Type = 1 And Left(Name ,4) <> "MSys"
ORDER BY Name
--
Marsh
MVP [MS Access]

I want to make this very generic - so that I don't have create a form for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are
displayed
as you suggest?


[email protected] wrote:
I am linking tables into my mdb and need to programatically open them
for my
user for review of the data...

I was planning on using the DoCmd.OpenTable "TableName"... method to
perform
this... the problem is, that how do I determine when the table is
closed?
(After closing the table, I need acknowledgement from the user about
the
state of the data)...


Don't display a table's sheet to user's. Create a form in
continuous view that looks like a datasheet. Then you can
use the form header/footer section's for command button's
for users to indicate what they intend for you to do. If
you don't display the form's X (close) button in the title
bar, then you can provide your own confirm button and
perform whatever actions you want.

 

[Marshall Barton]

Just goes to show that even after all these years, there are
still simple goodies to discover. How I missed seeing that
before is mothering me though.

Thanks for jumping in with that Duane.

 

[Duane Hookom]

Check Tools->Startup->Display Form/Page

--
Duane Hookom
MS Access MVP
--

Duane,
I downloaded a couple your samples at rogersaccesslibrary... very nice.
One
question that I acn't figure out... in the "DynamSubFrm.mdb" the "frmXTab"
form opens on open of the mdb... how are you doing this? The only way I
knew
how to accomplish this was throught the autorun macro and I don't see
that...

Thanks again for your help

There is a simple solution to displaying either a table or a query as a
"subform" on a form. There is a simple demo of how to do this with a
saved
query in the DynamicSubfrm at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I just modified this (on my PC) to include tables in the cboQueryName
combo
box by setting the
Column Count: 2
Bound Column: 1
Row Source:
SELECT msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type) In (1,5)) AND ((Left([Name],2)) Not In
("~s","ms")));

Then modify the After Update event of the combo box to:
Private Sub cboQueryName_AfterUpdate()
If Me.cboQueryName = "qxtbMthly" Then
Call BuildXTabSQL
End If
Select Case Me.cboQueryName.Column(1)
Case 5 'query
Me.subformctrl.SourceObject = "Query." & Me.cboQueryName
Case 1 'table
Me.subformctrl.SourceObject = "Table." & Me.cboQueryName
End Select
End Sub

This will display virtually any table in the subform control with very
little code.

--
Duane Hookom
MS Access MVP
--

You do not want to create a form programmatically at run
time. This would have all kinds of negative ramifications.

It is much better to pre create a form with the required
buttons and (their event procedures) in the form footer.
Add more invisible text boxes in the detail and labels in
the header than you will need for any table. Use a combo
box in the form's header section to selected the table for
the form's RecordSource.

You can then set the needed text box's and label's Visible
property, ControlSource or Caption and their Left and Width
properties. This is actually easier than creating the form
and all its controls on the fly.

If you name the text boxes and labels with a common prefix
and a sequential numeric suffix (eg txt0, txt1, txt2,... and
lbl0, lbl1, lbl2,...), the whole thing can be done in a
loop.

Here's some code to demonstrate the kind of logic involved:

Private Sub cboTables_AfterUpdate()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim k As Long
Dim lngWidth As Long
Dim lngTotalWidth As Long

Set db = CurrentDb()
Set tdf = db.TableDefs(Me.cboTables)
Me.RecordSource = Me.cboTables
Me.cboTables.SetFocus

For k = 0 To Me.Section(0).Controls.Count - 1
Me("txt" & k).Visible = False
Me("lbl" & k).Visible = False
Me("txt" & k).Left = 0
Me("lbl" & k).Left = 0
Me("txt" & k).Width = 0
Me("lbl" & k).Width = 0
Next k

k = 0
For Each fld In tdf.Fields
Me("txt" & k).Visible = True
Me("lbl" & k).Visible = True
Me("txt" & k).ControlSource = fld.Name
Me("lbl" & k).Caption = fld.Name

lngWidth = fld.Properties("ColumnWidth")
If lngWidth > 4000 Then lngWidth = 4000
If lngWidth = -1 Then lngWidth = 1000
Me.InsideWidth = lngTotalWidth + lngWidth
Me("txt" & k).Left = lngTotalWidth
Me("lbl" & k).Left = lngTotalWidth
Me("txt" & k).Width = lngWidth
Me("lbl" & k).Width = lngWidth
lngTotalWidth = lngTotalWidth + lngWidth
k = k + 1
Next fld
Set tdf = Nothing
Set db = Nothing
End Sub

The combo box, cboTables, in the form's header that's used
to select the table has the RowSource query:

SELECT Name
FROM MSysObjects
WHERE Type = 1 And Left(Name ,4) <> "MSys"
ORDER BY Name
--
Marsh
MVP [MS Access]


[email protected] wrote:
I want to make this very generic - so that I don't have create a form
for
each table (and maintain them later)... any suggestions how to
programatically build this form that that all the data elements are
displayed
as you suggest?


[email protected] wrote:
I am linking tables into my mdb and need to programatically open
them
for my
user for review of the data...

I was planning on using the DoCmd.OpenTable "TableName"... method to
perform
this... the problem is, that how do I determine when the table is
closed?
(After closing the table, I need acknowledgement from the user about
the
state of the data)...


Don't display a table's sheet to user's. Create a form in
continuous view that looks like a datasheet. Then you can
use the form header/footer section's for command button's
for users to indicate what they intend for you to do. If
you don't display the form's X (close) button in the title
bar, then you can provide your own confirm button and
perform whatever actions you want.

 

[Duane Hookom]

Sure saves on code and complexity.

--
Duane Hookom
MS Access MVP
--

Just goes to show that even after all these years, there are
still simple goodies to discover. How I missed seeing that
before is mothering me though.

Thanks for jumping in with that Duane.
--
Marsh
MVP [MS Access]

There is a simple solution to displaying either a table or a query as a
"subform" on a form. There is a simple demo of how to do this with a saved
query in the DynamicSubfrm at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

I just modified this (on my PC) to include tables in the cboQueryName
combo
box by setting the
Column Count: 2
Bound Column: 1
Row Source:
SELECT msysObjects.Name, msysObjects.Type
FROM msysObjects
WHERE (((msysObjects.Type) In (1,5)) AND ((Left([Name],2)) Not In
("~s","ms")));

Then modify the After Update event of the combo box to:
Private Sub cboQueryName_AfterUpdate()
If Me.cboQueryName = "qxtbMthly" Then
Call BuildXTabSQL
End If
Select Case Me.cboQueryName.Column(1)
Case 5 'query
Me.subformctrl.SourceObject = "Query." & Me.cboQueryName
Case 1 'table
Me.subformctrl.SourceObject = "Table." & Me.cboQueryName
End Select
End Sub

This will display virtually any table in the subform control with very
little code.