Form or table confusion

[Lisa Reber]

Help - have been working on this problem since February
and still don't get it! And now my deadline has arrived.
In trying to normalize my tables I have done the following:
Old table had contact information and car show
registration. Now have split into diff tables:
Contacts
Vehicle Makes (all makes/models registered in the past.)
Contact Vehicles (based on past registrations)

I now have a form for car registrations - the main form
shows contact information and the subform (linked on
ContactID) shows the cars for that contact. That much
works. What keeps NOT working is having a second subform
for registration information - date / amt paid and which
vehicle(s) the contact is bringing this year.

I don't know where I'm going wrong - I want to have a list
or combo box that extracts only ContactVehicles (but will
allow entry of a new veh for that contact).

Tables ContactVehicles and Registrations relate to
Contacts by ContactID. I've tried having Registrations
link to ContactVehicles, but always get ALL CV's, not just
the ones for that contact. I'm not sure if my problem is
in the table structure or the form design, and it's making
me buggy. Do I filter form data or change my table??? HOW?

Thanks so much, and sorry for all the CAPS - y'all have
always come through in the past.

Lisa

 

[Guest]

Using the table structure posted earlier, I built a Registration and included the combo and list boxes I suggested. With this form I can see a list of registrations for the contact in the listbox, and the drop down for vehicles lists only those that the contact owns.

If you'd like to know the details of how to do the same, post back to this thread.

 

[Lisa Reber]

Dear rpw - thanks for all the input. Haven't had time yet
this morning to read & *understand* your first post, but
a quick scan looks like you're right on. The only
difference I noted was - we host just one car show per
year (it's a sideline/fundraiser - we're a vehicle museum.)

If you could post the details mentioned below, it would be
great. I've had wonderful answers from a variety of people
through this site, but when I go to do it myself, there's
always more questions about the nuts and bolts.

Again, thanks a million! - Lisa

-----Original Message-----
Using the table structure posted earlier, I built a

Registration and included the combo and list boxes I
suggested. With this form I can see a list of
registrations for the contact in the listbox, and the drop
down for vehicles lists only those that the contact owns.

 

[Lisa Reber]

Hi - was trying this out this morning after my post. The
combo box for contacts is just too cumbersome - there are
3800+ records in Contacts, and we need most all the details
to differentiate between similar contacts.

The other problem I'm having is with the list of ContactVeh
- the lookup / drop-down list shows just VehID, not name.
Have been messing around with the column order but not
getting very far. I know I have some notes on this from
the last time I had this problem.

I've printed your post and will see how I make out this
afternoon. Sure I'll be in touch again.

Thanks a lot, again! - Lisa

-----Original Message-----
Hi Lisa,

The quick, easy way is to create the form using the form

wizard and base it on the Registration table. I have the
name of the form "frmShowRegistration". Convert the
CarShowID and ContactVehicleID textboxes to combo boxes.

Add an unbound combo to show the Contact name. I have

the name of this combo "cboContact". The record source of
mine is set up to list first name, last name and is sorted
last, then first. Here is the SQL (if your tables/fields
are different, you must modify this - otherwise you can
copy and paste it to the record source property.):

SELECT tblContact.ContactID, tblContact.FirstName, tblContact.LastName
FROM tblContact
ORDER BY tblContact.LastName, tblContact.FirstName;

In the format properties, set column count to 3 and

column widths to 0";1";1" - This makes it so that the
combo displays first name/last name.

Set up the other two combo's so that they display the info you want.

Next modify the record source of the ContactVehicleID combo to this SQL:

SELECT tblContactVehicle.ContactVehicleID,

tblVehicles.Make, tblVehicles.Model,
tblContactVehicle.ContactID

FROM tblVehicles INNER JOIN tblContactVehicle ON

tblVehicles.VehicleID = tblContactVehicle.VehicleID

WHERE (((tblContactVehicle.ContactID)=[forms]! [frmShowRegistration]![cboContact]));

This filters the list of the combo to show only those

vehicles that belong to the Contact in the contact combo.

Next, you put the following code in the After Update

event [Event Procedure] .... property of the contact combo.

Private Sub cboContact_AfterUpdate()
Me.ContactVehicleID.Requery
Me.lboRegistrations.Requery
End Sub

