Access 2010 VBA: SQL insert query is picking up 1 variable but not the other variable

Question

OK usually I'm pretty good at googling around and using debug.print to isolate and solve the problem but this one is escaping me.

The purpose of this code is to create a new record in a table, using a form in which a person has selected a team member's name from a dropdown and a project phase from a dropdown and then input a number of hours into a textbox, then clicked a button that says "Add". There are a few if/thens involved but I'm leaving out the irrelevant parts (the code produces the same error in all cases.)

All of the code takes place inside one public function. All variables are Dim.

First it runs some code to find the value of "MyPersonID". (Complicated and not relevant as that works just fine).

Then it runs some code to find the value of "MyProjectPhaseID" which looks like this:

MyProjectPhaseID = [Forms]![HourValidationsFromTeam]![InputProjectPhase]

This variable populates correctly (as per Debug.Print)

Then it creates the INSERT SQL statement and runs it:

strAppendHourRecordSQL = "INSERT INTO PersonCommitmentsHours ( PersonNameLookup, ProjectPhase, WeekOfCommitment, DateValidated, HourCommitment, ValidationResult ) SELECT '" & (MyPersonID) & "' AS PersonNameLookup, '" & MyProjectPhaseID & "' AS ProjectPhase, [Forms]![HourValidationsFromTeam]![LastWeekDate] AS Week, Date$() AS TodaysDate, [Forms]![HourValidationsFromTeam]![InputSuppliedHours] AS Hours, " & Chr(34) & "More" & Chr(34) & " AS ValidationType;"
Debug.Print MyProjectPhaseID
Debug.Print strAppendHourRecordSQL
DoCmd.RunSQL strAppendHourRecordSQL

This is what Debug.Print returns:

2069
INSERT INTO PersonCommitmentsHours ( PersonNameLookup, ProjectPhase, WeekOfCommitment, DateValidated, HourCommitment, ValidationResult ) SELECT '260' AS PersonNameLookup, '' AS ProjectPhase, [Forms]![HourValidationsFromTeam]![LastWeekDate] AS Week, Date$() AS TodaysDate, [Forms]![HourValidationsFromTeam]![InputSuppliedHours] AS Hours, "More" AS ValidationType;

The query runs correctly and inserts a record with everything in the right place except it's missing the value where MyProjectPhaseID should go. It's just null. I thought maybe the variable was null, but Debug.Print returns the correct value. Even the debugger fills the value in when I hover over the SQL.

I tried different combinations of adding and removing parentheses and quotes around the variable in the SQL but they have no effect. Please help!

I have all my variables as Dim and all my functions as public but I'm not sure if that answers your question. I haven't had to do anything with scope as far as I know. — Tara
– Tara, Commented Apr 4, 2014 at 15:58
Is the MyProjectPhase variable in the same method as strAppendHourRecordSQL? — crthompson
– crthompson, Commented Apr 4, 2014 at 16:15
Another thought, and I havent worked with Access in ages, but shoulnt ![InputProjectPhase] have a .Text property to get instead of just the object name? — crthompson
– crthompson, Commented Apr 4, 2014 at 16:16

Tara · Accepted Answer · 2014-04-04 18:53:31Z

I figured out the problem. The problem is that you cannot define the SQL before the variables have been populated. I thought you could define the SQL and then re-use it depending on where you get your variables from. But no. That's why it had the right value for the variable, but it couldn't put them together. I didn't make it clear from the way I wrote the question that this could be a suspect, i'm sorry about that.

So in order to not try to pre-define SQL for variables that don't exist yet, I isolated the part of the SQL that won't change and define that first as strBoilerplateSQL. Then do the IF statement for the stuff that could change, then define the part of the SQL statement that depends on that change, then concat the 2 sql statements together. Then it runs the completed SQL statement.

strBoilerplateSQL = "INSERT INTO PersonCommitmentsHours ( PersonNameLookup, WeekOfCommitment, DateValidated, HourCommitment, ValidationResult, ProjectPhaseLookup ) SELECT " & (MyPersonID) & " AS PersonNameLookup, [Forms]![HourValidationsFromTeam]![LastWeekDate] AS Week, Date$() AS TodaysDate, [Forms]![HourValidationsFromTeam]![InputSuppliedHours] AS Hours, " & Chr(34) & "More" & Chr(34) & " AS ValidationType, "

'Check to see if this is going in to an existing project or should we create a new project first
If (IsNull([Forms]![HourValidationsFromTeam]![InputNewProject].Value)) Then

'If the Input New Project text box is null, assemble the SQL and run it
MyProjectPhaseID = [Forms]![HourValidationsFromTeam]![InputProjectPhase].Value
strMyProjectPhaseSQL = "" & (MyProjectPhaseID) & " AS ProjectPhase;"
strReadySQL = (strBoilerplateSQL) & (strMyProjectPhaseSQL)
DoCmd.RunSQL strReadySQL

Else
'Some other stuff happens here
MyProjectPhaseID = GetPhaseID![TheProjectPhase]

'Now that we have the new project phase ID we can run the SQL from above (oh hey remember that?)
strMyProjectPhaseSQL = "" & (MyProjectPhaseID) & " AS ProjectPhaseLookup;"
strReadySQL = (strBoilerplateSQL) & (strMyProjectPhaseSQL)
DoCmd.RunSQL strReadySQL
End If

Collectives™ on Stack Overflow

Access 2010 VBA: SQL insert query is picking up 1 variable but not the other variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related