Toggle Button to Control Sub Form Behavior and Properties 1

[misscrf]

I have a sub form that is on a tab control of a main form. This sub form allows users to enter records of documents that can be related (linked) to the main form record, or another sub form record. To put this into context, the main form has entry of Projects. There are sub forms for team members who will be on the project, project resourcese, and buildings that the project will be taking place in. Within each building, there can be multiple staff (different from the team members).

I have combos worked out on this particular "documents" sub form to allow users to choose a doc type. Based on the doc type they choose, I set the row source of a 2nd combo, to the specific list of items that the document is to be related/linked to. So if the user chooses a doc type that is an ID which relates to project team members, then the 2nd combo refreshes to show a list of team members that exist for the current project. It stores the ID of that project/team member record in the documents table FK field for related item. The same goes for if the doc type chosen is for buildings, or for building/staff.

My issue is that I need to be able to have 2 views of this data, on this subform. 1 for viewing document records that are related to the main record or any of the main record's related sub records (3 sub form relationships and one sub-sub form relationship). The other view will be for adding new records or editing existing ones.

My idea is to have a toggle button on this one sub form. If it is true, then the form will be in a read only mode, continuous forms view, with the form filter set to a union filter to show the related records. The form has an unbound field that has a hyperlink for launching the documents (all tested and works).

If the toggle is false, then the sub-form will allow adding new records or editing existing ones. The default will be a new record, with the record source to the documents table. The form will be in single form view, with a button to search and launch a search form to find any record needing editing, if that is what is needed.

I am working on the code for the different parts of the sub form that I will have to set, based on the behavior of the toggle button. My issue is that there are so many settings, I cannot seem to find a good place to look them up. I have the behavior of the toggle button down, and the form view. I am wondering if anyone can help me compile a list of behaviors that I will need to set in the below code's with me.frmProjectDocuments. Besides for default view, I am thinking of the following:

[ul]
[li]record source for form and for combos[/li]
[li]navigation buttons[/li]
[li]scroll bars[/li]
[li]allow edits/additions/deletions properties[/li]
[li]form filter[/li]
[/ul]

Can anyone think of anything else I am forgettting? Does anyone know a good resource for looking up the syntax for these? I have been doing that, but one at a time and searching all of google, checking out site after site is time consuming.

This is what I have for the start of the code:

If Me.tgViewDoDocs.Value = True Then
	Me.tgViewDoDocs.Caption = "Add/Edit Document Records"
	Me.tgViewDoDocs.HoverColor = RGB(204, 102, 0)
	Me.tgViewDoDocs.PressedColor = RGB(204, 102, 0)
	Me.tgViewDoDocs.BackColor = RGB(204, 102, 0)
	Me.tgViewDoDocs.ForeColor = RGB(0, 0, 0)
	Me.tgViewDoDocs.HoverForeColor = RGB(255, 255, 255)
	Me.tgViewDoDocs.FontBold = True
	With Me.frmProjectDocuments
	'Set to single form view for adding and editing records
		.Form.DefaultView = 0
	End With
Else
	Me.tgViewDoDocs.Caption = "View Documents Related to Current Project"
	Me.tgViewDoDocs.HoverColor = RGB(0, 128, 0)
	Me.tgViewDoDocs.PressedColor = RGB(0, 128, 0)
	Me.tgViewDoDocs.BackColor = RGB(0, 128, 0)
	Me.tgViewDoDocs.ForeColor = RGB(0, 0, 0)
	Me.tgViewDoDocs.HoverForeColor = RGB(255, 255, 255)
	Me.tgViewDoDocs.FontBold = True
	'Set to continuous form for viewing all records tied to current project
	With Me.frmProjectDocuments
		.Form.DefaultView = 1
	End With
End If

I appreciate any help!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Well the way I do this sometimes is to have two forms, one with unbound controls for creating new records and/or editing selected records, while having a datasheet view form for viewing all records with a filter functionality for searching / date range etc.

That way you have the datasheet view as a snapshot so the app doesn't lock any records just because someone is viewing records, and then use a separate popup form to edit a selected record that can either be unbound and use a recordset to grab the data and manipulate it or a bound query that only grabs a single record as dynaset so only that record is locked while being edited.

Here is an example of a class I built for adding a new call log record

clsCallLog

Option Explicit

Private rs As DAO.Recordset
Private oCols As Object
Private oFailed As Object

