Senin, 26 Juni 2017

Re: [MS_AccessPros] How to link to subform on a parent form?

 

Art-


Your setup should be Form -> subform -> second subform within the first subform.  Is that not what you have?

If setup like that, it's easy to link the three.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jun 26, 2017, at 10:04 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



OK. I bow to your superior intellect! Now back to my original question. Linking to the two subforms...

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Monday, June 26, 2017 2:42 PM
Subject: Re: [MS_AccessPros] How to link to subform on a parent form?

Art-

FieldTypeID      Long    nn
FieldTypeName  Text   Date/Time
FieldTypeDesc   Text    Date and time field
FieldMinVal      Text      1990-01-01
FieldMaxVal      Text      2099-12-31
FieldInpVal       Text
FieldDefVal       Text       Date()
FieldDecPl        Integer


FieldTypeID      Long    nn
FieldTypeName  Text   Yes/No
FieldTypeDesc   Text    True or False field
FieldMinVal      Text      -1
FieldMaxVal      Text      0
FieldInpVal       Text       
FieldDefVal       Text       0
FieldDecPl        Integer


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jun 26, 2017, at 8:49 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



OK, sow me how it works for a date field and a yes/no field, pleas3. 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Monday, June 26, 2017 1:40 PM
Subject: Re: [MS_AccessPros] How to link to subform on a parent form?

Art-

My redesign greatly simplifies your table and removes all the repeating fields.  The only "extra" field is FieldDecPl that will have a value only for a Decimal data type.  As I explained, you can store any sort of value in a Text field, so the Min, Max, Def, and Val fields will work for any actual data type.  For example, for an integer data type, you can store a value like 0 in the FieldDefVal field that will convert nicely to integer if you have to assign it to an actual integer field.  In Yes/No, you can store either 0 or -1 (False, True).

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jun 26, 2017, at 8:18 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
The reason the table is all messed up is I have to handle different field types and not just text but also date, yes/no, currency, etc and each of them have their own properties. The FieldInpVal is the property that specifies if the field is required, can be blank, etc. 

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Monday, June 26, 2017 12:57 PM
Subject: Re: [MS_AccessPros] How to link to subform on a parent form?

Yeah.  tblFieldType makes no sense.  I would think it should be:

FieldTypeID      Long
FieldTypeName  Text
FieldTypeDesc   Text
FieldMinVal      Text
FieldMaxVal      Text
FieldInpVal       Text
FieldDefVal       Text
FieldDecPl        Integer

Text can hold any data type value, and if a number or a date, can be converted easily.  Not sure what you're using InpVal for, but I included it.


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jun 26, 2017, at 7:43 PM, Art Lorenzini dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,
this is the breakdown of the tables in this form:

Form Name: frmObjectTypeNew

Table: tblObjectType                                                                                                              Page: 1
  Columns
            Name                                                                 Type                               Size
            ObjectTypeID                                                       Long Integer                                        4
            ObjectTypeName                                                   Short Text                                        255
            ObjectTypePluralName                                             Short Text                                        255
            ObjectTypeAlias                                                     Short Text                                        255
            ObjectTypeDesc                                                    Short Text                                        255
            Created_By                                                          Short Text                                         50
            Created_On                                                         Date With Time                                     8
            Edited_By                                                            Short Text                                         50
            Edited_On                                                           Date With Time                                     8

  Table Indexes
           Name                                               Number of Fields
            ObjectTypeID                                     1
                        Fields:
                        ObjectTypeID                   Ascending
            PrimaryKey                                        1
                        Fields:
                        ObjectTypeID                   Ascending
 
Form Name: sfrmObjectTypeFields
Table: tblObjectField                                                                                                               Page: 1
  Columns
            Name                                                                 Type                               Size
            ObjectFieldID                                                       Long Integer                                        4
            ObjectTypeID                                                       Long Integer                                        4
            FieldName                                                           Short Text                                        255
            FieldTypeID                                                         Long Integer                                        4
            FieldDesc                                                            Short Text                                        255
            Created_By                                                          Short Text                                         50
            Created_On                                                         Date With Time                                     8
            Edited_By                                                            Short Text                                         50
            Edited_On                                                           Date With Time                                     8
  Table Indexes
            Name                                               Number of Fields
            ObjectTypeID                                     1
                        Fields:
                        ObjectTypeID                   Ascending
            PrimaryKey                                        1
                        Fields:
                       ObjectFieldID                   Ascending


