Prev: Conditional Formatting - Database performance
Next: Adding "°" symbol to numbers
From: InvisibleDuncan on 23 Apr 2010 04:24
"Lars Brownies" wrote:

> In mde applications I have succesfully changed the record source during
> runtime, so I'm not sure which problem you're refering to.
>
> Could you post your code? I'd like to take a look.
>
> Thanks,
>
> Lars
>

Okay, assume you have a basic form, set to datasheet view, called
frmAllData. You can then call this routine, passing in the name of the table
or query that you want to display. Mine is set to open it as read-only, but
you obviously don't need to do that:

Function CreateDataForm(pstrTableName As String) As Boolean
Dim tdfCurrent As TableDef
Dim qdfCurrent As QueryDef
Dim fldCurrent As Field
Dim txtCurrent() As TextBox
Dim intCounter As Integer
Dim frmCurrentForm As Form
Dim ctlControl As Control
Dim lngErrorNumber
Dim strErrorDescription As String

On Error GoTo ErrorHandler

Const kstrBaseForm As String = "frmAllData"

DoCmd.Echo False

'# Get the current database
Dim dbsCurrent As Database
Set dbsCurrent = CurrentDb

'# Open the form we'll be amending
DoCmd.OpenForm kstrBaseForm, acDesign
Set frmCurrentForm = Forms(kstrBaseForm)

'# Try to open it as a table. If it causes error 3265, try to open it as
a query.
On Error Resume Next
Set tdfCurrent = dbsCurrent.TableDefs(pstrTableName)
lngErrorNumber = Err.Number
If lngErrorNumber > 0 Then
strErrorDescription = Err.Description
End If
On Error GoTo ErrorHandler

If lngErrorNumber = 3265 Then
Set qdfCurrent = dbsCurrent.QueryDefs(pstrTableName)
frmCurrentForm.RecordSource = qdfCurrent.Name
For intCounter = frmCurrentForm.Controls.Count - 1 To 0 Step -1
DeleteControl frmCurrentForm.Name,
frmCurrentForm.Controls(intCounter).Name
Next intCounter

ReDim txtCurrent(qdfCurrent.Fields.Count - 1)
intCounter = 0
For Each fldCurrent In qdfCurrent.Fields
Set txtCurrent(intCounter) = CreateControl(kstrBaseForm,
acTextBox, acDetail, , , intCounter * 100, 0, 950, 450)
txtCurrent(intCounter).Name = fldCurrent.Name
txtCurrent(intCounter).ControlSource = fldCurrent.Name
intCounter = intCounter + 1
Next fldCurrent
ElseIf lngErrorNumber = 0 Then
frmCurrentForm.RecordSource = tdfCurrent.Name
For intCounter = frmCurrentForm.Controls.Count - 1 To 0 Step -1
DeleteControl frmCurrentForm.Name,
frmCurrentForm.Controls(intCounter).Name
Next intCounter

ReDim txtCurrent(tdfCurrent.Fields.Count - 1)
intCounter = 0
For Each fldCurrent In tdfCurrent.Fields
Set txtCurrent(intCounter) = CreateControl(kstrBaseForm,
acTextBox, acDetail, , , intCounter * 100, 0, 950, 450)
txtCurrent(intCounter).Name = fldCurrent.Name
txtCurrent(intCounter).ControlSource = fldCurrent.Name
intCounter = intCounter + 1
Next fldCurrent
Else
MsgBox "Error opening table: " & strErrorDescription
CreateDataForm = False
Exit Function
End If

frmCurrentForm.Caption = "Data: " & pstrTableName
DoCmd.Close acForm, kstrBaseForm, acSaveYes
DoCmd.Echo True
DoCmd.OpenForm kstrBaseForm, acFormDS, , , acFormReadOnly
CreateDataForm = True

Exit Function
ErrorHandler:
MsgBox "Error opening table: " & Err.Description
CreateDataForm = False
End Function



Hope this helps a bit.
From: Hans Up on 23 Apr 2010 23:35
InvisibleDuncan wrote:
> Okay, assume you have a basic form, set to datasheet view, called
> frmAllData. You can then call this routine, passing in the name of the table
> or query that you want to display.

Any errors which reach your ErrorHandler will exit the function without
turning echo back on. For example, if frmAllData is missing or
misspelled, the user will receive a message box with the error
description, but then be left with a blank unresponsive Access
application window.

If you use DoCmd.Echo False you must make sure the function can't exit
before DoCmd.Echo True
First  |  Prev  | 
Pages: 1 2
Prev: Conditional Formatting - Database performance
Next: Adding "°" symbol to numbers