Private Sub Class_Initialize()
    
    ' set up call log columns with required flag
    Set oCols = CreateObject("Scripting.dictionary")
    oCols.Add "Priority", True
    oCols.Add "CallType", True
    oCols.Add "Notes", True
    
    ' failed object
    Set oFailed = CreateObject("Scripting.dictionary")
    
    ' create blank record
    Call Clear
    
End Sub

Private Sub Class_Terminate()

    ' close recordsets and kill all objects
    Set rs = Nothing
    Set oCols = Nothing
    Set oFailed = Nothing
    
End Sub

' Failed validation columns
Public Property Get Failed() As Object
    Set Failed = oFailed
End Property

' get record
Public Property Get Rec() As DAO.Recordset
    Set Rec = rs
End Property

' 0bject attributes (columns collection)
Public Property Get Cols() As Object
    Set Cols = oCols
End Property

' add a new call log to the system
Public Function AddCallLog(ByVal frm As Form) As Boolean

    ' set ok flag
    AddCallLog = True
    
    ' set up vars for validation checking
    Dim k As Variant

    ' check form values against required cols
    For Each k In oCols.Keys
    
        If oCols.Item(k) Then
            If Nz(frm.Controls(k).Value, "") = "" Then
                oFailed.Add k, True
                AddCallLog = False
            Else
                rs.Fields(k).Value = frm.Controls(k).Value
            End If
        Else
            rs.Fields(k).Value = frm.Controls(k).Value
        End If
        
    Next

    If AddCallLog Then
        ' add default fields
        rs.Fields("ContactID").Value = frm.Controls("ContactID").Value
        rs.Fields("CallDate").Value = Format(Now(), "DD/MM/YYYY")
        rs.Fields("CallTime").Value = Format(Now(), "HH:MM:SS")
        rs.Fields("Created_By").Value = oUser.Name
        rs.Update
        Call Clear
    End If
    
End Function

Public Sub Clear()

    ' clear any exisiting recordset
    Set rs = Nothing

    ' create blank recordset
    Set rs = CurrentDb.OpenRecordset("SELECT CallID,ContactID,CallDate,CallTime,Notes,CallType,Created_By,Priority FROM Calls WHERE CallID = 0", dbOpenDynaset, dbSeeChanges)
    rs.AddNew
    
End Sub

Doing it this way means I can at any point use this class object in any part of the app to create a new call log.

The form itself has this code...

Option Explicit
Private oCallLog As New clsCallLog

Private Sub Add_Call_Log_Click()

    Dim bOK As Boolean

    bOK = oCallLog.AddCallLog(Me)
    
    Call UpdateView
    
    If bOK Then
        MsgBox "Call Log Notes has been added successfully."
        Call Close_Click
    Else
        MsgBox "Please complete the form where highlighted."
    End If
    
End Sub

Private Sub Clear_Click()
   
    oCallLog.Clear
    
    ' update view
    Call UpdateView

    
End Sub

Private Sub UpdateView()

    Dim k As Variant
    Dim ctl As Control
    
    ' reset colours
    For Each ctl In Me.Controls
        If oCallLog.Cols.Exists(ctl.Name) Then
            ctl.BackColor = RGB(255, 255, 255)
        End If
    Next
    
    ' highlight errors
    For Each k In oCallLog.Failed
        Me.Controls(k).BackColor = RGB(111, 255, 255)
    Next
    
    ' clear errors
    oCallLog.Failed.RemoveAll
    
    ' update form fields
    For Each k In oCallLog.Cols
        Me.Controls(k).Value = oCallLog.Rec.Fields(k).Value
    Next

End Sub

Private Sub Close_Click()
    Call Form_Communications_Progress.UpdateView(True)
End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.ContactID = lContactID
End Sub

Private Sub Notes_Enter()
    Me.Notes.BackColor = RGB(255, 255, 255)
End Sub

Private Sub Priority_Enter()
    Me.Priority.BackColor = RGB(255, 255, 255)
End Sub

Private Sub CallType_Enter()
    Me.CallType.BackColor = RGB(255, 255, 255)
End Sub