Next, add a listbox to the form (have the wizard turned

on) and select the option to find the record that matches
the control. The wizard will automatically add something
like this to the After Update of the listbox:

Private Sub lboRegistrations_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[RegID] = " & Str(Nz(Me! [lboRegistrations], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

(Watch out for the names of the controls - Access uses

generic names eg. List12, Combo10, etc. when the control
is added to the form. Your code must match the control
names or it won't work.)

The SQL for the record source property of the list box is:

SELECT tblShowRegistration.RegID, tblCarShow.Location,

tblVehicles.Make, tblVehicles.Model,
tblShowRegistration.RegDate,
tblShowRegistration.AmountPaid, tblContactVehicle.ContactID

FROM tblVehicles INNER JOIN (tblContactVehicle INNER JOIN

(tblCarShow INNER JOIN tblShowRegistration ON
tblCarShow.CarShowID = tblShowRegistration.CarShowID) ON
tblContactVehicle.ContactVehicleID =
tblShowRegistration.ContactVehicleID) ON
tblVehicles.VehicleID = tblContactVehicle.VehicleID

WHERE (((tblContactVehicle.ContactID)=[forms]! [frmShowRegistration]![cboContact]));

Again, you can copy and paste this directly only if all

of the table and field and control names match.
Otherwise, modify the text to match and then paste it.

If you have any questions about this, or need more help, post back.

Oh, and even though you only have 1 event per year in 1

location, this still works quite well.

 

[Lisa Reber]

Forgot this question(s) in my last - what about a subform?
My focus has always started with tblContacts, but that's
because that was the only table in existance when I
started working with the DB / and learning about Access.
It was up to 75 fields, which is why I'm still working at
normalizing my tables. What I've done that works is with
tblPayments - I have frmContacts and fsubPayments. That's
why I was leaning towards:
frmContacts
fsubContactVehicles
fsubRegistrations
I like the idea of making Registrations the main form, in
part because it's making me change my focus. anyhow, I'll
give the code you posted a try, if I can get my brain
around it.

Thanks - L

 

[Lisa Reber]

Well, this will be a multi-part answer - I'll try for
clarity!
We'd like to take you up on your generous offer. But(!) I
have to get the car registration stuff completed first.

Actually it's not as bad as it appears - I've been working
on normalizing for a while, so have many fewer fields. So,
if you can help on the following fairly soon (I'm sooooo
close) then we can both learn something as you have time.

I went back to my form & subforms and used the filter you
kindly suggested. Now the problem I'm having is with the
After Update event. Here's how I have it set up:

Main Form: frmNewTestRegis | source: Contacts
Linked child/master on ContactID for both subforms
Subforms: fsubContVehTest2 | source: tblContactVehicles
fsubDDRegTest | source: tblDDRegistrations
This includes an unbound combo (cboContVehFiltered) with
row source:
SELECT tblVehicleMakes.VehMake, tblContactVehicles.Year,_
tblContactVehicles.VehTyp FROM tblVehicleMakes INNER_
JOIN tblContactVehicles ON tblVehicleMakes.VehID = _
tblContactVehicles.VehID WHERE_
(((tblContactVehicles.ContactID)=[forms]!_
[frmNewTestRegis]![ContactID]));
('_' are line continuations)

This works great once, but I'm not sure where to put the
requery code. I've tried a variety of methods/locations
(after update in both main and fsubDDRegTest)
but am stumped. (Thanks so much, again. It's great to be
this close!!!!!!!!) So I can open the main form, go to a
contact with more than 1 vehicle & filter works, but that
data is never refreshed in cboContVehFiltered on
fsubDDRegTest. (And this is clarity!)

I tend to wonder about stuff - like why it's unbound - I
used the wizard to create the cbobox, and it works. Doesn't
need to be changed, just wondering.

This form / format works the best here, because the paper
registration form is laid out somewhat similarly, and
it won't be a major brain shift for me or anybody else
who's doing the data entry. We'll have between 400 - 550
vehicles registered before we're through. Lots of them are
day of show registrations, so we have a couple weeks after
the show to post them.

Probably related in some form to Tim Reber, back in the
old country (short answer - 3 Rebers came from Germany
in 1738 & the diehard genealogists haven't tied them
together so far . . . ). Lots of Rebers here in Penna.

Again, thank you very, very much for your help, and I will
be sending a fax before too long. Let me know how to get
in touch. My problem is that I haven't loaded Access at
home yet (not sure I want to bring that much work home!)
I'll do that; I can use my pre-paid cell minutes if we need
to speak. They are often wasted anyhow, so it won't cost
the museum much / anything.

Regards - Lisa

-----Original Message-----
Hi Lisa,

75 fields in one table and you're still normalizing and

the deadline is here.... Uh, this is a church function so
there's probably no funds to hire a pro to do this for
you. Plus, it seems that you want to learn Access along
with building the app. Everyone has their own ideas about
how to build an app. I had one idea, then you presented
another bit of info/idea and I'm now thinking of a
different method.

Maybe we can 'help' each other. I'm trying to learn

Access myself and sharing what little knowledge I have
with others, along with trying solve particular problems,
seems to be a path that is working for me currently.

If you don't mind waiting a short bit of time, I'll do

SOME of the work for you - normalize the tables, create
your "main form" and maybe a couple of other support
forms, and do the code to make those forms work.

What you would have to do is fax me the current table

structure and if you need to speak to me about things, you
make the phone call (your dime). [I cannot spend but
short amounts of time at work with this, so I'd do the
bulk of it at home during evenings and the weekend, and
because I'm not expecting any compensation for it, I'd
appreciate you taking on the cost of any phone calls that
might be needed.] If the work that I do is OK with you,
then you'd have to finish the project (or just put it in
the Recycle bin).

