Auto filling in a form

[Sheila]

I am (trying!) to build a database for a dance school that is putting on a
big show. A bit of a huge undertaking considering I have NEVER used Access
before in my life..!

I have managed most of it, but am now stuck.

I need to record which students are dancing to which music tracks, which
teacher teachers this dance, what type of dance it is (eg tap, ballet, jazz)
and whether it is in Act 1 or Act 2 of the show. This will then enable me to
produce a running order and a rehearsal schedule.

I have MANY tables/forms but to record which students are in which dance I
need a new one.

My new table/form has fields as follows:-
GPId - primary key
Track name
Teacher name (which teacher is choreographing the dance)
Act no (which Act the dance is in - either Act 1 or Act 2)
Dance Type (tap, ballet, jazz)
Student Name

My intention is to insert this as a subform within the student records so I
can see which dances they are in etc.

The problem is that at the moment, there is no corrolation between the track
Name/Teacher/Dance type. Obviously these fields wont change. For "Raindrops
keep falling on by head", it will always be a tap dance choreographed by
Teacher A but if it includes x10 students, then there are x10 possibilities
for incorrect data entry...! This show has over 200 students some of whom are
in more than x1 dance. The possibilities for data entry errors are endless...

I want to be able to just enter the track name and have the other fields
(teacher and Act #) completed automatically to avoid any data entry errors,
but I really don't know how to do this. Various postings on this web site
suggest that it may be VB stuff. I hope not, because I'm finding non VB
access hard enough.....

By the end of building this database, I hope to be an expert like you
guys........but in the meanwhile.......

Help........!

Thanks, Sheila

 

[strive4peace]

Hi Sheila,

in order to help you,we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub

'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub

'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Sheila]

Hi Crystal,

Many thanks for taking the time to reply, but I'm afraid I'm not advanced
enough to understand what you are asking me to do. In any case, many hours
later, I think I may have found the answer...sort of...

I managed to produce a form whereby I entered the track name, then the
remaining fields were automatically completed (eg dance type,
teacher/choreographer etc). I used a combo box.

But, all worked ok until I then added another field with the student name.
It would seem that because the original info was coming from x1 table, the
moment I then added another field, it upset the balance and I could no longer
autopopulate the fieds particular to the track/dance itself.

Am I making any sense at all......

Thanks, Sheila

 

[strive4peace]

Hi Sheila,

"I'm afraid I'm not advanced enough to understand what you are asking me
to do."

read this:

Access Basics (on Allen Browne's site)
http://www.allenbrowne.com/casu-22.html
8-part free tutorial that covers essentials in Access

Allen has a wealth of information on his site; after you get to the
bottom of this link, click on 'Index of Tips'

"all worked ok until I then added another field with the student name"

if you are collecting the StudentID, there is no need to include the
table of Students in the RecordSource for your form; you can simply use
a combo for the studentID and let the RowSource of the combo get
information from the Students table (read the Combobox example in
'Access Basics')

each form/subform should be based on just one table


Warm Regards,
Crystal



*
have an awesome day
*