Results 1 to 13 of 13

Thread: Can anyone help me with this code?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Can anyone help me with this code?

    What I need to happen is: if the string matches that in the Text2 text boxes then the text1 textbox displays the data from that field. (when option module is ticked followed by the cmd_click button)

    For example using this one if statement below i should get - if the string in the text box text2(0) = modreference then put into text1(i) the data from modReference in this case 123-English

    If Text2(0) = "modReference" Then
    strRecord = "Module"
    Text1(i).DataField = "modReference"
    End If

    but the only one that is working correctly is the last one:

    If Text2(4) = "Score" Then
    strRecord = "Attainment"
    Text1(i).DataField = "Score"
    End If

    But its putting the first record from score which is '50' into all the text1 boxes it should be going through and putting in the data from the first record from modReference in the first text1 then the data from the first record from modName into the second text1 so on and so on

    so I end up with something like

    123-Eng------English------Mr Jones--------StudentIIIII-------50%

    Any advise would be great
    Last edited by bubblegum_girl; Apr 12th, 2006 at 08:53 AM.

  2. #2
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    The way you wrote your code with the

    For...Next is setting Text1(4).DataField = "modReference" = "Score"

    If Text2(0) = "modReference" Then
    strRecord = "Module"
    Text1(i).DataField = "modReference"
    End If
    first i = 0 will set datafield = "moderefrence" for the Text1(0)
    then again for Text1(0) will set datafield = "modName"
    If Text2(1) = "modName" Then
    strRecord = "Module"
    Text1(i).DataField = "modName"
    End If
    ??????????????????????
    which one you need
    and at last i = 4 will again set the TEXT1(4).datafield for all boxes
    that is why you get right only the last one
    Last edited by BestS; Apr 9th, 2006 at 07:53 AM.

  3. #3
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    I think you do not need FOR .. NEXT
    just simple this

    If Text2(0) = "modReference" Then
    strRecord = "Module"
    Text1(0).DataField = "modReference"
    End If

    If Text2(1) = "modName" Then
    strRecord = "Module"
    Text1(1).DataField = "modName"
    End If

    If Text2(2) = "modLeader" Then
    strRecord = "Module"
    Text1(2).DataField = "modLeader"
    End If

    If Text2(3) = "stuID" Then
    strRecord = "Student"
    Text1(3).DataField = "stuID"
    End If

    If Text2(4) = "Score" Then
    strRecord = "Attainment"
    Text1(4).DataField = "Score"
    End If

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Re: Can anyone help me with this code?

    I've tried it like that but for some reason it doesn't show the modLeader of ModName datafields. Its like it only takes the first attribute from each time the strRecord changes. But i do end up with 3 out of the 5 displaying correctly this way i get

    456-sci ----- ----- ------IIIII ------50


    What I'm trying to do is display all of the 5 datafields into the Text1 boxes so I end up with the first record of each modReference, modName, modLeader, Stuid and score in the Text1 boxes

    i.e.

    456-sci ----- Science -----Prati ------IIIII ------50

    but I'm only just learning VB and don't understand too well (reason for my poor code)

    The problem I think lies in the for i loop but I don't know another way of checking through the boxes to see if the string matches that in text2 then if it does display the ,datafield for that string in text1
    Last edited by bubblegum_girl; Apr 9th, 2006 at 07:59 AM.

  5. #5
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    You need a SQL query that contains all the data you want to display
    and then
    set Data1.RecordSource = YOURSQLQUERY

    There is a lot to be explained here, you will need to read some more about database development

  6. #6
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    You have a query in your database, if it has all the data you need then

    strRecord = "SELECT Module.modName, Student.stuSurName, Student.stuForeName, Attainment.score
    FROM Student INNER JOIN ([Module] INNER JOIN Attainment ON Module.modReference = Attainment.modReference) ON Student.stuID = Attainment.stuID
    ORDER BY Module.modName, Student.stuSurName, Student.stuForeName"
    Set Data1.recordsource = strSQL

    or directly write the query name
    Set Data1.recordsource = "qryScores"

    then remove all the lines
    strRecord = "Attainment" ... etc, remove them all

  7. #7
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    THERE IS A LOT OF THING TO TALK ABOUT HERE BUT now I do not have so much time
    Just replace your code with this one

    Option Explicit
    Dim strRecord As String
    Dim i As Integer


    Private Sub Command1_Click()
    If btnParent(0).Value = True And btnParent(1).Value = False And btnParent(2).Value = False Then
    Text2(0).Text = "modReference"
    Text2(1).Text = "modName"
    Text2(2).Text = "modLeader"
    Text2(3).Text = "stuID"
    Text2(4).Text = "Score"
    End If



    If Text2(0) = "modReference" Then
    Text1(0).DataField = "modReference"
    End If

    If Text2(1) = "modName" Then
    Text1(1).DataField = "modName"
    End If

    If Text2(2) = "modLeader" Then
    Text1(2).DataField = "modLeader"
    End If

    If Text2(3) = "stuID" Then
    Text1(3).DataField = "stuID"
    End If

    If Text2(4) = "Score" Then
    Text1(4).DataField = "Score"
    End If





    Data1.Refresh
    End Sub


    Private Sub Form_Load()
    With Data1
    .DatabaseName = App.Path & "\newResults.mdb"
    .RecordSource = "SELECT Module.modReference, Module.modName, Attainment.score, Student.stuForeName, Student.stuSurName, Module.modLeader, Student.stuID" & _
    " FROM Student INNER JOIN ([Module] INNER JOIN Attainment ON Module.modReference = Attainment.modReference) ON Student.stuID = Attainment.stuID"
    .Caption = "Student table - parent - single record"
    .RecordsetType = 2
    .Visible = True
    End With

    With Data2
    .DatabaseName = App.Path & "\newResults.mdb"
    .RecordsetType = 2
    .Visible = False
    End With

    With DBGrid1
    .Enabled = False
    .ForeColor = &H8000000B
    End With

    End Sub

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Re: Can anyone help me with this code?

    Thanks
    Last edited by bubblegum_girl; Apr 9th, 2006 at 08:22 AM.

  9. #9
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    Quote Originally Posted by bubblegum_girl
    That doesn't work because still I can only see one datafield per table

    I can't do it that way also because the strings in text2 wont always be the same - the strings in text2 will only be there if a check option is ticked

    so sometimes the results will be just modRefernce, modName, modLeader another time it could be all of them another just one attribute
    Ok then you need to make string dynamicaly, or use the full code and only make the textboxes = "" if they are not all needed.
    That way you will see the result of only modReference for instance...
    Last edited by BestS; Apr 9th, 2006 at 08:27 AM.

  10. #10
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    Quote Originally Posted by bubblegum_girl
    Thanks
    What do you mean "THANKS"
    is it OK is it not OK?
    Is this what you needed?

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Re: Can anyone help me with this code?

    Hi Sorry for the quick answer I went to play around with the code

    I edited the message saying I couldn't do it like that to just thanks because I realised I maybe could and went to try it

    Yes - it worked but because the text2 boxes changes (the input) I couldn't have a set sql string as the recordsource

    So i've played around with the code and now when a checkbox is ticked the string in the text2 box is added to the sql string I'm still in the middle of working on it - building the sql strings up but i'm getting there (slowly)

    So Thank you for the input - your a star
    Last edited by bubblegum_girl; Apr 9th, 2006 at 09:43 AM.

  12. #12
    Addicted Member BestS's Avatar
    Join Date
    Mar 2005
    Posts
    222

    Re: Can anyone help me with this code?

    If you have more trouble, upload again the project

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Apr 2006
    Posts
    147

    Re: Can anyone help me with this code?

    Cheers mate - now all I got to do is brush up on my SQL
    Last edited by bubblegum_girl; Apr 9th, 2006 at 01:10 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width