generic code (Access 2000)

Topic

New Reply

I have the folllowing code that repeats in each object

Dim strDocName As String
strDocName = “frmMain”
Dim Department As Control
Set Department = Forms![frmMain]![Department]
If IsOpen(strDocName) = True Then
If Department = 1 Then
do someting
ElseIf Department = 2 Then
do something
ElseIf Department = 3 Then
do something
ElseIf Department = 4 Then
do someting
ElseIf Department = 5 Then
do something
ElseIf Department = 6 Then
do something
End If

And here is an example:

Dim strDocName As String
strDocName = “frmMain”
Dim Department As Control
Set Department = Forms![frmMain]![Department]
If IsOpen(strDocName) = True Then
If Department = 1 Then
base = Ste
ElseIf Department = 2 Then
base = Va
ElseIf Department = 3 Then
base = Bl
ElseIf Department = 4 Then
base = Ha
ElseIf Department = 5 Then
base = Pl
ElseIf Department = 6 Then
base = Ta
End If
Me.RecordSource = base

I want to rationalize my coding and if possible not to repat one and the same condition many times.
I will be very grateful if i could simplify my coding with somebody’s help.
What i repeat in many forms and reports is the condition in my main form called frmMain. In this form i have
an option control called Department. This is a key condition in my database becasue many functions and objects depend
on the choice in my main form.Of course they are performed only if the main form is opened.
Is it possible that i do not describe each time these conditions and do not enumerate each time the different options,
but write them only once.
I imagine the code something like that:

base = ste (Forms![frmMain]Department]
base = Va((Forms![frmMain]Department])

It didnt work for me so i am asking for help, and but may be somebody might give me a better advice.So in short i want to refer to the

options
in my main control without enumerating them each time
Thanks in advance

Reply | Quote

Replies

I see that your code sets a string variable for “frmMain”, but then you go ahead and hard code a reference to Forms!frmMain into the code, which doesn’t make much sense. I’m also getting lost in stuff like

base = ste (Forms![frmMain]Department]
base = Va((Forms![frmMain]Department])

What the heck are Ste and Va? Are these functions?

What you appear to want to do is to look at your frmMain and check the Department option group to see which department to use. Then, based on the selected department, you want to specify a particular recordsource for your form.

Unfortunately, without having a clue as to what those “Base = Va”-type lines mean, it’s hard to know what to do with this code. There are a couple of ways you could clean up and simplify it, however.

First off, you cant set a reference to control on a form that isn’t open, so you need to put the line that sets Department after the line that tests for the open form.

Second, you can use the strDocName variable in the Set Department line like this:

Set Department = forms(strDocName)!Department

If, in fact, things like Va and Ste are functions (if they are, it’s better to give functions meaningful names that describe what they do) and you want to pass them the control as an argument, then all you need to do is exactly that:

Base = Va Department

The control object carries the information about the form it’s on, so you don’t need the entire reference.

Reply | Quote

One more thought. If things like Va and Ste are functions that return a SQL string, you don’t need separate functions. The simplest approach is to have a single function that builds the SQL string and simply pass it the department control. Let that function determine what should be in the string based on the value of Department. That way, all you need in your calling code is something like this:

Dim strDocName As String
Dim ctlDepartment as Control

strDocName = “frmMain”
If IsOpen(strDocName) Then
Set ctlDepartment = forms(strDocName)!Department
Me.Recordsource = GetRecsource(ctlDepartment)
Else
‘handle the condition where frmMain isn’t open
End If

Then in GetRecsource, you would put the code that says if Department is 1, etc., do whatever. And you would return the result to the calling routine.

Reply | Quote

Excellent. Thats what i meant and your suggestions are very useful

Reply | Quote