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

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

---

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

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)

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

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

The SQL is wrong. What are you trying to do?

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).... :confused:

mark

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?

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

Quote:

---

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.

---

http://images.shad.tv/stvg/crepapelle.gif

Good 'ol Access eh :P

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.

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?)

Quote:

---

Originally posted by Shad

Good 'ol Access eh :P

---

Careful shad, you'll start Alan ranting again :)

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...

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

mark