Data from two sub records into one form?

[Jack]

Data from two sub records into one form?

Hi. Need help. I’m a single dad so when kids in bed at night, I only have
few hours every day to myself. Can’t afford to waste time looking up answer
in books on Access 97 for a problem if I don’t know where to look! So I
decide to ask you as many of you are far more experienced than I am. Okay,
here it is…

I have designed a database to contents records of what television programmes
I recorded onto VHS tapes. Works as follows, main record contents only
details about the tape, (ie: tape title, tape length, which video recorder
used, etc.) and a one to many relationship with a sub-records that contents
programme details (ie: programme title, date recorded, etc).

Now I have a DVD Recorder and want details added to the VHS records. I can
either modify my current database to include DVD or I can start a new one.
But I have two options…

Either add new fields to ‘Tapes’ main records (it can become ‘Tapes/Discs’)
or I could create another main record but only for ‘Discs’. Having one main
record for ‘Tapes’ and one main record for ‘Discs’ makes it easier as the
fields are different from each other. (Apart from fields shared by both
types of media such as ‘title’, ‘location’, etc, there are some fields that
can’t be shared, as each media have its own system such as tapes have E-180,
E-240 or LP, SP, etc while disc have DVD-R, DVD-RW, etc.) You see, if one
main record for both media, this would mean something like:
Title: Star Trek collection vol 1. Length: E-240. Type:
Title: Star Trek collection vol 2. Length: Type: DVD-R
Title: Star Trek collection vol 3. Length: E-180. Type: DVD-R
Opps! You can image if I mistakenly type in ‘E-180’ when vol 3 should be a
DVD since DVD-R don’t have such thing as ‘E-180’. Hence I thought it best to
have two separate main records, one for discs and one for tapes.

But, my problem and worry, which I am asking for help here, is that if I
were to have two main records, each having its own sub-record which contents
details of what did I record, will it be possible for data from each
sub-record to go into one form, for viewing or selecting, not for editing.
For example…

MAIN RECORD - VHS.
ID: V1. Title: Sci-Fi Vol 1.
One to many relationship to below
SUB RECORD - VHS.
Title: Stargate SG-1, Episode 1
Title: Star Trek, Episode 2

MAIN RECORD - DVD
ID: D1. Title: Sci-Fi Vol 2.
One to many relationship to below
SUB RECORD - DVD
Title: Star Trek, Episode 1
Title: Space 1999, Episode 5
Title: Stargate SG-1, Episode 2

For searching, viewing, printing, etc purpose (not for editing), channel
both sub-records into a query table so that I can have a form view that
shows…

OVERALL INDEX (For example: A form view of a Query table)
PROGRAMME TITLE MEDIA MEDIA TITLE
Space 1999, Episode 5 DVD Sci-Fi Vol 2
Star Trek, Episode 1 DVD Sci-Fi Vol 2
Star Trek, Episode 2 VHS Sci-Fi Vol 1
Stargate SG-1, Episode 1 VHS Sci-Fi Vol 1
Stargate SG-1, Episode 2 DVD Sci-Fi Vol 2

Or is it impossible and I am forced to have one main table that both VHS and
DVD must share?

Many thanks in advance to those who read and those who reply.

Regards,

Jack.

PS: Do not reply to my e-mail address. It picks up far too many spam, that i
don't bother checking it out. Pls reply on the newsgroup.

 

[Arvin Meyer]

If it's only a few fields that are radically different in the 2 types of
media, I'd still only use 1 table and 1 form. With some minor validation, or
before update code, you can eliminate possible errors similar to what you
showed. Something like this ought to do it (substituting for textbox named,
of course) This is untested (aircode):

Sub txtType_BeforeUpdate(CancelAs Integer)

If Len(Me.txtType & vbNullString) > 0 Then
If Len(Me.txtLength & vbNullString) > 0 Then
Msgbox "You've entered incorrect data"
Cancel = True
' Do something here
End If
End If

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Jack]

If it's only a few fields that are radically different in the 2 types of
media, I'd still only use 1 table and 1 form.

Okay then, in that case, if I am forced to have only one table for both
types of media, bearing in mind, both can have the same fields such as
‘title’, ‘time remaining’, ‘location of media’ and both can use the same
sub-record with the expect for small variations between the two where, VHS
have ‘E-180’, E-240’, ‘LP’, ‘SP’, etc., and DVD have ‘DVD-R’, ‘DVD-RW’, then
I could add a field labelled ‘Media Type’ where I have to select either
‘Tape’ or ‘Disc’, then…

Tell me how the heck do I get a form to display only some fields depending
on the data selected?

I mean, I could create a form where it have the basic fields suitable for
both types of media, and then add some fields suitable for tape media as
well as adding some fields suitable for disc media. So that if I selected
‘Tape’ in say a field labelled ‘Media Type’, the form will display all
fields suitable for both media and display fields only suitable for tape
media, hiding fields for disc media.

I’ve seen it happened in a simple database (don’t remember if it was the
Northwind simple database or some other simple database that came as part of
some books written by someone else.)

Many thanks.

 

[Arvin Meyer]

media, I'd still only use 1 table and 1 form.

Okay then, in that case, if I am forced to have only one table for both
types of media, bearing in mind, both can have the same fields such as
'title', 'time remaining', 'location of media' and both can use the same
sub-record with the expect for small variations between the two where, VHS
have 'E-180', E-240', 'LP', 'SP', etc., and DVD have 'DVD-R', 'DVD-RW', then
I could add a field labelled 'Media Type' where I have to select either
'Tape' or 'Disc', then.

Tell me how the heck do I get a form to display only some fields depending
on the data selected?

Sub txtMediaType_AfterUpdate()
If Me.txtMediaType = "DVD" Then
Me.txtWhatever.Visible = True
Me.txtTheOtherOne.Visible = False
Else
Me.txtWhatever.Visible = False
Me.txtTheOtherOne.Visible = True
End Sub

Also add this logic to the form's Current event, so that the proper controls
are displayed as you scroll through the records.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access