PK of fields in a form that is sourced by a query

[accessuser1023]

all,

this is a cross post from AWF:

http://www.access-programmers.co.uk/...d.php?t=239253

Here's my requirement, which I am looking for the easiest way out on:



=> Grab all form fields that have control sources which are sourced PKs at the lowest-level (table). Problem is, many of these forms are bound to queries which involve multiple tables. I know there is a way, but I don't have any of the Access OOP model documented. So that leaves me with skimming the help articles forever to find the enums and props I need.

help anyone? this isn't really my job and the DB architect is not a professional which is why the structure is very prone to corruption. but they are not worried about that (per the other thread).

thanks all, and Merry Christmas to everybody here.

[June7]

This shows code to get the PK of a given table http://www.utteraccess.com/forum/Pri...ed&pid=1086591

The code could be modified to loop through all tables in the db.

Now determining which forms have those fields is another issue. Why do you need this?

[help_me_with_access]

Why do you need this?

like I stated June, the database architect is not a pro. and thus, patch work will ensure for the person from now on. I don't lecture people if they contact me to solve a problem. I give them advice on how they got to the problem (if they can emotionally handle it), what the correct way to design a database is, and then tell them I can solve the problem if there's any solution available or not, regardless of how "piecemeal" it is.

a check is a check, in this guys' view. but in all fairness to small business people as well, the USA economy and the panic that consumers run on these days, a lot of times that doesn't really give much time to think about doing things "right" for small business people if they are doing the tech work themselves. so I really don't blame this person for doing what he can with the time he's got.

to re-iterate, I need it because of a lack of planning and lack of knowledge about proper base-lining by the original author. nothing can be done about it at this point, unfortunately. thanks for asking though. eventually people do learn.

I knew this could be done the minute I got the request, but as I said I just don't have the OOP model of VBA documented well enough to establish the steps I need to take to get this done without taking a day or so to research it.

[orange]

Here's a routine to get the recordsource for each form in a database.

'---------------------------------------------------------------------------------------
' Procedure : getFormRecordSource
' Author    : Jack
' Date      : 29-12-2012
' Purpose   : To get the recordsource of each form in the current access database
' Outputs formname and recordsource to the immediate window
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Sub getFormRecordSource()
        Dim afrm As AccessObject
        Dim frm As Access.Form
10       On Error GoTo getFormRecordSource_Error

20      For Each afrm In CurrentProject.AllForms
        
30          If Not afrm.IsLoaded Then
40            DoCmd.OpenForm afrm.name, acDesign, , , , acHidden
              
50            Debug.Print afrm.name & "  -- " & Forms(afrm.name).RecordSource
60            DoCmd.Close acForm, afrm.name
70          Else
80            Debug.Print afrm.name & "  -- " & Forms(afrm.name).RecordSource
90          End If
          
100     Next afrm

110      On Error GoTo 0
120      Exit Sub

getFormRecordSource_Error:

130       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure getFormRecordSource of Module AWF_Related"
End Sub

It may help. Not all forms have record sources.

My guess as to how it would be set up is along this line:

For each Form in the Database
  For each Control on the Form
   'you will need to deal with type of Control
    get the controlsource or rowsource
    write the Formname, ControlName and Rowsource/controlsource value to a table FormControlInfo
  next control
next Form

Subsequently;

For each Table in the FormControlTable
   Identify the Pks
   Write Tablename, PrimaryKey (may be compound) to some final location
Next Table

Research Google 'identifying all controls on a form MS Access'

Some links
http://www.devhut.net/2010/12/05/ms-...ols-on-a-form/
http://stackoverflow.com/questions/3...n-a-subform-ac

But there are many more

Good luck with the project.

EDIT::::
Following the original post I found this link from Allen Browne which may be helpful
http://allenbrowne.com/ser-73.html