D count issue

[chrisjames25]

Hi. Business quiet again so plodding along with my database.

Trying to do a DCount in vba but having issues with error that keeps coming up.

Background - initially i had a form with three combos. Category - genus - variety. Once i chose category it would run query to limit options in genus and then i would choose a genus that would limit options in variety.

I want to tweak this to save time.

What i want is to choose Category then jump straight to variety and choose a variety form the list that has been limited based on Category selected. This has been achieved sono problems there. When i choose the variety I want it to do a dcount to check that there is only 1 varitety name within that category. If there is i want it to autopopulate the genus cbo. However very occasionally there will be two variety names the same so in that instant i want it to not autopopulate the genus cbo. Instead i want it to alert me to the issue and ask me to please select the correct genus from combobox.

HOpe that makes sense. If not happy to elaborate more.

 

[Minty]

$64,000 question, what the code you've tried and what's the error?

 

[chrisjames25]

As with all the code i write i try to start simple and then build to what want so to start with i was just trying to check whether there was more than 1 variety in total across the total tbl_variety and then i was planning to add in the check to limit it to the particular category i had selected in cbo_category.

Currenty code is

Private Sub Cbo_Tier3_AfterUpdate()
       Dim msg, style, title, response, MyString, field, source, criteria
 
        field = "Variety"
        source = "Tbl_Variety"
        criteria = field & "= " & Me.Cbo_Tier3.Column(1)
        
        
        If DCount(field, source, criteria) = 1 Then
                
         End If
     
End Sub

Erro message comes up as attached

 

[isladogs]

1. When you write code like this:

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

all variables are defined as variants
You should explicitly state the datatype for each item e.g.

 Dim msg As String, style As String, title As String .... (etc)

2. Variety is a text field so you need text delimiters

I don't think you can reference a column value in a DCount
Set it to a variable first e..g.

Dim strVariety As String 
strVariety=Me.Cbo_Tier3.Column(1))

Also have you tried just entering the expressions direct into the DCount function?

 If DCount("Variety","Tbl_Variety", "Variety = '" & strVariety & "'") = 1 Then

OR

 If DCount("*","Tbl_Variety","Variety = '" & strVariety & "'") = 1 Then

 

[chrisjames25]

Hi Minty that worked a charm. Many thanks. I went with the following code you suggested

 Dim strVariety As String 
strVariety=Me.Cbo_Tier3.Column(1))
If DCount("Variety","Tbl_Variety", "Variety = '" & strVariety & "'") = 1 Then

Question is how i now add the qualifier that it needs to also check that the Categorg_ID is the same also.

FOr exmaple if i have three varieties called alba but only 1 is in category clematis i want the dcount to return 1 not 3.

I tried the below but it is not working correctly:

        Dim strVariety As String
        strVariety = Me.Cbo_Tier3.Column(1)
        Dim IntCategoryID As Integer
        IntCategoryID = Me.Cbo_Tier1
        
        
        If DCount("Variety", "Tbl_Variety", "Variety = '" & strVariety & "' and Category_ID = ' & IntCategory & ' ") = 1 Then
                
         MsgBox ("hello")
         Else
         
         MsgBox ("Please choose correct genus")
                
        End If

 

[chrisjames25]

Hi Ridders, apologies i said Minty instead of you.

THanks to you both for contributing so far.

 

[Minty]

I don't mind

Your quotes are a bit out.

        If DCount("Variety", "Tbl_Variety", "Variety = '" & strVariety & "' and Category_ID = " & IntCategory & ") = 1 Then

 

[chrisjames25]

Hi Minty

tried the code and get error pop up saying syntax error. Any ideas

 

[Minty]

Apologies I was doing this in a text editor and it helpfully adjusted what I thought I had written. Try

DCount("Variety", "Tbl_Variety", "Variety = '" & strVariety & "' and Category_ID = " & IntCategory ) = 1 Then

 

[chrisjames25]

CLose but no cigar. Now get the following error attached.

 

[Minty]

That would indicate there is no value in IntCategory.

Try the following ;

        Dim strVariety As String
        Dim IntCategoryID As Integer
        
        strVariety = Me.Cbo_Tier3.Column(1)
        IntCategoryID = Me.Cbo_Tier1
        
        Debug.Print "Variety = " & strVariety &  " , Cat ID = " &  Nz(IntCategoryID ,"Nothing Set")
        
        If DCount("Variety", "Tbl_Variety", "[Variety] = '" & strVariety & "' and [Category_ID] = " & IntCategory & "") = 1 Then
                
              MsgBox ("hello")
         Else
         
              MsgBox ("Please choose correct genus")
                
         End If

If it errors out then press ctrl + G in the immediate window to see the debug message.

 

[chrisjames25]

OK SO tweaked code as noted id made couple of erros. Tbl_Variety didnt have category id in it so changed that to Qry_Variety and added the letters ID to IntCategory in the dcount line of code

     Dim strVariety As String
        Dim IntCategoryID As Integer
            
        strVariety = Me.Cbo_Tier3.Column(1)
        IntCategoryID = Me.Cbo_Tier1
        
        Debug.Print "Variety = " & strVariety & " , Cat ID = " & Nz(IntCategoryID, "Nothing Set")
        
        If DCount("Variety", "Qry_Variety", "Variety = '" & strVariety & "' and Category_ID = ' & IntCategoryID & ' ") = 1 Then
                
              MsgBox ("hello")
         Else
         
              MsgBox ("Please choose correct genus")
                
         End If

Now saying data mismatch. THe catID part is a number

 

[isladogs]

You've forgotten about delimiters again...

If DCount("Variety", "Qry_Variety", "Variety = '" & strVariety & "' and Category_ID = " & IntCategoryID) = 1 Then

 

[Minty]

You've messed up the quotes again ;

        Dim strVariety As String
        Dim IntCategoryID As Integer
            
        strVariety = Me.Cbo_Tier3.Column(1)
        IntCategoryID = Me.Cbo_Tier1
        
        Debug.Print "Variety = " & strVariety & " , Cat ID = " & Nz(IntCategoryID, 0)
        
        If DCount("Variety", "Qry_Variety", "[Variety] = '" & strVariety & "' and [Category_ID] = [COLOR="Red"]"[/COLOR] & IntCategoryID & [COLOR="red"]""[/COLOR]) = 1 Then
                
              MsgBox ("hello")
        Else
         
              MsgBox ("Please choose correct genus")
                
        End If

I'm certain you don't need the two double quote at the end, but have included them for clarity.

 

[chrisjames25]

Hi BOth. Yep that fixed it.

Minty - double quotes not required like you suggest. Thanks for sticking with me on that one. Delimiters confuse the hell out of me. find it hard to follow them through.

THanks again

 

[isladogs]

Both solutions would work but I MUCH prefer the simpler one without the "" at the end