Reusing code

Dairy Farmer

Registered User.
Local time
Today, 22:53
Joined
Sep 23, 2010
Messages
244
The DB has 2 BE's. One for each farm. Reason: replication is done using sneakerware. One farm doesn't edit the other's data.

The FE has duplicates of each form, just the references are different so as to use the correct BE. i.e. F_1_DateCheck and F_2_DateCheck.

I would like some help on the VB code side. I have part of the code listed below.

Is it possible to set Forms![F_1_DateCheck]![CIM] as FormDateCheckCIM at the start of the code. That way I don't have to go through the whole code and change the reference.

Code:
[B]Current:[/B]Private Sub CowDairy_AfterUpdate()

If CIM > [COLOR="blue"]Forms![F_1_DateCheck]![CIM][/COLOR] Then
    Beep
    MsgBox "That is the most cows in milk on one day!", vbInformation, "CONGRATULATIONS"
    [COLOR="blue"]Forms!F_1_DateCheck.CIM[/COLOR].Requery
End If
End Sub

[B]Possible?:[/B][COLOR="Blue"]Forms![F_1_DateCheck]![CIM][/COLOR] = [COLOR="Red"]FormDateCheckCIM[/COLOR]
'or in form2
'[COLOR="Blue"]Forms![F_2_DateCheck]![CIM][/COLOR] = [COLOR="Red"]FormDateCheckCIM[/COLOR]

Private Sub CowDairy_AfterUpdate()

If CIM > [COLOR="Red"]FormDateCheckCIM[/COLOR] Then
    Beep
    MsgBox "That is the most cows in milk on one day!", vbInformation, "CONGRATULATIONS"
    [COLOR="Red"]FormDateCheckCIM[/COLOR].Requery
End If
End Sub
 
Would something like this work?

Code:
Dim FormDateCheckCIM As Access.Control

Set FormDateCheckCIM = Forms![F_1_DateCheck]![CIM]

...

Set FormDateCheckCIM = Nothing

Also, as an alternative, if you want to be more explicit, you can do this instead:

Code:
Dim FormDateCheck As Form_F_1_DateCheck

Set FormDateCheck = Forms![F_1_DateCheck]

Debug.Print FormDateCheck.CIM

Set FormDateCheck = Nothing


But more to the point, why have two forms? Why not just use one form with a database identifier to identify the source?
 
why have two forms?
I had written "identical" queries to read the two datasets.

Why not just use one form with a database identifier to identify the source?
I didn't know how to do that, but I am looking into it learning it.

The basic setup is:

Select farm
Click menu button (form opens as per farm selection)
Form is:
Header = month/year dropdown (query dates from "main" table, converts dates to distinct month/year)
Detail = TabCtrl
Each tab has subform linked to month/year dropdown
 
another why of looking at it is separating the two farms like you would different companies. If you used two FEs rather than two BEs you just change the default Farm.

Simon
 
I have 2 extra sections in the DB that have combined figures and compare figures.
 
Maybe I should attach the DB. I'll do it tomorrow. It will be password protected. If you want the password I will PM it.
 
I had written "identical" queries to read the two datasets.

Are they different only in say, "WHERE FarmID = XX"? If so, then you actually do not need two queries; only one query. There are two possible approaches you can use to reuse the same query for both farm:

1) Use WhereCondition of the form to apply the additional criteria of FarmID = XXX just in time. It's one of argument in the OpenForm:

Code:
DoCmd.OpenForm "MyForm", WhereCondition:="FarmID=" & CurrentFarmID

So the form's query only need to have the criteria that applies to both farms and when you run the application and set the variable "CurrentFarmID", you will get all results specific to the chosen farm.

The second technique is to use a parameter query. For simplicity's sake, let's say we have a hidden form that's open at startup and determine which farm ID it should use and stores it in an unbound textbox. Users will never see this form; it's only for programming convenience. In such case, your query can be then:

Code:
WHERE FarmID = [Forms]![MyHiddenForm]![txtFarmID]

And you'll achieve the same result as you did with WhereCondition. The tradeoff is that you must have this hidden form open (but hidden) at all times. If you try to open the form/query and the hidden form isn't open, this will cause an error. There is also a way to create parameters by itself, like this:

Code:
PARAMETERS myParam LONG;
SELECT *
FROM myTable
WHERE FarmID = [myParam]

which if is not filled already, will display a small inputbox asking for user to fill in the parameter. Some people may try to do this:
Code:
SELECT *
FROM myTable
INNER JOIN myFarms
  ON myTable.FarmID = myFarms.FarmID
WHERE FarmName = [Enter the farm name]

so the little input box will show "Enter the farm name" which is slightly user-friendly but I want to emphasize: DON"T do that. On the first blush, this may work nicely but what if the user mistype the name? Or maybe didn't type in anything? Or enter invalid data? There, you have no protection and are forced to deal with ugly errors. The previous technique of referencing other form controls is more acceptable on the reasoning that you can validate the data within the form -- that is especially true for report criteria - it's easier to show users a form with say, combobox listing farm name for user to choose from and have the FarmID be inputed into the report using the previous technique than to rely on the user being accurate in the input as in this technique.

Yet another technique is to use dynamic SQL. For examples of this technique refer to this wiki article. This is more primarily useful when we need more complex parameters and especially when we want to allow some parameters to be optional, in which all techniques discussed above except for WhereCondition is of no help (well, it could be worked around but it tend to hurts more so not worth it anyway).


I hope this helps somewhat.


@SimonMT: Note that Dairy Farmer said that two BEs were "synchronized" using sneakerware which I take to assume that the computers at both farms are not connected so 2 FE/1 BE isn't feasible unless DF deign to invest in a Terminal Server (likely overkill) or a server-based RDBMS that is more tolerant of WAN connections from both FEs (of which there are plenty of free engines to download & install if DF doesn't mind the additional complexity)
 
There has to be 2 BE's because we use cell phones to connect to the internet using GPRS which is slooooooow. We have no dsl or 3G and the data caps are extremely low. The price of living in rural third world. We visit each others farms at least once a week. Hence the sneakerware (maybe I could coin the phrase USBTyreWare?).

I was going to ost the DB, but I am currently working on a new "switchboard" and menu system. It shouldn't take more than a week to get it working and the code cleaned up.

@Banana, I'll look into this. I need time to digest.

At the moment I don't use the farm name as a parameter in my queries. The query uses the specific BE to gather the info needed.
 
Please pm me for password.

The graphs don't work yet and some of the avg and sum are a bit out. But the idea is there.

Make sure you have the db in a trusted folder.
 

Attachments

Users who are viewing this thread

Back
Top Bottom