Referencing subform controls

[cheyanne]

I have a Main form named frmPharmacy upon which is a subform named frmSubAuthorisedSales.
Within this subform is a combo box named cboApprovedSales. At the moment when a Main form record
is moved either backwards or forwards the subform still shows the previous data.

I want to either Refresh or Requery the subform when a record is moved. I presume that I would enter VBA
code on the On Current event on the main form i.e frmPharmacy.

I have tried innumerable cominationf code to Refresh or Requery the data in cboApprovedSales but to no
avail.

Currently the code I have is

Forms!frmPharmacy!frmSubAuthorisedSales.Form.cboApprovedSales.Refresh

.
However upoon opening the file Access reports that it cannot find the field frmSubAuthorisedSales. I have tried prefacing it
with Forms! or Form! but neither has lved the problem.

Can anybody please assist me in producing the correct VBA code whilst also advising upon whichform and event to place the code.
Finally should one use a Refresh or Requery to correctb this matter. (or is there anoither method I should use?)

Many thanks



Cheyanne

[Bob Fitz]

Have you tried

Forms.frmPharmacy.frmSubAuthorisedSales.Form.cboApprovedSales.Requery

Also, check that frmSubAuthorisedSales is the name of the subform control. That may be the name of the actual subform but the subform control can have a different name.

[cheyanne]

Hi Bob

Many thanks for your reply.

I did give you the subform name rather than its control name.
The control name is ApprovedSales and I have amended your code accordingly - see below.

In the On Current event of the Main form frmPharmacy the code now reads - 3rd line pertains

Private Sub Form_Current()
Me.cboPetNames.Requery
Forms.frmPharmacy.AuthorisedSales.Form.cboApproved Sales.Requery
End Sub

However this seems to have produced as secondary problem namely that
the item chosen, even having saved it, when one returns to that record the
combo entry header is blank - showing (New) i.e. ready to insert a new record
onto what is already a record.


This is a specific record and, although it stores it to the table it would be useful
to see what had been bought on that form.A

Any ideas? Maybe a DLookup from the table.

In the screenshot
The top combo shows that product number 4 is authorised for Pet 2 and product
number 14, whilst authorised for Pet 1, is not authorised for Pet 2. When one
clicks the arrow on can select the correct product for Pet 2 namely product number 4.

Hope that is not too confusing!

Regards

Peter

[Bob Fitz]

In the screenshot
The top combo shows that product number 4 is authorised for Pet 2 and product
number 14, whilst authorised for Pet 1, is not authorised for Pet 2. When one
clicks the arrow on can select the correct product for Pet 2 namely product number 4.

Hope that is not too confusing!

Actually I don't really understand what you are trying do. Maybe you could give a little detail of what this form is for.
Is it a form with two subforms on it?

[cheyanne]

Hi Bob

I completely understand how difficult it is to advise from afar.
I live in Spain, near Valencia, and have been asked by a local Vet, who is a good friend
of ours, to design her a database for her clinics.

Currently she uses a database which is not bespoke and is generic in concept. This means
that it does not offer all the facilities she needs but is cumbersome to use. The end product is that
often it does not get used as she has to fit in with the database which often is illogical for her
needs.

Her clinics have Clients that visit her with their Pet for treatment by her. However because people
are reluctant to wait for a "repeat" prescription, these Clients see a Veterinary Nurse or receptionist
and say "Can I buy some more tablets for Sabre". Often this is fine as both the Nurse and Client know
the name and strength of the tablets required. The Vet however has expressed concerns that, on occasions,
she feels that an element of guesswork comes into play which could have serious ramifications for the pet.
Any such problems would, of couse, fall on her neck ultimately.

We decided the best way to resolve this was to have a "Pharmacy page" which the Nurse, or receptionist, could use
to ensure that they sold the right product for a particular pet. This is where I am now up to see screenshot Pharmacy1.
This form, frmPharmacy, advises the Nurse or receptionist exactly which tablets the Vet has authorised for a specific pet,
to be sold in the Pharmacy area. In subform frmSubAuthorisedByLine, which is an non editable form, it itemises
specific products, authorised by the Vet, for a clients particular pet (Clients may of course have more than one pet).

The Client combo box, towards the top of the form, lists all the Clients who have been approved to purchase in the Pharmacy
area. ( I use a check box on their Client file record) Having selected the correct Client the receptionist clicks the pets combo box
below it which lists the Client's pet(s) that have been been authorised by the Vet to receive specific medications. (again a
check box is used ion the pet's record) Sub forms appear, to the right, showing the Clients and Pet details when this data is entered.

On clicking the Pets combo box not only does a list of the Clients authorise pets appear, but in the sub form
frmSubAuthorisedByLine ONLY the medicines authorised by the Vet, for that specific pet, appear. As I said before
this is a non-editable subform to avoid mistakes and erroneous editing.

The subform below the above, frmSubAuthorisedSales, offers a facility to allow the Nurse or receptionist to select an
authorised medicine for the client to buy for that specific pet. The drop down combo shows exactly the same products as
shown in the non-editable sub form above. See screenshot Pharmacy 2

Everything is currently working as intended with the exception that once the record is saved and subsequently returned to
the frmSubAuthorisedSales combo box "ApprovedSales" is empty. My concern is that inadvertently a saved record will be
amended for a new sale. This will of course overwrite the original record which has been saved to a table.

I need something entered into the combo box for each "old" record and need it blank only when a new record is required.
Ideally on every old record it should show the product(s) the Client bought on that particular occasion.

Hope this makes sense to you.

Regards

Cheyanne

[R_Badger]

I'm half asleep so please bear with me if this seems a little nonsensical!

Stupid question, on the subform "AuthorisedSales" have you set the form properties to data entry? That would only allow creating new entries.

Otherwise if you want to be able to view old sales and add new ones, that should be simple using the form properties on the subform (of course assuming the record source for the sub form allows it).

Allowedits=false
Allowdeletions=false
Allowadditions=true
data entry= false/no

[cheyanne]

Hi

Thanks for your comments.
Yes I have set the form properties on subform AuthorisedSales (control name) to data entry and the other form properties
you refer to are set as you suggest.

I have just cleared the two tables (i.e. made copies saving structure only - deleted the originals and renamed the copies)
namely tblPharmacy and tblAuthorisedSalesByLine - the latter being where the subform AuthorisedSales stores its data.

I entered two records into frmPharmacy and the information entered was stored, as expected, in both their respective tables.
However the subform AuthorisedSales, although it accepted the values I entered, remains empty once one has left the form and
returned to it.

I have uploaded some screenshots which might assist you understanding what is happening

Regards

Cheyanne

[cheyanne]

Hi

Apologies it seems to be working fine now. Please ignore my previous reply.

I had mistaken one of the values you gave for the subform. (having failed
to remedy the problem, I decided to re-enter all the form properties and)
it worked.

Thanks again. I mark the problem as having been solved.

No doubt there will be others areas I shall need valuable assistance.

Thanks to both of you for your comments which were much appreciated

Regards

Cheyanne