Why do this offline from this site? Um, as you can see,

some 'solutions' can get quite lengthy even for a 'small'
problem. Your situation seems like it would be
faster/easier to just work on it than try to explain it
all here.

If this is OK with you, then fax me a printout of the

db's current relationship window along with an email
address and a work phone number. (310) 516-8065 Mark the
fax "Attn: Rick"

After I analyze the situation, I'll let you know if it is

something that I can handle in the next week or so...

 

[Lisa Reber]

Rick -
Thanks for the info on where to stick the requery - I'll
let you know if it doesn't work. The only reason I got this
far is due to replies here, yours and others.

I've been working as time allows on a Version 2 with
sample data, but I can probably copy my live version to
a new DB and delete all the data, then enter samples.

Good question re year & veh type. I kept them out of
tblVehMakes because that would lead to too many dupes -
with 15 million Model T's produced over about 20 years,
you can see where dupes can occur. I do need Year in
tblContVeh, because 1 guy will have more than one year
of the same make/model car, but might bring a different
one each year, or both. And type (car, truck, tractor,
other) is just to be able to track "How many trucks are
registered so far?" Good call in the earlier post too,
about having more than 1 contact owning a car. It has
come up just once (a friend), and he died . . . I'm
not spending that much time. But it's an interesting
and valid point.

again, thanks very, very much! Once it works, I just have
to copy it to the live version. Time for a backup!

Regards, Lisa

-----Original Message-----
I'll look forward to the fax of the table relationship

layout. And maybe you are so far along, I won't need
to 'help' the way that I thought.

For the requery - put it in the ContactID of the main

