A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Form/subform setup



 
 
Thread Tools Display Modes
  #1  
Old March 27th, 2010, 09:16 PM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default Form/subform setup

Hello!

For a Veterinarian database, I can't seem to get the form/subform
structure working.

Open the form. Enter the date. Then, in a subform below, enter the ID#
of the dogs that showed up that day. (could be one, could be 50). When
you enter the ID, info for that dog can be seen (name, breed,etc.)


Then for the selected dog (another subform?), enter the conditions the
dog has (could be 1, could be 50). (Cough, limp, etc.)

??

VR/Lost
  #2  
Old March 27th, 2010, 09:39 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Form/subform setup

On Sat, 27 Mar 2010 13:16:13 -0700 (PDT), Lostguy wrote:

Hello!

For a Veterinarian database, I can't seem to get the form/subform
structure working.

Open the form. Enter the date. Then, in a subform below, enter the ID#
of the dogs that showed up that day. (could be one, could be 50). When
you enter the ID, info for that dog can be seen (name, breed,etc.)


Then for the selected dog (another subform?), enter the conditions the
dog has (could be 1, could be 50). (Cough, limp, etc.)

??

VR/Lost


Let's start with tables, since they are fundamental; forms are just windows,
tools to manage data stored in tables. If your table relationships aren't set
up correctly you'll have no end of trouble with your forms! How ARE your
tables set up?

Just FWIW, in my animal-shelter database veterinary services module, I use
tables for AnimalOwners, Animals, Conditions (a table of all the medical
issues that might be encountered), AnimalConditions (linked to Animals and
Conditions), Visits, and several more auxiiliary and lookup tables - not
trivial!
--

John W. Vinson [MVP]
  #3  
Old March 27th, 2010, 10:26 PM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default Form/subform setup

Sir,

Here are my tables:

tblDog
DogIDpk, Autonumber
DogName, Text
Breed,text

tblCondition
ConditionIDpk, Autonumber
ConditionDesc, text

tblDogCondition
DogIDfk, number
ConditionIDfk, number

I understand about forms. I was just trying to explain how the form
should look to a user. I am getting wrapped around the "pick date,
then enter dogs, then conditions for each dog" which seems to be a
mainform-subform-subform setup that i don't know how to do.

I appreciate your help!

VR/Lost
  #4  
Old March 27th, 2010, 10:56 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Form/subform setup

On Sat, 27 Mar 2010 14:26:48 -0700 (PDT), Lostguy wrote:

Sir,

Here are my tables:

tblDog
DogIDpk, Autonumber
DogName, Text
Breed,text

tblCondition
ConditionIDpk, Autonumber
ConditionDesc, text

tblDogCondition
DogIDfk, number
ConditionIDfk, number

I understand about forms. I was just trying to explain how the form
should look to a user. I am getting wrapped around the "pick date,
then enter dogs, then conditions for each dog" which seems to be a
mainform-subform-subform setup that i don't know how to do.

I appreciate your help!

VR/Lost


But there is no date in any of your tables, and a date is in any case just one
data field - I can't see any point in having a Form with just a date! What is
this the date OF? An examination/visit? Don't you need at least one or two
more tables?
--

John W. Vinson [MVP]
  #5  
Old March 28th, 2010, 12:18 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default Form/subform setup

Sir,

Yes, I missed that one.

tblVisit
VisitIDpk, autonumber
VisitDate, date/time
DogIDfk, number

Then I linked all the IDpks to their respective IDfks.

Can you see anything else missing?

VR/Lost
  #6  
Old March 28th, 2010, 01:10 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Form/subform setup

On Sat, 27 Mar 2010 16:18:35 -0700 (PDT), Lostguy wrote:

Sir,

Yes, I missed that one.

tblVisit
VisitIDpk, autonumber
VisitDate, date/time
DogIDfk, number

Then I linked all the IDpks to their respective IDfks.

Can you see anything else missing?

VR/Lost


So there's one and only one dog on any visitdate? Surely not.

