Results 1 to 15 of 15

Thread: passing a variable in VB?

  1. #1
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb

    Question passing a variable in VB?

    ok, basically i want this piece of code to take in two variables (strDay and strPeriod) run the case statement and then return the updated version of strDay and strPeriod... confused? read on

    Code:
    Public Function NextPeriod(strDay As String, strPeriod As String)
    
    Select Case strDay
        Case "Friday"
            If strPeriod = "Period6" Then strPeriod = "finished"
            If strPeriod = "Period5" Then strPeriod = "Period6"
            If strPeriod = "Period4" Then strPeriod = "Period5"
            If strPeriod = "Period3" Then strPeriod = "Period4"
            If strPeriod = "Period2" Then strPeriod = "Period3"
            If strPeriod = "Period1" Then strPeriod = "Period2"
        
        Case "Thursday"
            If strPeriod = "Period6" Then strDay = "Friday": strPeriod = "temp"
            If strPeriod = "Period5" Then strPeriod = "Period6"
            If strPeriod = "Period4" Then strPeriod = "Period5"
            If strPeriod = "Period3" Then strPeriod = "Period4"
            If strPeriod = "Period2" Then strPeriod = "Period3"
            If strPeriod = "Period1" Then strPeriod = "Period2"
            If strPeriod = "temp" Then strPeriod = "Period1"
            
        Case "Wednesday"
            If strPeriod = "Period6" Then strDay = "Thursday": strPeriod = "temp"
            If strPeriod = "Period5" Then strPeriod = "Period6"
            If strPeriod = "Period4" Then strPeriod = "Period5"
            If strPeriod = "Period3" Then strPeriod = "Period4"
            If strPeriod = "Period2" Then strPeriod = "Period3"
            If strPeriod = "Period1" Then strPeriod = "Period2"
            If strPeriod = "temp" Then strPeriod = "Period1"
        
        Case "Tuesday"
            If strPeriod = "Period6" Then strDay = "Wednesday": strPeriod = "temp"
            If strPeriod = "Period5" Then strPeriod = "Period6"
            If strPeriod = "Period4" Then strPeriod = "Period5"
            If strPeriod = "Period3" Then strPeriod = "Period4"
            If strPeriod = "Period2" Then strPeriod = "Period3"
            If strPeriod = "Period1" Then strPeriod = "Period2"
            If strPeriod = "temp" Then strPeriod = "Period1"
        
        Case "Monday"
            If strPeriod = "Period6" Then strDay = "Tuesday": strPeriod = "temp"
            If strPeriod = "Period5" Then strPeriod = "Period6"
            If strPeriod = "Period4" Then strPeriod = "Period5"
            If strPeriod = "Period3" Then strPeriod = "Period4"
            If strPeriod = "Period2" Then strPeriod = "Period3"
            If strPeriod = "Period1" Then strPeriod = "Period2"
            If strPeriod = "temp" Then strPeriod = "Period1"
            
    End Select
    
    MsgBox "" & strPeriod & " and " & strDay & ""
    
    End Function
    
    Public Sub test()
    
    strPeriod = "Period1"
    strDay = "Monday"
    
    Call NextPeriod("Monday", "Period1")
    
    MsgBox "" & strPeriod & " and " & strDay & ""
    
    
    End Sub

    ok, basically the strPeriod and strDay from the NextPeriod function are updated (in this case to Period2 and Monday), but the values are not updated in the 'test' subform...

    so basically, i dont know what i'm doing in VB but i know what i want to do, and i'm not sure how - i'm obviously going about this completely wrong...

    any help is appreciated

    mark

  2. #2
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    What you need to do is globally define two variables (for the class/module), for day and period. Something like this:

    Code:
    Dim strDayG, strPeriodG As String
    
    Sub NextPeriod(theday, theperiod)
        'run the code here, collect your results into local variables
        ...
        strDayG = [local var for day]
        strPeriodG = [local var for period]
    End sub
    
    Sub Test()
        'run your code...
        strDayG = "Monday"
        strPeriodG = "Period1"
        NextPeriod strDayG, strPeriodG
        'global variables have changed and will be available
        Console.Writeline("strDayG = " & strDayG & ", strPeriodG = " & strPeriodG)
    End sub
    Simon


  3. #3
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    aha sounds good i'll work on that a bit more then... thing is. thats the simple part of the problem i'm trying to solve, i'm prolly gonna have a ton more questions to come

    mark

  4. #4
    Member
    Join Date
    Aug 2003
    Location
    Wimbledon
    Posts
    141
    Thanks
    0
    Thanked
    0 times in 0 posts
    By default VB passes arguments by reference so the following should work fine...

    Code:
    NextPeriod(strDay, strPeriod )
    But it doesn't unless you remove the brackets thus...

    Code:
    NextPeriod strDay, strPeriod
    Which works just fine.
    (Don't need to use Call)
    Last edited by Mart; 31-12-2003 at 01:22 PM.

  5. #5
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    woop, just tried that, and it worked first time

    then i changed the order of the msgbox and calling the 'nextperiod' sub, and it goes from monday period1 to friday period6.. nice just need to actually do some of the more complex stuff now, but that has me completely confused

    think i need to use the docmd.runSQL command along with making a table and filling it with stuff - based upon the values of strPeriod, and strDay. FUN!

    mark

  6. #6
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    ok next problem... and yeh its sql.

    what i've got so far is :

    Code:
    Sub blahblah()
    Dim strSQLstatement As String
    
    strSQLstatement = "SELECT tbl_timetable_student.Student_ID, tbl_timetable_student.Day, tbl_timetable_student.Period, tbl_timetable_student.Subject, " _
                    & "tbl_timetable_student.Form, tbl_timetable_student.Needs_Support" _
                    & "FROM tbl_timetable_student" _
                    & "WHERE (((tbl_timetable_student.Student_ID)='" & strStudentID & "') AND ((tbl_timetable_student.Day)='" & gstrDay & "')" _
                    & "AND ((tbl_timetable_student.Period)='" & gstrPeriod & "') AND ((tbl_timetable_student.Needs_Support)=True));"
    
    DoCmd.RunSQL "CREATE TABLE arse AS ('" & strSQLstatement & "') DEFINITION ONLY; INSERT INTO arse ('" & strSQLstatement & "')"
    End Sub
    and it doesnt work.. i cant work out how to include the string strSQLstatement into the sql statement as it just doesnt work, tells me i'm crap and there is a syntax error in the create table statement.. thing is, i'm not even sure that its right, i found it on a site on google, but it didnt have any info about using a variable

    mark

  7. #7
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    The SQL is wrong. What are you trying to do?
    Simon


  8. #8
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    erm.. make a table based upon some stuff from a query. i made the query (using the QBE grid in access), and it worked (with like 'Monday' instead of strDay) so copied in the variable names (not sure if its right at all as i'm completely new to VB and making it up as i go along with some help from google)....

    mark
    Last edited by streetster; 01-01-2004 at 08:04 PM.

  9. #9
    Member
    Join Date
    Aug 2003
    Location
    Wimbledon
    Posts
    141
    Thanks
    0
    Thanked
    0 times in 0 posts
    Have you created a .mdb project or a .adp project?

    If you have created a .mdb project the underlying database engine is JET and you can't use CREATE TABLE sql with JET.

    To create tables programmatically you need use the objects in the DAO 3.5 library.

    Normally you don't need to create tables in code as you build them using the table designer in Access. I would check your design, do you really need to create tables at runtime?

  10. #10
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    its mdb.. and no i guess i dont need to create a table, aslong as i can write a query which populates the table based on a set of criteria, i'll have a look on google as i dont want to be relying on you guys to help me with my project, so i apologise for being a complete noob at this . anyhow i'll go see whats what.

    cheers
    Mark

  11. #11
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    Originally posted by Mart
    If you have created a .mdb project the underlying database engine is JET and you can't use CREATE TABLE sql with JET.


    Good 'ol Access eh

    Anyway, as the man said, use another interface to connecto the the database if you need to programatically create tables. I'd suggest ADO. This is of course assuming all of this VB stuff is going on outside of Access...? It might not work otherwise, I've never done it.

    As for populating a table based on a query of another table, you'd be best off doing this with a loop. Something like...

    Code:
    SQL = "SELECT * FROM [query]"
    'execute command...
    If not ([objCommand].BOF and [objCommand].EOF) then
        Do while not [objCommand].EOF
            newSQL = newSQL & ";INSERT INTO [table] ([column1],column2]) VALUES (" & [objCommand]("field1") & "," & [objCommand]("field2") & ")"
            [objCommand].Movenext
        Loop
    End if
    You'd end up with a long string of insert commands to create records for your new table. In SQL Server you could write a stored procedure to do this and make the whole thing more efficient, but that's a bit above the level of what we're doing here

    Hope that helps.
    Simon


  12. #12
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    hmm, no sadly i'm using access for it all and its all stupidly hard considering we've not really been taught access (all we got taught last year was some pascal.. which doesnt help a great deal - the thing is, i know what i *want* to do, i just dont have a clue how to write the code to do it

    also, is it possible to add variables into an SQL statement, (hence the reason for the big Case Select statement somewhere up there ^^) because if not thats actually gay, as i need to run 30 queries with changing criteria (ie from monday period1 to friday period6) aswell as the studenID (which i have got to somehow extract from a table and encorporate into the query aswell).. *sigh* its all to complicated...

    hmmm

    mark

    ( i can scan in a rough system flowchart-type thing if that'll make what i'm trying to acheive more easy to understand?)

  13. #13
    Member
    Join Date
    Aug 2003
    Location
    Wimbledon
    Posts
    141
    Thanks
    0
    Thanked
    0 times in 0 posts
    Originally posted by Shad

    Good 'ol Access eh
    Careful shad, you'll start Alan ranting again

  14. #14
    Senior Member Shad's Avatar
    Join Date
    Jul 2003
    Location
    In front
    Posts
    2,773
    Thanks
    22
    Thanked
    40 times in 24 posts
    Hehe

    Streetster, just do it the same way you'd do it for any old string. SQL = "SELECT blah blah FROM " & strTableName & " WHERE " & strClause etc...
    Simon


  15. #15
    cat /dev/null streetster's Avatar
    Join Date
    Jul 2003
    Location
    London
    Posts
    4,138
    Thanks
    119
    Thanked
    100 times in 82 posts
    • streetster's system
      • Motherboard:
      • Asus P7P55D-E
      • CPU:
      • Intel i5 750 2.67 @ 4.0Ghz
      • Memory:
      • 4GB Corsair XMS DDR3
      • Storage:
      • 2x1TB Drives [RAID0]
      • Graphics card(s):
      • 2xSapphire HD 4870 512MB CrossFireX
      • PSU:
      • Corsair HX520W
      • Case:
      • Coolermaster Black Widow
      • Operating System:
      • Windows 7 x64
      • Monitor(s):
      • DELL U2311
      • Internet:
      • Virgin 50Mb
    cheers buddy i've got tomorrow to do a load of this computing and all my maths homework should be a fun day...

    thanks for the help again guys

    mark

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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