Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2002
    Posts
    63

    Question Unanswered: Parsing, or field in a field?

    Hi

    I'm writing a database for creating kids reports for school. One of the tables is called tblscentence, in which there are a number of scentences each coralting to a level and a subject.

    To make the scentences are 'personal' as possible I want to be able to write in a value such as:

    [Name] has been working very well recently in science [he/she] has excelled in [somesciencearea].

    Then I have a tabke called tblpupil where all the pupils details are held, and another table called tbllevels where the teacher can enter the the subject and the level which pupil has achieved.

    There will be a button somewhere that you press that will create a final 'report record' (in yet another table), including all the coresponding scentences, note: not linked to the scentences litterally copies of the scentences. How can i make access 'parse' the areas such as [name].

    Is it possible to enter [tblpupil].[name] into the scentence table and access pick up the name? Or do I need to write a module to do this?

    Thanks

  2. #2
    Join Date
    Jul 2002
    Location
    Australia
    Posts
    147
    The variables to be used in those [areas] should be stored in tables like you are doing.

    The sentences thesleves can simply be displayed on a form or report. I don't see a need to store these in a table at all.

    So basically you just create a label on the form or report and insert the fields from the tables in the right spots..

    Cheers,
    Andrew
    There have been many posts made throughout the world.
    This was one of them.

  3. #3
    Join Date
    Jun 2002
    Posts
    63
    The scentences HAVE to be held in a table, becasue the user needs to have the ability to alter them.

    My question really is how do I cause the [areas] to be 'filled in' In most cases the areas will be values in the pupils record, such as their name, occasionally I'm expecting the user to create a scentencethat when the report is created the user will need to enter some data, such as the area they excelled in. Really i want popups for this to ensure they don't miss any.

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Base the record source of the report on a query

    Use a form button to open a report...

    ...The report is based on a query

    Concatenate your sentance and pupil details fields in the underlying query of the report;

    i.e. Have a visible / invisible field on the report pupilID

    Use the query builder to create the concatenated fields;

    i.e.

    PupilSentance: [PupilName] & " " & [sentancepart1] &" " & [part2etc...]

    Hope this puts you on the right track

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    there are 2 method 1 prat around with instr or 2

    store the sentence in a table like

    tblscentence(Sencode, part1,part2,...)

    then do tblpupil!name & tblscentence!part1 & iif(tblpupil!sex = 'f',"s","")& tblscentence!part2 & <Area> & "."

    part1 = " has been working very well recently in science "
    part2 = "he has excelled in "

    this can be used anywhere not just a report
    Last edited by m.timoney; 02-27-03 at 09:24.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Jun 2002
    Posts
    63
    prating around with instructions sounds good .

    I understand the method of spliting the scentences up into parts, but thats gonna be a real bastard to implement in a user friendly way.

    you showed it as part1 part2 .... in the same table! well I would have creat4ed one table with

    tblscentences (scentenceid, scentencepart, scentence);

    and then stick em all 2gether. i.e. where secentenceid=scentenceid order by scentencepart.

    But that really is a bastard to write into a database when I only want one box... hmmm maybe it'd work done as a subform... hmmmm....... :::thinks hard:::

    what is the VBA equivalant of ereg in PHP? (replace)

    Thanks

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i do hope your joking about not knowing inStr and any how

    not really heres a quick knock up that i've done

    Originally posted by mythix
    prating around with instructions sounds good .

    I understand the method of spliting the scentences up into parts, but thats gonna be a real bastard to implement in a user friendly way.

    you showed it as part1 part2 .... in the same table! well I would have creat4ed one table with

    tblscentences (scentenceid, scentencepart, scentence);

    and then stick em all 2gether. i.e. where secentenceid=scentenceid order by scentencepart.

    But that really is a bastard to write into a database when I only want one box... hmmm maybe it'd work done as a subform... hmmmm....... :::thinks hard:::

    what is the VBA equivalant of ereg in PHP? (replace)

    Thanks
    Attached Files Attached Files
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Jun 2002
    Posts
    63
    Right, no, i had never heard of inStr (haven't done any Access or VBA for nearly 2 years!). As for the DB you made, I completely understand the concept, I just dislike the way you've done it, and I think it's a bit cumbersome.

    i think I failed to explain the way i tackling this

    Here's what i've got so far:
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by mythix
    Right, no, i had never heard of inStr (haven't done any Access or VBA for nearly 2 years!). As for the DB you made, I completely understand the concept, I just dislike the way you've done it, and I think it's a bit cumbersome.

    i think I failed to explain the way i tackling this

    Here's what i've got so far:
    well i for some odd reason i can't download from the forum but if you really don't like the method i've used them i suggest you read the help files on the InStr and Replace functions (I've just remembered about replace)
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Jun 2002
    Posts
    63
    Hi,

    This is what I want to do:

    Perform Query
    For each record
    blah blah
    next record

    how do I do this in VBA?

    Thanks

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by mythix
    Hi,

    This is what I want to do:

    Perform Query
    For each record
    blah blah
    next record

    how do I do this in VBA?

    Thanks
    i'm going to make a few assumtion here
    1. your going to have a table that contains the pupilid the sentence id and the area text
    2 the table your going to put them in already excists and is full of data you don't what to keep

    Dim pupil As DAO.Recordset, sentence As DAO.Recordset, link As DAO.Recordset
    Dim text As String
    Set pupil = CurrentDb.OpenRecordset("tblPupil", dbOpenSnapshot)
    Set sentence = CurrentDb.OpenRecordset("tblSentence", dbOpenSnapshot)
    Set link = CurrentDb.OpenRecordset("<name of link table>", dbOpenSnapshot)
    DoCmd.RunSQL ("DELETE * FROM <destination table>;")
    link.MoveFirst
    While Not link.EOF
    sentence.FindFirst "SenID = " & link("SenID").Value
    pupil.FindFirst "PupID = " & link("PupID").Value
    text = sentence("scentence").Value 'your spelling not mine or should that be smelling
    text = Replace(text, "[name]", pupil("Name").Value)
    If pupil("sex").Value = "F" Then
    text = Replace(text, "[he/she]", "she")
    Else
    text = Replace(text, "[he/she]", "he")
    End If
    text = Replace(text, "[somesciencearea]", link("area").Value)
    DoCmd.RunSQL ("INSERT INTO <desination table>(sentence) SELECT '" & text & "';") 'see the ' outside the "
    link.MoveNext
    Wend
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Jun 2002
    Posts
    63
    Wow,

    i understand! (yeah your right its smelling not spelling lol)

    There's just one problem. When i alter the code and try to run i don't even get past the variable declartions. I get the following error:

    "user-defined type not defined"

    Am using officeXP.

    Thanks.

  13. #13
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by mythix
    Wow,

    i understand! (yeah your right its smelling not spelling lol)

    There's just one problem. When i alter the code and try to run i don't even get past the variable declartions. I get the following error:

    "user-defined type not defined"

    Am using officeXP.

    Thanks.
    go to tools refferences and inclued microsoft DOA 3.X the lastest is 6 i think

    what i wouldn't give to do that with a simple import line of code like with C, java, haskel, perl, pascel
    Last edited by m.timoney; 02-27-03 at 14:00.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •