More Dcount issues

[chrisjames25]

Hi I am trying to do an if statment testing whether a dcount returns a value greater than 0

test = StrTier1
test2 = "Tbl_" & test

'If DCount(StrTier1, "Tbl_" & StrTier1, "&StrTier1 & '=" & Txt_NewTier1 & "'") > 0 Then
'If DCount(test, "Tbl_" & test, test = Me.Txt_NewTier1) > 0 Then
If DCount(test, test2, test = Me.Txt_NewTier1) > 0 Then
        msg = "This " & StrTier11 & "already exsits!!!"
        style = vbOKOnly + vbExclamation
        title = StrTier1 & " Duplicate Error"

        response = MsgBox(msg, style, title)

WHen i enter a variable in Txt_NewTier1 that i know exists in the table i am referencing nothing fires.

I am using defined terms in the code rather than hard coding "Tbl_Category" and "Category" as i have created this form so that i can replicate it for another purpose i.e Add Supplier as opposed to add Category by just changing the following code with the form:

Private Function StrTier1() As String
StrTier1 = "Category"
End Function
Private Function StrTier11() As String
StrTier11 = "category"
End Function



'## ON LOAD
'#####################################################
'Tier 1 Settings
'1) Copy and Paste this form
'2) Enter the variable in Tier1Str (Uppercase) and tier1String (lowercase)
'3) Amend the forms data sources and cbo record source and bound controls
'4) Amend the subforms data sources and bound controls

Private Sub Form_Load()

Me.Form.Caption = "Add " & StrTier1
Me.Lbl_Tier1Heading.Caption = StrTier1
Me.Lbl_NewTier1.Caption = "Please enter the new " & StrTier11 & " name:"
Me.Lbl_ConfirmTier1.Caption = "Please re-enter the new " & StrTier11 & " name:"
Me.Cmd_Add.Caption = "Add " & StrTier1

End Sub

WOuld simply have to change the top two defined strings to Supplier. Obv this is assuming i have kept naming conventions correct in all tables and fields.

 

[chrisjames25]

Solved it. Find it difficult to write it all as one so did following and played about to no error occurred and i got the result i was after:

Dim msg, style, title, response, MyString, Field, source, criteria

Field = StrTier1
source = "Tbl_" & Field
criteria = Field & "= '" & Txt_NewTier1 & "'"

If DCount(Field, source, criteria) > 0 Then
        msg = "This " & StrTier11 & " already exsits!!!"
        style = vbOKOnly + vbExclamation
        title = StrTier1 & " Duplicate Error"

        response = MsgBox(msg, style, title)

 

[Minty]

Your Dim syntax is incorrect.

You have to explicitly dim variable items as something, when you don't they are all dimmed as Variants - so any value could go in there. Use this layout

Dim msg As String, 
Dim style as String, title as String 
etc

It's also helpful for fault finding if you prefix your variables with an identifier so

Dim sStringVariable as String
Dim bBooleanVar  as Boolean
Dim dSalesDate  as Date
Dim iNumber as Integer

Makes it much easier to see what type of variable you have declared further into your code.

 

[chrisjames25]

Hi Minty

Many thanks for your input on this one and spotting my errors.

Like your prefix tip. Will get them adjusted.

 

[Mark_]

I use a similar concept to Minty, though I preface normally with an "a" for procedure level variables, "A" for module level, or "G" for global variables. Helps avoid having a variable used in the wrong place for its scope OR mucking about with a variable I'd rather not change.