No Value Given runtime..... SQL problem

[Kryst51]

OK... In a previous thread I came out with some code that works and looks like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Sets ADO connection
Set MasterDbConn = CurrentProject.Connection
 
'Declarations
Dim rsNewRTId As ADODB.Recordset
 
'Dim rsNewRTIDSQL As String
Dim NewRTID As Integer
 
Set rsNewRTId = New ADODB.Recordset
 
'Add Record to tag relationships
Dim NewTagRelQRY As String
Dim AssignNCR As String
Dim NCRID As Integer
Dim fkTRID As Integer
 
[COLOR=red]NCRID = Me.Parent.txtNCRHeaderID[/COLOR]
  
'SQL to create a new record in the tblTagRelationships
[COLOR=cyan]NewTagRelQRY = "Insert INTO tblTagRelationships(DateTimeStamp)" & _[/COLOR]
[COLOR=cyan]" VALUES (Now());"[/COLOR]
 
[COLOR=red]AssignNCR = "Update tblTagRelationships" & _[/COLOR]
[COLOR=red]" Set fkNCRHeaderID =" & NCRID & _[/COLOR]
[COLOR=red]" Where pkTagRelationshipID =" & fkTRID[/COLOR]
 
'If the record is a new record then a Tag Relationship
'is created and assigned. But if it is not a new
'record then this has already been done, and there
'will be problems if it is given a new one.
If Me.NewRecord = True Then
 
    'This will add a new record in the tag relationships
    'and put that number into the tag foreign key
    With CurrentProject.Connection
        [COLOR=cyan].Execute NewTagRelQRY[/COLOR]
        NewRTID = .Execute("SELECT @@Identity")(0)
        Me.fkTagRelationshipID = NewRTID
        fkTRID = Me.fkTagRelationshipID
    End With
 
End If
 
'This will assign the current NCR to the tagrelationship
[COLOR=red]If Me.NewRecord = True Then[/COLOR]
[COLOR=red]   CurrentProject.Connection.Execute AssignNCR[/COLOR]
[COLOR=red]End If[/COLOR]
End Sub

Currently this adds a new record to my table, "tblTagRelationships" by adding the current date and time to a new record. This date/time is really irrelevant to anything I am doing and is useless data given the help in a previous thread to do what I need in a better way.

What I found, though, is that now the bottom part of the code where I am adding "NCRID" to the newly created record doesn't work. And I thought, too, that it might be cleaner to use this NCRID to create the new record in "tblTagRelationships" instead of creating a useless date/time stamp.... The problem is that I get a run time error (The Red text is the items that the Blue text uses, and the blue text is what throws the error when I hit debug) :

Run-time error '2147217904(80040e10) No value given for one or more parameters

I assume this means that the SQL isn't reading my "NCRID" value which is set to the current ID, and I don't know why, am I referring to it incorrectly? Here is the updated code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
'Sets ADO connection
Set MasterDbConn = CurrentProject.Connection
 
'Declarations
Dim rsNewRTId As ADODB.Recordset
'Dim rsNewRTIDSQL As String
Dim NewRTID As Integer
Set rsNewRTId = New ADODB.Recordset
 
'Add Record to tag relationships
Dim NewTagRelQRY As String
Dim AssignNCR As String
Dim NCRID As Integer
Dim fkTRID As Integer
 
[COLOR=red][B]NCRID = Me.Parent.txtNCRHeaderID[/B][/COLOR]
  
'SQL to create a new record in the tblTagRelationships
[COLOR=red][B]NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _[/B][/COLOR]
[B][COLOR=red]" VALUES (NCRID);"[/COLOR][/B]
 
AssignNCR = "Update tblTagRelationships" & _
" Set fkNCRHeaderID =" & NCRID & _
" Where pkTagRelationshipID =" & fkTRID
 
'If the record is a new record then a Tag Relationship
'is created and assigned. But if it is not a new
'record then this has already been done, and there
'will be problems if it is given a new one.
If Me.NewRecord = True Then
 
    'This will add a new record in the tag relationships
    'and put that number into the tag foreign key
    With CurrentProject.Connection
       [COLOR=cyan][B][U].Execute NewTagRelQRY[/U][/B][/COLOR]
        NewRTID = .Execute("SELECT @@Identity")(0)
        Me.fkTagRelationshipID = NewRTID
        fkTRID = Me.fkTagRelationshipID
    End With
 
End If
 
'This will assign the current NCR to the tagrelationship
If Me.NewRecord = True Then
    CurrentProject.Connection.Execute AssignNCR
End If
End Sub

 

[Kryst51]

I figured it out after more Internet searching.....

the first 10 sites I looked at didn't give any help.. but the 11th, that worked.

Here is what I had:

NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _
" VALUES (NCRID);"

Here is what I needed:

NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _
" VALUES (" & Chr(39) & NCRID & Chr(39) & ");"

Tricky Tricky.... I thought it might have something to do with quotes, but when I tried quotes I always ended up with a mismatch error.... Ahhh quotes.

Thanks all, and sorry for the time wasting.

 

[MSAccessRookie]

Kryst51,