form - when it changes, then the others will follow (if it
works correctly once [probably when you open the form],
then they're set up correctly).

Not knowing about your subjects, this may be way off

base, but I don't understand why Year and VehTyp are in
tblContactVehicles. I would think that VehTyp and Year
would be description fields for tblVehicleMakes.

Now I'm thinking that it might be better if you can email

me a blank (no data) version of your db. I've read other
posts here where you should do that (empty the db out)
before you distribute it to the end user, so the how-to
could be in one of your Access books? If you don't wanna
take the time AND there is no private/confidential
information in the db, zip it down and send a copy
complete.

Why a control is unbound? Hmm, I'm not an expert but

I'll offer my 'logic'. Let's take the registration form
that I'd suggested - it was based on the table. All of
the controls that the wizard added are "bound" to one of
the fields in the table. The control for ContactID does
not have a field in that table to be "bound" to thereby
making it "unbound". Bound means that when a record is
displayed and you modify the display, you are actually
modifying the data in the table (as soon as you tab out of
the control). Unbound then means that there is no record
modification occurring. It's probably not the technically
correct answer, but I think that it comes close. (If any
of the MVP's or other expert is following this, please
feel free to confirm, add to, or correct.)

Send email to:

(e-mail address removed). Just remove
the NOSPAM from the address.

 

[Lisa Reber]

For the requery - put it in the ContactID of the main

form - when it changes, then the others will follow (if it
works correctly once [probably when you open the form],
then they're set up correctly).Well, as promised, I'm writing because it didn't work.
Here's the code in the ContactID control, AFterUpdate
event.

Private Sub ContactID_AfterUpdate()
Me.ContVehID.Requery
Me.fsubDDRegTest.Requery
End Sub

When I go to a specific contact, (I look at one with more
than one vehicle) the fsubDDReg shows the correct filtered
list of vehicles in cboContactVehFiltered. Then when I
select another contact with mulitple vehicles, the subform
has not refreshed. Thanks again!

 

[Lisa Reber]

Rick -

OK, I'll let you know (tomorrow) how this works. I'm
moving my tables / forms to a copy of the live version;
once that works, it will go fully live. Thank you very
much for all the thought you're putting into this - and
your post above, too.

As usual, thinking while I write, and I know seeing it in
the flesh will make more sense... how can I make a query
of the registrations table when there are no records? This
is one avenue I pursued yesterday that led me to go back
to my 3 panel form. Way back when (February) I had a
reply posted that said (paraphrased):

"Data structure (3 tables -
contacts/vehicles/registrations)
is what i'd recommend. I would not use a sub-sub form,
instead, I would put contact on main form, vehicles owned
on a continuous subform on the left and regis details
on a subform on the right. For the details sf, I would not
link parent/child fields; instead I would filter it by the
vehicleID from the vehicles subform in the vehicles sf
current event. I think this kind of design looks neater."

This is making a lot more sense thanks to your posts - I'll
try that tomorrow too and let you know.

Regarding sending db without data, it looks like I can
set up a new empty DB and export all the objects as
structure only, no data. Have another appt tonight, but
with luck I can get the two tries above done tomorrow, then
e-mail you the blank db before I leave, or from home.

Again, many thanks! - Lisa

-----Original Message-----
I'm not that experienced to know exactly what the problem

is from this description, hence why I'd like you to email
the db to me so I can ponder through things. But, just
off the top of my head; earlier you said that the subform
is based on a table - I would base it on a query of the
table and filter the query with the contents of the
contactId control of the main form. If the form is based
on the table directly, then there is no query to requery.

--
rpw

For the requery - put it in the ContactID of the main

form - when it changes, then the others will follow (if it
works correctly once [probably when you open the form],
then they're set up correctly).Well, as promised, I'm writing because it didn't work.
Here's the code in the ContactID control, AFterUpdate
event.

Private Sub ContactID_AfterUpdate()
Me.ContVehID.Requery
Me.fsubDDRegTest.Requery
End Sub

When I go to a specific contact, (I look at one with more
than one vehicle) the fsubDDReg shows the correct filtered
list of vehicles in cboContactVehFiltered. Then when I
select another contact with mulitple vehicles, the subform
has not refreshed. Thanks again!

.

 

[Lisa Reber]

Another method of doing a main and two subs is to put in

a tab control and have contacts on page one, vehicles on
page two, registrations on page three, etc. That is one
of the ideas I had considered building. With that layout
you could mount other controls for adding, deleting,
finding, etc.

That works for me where each tab is a different 'subject'
such as car registrations, membership payments, and work
information. At least that's my goal!

As far as this previous suggestion that you have from

February, I think I would have gone with listboxes - but
that is probably due to my lack of experience with
subforms.

Well, between the Feb suggestion, your help and one of the
books, I have the correct data appearing! I ended up with:
Private Sub Form_Current()
Me.cboContVehFiltered.Requery
End Sub
In the On Current event of the subform. (Guess that's what
the first line is saying . . . ) Now the problem is with
data entry. I'm running out of time here, so I will create
the structure only database copy and also fax the relation-
ships printout. Thanks very much for your help. And I
learned a lot in the process.