Basically now I have a form with unbound controls, that uses an MVC type paradigm (OK it's more MV) that can be used with ease anywhere else on any form.

I have a tab control with a datasheet master view, I dynamically load the tab control form when one of the menu icons is clicked and use the class to create the new record, after which the datasheet view is refreshed and instantly the new record is visible. You'll also notice it gives me the ability to highlight invalid / required fields.

OK, this doesn't deal with editing exiting record but the principle is the same you just need to add the updateview of the form for an exiting record plus an the update button functionality.

Though you could use another form with a bound query for a single record.

Dunno perhaps others here think having one form that you dynamically manipulate through code is better as you are trying?

But having a form bound to a query that needs to be a dynaset for updating can cause you a lot of problems with record locking if using the app in a multi-user environment.

I even have a locking flag in some of my forms, so if a user double clicks a datasheet record selector to select a record, it opens the form and marks the record with the user ID, so if anyone else tries to open the same record they will get a user friendly message telling them that user XYZ is currently editing that record. I no longer get that annoying and user baffling message that someone else has changed the record and you have to drop your changes.

I find developing separate classes away from form binding means I don't even need to use a form to create a new record in the DB, I can use my class and pass it the required data programmatically.

This is only an example of the way I do this sometimes, you may not like it, and I apologise if I wasted your time having to read my post!



"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

Thank you so much for your detailed response. I'm glad you understand what I am doing! I haven't really worked with classes before in Access, so not sure I will be able to figure all that out, but it looks like you have a really good solution. Some of the concerns I will have is that when people are just viewing the records, I don't think I can use datasheet view. I have an unbound text box control that is formatted and has a function behind it to act as a hyperlink. It opens the document from our document management system. I am not sure that will work in datasheet view.

I also don't really have a concern about people editing the same record. The users are going to be splitting up docs to enter in, and then them or others may be looking up docs to view, but editing will be rare.

I will look through your solution and see if it can work. Do you, by chance, know a site that has a good reference for the syntax of controlling a sub form's properties? Just would like to find it all in one place.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Thank you so much for your detailed response. I'm glad you understand what I am doing! I haven't really worked with classes before in Access, so not sure I will be able to figure all that out, but it looks like you have a really good solution.

No problem, you are most welcome and I completely understand your concerns.

It took me a long while with the nice folks here on Tek-Tips bashing me over the head until it started to sink in, and even then I sent myself back to college via the Open university to get a better understanding of OOP / MVC / classes etc.. And I'm still learning and have a long way to go!

I don't think I can use datasheet view. I have an unbound text box control that is formatted and has a function behind it to act as a hyperlink. It opens the document from our document management system. I am not sure that will work in datasheet view.

Remember you can have a datasheet view as a subform, with a button on the parent that can perform your action, as it can interrogate a subform for information on the current selected record in the datasheet.

In that screen shot there are several sub forms including the nav control.... look at it in design mode...

1 = Master Parent form
2 = Nav control with built in subform container
3 = Call logs subform (datasheet view)
4 = Email logs subform (datasheet view)

But they all work together as a whole application, plus as you know you can simulate a datasheet by creating it as a continuous form giving you the ability to add controls to the form header / footer if desired.

Do you, by chance, know a site that has a good reference for the syntax of controlling a sub form's properties?

No sorry, I built up my understanding of all the properties over time as I got more familiar with Access and better understood what I was doing, the properties then start to make more sense, rather than being a huge list of mystery!

If you have a specific question about a specific property, you have already found the best place to ask ;-)




"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

That was what I was hoping wouldn't be the answer, lol. I have been struggling with finding that you can't change the view of a form (or defaultview property) unless you are in design mode. This forced me to make a decision. I would have to hide the form, make it go into design view, change the view property, save it, and then make it visible again. It will definitely be easier to copy the form and have 2 forms, that I show based on toggle button.

I definitely appreciate that I need to learn about classes. What are MVC (MV)? I did attempt to learn HTML5, CSS3, and vb.net/asp.net, but I have yet to put it all together, which is why I use access. I definitely need to learn more about MVC in general, and how I can implement it into my access applications. I've gotten pretty good at SQL schemas, with referential integrity, and normalization. In Access, I need to get better at designing the interface in a dynamic way that allows users to manipulate the data in a seamless manner.

Thanks for your help.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

MVC = Model / View / Controller

MV just means I skipped the controller part and have the model / view interact directly.

MVC in some circles is a dying paradigm and even a bad word.

