Lookupform(s)

[Lars Brownies]

I'm in the pre-stage of building an application with about 15 lookup tables.
In stead of building 15 lookup forms, I'm thinking about making one generic
lookup form and changing the record source depending on the table needed. I
would have to add code for validation purposes.

Is this doable or should I make 15 separate forms?

Thanks,

Lars

 

[Lars Brownies]

Oops. I didn't mean lookup forms. I mean control forms, in which the lookup
tables are maintained.

Lars

 

[golfinray]

IMHO, that would not be the way to go. Normally, you might want one mainform
and two or three subforms at most. 15 is going to be difficult. What are your
relationships between these tables? If you have one-to-one relationships,
simply combine the tables. If you have one-to-many, you would need to build
maybe 5 forms with two subforms each.

 

[John W. Vinson]

I'm in the pre-stage of building an application with about 15 lookup tables.

Just don't use lookup *FIELDS* (www.mvps.org/access/lookupfields.htm)

In stead of building 15 lookup forms, I'm thinking about making one generic
lookup form and changing the record source depending on the table needed. I
would have to add code for validation purposes.

Is this doable or should I make 15 separate forms?

It's certainly doable but might not be worth the hassle. One thing to consider
if you'll mainly be using these lookup tables in combo boxes on forms is to
use generic VBA code in the combo's NotInList event to give the user the
option of adding a value to the lookup table without opening a form at all.
I have a routine I developed a few years ago, but it's copyrighted by my
former employer (now out of business) so I can't post it here, but a basic
version should be pretty easy.

 

[Lars Brownies]

Thanks John, Milton,

I have notinlist code so that won't be a problem. I want to restrict
additions to some lookup tables to the dba controller. For instance a
category table for my reports. Maybe I'll build forms only for those kind of
tables.

Lars

 

[InvisibleDuncan]

The code for making a generic form that you adapt depending on the
recordsource is reasonably straightforward, but I did discover a problem with
it - it won't work if you make an MDE version of your database, because MDEs
aren't able to open forms for editing.

The version I built actually only put up datasheet forms, which meant I
didn't have to muck about with field widths, etc; it simply looked at all the
fields in the table or query in question, and loaded them into the form. If
that's the kind of thing you're after, I can put the code up...

 

[Lars Brownies]

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

 

[InvisibleDuncan]

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.

 

[Hans Up]

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