You are right about the quotes being the reason for the problem, and indeed, chr(39) is the single quote character. One thing to consider is whether your strings could have a single quote as part of their content (for example a Last name like O'Hara). If so, then this approach would fall apart for any such strings. Another possibility is using chr(34), which is the double quote character, and is less likely to be a part of a string you are intending to use.

 

[Kryst51]

Kryst51,

You are right about the quotes being the reason for the problem, and indeed, chr(39) is the single quote character. One thing to consider is whether your strings could have a single quote as part of their content (for example a Last name like O'Hara). If so, then this approach would fall apart for any such strings. Another possibility is using chr(34), which is the double quote character, and is less likely to be a part of a string you are intending to use.

Hey, thanks for the comments, I will have to remember that when I try this in the future, although it probably wouldn't hurt to do now. But the field, currently, is an ID field and is a number format, Otherwise I would still be ripping my hair out. Well, not really, I am enjoying this and didn't spend too much time on it.

 

[boblarson]

Just a suggestion -

When naming fields, using ID in it implies a numeric value (at least it does to me) and so I would not name a text field with ID in the name. It is just one of those naming things that can throw you off in code because normally when I see ID I am looking for a Long Integer and not text.

 

[Kryst51]

Just a suggestion -

When naming fields, using ID in it implies a numeric value (at least it does to me) and so I would not name a text field with ID in the name. It is just one of those naming things that can throw you off in code because normally when I see ID I am looking for a Long Integer and not text.

Hi Bob, the field is not a text field, it is a number field. an FKID

 

[boblarson]

Hi Bob, the field is not a text field, it is a number field. an FKID

Then why would you need to encapsulate the field with double quotes? That doesn't make sense to me.

 

[Kryst51]

Well MSAccess Rookie said if it was a text field then it would be better if I used double quotes. I assumed that it wouldn't matter one way or another with a number field, so why not get into the habit of doing it? Is that not the case? If it is a number then single quotes, if text then doublequotes need to be used?

 

[LPurvis]

In an Insert statement, you can allow more type coersion than is even normal in Jet (which is quite a lot anyway).
However your code did more than add quotes if you take a squiz at it.

NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _
" VALUES (NCRID);"

became

NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _
" VALUES (" & Chr(39) & NCRID & Chr(39) & ");"

Not only did the quotes appear - but the variable was concatenated with the string rather than contained in it.
i.e.
NewTagRelQRY = "Insert INTO tblTagRelationships(fkNCRHeaderID)" & _
" VALUES (" & NCRID & ")"
would also have worked.
There's not a huge amount of risk with data type coersion here - your locale will generally handle issues such as date formats. But it's generally a better practice to match types appropriately.

Cheers

 

[MSAccessRookie]

Well MSAccess Rookie said if it was a text field then it would be better if I used double quotes. I assumed that it wouldn't matter one way or another with a number field, so why not get into the habit of doing it? Is that not the case? If it is a number then single quotes, if text then doublequotes need to be used?

Indeed, I did say that, but that was based on the assumption that the conversion failed due to the fact that the field contained text data that needed to be surrounded by quotes. I have a feeling that quotes would not be needed in a numeric context, and in fact could cause problems at a future date if the application is converted to use an Non Access SQL host like SQL Server or Oracle.

 

[Kryst51]

So for a number format it is more correct to do it Like this: " & NCRID & "

where if it had been a text format the way I did do it would be correct? If I am understanding you rightly. But the reason the way I did it still works is because of the flexibility of vba?


Edit: MSACCESSROOKIE, I just read your response, I'll change my code. Hmm. I'm learning a lot today AND making progress, fantastic.

 

[LPurvis]

The flexability of Jet (or ACE if you're using 2007+, either way it's the database engine).
But apart from that - yes.
(Though SQL Server accepts the syntax too - I couldn't tell you about Oracle, MySQL etc...)

I'm not saying, in this case, that it's a particularly good flexability.
Enforcing data types is generally a good thing.
But ultimately it doesn't matter too much as long as it's consistent.
What you don't want is a version of Access (or, say, SQL Server :-s) which allows some datatype coersion in one release and then, subsequently doesn't support it.
Breaking code all over the place. :-s

I've seen such databases built by others (even professionals) - and they umm... irritated me. ;-)

 

[Kryst51]

The flexability of Jet (or ACE if you're using 2007+, either way it's the database engine).
But apart from that - yes.
(Though SQL Server accepts the syntax too - I couldn't tell you about Oracle, MySQL etc...)

I'm not saying, in this case, that it's a particularly good flexability.
Enforcing data types is generally a good thing.
But ultimately it doesn't matter too much as long as it's consistent.
What you don't want is a version of Access (or, say, SQL Server :-s) which allows some datatype coersion in one release and then, subsequently doesn't support it.
Breaking code all over the place. :-s

I've seen such databases built by others (even professionals) - and they umm... irritated me. ;-)

I certainly want to make sure this DB is coded accurately, I want it to last and be able to be upgrade at a later time to SQL if possible, so I'll do anything I need to for that to happen. Thank you all for explaining!

 

[Banana]

Hi, As Leigh alluded, it's not really VBA's flexibility but rather Jet happily lending you a helping hand in coercing the string into number. In Jet context, there are 3 different ways to delimit:

For any numeric data:
(in SQL)

... myField = 42 ...

(in VBA as a SQL string)

" ... myField = " & MyNumber & " ... "

For any strings:
(in SQL)

 ... myField = "foobar" ...

(in VBA as a SQL string)

" ... myField = """ & MyText & """ ... "

" ... myField = " & Chr(34) & MyText & Chr(34) & " ... "

" ... myField = '" & MyText & "' ... "

For any date types:
(in SQL):

... myfield = #1/1/2010# ...

(in VBA as a SQL string)

" ... myField = #" & MyDate & "# ..."

Do further note that because you are using ADO, you are expected to use the native syntax unlike DAO. This means that if you are querying SQL Server, you follow SQL Server's rules of delimiting. So, instead of # for dates, you use ' for dates. Furthermore, you must use ' for string; double quote is unacceptable (well... I think one can change the configuration to accept but that's not the default behavior).

The only reason string is more complicated is because it happens to use same character that VBA uses to delimit the string so you're looking at two layer... a string in VBA which eventually become a SQL string - so you have to make sure your VBA string will collapse into a valid SQL statement once all concatenation and variables are resolved.

HTH.