Referencing a form value to another form

[cheyanne]

I have two tables tblPets and tblVisits and two forms named, respectively, frmPets and frmVisits.

On the frmVisits form I have a unbound field with a DLookup which, for the current record


on frmVisits goes to table tblPets, looks up the weight of the pet concerned and places it in the unbound field on frmVisits.
Fine so far!

I have bound field on the frmvisits called WeightToday with a name of txtWeightToday.

What I need to happen is that when the weight is entered into this field (on frmVisits) and the current record saved, is for that
weight to replace the previous weight in tblPets so that, on the next visit, the unbound field on frmVisits now shows
the latest value i.e. the value of txtWeightToday.


I have tried this code

[code]
Private Sub txtWeightToday_AfterUpdate()
Forms!frmPets.txtWeight = Me.txtWeightToday
End Sub
[/code]

but get an error message saying that it cannot find the referenced form i.e. frmPets

Has anyone any idea why this code will not work and importnatly how to get it to work?

Any assistance will be greatly appreciated.

Regards

Cheyanne

[cplmckenzie]

Try this ....

Forms![frmPets]![txtWeight] = Me![txtWeightToday]

If that is no help post you db for a look and see.

cplmckenzie

[June7]

Is frmPets open to the associated pet record when the code runs?

Have you considered form/subform arrangement? Main form bound to tblPets, subform bound to tblVisits. Open main form to pet record and all visits can be viewed in subform and the last weight data is presented. No need to save weight to tblPets.

[cheyanne]

Hi

No sadly it didn't work. It appears to be very close to the code I tried before namely

Forms!frmPets.txtWeight = Me.txtWeightToday - i,e, just minus your square brackets.

The problem may be because the frmPets is not open when frmVisits is being used.

Thanks anyway.

Regards

Cheyanne
.

[cplmckenzie]

Yes. To access a control on a form the form must be opened.

Visit this url to Microsoft Devvelopers Network.

It may help u as it has me.

http://support.microsoft.com/kb/209099

If you want post your work and let me look at it.

cplmckenzie

[cheyanne]

Hi June7

As usual thank you for your interest shown.

I actually have two subforms already on the frmVisits page, the top one brings in the Cllient details
whilst the lower one brings in the details of their specific pet. I have uploaded a screenshot of
frmVisits which hopefully will show you what I mean.

I have found a way to bring in to the frmVisits the exisitng weight value from tblPets (DLookup). I also
have a bound field to record "Todays" weight and have even introduced a facility to show the difference
in weight - showing gains in black and losses of weight in red, along with the percentage loss or gain.

The screenshot shows a weight loss. Apparently weight loss can be serious and is something Vets need to
monitor - I therefore thought that providing not just the weight difference but also the % gained or lost would
be appreciated by them.

If it is at all possible I would like to "update the tblPets table with the new weight, once the Vet has saved that particular
record. Any other queries please feel free to ask.

Have a nice weekend

Regards

Cheyanne

[cplmckenzie]

"If it is at all possible I would like to "update the tblPets table with the new weight, once the Vet has saved that particular
record. Any other queries please feel free to ask."


First thing comes to mind is...

If weight is a field in the underlying table for tblPets, then you can run some VBA code to search the table based on the particular criteria for that pet then update the tblPets.weight field with the new then perform an update to the record.

cplmckenzie

[cheyanne]

Yes tblPets has a numeric field that holds the value of the weight. This weight was taken on the Pets previous visit.
The table is called tblPets and ther bound field Weight with the name txtWeight. Moving to the Visits table a DLookup
brings the previous weight onto the Visits page. You can see this in the screenshot I uploaded recently. The Visits form
has several subforms the top two being data from the tblClients and the one immediately below it is one that reflects
data from tblPets. This is the code I used to do that.

Me.txtWeightPrevious = DLookup("[Weight]", "tblPets", "[PetRef]=forms!frmVisits![txtPetRef]")




I wondered if I could find some VBA code to find the "old" weight and substitute "Todays" weight and send it back to tblPets
and update that particular record..

Regards

Cheyanne

[cplmckenzie]

cheyanne,

just for clarity, I am cplmckenzie.

Now, what field in the tblPets specifies a partular pet.

There is an afterupdate property for txtWeightPrevious that will fire once dloopup put the weight in that that field.

Okay, without seeing your form, we will take a shot in the dark.

