can values in a table be used as criteria to filter a query

[Navy Ken]

Should it stay that way? SQL is the native language in a database, you can't ignore it permanently.
So it would be a good time to invest some effort in knowledge or run away and hand the whole thing over to someone else.

The query in #9, supplemented with a filter on the current ID_Employees, is assigned as a RowSource to the ComboBox in the form.
This would then offer all available and not yet assigned areas for a selected Employee to choose from. That is what a user needs. He only needs to select from the correct ones and cannot make any errors. In this way, one could also offer and implement multiple selection.

My criticism of the DCount suggestion is that it is a trial and error process. You think of an ID and check whether it is in the table and can be entered. This gets frustrating very quickly.

That's fair. Just trying to take in a lot of new VBA and tossing in SQL... It's a steep learning curve. If I implement #9 where does the code go?

 

[Navy Ken]

DCount("*", "table-name or query-name", "( [UserID] = " & Me.UserID & ") AND ( [AreaID] = " & Me.AreaID & ")" )

I think I will use this in the form BeforeUpdate Event to simply trap out any attempt to add an area that has already been assigned. Would you mind helping better understand this code?

DCount("*", " AreaXEmployeeT",
This will count all records in the AreaXEmployeeT. How does the rest work? Could you help me break it down. I would rather understand than blindly copy code.

 

[Gasman]

The rest is the criteria.
In this case checking the userid to that on the form plus also checking the area to the area on the form. If that combination exists in any record, then the dcount will tell you how many. If it returns zero, then that combination does not exist.

 

[Navy Ken]

The rest is the criteria.
In this case checking the userid to that on the form plus also checking the area to the area on the form. If that combination exists in any record, then the dcount will tell you how many. If it returns zero, then that combination does not exist.

So it should be an IF THEN ELSE argument.

 

[Gasman]

That would be more than likely.

 

[Navy Ken]

I'm getting a Compile error: Expected list separator or )
Not sure where I went wrong, I followed what Doc gave me and VBA seem happy while I was entering it, and all the parentheses and quotes are closed up.

 

[Navy Ken]

Wait no they aren't hold on. Yes they are.

 

[Gasman]

Try

DCount("*", "AreaXEmployeeT", "[UserID] = " & Me.UserID & " AND [AreaID] = " & Me.AreaID )

Plus that assumes that both ID fields are numeric.

 

[Navy Ken]

They both are.

 

[Gasman]

If the criteria was any more than that, then I would put it all into a string variable. Then you can debug.print that until you get it correct.
Then you use it in the function.

 

[Navy Ken]

I Don't know Gas, editor still hates it.

 

[Gasman]

Copy and paste exactly what you have.

 

[Pat Hartman]

@Navy Ken You're very new here and if I haven't said welcome yet, Welcome

Going forward, you'll save yourself and us a lot of time if you ALWAYS post the code YOU are using. Saying "it's just like what Joe told me" doesn't cut it. Also include any error messages and what is wrong with the expected results if that is what you are asking about.

 

[The_Doc_Man]

Expected list separator or )

That error suggests some kind of parenthesis imbalance. Is there a chance that a parenthesis would be embedded in any of the arguments used in the "criteria" section of the function DCount? Because when you do that kind of substitution, you sometimes bring along unexplained baggage.

Pat's comments and Gasman's suggestion are debugging tips.

Let's say you wanted to debug this code and weren't exactly sure of what was going on. A typical approach is to assign a string to contain your criteria and then use Debug.Print to show what you passed in.

DIM strCrit As String
...
strCrit = "[UserID] = " & Me.UserID & " AND [AreaID] = " & Me.AreaID
Debug.Print strCrit
if DCount("*", "AreaXEmployeeT", strCrit ) = 0 then
    {do what you do for no duplicates}
Else
    {do what you do for duplicates}
End If

If you have the Immediate window (open via View >> Immediate Window) in the VBA code window, you will see exactly what you are passing in through the string strCrit. Then you can inspect it and you can do a copy/paste of the offending string along with any error messages. If you have any unexplained baggage, then that will tell you right away.

When I did a copy/paste to build this example, I noted some strange text. I don't know if the text in question was added by the Xenforo software that drives this forum or whether it came from some web tool or whether it somehow got into your actual input. I have highlighted the oddball text. I have seen it before in other contexts but I'm on the wrong end of the telescope to see where this originated.

"[UserID] = " & Me.UserID & " AND [AreaID] = " & Me.AreaID

If that semicolon is actually in the string, it would explain that error exactly, because the semicolon cuts off the line and the unbalanced parenthesis is to the left while the EFFECTIVE end of the current line is that semicolon. The closing semicolon is to the right but is read as being on a different line. The thing is, I don't know if that is an artifact of the Xenforo software as it copies something with special characters, so it might not actually be in the line on your system. That's why it is important to see with the Debug.Print what Access VBA thinks IT is seeing.

 

[Navy Ken]

So troubleshooting I have this and get no errors with this snipit. I think I may have an issue with the criteria. Just need a bit to get my head around what the criteria Doc suggested is doing. It APPEARS to me that its concatenating but I'm not sure why.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim WasItUsed As Integer
        WasItUsed = DCount("*", "[AreaXEmployeeT]") 'This give the correct count with no errors'
    Me.Label12.Caption = "WasItUsed =" & WasItUsed
End Sub

 

[Navy Ken]

OK the naming convention is a bit of a mess I know but I am afraid I will break something if I go normalizing it. When I add the criteria I get the error.

EmployeeVitalForeign = UserID
TblProgramAreasForeign = AreaID

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim WasItUsed As Integer
        WasItUsed = DCount("*", "[AreaXEmployeeT]","(EmployeeVitalForeign="&Me.EmployeeVitalForeign&")AND(TblProgramAreasForeign="&Me.TblProgramAreasForeign&")")
    Me.Label12.Caption = "WasItUsed =" & WasItUsed
End Sub

The error is now: Syntax error.
I may have a spelling error, need to go check or a procedure is not defined.

 

[Gasman]

So troubleshooting I have this and get no errors with this snipit. I think I may have an issue with the criteria. Just need a bit to get my head around what the criteria Doc suggested is doing. It APPEARS to me that its concatenating but I'm not sure why.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim WasItUsed As Integer
        WasItUsed = DCount("*", "[AreaXEmployeeT]") 'This give the correct count with no errors'
    Me.Label12.Caption = "WasItUsed =" & WasItUsed
End Sub

That just gives you a record count of ALL records. ?

 

[Navy Ken]

That just gives you a record count of ALL records. ?

In the AreaXEmployeeT table, Yes.

 

[Gasman]

Get rid of the ( ) brackets, except for those for the actual function.
Put the criteria into a string and debug.print it until you get it correct.

WasItUsed = DCount("*", "[AreaXEmployeeT]","EmployeeVitalForeign = " & Me.EmployeeVitalForeign & " AND TblProgramAreasForeign =" & Me.TblProgramAreasForeign)

 

[Navy Ken]

Got it!
Believe it or not it was spaces. I needed spaces around the ampersands. Unbelieveable.