What are the Recordsources of your form, subform, and sub-subform? What are
their master/child link fields?

--

John W. Vinson [MVP]
  #7  
Old March 28th, 2010, 02:21 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default Form/subform setup

Sir,

Hopefully, these tables are the correct base to build on:

tblDog
DogIDpk, Autonumber
DogName, Text

tblDogCondition
DogIDfk, number
ConditionIDfk, number

tblCondition
ConditionIDpk, Autonumber
ConditionDesc, text

tblDogVisit
tblDogVisitpk, autonumber
DogIDfk, number
VisitIDfk

tblVisit
VisitIDpk, Autonumber
VisitDate, Date/Time

I am working on the Record Source and Master/Child portion of your
response. Thanks for your time!

VR/Lost
  #8  
Old March 28th, 2010, 09:11 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Form/subform setup

On Sat, 27 Mar 2010 18:21:04 -0700 (PDT), Lostguy wrote:

tblDogVisit
tblDogVisitpk, autonumber
DogIDfk, number
VisitIDfk

tblVisit
VisitIDpk, Autonumber
VisitDate, Date/Time

I am working on the Record Source and Master/Child portion of your
response. Thanks for your time!


I'd simply put a date/time field in tblDogVisit rather than having an entire
table and form containing nothing but a date. You don't need to keep any
information that is specific to March 29, as a date, independent of any dogs
or visits. I think you're overnormalizing here!

You can certainly base a form on a query selecting a date or a range of dates.
--

John W. Vinson [MVP]
  #9  
Old March 29th, 2010, 01:47 AM posted to microsoft.public.access.gettingstarted
Lostguy
external usenet poster
 
Posts: 73
Default Form/subform setup

Hello!

(I originally posted this using a vet database analogy, because it
looks to me like the same setup as what I really need the database
for. Anyway, I hope I have provided enough detail here about the real
use of the database that the errors I am making are really obvious. I
tend to over-analogize.)


At our daily Quality Review Program (QRP), we review applicant records
for discrepancies. We then turn that list of discrepancies over to be
corrected. The next day, we do the same thing to another group of
applicant records. On same days, we review 10 records; on others, we
review 200. So, I thought that we can database/userform this instead
of using individual Excel workbooks by day that we keep for a year.
(One database file rather than 365 separate workbooks or one workbook
with 365 tabs.)

So, we have a QRP table:
tblQRP with QRPID (autonumber) and QRPDate (Date)

We have an applicant table:
tblApplicant with ApplicantID (Autonumber), ApplicantSSN (Text),
ApplicantLName (Text), QRPID (Number)

We have a Discrepancy table:

tblDiscrepancy with DiscrepancyID (Autonumber), Discrepancy (Text) and
ApplicantID (Number)

There is a mainform with tblQRP as its Record Source and the QRPDate
field and two subforms:

Subform control: subfrmApplicant (name) and Source Object
(frmApplicant) with LMF (QRPID) and LCF (QRPID)
Form: Record Source (tblApplicant)
Three textbox controls: ApplicantSSN, ApplicantLName, and ApplicantID
(Control Source is the same as the Name.)

Subform control: subfrmDiscrepancy (name) and Source Object
(frmDiscrepancy) with LMF (QRPID) and LCF (ApplicantID)
Form: Record Source (SELECT tblDiscrepancy.* FROM tblDiscrepancy ORDER
BY [tblDiscrepancy].Discrepancy DESC
Two textbox controls: DiscrepancyID and Discrepancy (Control Source is
the same as the Name)

What I would like to happen is to enter the QRP date in the mainform,
then enter the applicant's SSN and name into Subform 1, and then enter
the discrepancies for that applicant into Subform 2. Back to subform 1
for the second applicant for the same date, and then the subform 2
should be blank. It works mostly, but it is still showing the
discrepancies from the first applicant instead of clearing with each
applicant.

Looking at this setup, what am I doing wrong?

VR/Lost

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:18 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.