In the VBA editor, in the main Form there swhould be a procedure name cmdtxtWeightPrevious_Click().

Begin by dimensioning variables and setting references to the database and record objects...like.

Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("yourdatabasename.mdb or accdb")
Set rs = db.OpenRecordset("tblPets", dbOpenTable)

Loop through the records using the "particular_pet_data" from comparing it to tblPets.petweight.

Once that record is found then assign rs.particular_pet_data = Forms![MainFormName]![SubformName Control]

update the record.

end loop

close rs
close db.

in the rough without seeing the actual form.

Do you know the names of the form controls and the table fields

cplmckenzie

[June7]

Again, I want to point out that duplicating the pet weight appears to be an unnecesary complication to the data structure. However, if you really want this, I suggest an UPDATE sql action in the AfterUpdate event of weight textbox on the visits form.

CurrentDb.Execute "UPDATE tblPets Set Weight=" & Me.Weight & " WHERE PetRef=" & Me.PetRef

[cheyanne]

Hi June7

Thanks for that it is absolutely brilliant and works like a dream - Silvina (the Vet) will be very happy with your efforts.

I did find another problem over the weekend about "authorised" medicines that can be bought at the Pharmacy. Her clinics are basically
divided into two sections - her (or another Vet's( treatment clinic) and the Pharmacy which lies at at the front of the clinic. The Pharmacy
is used by "authorised" Clients to buy "authorised" medicines for one, or more, of their "authorised " pets.

Instead of wasting Silvina's time the Client simply visits the Pharmacy area and asks the assistant for a repeat medicine.

Silvina authorises a Client by ticking a checkbox on frmClient. She then uses tblPets and identifies that specific pet, of that Client, and ticks the
"Do you want to authorise this pet" checkbox. When she vists frmAuthorisedProducts, Silvina clicks on the combo box cboClients and this shows her, in alphabetical order,
all her Clients whom she has authorised to buy medicines at the Pharmacy. She then clicks on the cboPetNames combo and selects a pet, of that Client (NB only "authorised Pets
are shown) and then proceeds to select a medicine from cboApprovedList which is then associated with that particular pet.

Because we have experienced problems in the past e.g. a Client cannot remeber the medicine name or strength, Silvina has been concerned that the assistant or Client
may have guessed what they thould should be dispensed.

When the database becomes functional, the assistant will only be allowed to sell a Client medicines if both the Client and a particular medicine have been "authorised" for a specific pet.

I have resolved that aspect but have realised that on the frmAuthorisedProduct it is possible for Silvina to authorise the same medicine for the same pet more than once.
Clearly this is unhelpful. I thought of providing a combo box, on that page, which showed the products already authorised for that specific pet. I then wondered if it was
possible, having selected a product for a second time, to have effectively a count facility (maybe on the txtProduct AfterUpdate event) which, in the event of a duplicated selection,
showed a message box advising that this product had already been authorised for this particular pet.

Have you any ideas which would be the better way?

Regards

Cheyanne

Update

Have decided to have a dropdown combo box because it will show the Vet all the medicines that have been approved for a particular pet.
The alternative of checking to see if a medicine had been duplicated or not does not provide the whole picture. You live and learn!
Spent most of today doing the same and am now able to report that I have finally achieved my goal and the form is working as intended.

[cheyanne]

Hi June7

I recall you, or Recyan advising me that it was bad practice to have fields from table1 in table2 (except for ref ids e.g. where the forms have
a relationship of course).

My frmVisits committed that sin in that I had two fields Surname from tblClients and PetName from tblPets (these were in addition to ClientRef and PetRef
which were the ID fields for tblClients and tblPets respectively. In an effort to be "more professional" I deleted Surname and PetName from tblVisits.
Rather than, as before, using tblVisits as the control source for frmVisits, I made a query qryVisitsForm which had the tblVisits - all fields separately entered
and to that I added tblClient (for Surname) and tblPets (for PetName and Weight) and then used ClientRef and PetRef as the linking fields.


The form worked well until I made field Weight (previously unbound) bound to Weight from tblPets. The problem I had before was that although the "Weights"
section worked perfectly, at that time, when you saved the record and revisited it the weight values were lost even though this was needed as an historical record.
Partly why I decided to not use unbound fields.

Having made Weight (the field from tblPets) bound it now shows that weight, for a specific pet, as one moves through the records. However now when one enter WeightToday
(still unbound although I have such a field in tblVisits) it accepts the entered value and makes the calculations but does not update tblPets with the new value as was the case
before I made these alterations. Indeed I get an error message saying that it can't update the record. I do not see what I have done to disrupt your "update"code which worked
perfectly before I made the changes referred to above.

Can you see what is causing the problem? I have a backup of the pre changes situation so I can always go back to that albeit tblVisits would contain fields whci should
be in tblClients and tblPets. I should add that if I make field WeightToday (held in tblVisits) bound when I enter the field an error sound occurs and although the cursor
is flashing, in that field, I cannot enter data.

Update I have just realised that the field WeightToday was bound in the previous version. I have now made that bound, but the update error still shows. Also the
cboClients and cboPets are now making a sound and refusing to accept a selection. Why does this happen. Do you know? I do get this problem fairly regularly and
normally resort to deleting the old combo box or field and making new ones. It would be useful to know why this happens rather than my scraching my head and
then simply deleting the problem.

I'll keep trying, but resorting to the old version seems favorite - at least it works!

Update 2 Restored the backup database and this time removed just the PetName field from tblVisits. I then made a query using all the tblVisits fields and then joined tblPets and
addedn tblPets.PetName to the query. I tehn saved the query and used it as the control source for frmVisits. Everything works perfectly - no errors.

It would appear that when I remove the Surname field, from frm tblVisits, and add tblClients to the query and then the field tblClients.Surname that the errors appear.
I am not too wooried about having two foreign fields, from tblClients, in tblVisits. I suspect that in the VBA code of tblVisits there is a conflict which is unhappy if there
is no direct reference to tblVisits.Surname.

Regardless I am pleased with the database.

Regards

Cheyanne

[June7]

We advise not to duplicate data without strong justification, such as to prevent loss of data in effect at time record was created. This can be a concern for something like pricing because fees can change over time. Either the data must be saved or another 'price' record must be created and price records would be tagged as 'active' or 'inactive'.

Not quite following all your description of the latest issue. Do you want to provide project for analysis? Follow instructions at bottom of my post.

[cheyanne]

Hi

I am not surprised that you are confused!.

What I was saying is that either yourself, or Recyan, had commented upon my not using fields from say tblClient in tblVisits.
- that is other than ther link field e.g. txtClientRef. In both tblPharmacy and tblVisits In was guilty of that offense. I decided to
remove the offending fields namely tblClient.Surname and tblPets.PetName and ended up with several error messages and partso
of frmVisits not working.

I re-installed the Backup copy of the database but this time only removed tblPets.PetName, made a query to include all these
fields and ran it - it worked perfectly. I then made that query the control source for tblVisits (previosly tblVisits was the control
source.

However when I did the same execise but with just tblClient.Surname, i.e. removing it from tblVisits and produceing a query as the
control source, I again got errors. proving that the problems lay with the removal of ClientRef.

Of all the tables I have in the database only tblVisits violates the rules of normalisation, because it has tblClients.Client Ref as
the field ClientRef in its table. As the rest of the database currently does all that has been asked of me I am minded to leave well
alone and accept that singular piece of bad practice.

I shall be seeing Silvina again on Saturday morning to see if she likes what has been created. Although a little biased I believe it is
seriously better than what she has now - albeit a professionally designed programme costing serious money. I may have mentioned
before it has over 230 tables. I have not seen the front end as their backup system only stores tables. It is that complicated, one mamber
of staff told me he had to vists four or five forms simply to produce an invoice. The staff at Silvina's clinics rarely use the programme from
what I have seen and been told. The same is true for Silvina who has readily admitted the same to me. If programmes are long winded,
difficult to use or overly complex then Staff will not use them - no matter how good technically the programme may be.

I re-iterate that without your and Recyan's assistance, my endeavours would have been greatly diminished.

The Intenet has very many serious failings but forums, such as this one, are marvellous and without the Internet such global interaction
would be impossible. Remember that, over twenty years ago, I designed databases, and then you were really ON YOUR OWN, having to
rely on expensive books as your "tutor". Thank God times have changed albeit I would love to see the end of the "bad" parts of the
Internet i.e. spamming, interlectual theft, identity thefy etc.


I'll let you know what happens on Saturday.

Regards


Cheyanne