Hi,
I am trying to make a public sub that checks to see if an item is already in a table
I have three separate tables that I want to check and each table has different names for the field I want to check. They all follow the same name structure, so I want to use text variables in a DCount function.
I cannot seem to wrap my head around where to use quotation marks and where not to.
I like to use chr(34) if the quotation mark will be part of a string. I have a debug.print after the DCount function and it's not returning the correct number - it should return a 0 if the text isn't located and a 1 if it is.
Here is what I have so far: (I know this is a not a public sub, but I'm trying to work out the correct way to write the DCount with the variables.
Code:
Private Sub Command7_Click()
Dim GroupFieldName As String
Dim GroupTableName As String
Dim NewData As String
Dim Tbl2Find As String
Dim Field2Check As String
Dim intCount As Integer
Dim Criteria As String
GroupFieldName = Me.Field_Name 'this will ultimately be a passed argument, right now it's a text box on a sample form
GroupTableName = Me.Table_Name 'this will ultimately be a passed argument, right now it's a text box on a sample form
NewData = Me.Data2Check 'this will ultimately be a passed argument, right now it's a text box on a sample form
Tbl2Find = "tlu_" & GroupTableName ' given GroupTableName = Categories, this should return exactly --> tlu_Categories
Field2Check = GroupFieldName & "_Name" ' given GroupFieldName = Cat, this should return exactly --> Cat_Name
Criteria = Field2Check & "=" & NewData 'given NewData = bird, this should return exactly --> Cat_Name=bird
intCount = DCount(Field2Check, Tbl2Find, chr(34) & Criteria & chr(34))
Debug.Print intCount
If intCount <> 0 Then
MsgBox ("already exists!")
Else
MsgBox ("not in table!")
End If
End Sub
In the Locals window, the variables are listed as:
: Tbl2Find : "tlu_Categories" : String
: Field2Check : "Cat_Name" : String
: Criteria : "Cat_Name=bird" : String
The intCount always returns 6, which is the number of records in the tlu_Categories table. It should have been 1 because "bird" is in the table. If the NewData is "Desk" then, intCount is still 6. Because "desk" isn't in the table, the value should have been 0.
I'm quite sure it's a quotation mark issue, but I've tried a bunch of different permutations and nothing is working.
To test the placement of the chr(34), I tried:
intCount = DCount(Field2Check, Tbl2Find, "'" & Criteria & "'")
intCount = DCount(Field2Check, Tbl2Find, """ & Criteria & """)
intCount = DCount Field2Check, Tbl2Find, "”"" & Criteria & "”"")
Each one always returns 6, no matter what is entered.
So, I left the Dcount as (Field2Check, Tbl2Find, Criteria) and worked on the line that builds the Criteria string
I tried:
Criteria = Chr(34) & Field2Check & "=" & NewData & Chr(34)
and it always returned 6. The locals table showed : Criteria : ""Cat_Name=bird"" : String
I tried:
Criteria = “’” & Field2Check & "=" & NewData & “’”
and it always returned 6. The locals table showed : Criteria : "'Cat_Name=bird'" : String
Then I tried: Field2Check & "=" & NewData
This didn't work either, but instead of the incorrect intCount value, I got the following error:
The expression you entered as a query parameter produced this error: 'bird'
When I use:
intCount = DCount(Field2Check, Tbl2Find, "Cat_Name=" & Chr(34) & NewData & Chr(34))
I get the correct intCount and correct messages.
What syntax will let me substitute the field name in the criteria with a string?