Form Name: sfrmFieldProperties
Table: tblFieldType                                                                                                                Page: 1
  Columns
            Name                                                                 Type                               Size
            FieldTypeID                                                         Long Integer                                        4
            FieldTypeName                                                     Short Text                                        255
            FieldTypeDesc                                                       Short Text                                        255
            CurrMinVal                                                           Currency                                             8
            CurMaxVal                                                           Currency                                             8
            CurDefVal                                                            Currency                                             8
            CurInpVal                                                            Long Integer                                        4
            DateMinVal                                                          Date With Time                                     8
            DateMaxVal                                                         Date With Time                                     8
            DateDefVal                                                          Date With Time                                     8
            DateInpVal                                                          Long Integer                                        4
            DateTimeMinVal                                                    Date With Time                                     8
            DateTimeMaxVal                                                    Date With Time                                     8
            DateTimeDefVal                                                    Date With Time                                     8
            DateTimeInpVal                                                    Long Integer                                        4
            DecPlace                                                             Long Integer                                        4
            DecMinVal                                                           Long Integer                                        4
            DecMaxVal                                                           Long Integer                                        4
            DecDefVal                                                           Long Integer                                        4
            DecInpVal                                                           Long Integer                                        4
            DecAllowDups                                                       Yes/No                                               1
            EmailDefValu                                                        Short Text                                        255
            EmailInpVal                                                         Long Integer                                        4
            EmailAllowDups                                                     Yes/No                                               1
            IntMinVal                                                            Long Integer                                        4
            IntMaxVal                                                            Long Integer                                        4
            IntDefVal                                                            Long Integer                                        4
            IntInpVal                                                            Long Integer                                        4
            IntAllowDups                                                        Yes/No                                               1
            LookupListSource                                                   Short Text                                        255
            LookupInpVal                                                       Long Integer                                        4
            MemoMinVal                                                        Long Integer                                        4
            MemoMaxVal                                                        Long Integer                                        4
            MemoDefVal                                                         Short Text                                        255
            MemoInpVal                                                         Long Integer                                        4
            TextMinVal                                                          Long Integer                                        4
            TextMaxVal                                                          Long Integer                                        4
            TextDefVal                                                           Short Text                                        255
            TextInpVal                                                           Long Integer                                        4
            TextAllowDups                                                      Yes/No                                               1
            TimeMaxVal                                                         Date With Time                                     8
            TimeMinVal                                                          Date With Time                                     8
            TimeDefVal                                                          Date With Time                                     8
            TimeInpVal                                                          Long Integer                                        4
            URLDefVal                                                           Short Text                                        255
            URLInpVal                                                           Long Integer                                        4
            URLAllowDups                                                      Yes/No                                               1
            YesNoDefVal                                                        Short Text                                        255

  Table Indexes
            Name                                               Number of Fields
            FieldTypeID                                       1
                        Fields:
                        FieldTypeID                     Ascending
            PrimaryKey                                        1
                        Fields:
                        FieldTypeID                     Ascending

I know there is probably a better way to setup the tblFieldType so please let me know.

With Warm Regards,
 
Arthur D. Lorenzini
IT System Manager
Cheyenne River Housing Authority
Wk.(605)964-4265  Ext. 130
Fax (605)964-1070

"Anyone who claimed that old age had brought them patience was either lying or senile."   







From: "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com> 
Sent: Monday, June 26, 2017 12:11 PM
Subject: Re: [MS_AccessPros] How to link to subform on a parent form?

Art-

So, the tables should be linked like this:

tblObjectType:ObjectTypeID -> ObjectTypeID:tblObjectField:FieldTypeID -> FieldTypeID:tblFieldType

So, tblObjectField must have a field called FieldTypeID in it that links to the Field Type table.  Does it not?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Jun 26, 2017, at 6:44 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:




I have my main form called frmObjectType whose record source it tblObjectType. On this form I have a subform sfrmObjectTypeFields whih record source is tbObjectField and another subform called sfrmFieldProperties whose record source is tblFieldType.

Now sfrmObjectTypeFields is tied to the mainform frmObjectType through a field called ObjetTypeID which resides in both forms. 

Now the subform I need to tie together is sfrmObjectTypeFields and sfrmFieldProperties via the FieldTypeID which resides in both form but is not available in the link properties of sfrmFieldProperties. Any Ideas?

Thank you, 

Art Lorenzini
Sioux Falls, SD























__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Tidak ada komentar:

Posting Komentar