Conditional If with two conditions and Dlookup

[charly.csh]

Hi everyone!

I have a question regarding a code;

it starts with my table [Work_Team] with several fields as [Name] [Department] and [ProductionLine]; into my Main_Form I have definided this code:



'************************************
Dim Warehouse1 As String
Dim Warehouse2 As String

If Me.WT_Warehouse = 0 Then 'This is a checkbox

'In this case my event stops in the line below; since person from warehouse has no "Production line" so it points out this line in yellow. What I want is to go to next ElseIf and use 'the criteria of variable Warehouse2

'I am using two criteria because will be other departments that they should have a ProductionLine and should take the first criteria of Warehouse1

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

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

ElseIf Me.WT_Warehouse = 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

'YOUR HELP TO CORRECT THIS IS HIGHLY APPRECIATED!!

[A S MANN]

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

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


If Me.WT_Warehouse = 0 And StrWarehouse1 >= 1 Then
Me.TeamM = Replace(Me.TeamM, Warehouse1, "")
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
Try Above code

[charly.csh]

Wow it works pretty good!

Just one question...
The variable Warehouse1 is not defined as anything into the code; does it not affect?

***********************************
Dim Warehouse1, StrWarehouse1 As String
Dim Warehouse2,StrWarehouse2 As String

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

Me.TeamM = Replace(Me.TeamM, Warehouse1, "")

[ssanfu]

Note:

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

Variables "StrWarehouse1" and "StrWarehouse2" are declared as type String
Variables "Warehouse1" and "Warehouse2" are declared as type Variant.
You MUST Explicitly declare variable types, otherwise they default to type Variant.

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

------------------------------------------------------------------------------------------

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

"Name" is a reserved word in Access and shouldn't be used as object names. Plus, it is not very descriptive - NAME of what??? Your dog's name? Street name you live on?

[A S MANN]

Yes You are right. Correction
If Me.WT_Warehouse = 0 And StrWarehouse1 >= 1 Then
Warehouse1 = DLookup("[Name]", "[Work_Team]", "[Department]= 'Warehouse' And [ProductionLine]= '" & Me.Line_Production & "'")
Rest is same. Kindly Try.

[charly.csh]

Hi A S MANN,

I was reading my code and maybe I needed to clarify some stuff and in fact I put it into incomplete;
could you help me please to correct this?

if the person assigned to the Warehouse has No production line, the "event" take the person by default from warehouse but If I assigned into my table a production line for the warehouse's 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 full code

************************************************

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

[June7]

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.

Only Variant datatype can hold Null. Other variable types will error if try to set with Null. DLookup() will return Null if no match.

[A S MANN]

i could not figure out your requirement fully and your code.
Kindly use Dcount () in all conditions in place of Dlookup () If conditions statements.
Use DLookUp () in Set Value in Field Me.TeamM .
DlookUp will give Error if No Records are found so Use DCount() =0 to define it.