Elegant solution required to negate the use of additional forms

[russiver]

Hi all,

I’m looking for an elegant solution to achieve the following?

I maintain a club membership database, the principle tables of which are member_deatails (name + addresses etc) and club_membership (a members membership details by year). The tables are linked by a one-to-many field member_id.

Currently membership renewals are processed via a continuous form. membership_renewal (fields: member name + renewal yes/no check box) which is populated in the following way:

Firstly an append query adds a record for the new membership year (say 2009) to the club_membership table for all members who were members the previous year (2008).

I then populate the membership_renewal form with a query based on both the member_deatils and club_membership tables using the select criteria 2009 for membership year.

Finally I use a delete query to delete any 2009 entries in the club_membership table for unchecked (yes/no checkbox) members.

This all works fine, however elsewhere (single member updates) I’d also like to use the same membership_renewal form and background queries with different select criteria and this is where I’m struggling. I do not think form filters will work in this situation.

It would seem inelegant to me to have to create another renewal form and set of queries and this is where I’m looking for direction.

Should I be looking to use programmatically defined queries?

Is it possibly to modify the where criteria of query (much like a filter) programmatically?

Is it possible to specify a forms record source programmatically?

Many thanks,

Russell Skinner

 

[DCrake]

If form C is being called from say from form A or Form B and only one of them will be open at any one time then you could use the IsLoaded() principle

Sub FormC OnLoad()

If IsLoaded("FormA") then 
   Me.Recordsource = "QueryA"
Else If IsLoaded("FormB") Then
   Me.Recordsource = "QueryB"
Else
   ....
End If

End Sub

Place this code in a module

Public Function IsLoaded(strFormName As String) As Boolean
    Dim i As Integer
    For i = 0 To Forms.Count - 1
        If (Forms(i).Name = strFormName) Then
            IsLoaded = True
            Exit For
        End If
    Next
    
End Function

 

[russiver]

Thanks Dcrak for an elegant solution.

Form C, as you put it, in my case will either be opened via form A, or a switchboard menu item, but I think that will only mean a simple mod to the conditional statement you suggest which I hope I can manage.

Thanks again,

Russ

 

[gemma-the-husky]

if you have a single item form, its often displayed in a different way to continuous forms

if you have buttons to do things, sometimes something that is relevant for a number of users will be meaningless for more than one

because of things like this, although it may seem inelegant to duplicate stuff, its often quicker!.

a lot of the time, you can put the functions in a standard module, and call them form each form, so you dont have to worry about maintaining code

i often end up with separate forms to
a) add a new item and
b) amend existing items for similar reasons

 

[russiver]

Gemma,

You make a good point and I really like the standard module idea.

As for continuous forms, I've had my fingers burnt in the past, but they do make processing a lot of simple entries , like a check off list in the case, easier for the user.