Recordset retrieving Source

[ichabob]

I'm trying to create a dynamic form that is built based on a table that is given when the function is called.

The form has Textboxes labeled txtFieldy where y is 0 - 18. My lookup fields always have ID in them and is converted to a combobox. The first ID field is skipped.

My problem is that I need a property that will look at the table fields and return the Lookup or Rowsource, so I can define my combo box in my dynamic form the same way.

Any Ideas?

Function PopulateFields(strTableName As String)

Const conNumButtons = 9

DoCmd.OpenForm "frmDynForms", acDesign
Forms.Caption = strTableName



For intOption = 1 To conNumButtons
Forms![frmDynForms]("txtField" & intOption).Visible = False
Forms![frmDynForms]("lblField" & intOption).Visible = False
Next intOption

Set con = Application.CurrentProject.Connection
stSql = strTableName

Forms![frmDynForms].Form.RecordSource = strTableName

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset
i2 = 0

For i2 = 0 To rs.Fields.Count - 1
Forms![frmDynForms]("txtField" & i2).Visible = True
Forms![frmDynForms]("lblField" & i2).Visible = True
Forms![frmDynForms]("lblField" & i2).Caption = rs.Fields(i2).Name

If InStr(rs.Fields(i2).Name, "ID") > 0 And i2 <> 0 Then
If Forms![frmDynForms]("txtField" & i2).ControlType = acTextBox Then
Forms![frmDynForms]("txtField" & i2).ControlType = acComboBox
Forms![frmDynForms]("txtField" & i2).BoundColumn = 1
Forms![frmDynForms]("txtField" & i2).ColumnCount = 2
'Forms![frmDynForms]("txtField" & i2).ColumnWidths = "0in;1in"
'strdan = rs.Fields(i2).Source
'MsgBox ("Rowsource = " & strdan)
End If
End If
Forms![frmDynForms]("txtField" & i2).ControlSource = rs.Fields(i2).Name
Next i2

DoCmd.Save
DoCmd.Close

rs.Close
Set rs = Nothing
Set con = Nothing

DoCmd.OpenForm "frmDynForms", acNormal


End Function

 

[Remou]

You can get the data from the TableDef:

[tt]Set db = CurrentDb
Set tdf = db.TableDefs("tblEvil")
Debug.Print tdf.Fields("LU").Properties("RowSourceType")
Debug.Print tdf.Fields("LU").Properties("RowSource")[/tt]

But you may wish to consider:

http://www.mvps.org/access/lookupfields.htm

 

[ichabob]

Thank you. I think that might do the trick. I did read, howeve, the Evils of Lookup fields and I'm confused. How do I create relationships in Access (relationships view) if I do this? Should I just control the relationships with VBA and keep Access in the dark?

 

[Remou]

You can create relationships in relationship view by dragging and dropping fields from one table to another. Lookup fields are something different; look-ups should be kept for forms and reports, they should not be used in tables. Is this what you mean?

 

[ichabob]

Yes. OK, now that I understand you ... if I don't use lookups in the table, how can I create a dynamic form that decides when to use combo boxes? Is there a way to query the relationship model or do I hard code it in VBA or is dynamic forms out of reach?

Thank you for your help.

 

[Remou]

You could consider using the description property of the field (table design view), however, be aware that this property is used for Status Bar Text when creating a form. If a field does not have a description, you will get an error, so a little code is needed, for example:

Function GetDesc(tbl As String, fld As String)
On Error GoTo HandleError

GetDesc = CurrentDb.TableDefs(tbl).Fields(fld).Properties("Description")

ExitHere:
Exit Function

HandleError:
If Err.Number = 3270 Then
    GetDesc = "None"
End If
End Function

Another option is to use a table:
[tt]FieldName LookUp Using
Country tblCountry CoID[/tt]

You can check this table when building your form.

 

[danvlas]

That situation led me to have standard foreign key field names, built as FK_TableName_PrimaryKeyName...

This way, FK_ indicates it's a value from a query built as Select [PrimaryKeyName], [DescriptionField] From [TableName]...

Of course, this also lead to a standard naming for the 'description' field.

Solving one problem may generate 10 new problems....

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html