Re: Auto filling in a form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" wrote:

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 wrote:
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

.

Relevant Pages

  • Re: Auto filling in a form
    ... 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 ... 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). ... Sub RunShowFieldsForTable() ... I need to record which students are dancing to which music tracks, which teacher teachers this dance, what type of dance it is and whether it is in Act 1 or Act 2 of the show. ...
    (microsoft.public.access.forms)
  • Re: Auto filling in a form
    ... Sub RunShowFieldsForTable() ... I need to record which students are dancing to which music tracks, which teacher teachers this dance, what type of dance it is and whether it is in Act 1 or Act 2 of the show. ...
    (microsoft.public.access.forms)
  • Auto filling in a form
    ... teacher teachers this dance, what type of dance it is ... I have MANY tables/forms but to record which students are in which dance I ... Teacher name ... By the end of building this database, I hope to be an expert like you ...
    (microsoft.public.access.forms)
  • Lodi High bans risqué dancing at prom (CA)
    ... Hundreds of Lodi teens will gather tonight at this year's prom to carouse and dance, ... dancing at this year's prom, ... Lodi High students and date as far back as 1932. ...
    (rec.arts.marching.drumcorps)
  • Re: Shes been taking lessons from a chain studio . . .
    ... their secondary interest is teaching students to ... I further suggested that independent studios' primary interest is teaching students to dance with the hope that they can make a ... What I was failing in the attempt to say is that independent studios are ...
    (rec.arts.dance)