But for me it helped with separation of concerns.. which is all about not dumping the whole application on a single button function attached to a form.. it also helps considerably with DRY (Don't Repeat Yourself).

A form should only be concerned with keeping the GUI accurately up-to-date, not perform business logic and functionality.

If you separate out the function of manipulating and creating DB records to a class, it is no longer bound to the event handler on a form, giving you flexibility as well as being able to use the class else where without having to write the same code on yet another form event handler!

If you have the data modelling sussed as you imply with proper referential integrity embracing normal form and the other data modelling paradigms, you are already putting yourself in good stead to build powerful applications that will provide your users with functional applications.

Seriously though if you are having to programmatically switch between design and other modes to achieve something, your approach is either flawed, or my knowledge is lacking as I have never had to perform such circus tricks to get an application working in a corporate production environment.

vb.net/asp.net, but I have yet to put it all together, which is why I use access.

Don't ever think like that again, you use tools to achieve a specific goal, it matters not what tools you use, only the end goal is important.

Access is awesome, yes it is not full scale hardcore programming with true OOP / inheritance etc.. Hell access methods don't even have a constructor let alone inheritance / overriding / overloading / polymorphism / blah / blah / blah.

Would I like to be a true VB.NET coder, of course, but that doesn't stop me squeezing every last bit of juice out of access to achieve the same thing, use what you have to it's full potential to get your goal done, that's the no.1 goal, not putting the latest buzzword on your CV!

Hell I still code and absolutely love Perl as my server side web app language of choice, I have yet to have a problem that I couldn't solve with Perl (though of course I have had plenty of bruises on my head trying to get something to work - thought that's probably more me than the actual language),but until Perl can no longer solve my IT problems, I have no reason to change the web language I use, regardless of what ever language or framework is current flavour of the month!

So don't say

which is why I use access.

as though you are letting yourself down, it is a tool to achieve a goal, and if you get to grips with it, it's pretty damn good at it!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

Thanks so much.


That explains the concept so well! It really does motivate me to separate it out, because I see the purpose for the separation, even if I will see the benefit more clearly once I start to organize things like that.

My old boss says the same thing about programming in access, vs other methods. It does have a lot of uses, and I do love being able to use it. I just know my end users want a web page they can go to, and have everything fancy.

I just want it to work and not be buggy!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

and have everything fancy.

Define fancy? Making it work and making it look pretty are two completely different skill sets, of which I only possess the first, that's why we have a graphic designer!

users want a web page they can go to

I've never used Access in a web application (other than backend DB storage), perhaps you could teach me something

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

What I mean is they would prefer a web application over Access. I don't user access in web applications either.

In any event, I am now settled into have 2 forms and just making the right one visible and then controlling it's behavior on it's own. This project needs to get done ASAP, so I may not have time to really get into making classes for this, but I definitely want to check them out. I will probably start cleaning up all my apps with them.

Thanks again!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

What I mean is they would prefer a web application over Access. I don't user access in web applications either.

Oh I assumed you were doing this new on demand access SharePoint web based thingy - something I know nothing about!

Is there a reason they want the app worldwide rather than a secure in-house application, do you have many remote users and web based employees? Or is your company simply wanting everything over the TCP/IP Web based protocol rather than client based installed apps?

In any event, I am now settled into have 2 forms and just making the right one visible and then controlling it's behavior on it's own. This project needs to get done ASAP, so I may not have time to really get into making classes for this,

For sure, my deadlines are always yesterday.

It will take you time to abstract your thinking into classes, and it is never easy when your mind tells you that doing it anyway now is more important than the right way later. The end goal is always the prise, the route is simply the journey!

But it should at least get you thinking that breaking it down to smaller parts that deal with only their specific concerns is better than trying to be too clever and make an entire app with only one form and a gazillion form event handlers doing acrobatics with low level features to make your app work.

Which hopefully you have already found is the case with simply going the two form route with their respective designated purposes ;-)

Though don't get me wrong, HTML5 is ushering in a new era of single page web apps, but that's another discussion NOT for the MS Access forum!

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

I thought I would update you and let you know that I ended up turning this into a public function with formal form references. Now I call that and have replaced about 60 lines of code with about 5. It's great. All my code is going to get changed into functions!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[1DMF]

Nice job!

Once you separate what you can into generic functions and classes, you will find you write less and do more.

I've yet to refactor all my apps like this, and still have spaghetti code on event handlers in some legacy apps, as they say -> "Rome wasn't built in a day"

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[misscrf]

So true! I can so see turning my navigation into classes.

misscrf

It is never too late to become what you could have been ~ George Eliot