Checkbox with conditional If and Dlookup with two conditions

[charly.csh]

Hi everyone,

I have question regarding a code I am trying to do and it's driving me crazy...



I have a table named as "Work_Team" which contains a lot of people from a company with diferent departments; the most of them have assigned a Production line like the Quality department, Engineering, Production Department, etc. There are other departments more generic that can attend everybody and not neccesary needs to assign a "production line" like warehouse and human resources or sales, etc.

I inserted some checkboxes (One by department) into a form named "Main_Form", The idea is to assign people into a Textbox named "TeamM" and it should select this people from the table "Work_Team" where the production line coincide with the form's droplist "Me.ProductionLine"

As a example one of this checkboxes is "WT_Warehouse" so I what I want to do is...

if the person assigned to the Warehouse has any productionline the "event" take the person by default from warehouse
but if I assigned into my table a production line for the warehouse guy the "event" would look for the guy where the department is Warehouse AND Production_Line coincide with Me.Line_Production

Also when I unselect the checkbox this guy should be removed


This is my code, I made that by parts but all together does not work... Your help is highly appreciated

************************************************** *****************************
Private Sub WT_Warehouse_Click()

Dim Warehouse1 As String
Dim StrWarehouse1 As String
Dim Warehouse2 As String
Dim StrWarehouse2 As String

'Clicking on the Warehouse checkbox and the field TeamM is empty

If Me.WT_Warehouse = -1 And Len(Me.TeamM & vbNullString) = 0 Then

'Here the field "Name" from the table Work_Team is the person responsible of the Warehouse department and it would work if this guy has a production line assigned, if not, should move to the next condition

Me.TeamM = DLookup("[Name]", "[Work_Team]", "[Department]= 'Warehouse' And [Line_Production]= '" & Me.Line_Production & "'")

'If the line below is empty it should move to the line below; I do not know if this is the issue...

ElseIf Me.TeamM = DLookup("[Name]", "[Work_Team]", "[Department]= 'Warehouse'") Then

StrWarehouse1 = DCount("[Name]", "[Work_Team]", "[Department]= 'Warehouse' And [LineProduction]= '" & Me.Line_Production & "'")

'Here should start the next condition... If I unclick on the checkbox to eliminate from the "TeamM" textbox the guy from warehouse

ElseIf Me.WT_Warehouse = 0 And StrWarehouse1 >= 1 Then

Me.TeamM = Replace(Me.TeamM, StrWarehouse1, "")
Me.TeamM = Replace(Me.TeamM, Chr(10), "")
Me.TeamM = Replace(Me.TeamM, Chr(13), "")

ElseIf Me.WT_Warehouse = 0 And StrWarehouse1 = 0 Then

Warehouse2 = DLookup("[Name]", "[Work_Team]", "[Department]= 'Warehouse' ")

Me.TeamM = Replace(Me.TeamM, Warehouse2, "")
Me.TeamM = Replace(Me.TeamM, Chr(10), "")
Me.TeamM = Replace(Me.TeamM, Chr(13), "")

End If

'I highly appreciate if you can help me to correct what it is wrong from my code

[June7]

How is this question different from your other thread https://www.accessforums.net/showthread.php?t=80494 ?

[charly.csh]

Hi June7

It 's different in the way that I just put into a part of the code... but all together does not work, maybe I should start my last thread with all the explaination I included in this new one
Could you help me on this new one?

[June7]

"Does not work" means what - error message, wrong result, nothing happens? Have you set a breakpoint and step debugged? Follow the code as it executes. Does is do what is expected?

[charly.csh]

It is stopping when I unclick on the WT_Warehouse in this line

'Here should start the next condition... If I unclick on the checkbox to eliminate from the "TeamM" textbox the guy from warehouse
ElseIf Me.WT_Warehouse = 0 And StrWarehouse1 >= 1 Then

Do you know what would be changed?

[June7]

What do you mean by "stopping" - is there an error message?

[charly.csh]

Nop is just point this line in yellow out

ElseIf Me.WT_Warehouse = 0 And StrWarehouse1 >= 1 Then

When I review the code it is showing as result ""

[June7]

There has to be some sort of message.

StrWarehouse1 is declared as a String yet your expression tests for a numeric value. In another place you set the variable to a number with DCount(). Declare the variable as an Integer.