I think you may be trying to do everything in 1 step.
What exactly is this form/query for?
In post 1
I'm trying to create a rooms form, titled frmMainRooms. I want to be able to search for a building name and room number. Then I want to see:
1. Who owns the room (may be more than one, datasheet view would work). Will need all information about this customer.
2. How room is secured.
3. Cabinet Name and key name for cabinet (can be more than one cabinet).
4. Equipment which is in the room.
5. Equipment which is in the cabinet.
Going back to post 3
it queries building table and rooms table. I need to see all the buildings and rooms.
When you are looking at an application - specifically information about some entity(s) -you have to consider:
-how to populate the XXX
-what range of values is appropriate
-is it dependent on something else
-if so, does that thing already have a value
-another concern is, is this the right time/place to capture that info
Obviously, you can't get data out, if it never went/got input.
If you have things that are independent until brought together ie: Customer and Room(I'm ignoring bldg for this)
you could have a "populating form" including a list of Customers, and a list of Rooms (independent combos for example), and a Button.
You make a selection in cboCustomer, and a selection in cboRoom --then click the button to create a record indicating that this Customer is assigned to this Room (in your CustomerToRoom table).
You would also have a compound unique index on that table to prevent duplicate assignments.
On the code behind the button click, you would also be validating that there was no existing record for that combination. If there was an attempt to add a duplicate, intercept the Access error with something more friendly.
In overview, the method to populate the tables, may not be the same as the method you use to retrieve or display related records. When you populate a table, there are no existing records, and when you add a record (or subsequent records), you have to avoid duplicates.
It is important to get the logic of what you want to happen clearly defined and tested. You could populate several things from one form, but don't make it too complex or the user friendliness will suffer. Some testing with users will clarify what is acceptable and often get you some "new ideas" to consider. There will be a "flow" to the capturing of data/populating tables. Keeping you data capture in sync with the "business flow" adds to the user friendliness. If you get an awkward process for data capture, users will let you know, and you'll be encouraged to revise the data capture/population process.
As for logging changes, the techniques can vary. I have seen a logger table that recorded date and time, who logged the record and 250 char of free form text. I could see having a few fields with specific names for assisting search and retrieval. Thinking and typing -things like
-object involved (query, table, form, report, module, procedure)
-nameOfObject
-description
-importance
-dependency
This could be a part of or related to a task/to do list with assignedTo, assignedDate, expectedCompletion. This could also be part of a QA set up with scheduled, testNo, Issues, AcceptedBy, newTask...
For documentation --that is the longer term materials that are part of the application --you might consider Word, rather than the database itself. With Word you can capture screenshots and incorporate in documentation. Some organizations have templates/formats specific to documentation, and procedures for completion, acceptance and storage.
Here is a sample logger function for illustration. This one writes to a filesystem file (not in the database).
Code:
'---------------------------------------------------------------------------------------
' Procedure : Logger
' Author : jack
' Date : 1/21/2009
' Purpose : To write records to a LOG file using FileSystemObject.
'
'Parameters
' sLogName As String -- full path and file name of the log file
' sLogRec As String -- record to be written to the log
'
' NOTE: Each log record has a timestamp appended
'
' Special Note/restriction:
'***** Must set a reference to MICROSOFT SCRIPTING RUNTIME ***
'---------------------------------------------------------------------------------------
'
Sub Logger(sLogName As String, sLogRec As String)
Dim tslog As TextStream
Dim fileLog As file
Dim i As Integer
Dim fso As FileSystemObject
10 On Error GoTo Logger_Error
20 Set fso = New FileSystemObject
30 Set fileLog = fso.GetFile(sLogName) '"I:\wordtest\output\Results.log")
40 Set tslog = fileLog.OpenAsTextStream(ForAppending)
50 tslog.WriteLine Now() & vbTab & sLogRec
60 tslog.Close
70 On Error GoTo 0
80 Exit Sub
Logger_Error:
90 MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Logger of Module ADO_Etc"
End Sub
Here is another, used to assist in "what have we been working on recently" -- was helppful when there were several small database projects for a variety of users. (This gave some indication of recent activity)
Code:
Function fJLogIT(sActivity As String)
'write a record into an access log file called jAccessLog.Log
'to indicate what files/databases have been used recently
'written Mar 2000
'jed
'Parameter:
'sActivity a short text about activity in this current database, can be null
Open "E:\Work_DATA_20071207\C_Drive_WORK\Jack\WorkInProgress\jAccessLog.log" For Append As #1
Print #1, Now() & vbTab & CurrentDb.Name & vbCrLf & vbTab & "---> " & sActivity
Close #1
End Function
Note: The log files could be Access tables. By using the Filesystem it was easy to write from a variety of databases. Pretty crude but was effective and useful - especially when someone asked --what has been going on in database area.
Windows 8 
Access 2013
Reply With Quote
