Need Help in DLookup Function

[kiranz2002]

Hi Everybody,
I have a question, but before i go into the question let me give you
the following information
Table---SYS_INFO-- PK (SYS_ID_CODE)-- FK(TESTER_NME_ID)
Fields in SYS_INFO -- SYS_ID_CODE,TEST_BEGIN_DATE,
TEST_END_DATE,TESTER_NME_ID
Table TESTER_NME-- PK(TESTER_NME_ID), TESTER_NME
I have a form based on table SYS_INFO where i have
txtTEST_BEGIN_DATE As Textbox
txtTEST_END_DATE as Textbox
cboTESTER_NME as a combo box-- the values in combo box is based on
table TESTER_NME. cboTESTER_NME is bound with control TESTER_NME_ID.

Now the problem is when the user enters the testbegindate and
testenddate and select a tester name from the combo box, i want to have
something in the afterupdate event of this combo box where it lookup
the selected tester name in the table SYS_INFO to see if the selected
testername is already been selected for the given testbegindate and
testenddate.

In other word, i want to have a function which tells the user after the
user enter the testbegindate and testenddate, that this user is busy
between these dates, please select another tester name, and a query
should popup for available tester names for those dates.
Query is no problem, but i am not familiar with DLookup function.
So if anyone has a suggestion of how do i achieve this please do let me
know.

Thanks Millions in advance
Moe

 

[Ken Snell \(MVP\)]

DLookup would be the correct function to use. Check it out in the Help files
for its syntax, then post back with what you've tried if it doesn't work.

 

[kiranz2002]

I have the following codes in the afterupdate event of my combobox
cboTesterName
If Me.cboTesterName Between DLookup("TEST_BEGIN_DATE]","dbo_SYS_INFO",
"[SYS_ID_CODE]" And "[TEST_END_DATE]","dbo_SYS_INFO", "[SYS_ID_CODE]")
Then
MsgBox"Please select a different tester for the required dates"VBOKONLY
DoCmnd.RunQuery,"qryTesterAvailability"
EndIf

Its not working at all. Also when i run the query, i want the pass two
parameters from this code TEST_BEGIN_DATE, and TEST_END_DATE to show
the result between these dates.
I am totally lost what to do with this.

Please help me

Thanks

 

[kiranz2002]

i wanted to do something like the following.
Check TESTER_NME_ID in the SYS_INFO table. If the same TESTER_NAME_ID
exist in the table then check the same record for TEST_BEGIN_DATE and
TEST_END_DATE to see if Me.TEST_BEGIN_DATE and Me.TEST_END_DATE are
same or in between TEST_BEGIN_DATE and TEST_END_DATE in the table. if
so then do the message box and query.
I am really lost in how to put it together.

Please help me, i would be much much obliged to you.

Thanks

 

[Ken Snell \(MVP\)]

I apologize, but work has kept me busy and I haven't had chance to reply
yet... but I will.

 

[FA]

Thanks Ken i have not been able to resolve the issue by myself and i
need your help in making it work. Please Please help me.

Thanks

Moe

 

[FA]

FA is also my ID, i didnt notice when i first place the message with
Kiranz ID, but my actuall id is FA.
Sorry about that

Moe

 

[Ken Snell \(MVP\)]

I don't understand how you want to test the "begin" and "end" dates
specifically, but here is how you first test the tester name's existence:

If IsNull(DLookup("TEST_BEGIN_DATE]","dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "'")) = False Then
' code goes here to test the dates -- provide more details, please,
about
' the exact comparison that you want to do
End If

 

[FA]

Thanks Ken
Here is the function definition in a simple words so you will have
better understanding of what it suppose to do
When the user select a tester name from the drop down, in the
afterupdate event of that drop down

' First check the TESTER_NME_ID to see if there is any record of
Me.TESTER_NME_ID in the table dbo_SYS_INFO
'If any record of Me.TESTER_NME_ID exist in the table, check the same
record to see if TEST_BEGIN_DATE and TEST_END_DATE Fields have some
values in the same record
'if TEST_BEGIN_DATE and TEST_END_DATE Fields are null in that record
then do nothing and let the user chose the Me.TESTER_NME_ID, if those
dates are NotNull then
'Check the both fields dates to see if they are equal or in between
Me.TEST_BEGIN_DATE and Me.TEST_END_DATE
'if so then prompt the user to select a different tester name since
this tester is busy between these dates.

Thats pretty much it i believe.
Thanks millions for your help

Moe


If IsNull(DLookup("TEST_BEGIN_DATE]","dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "'")) = False Then
'Lookup the .

 

[Ken Snell \(MVP\)]

OK - I think I see what you want (note that this code does not trap for
"blank" entries in the TEST_BEGIN_DATE and TEST_END_DATE controls -- you may
want to handle those possibilities using Nz function to replace a blank
entry with some default value, or you may want to explicitly test for the
presence of entries in those controls before you start the If .. Then
block):

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

Dim strBetween As String
If DCount("*","dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "'") > 0 Then

If IsNull(DLookup("TEST_BEGIN_DATE","dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "'")) = False _
Or IsNull(DLookup("TEST_END_DATE","dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "'")) = False Then

strBetween = "#" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.cboTesterName & "' And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then

MsgBox "Pick another tester... this one is busy!"

End If

End If

End If

 

[FA]

Thanks So Much for your help Ken. I copied this code in the after
update event of TesterName Combo box
but its giving me error every time i try to test this function by
selecting a tester name from the combo box. I made sure that i have
some value in ME.TEST_BEGIN_DATE, and ME.TEST_END_DATE. The error
message its giving me is this; "Run-time error '2001': You Canceled the
Previous Operiation.
When i click on Debug, the pointer goes to the flollowing part of the
code.
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.TesterName & "'") > 0 Then


Private Sub TesterName_AfterUpdate()
Dim strBetween As String
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.TesterName & "'") > 0 Then
If IsNull(DLookup("TEST_BEGIN_DATE", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.TesterName & "'")) = False _
Or IsNull(DLookup("TEST_END_DATE", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.TesterName & "'")) = False Then
strBetween = "#" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NAME_ID]='" & Me.TesterName & "' And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then
MsgBox "Pick another tester... this one is busy!"
End If
End If
End If
End Sub

If possible can you tell me what could go wrong.

Thanks again

Moe

 

[Ken Snell \(MVP\)]

That error from the DLookup function is a bit misleading. What it means is
that a field name or table name in one or more of the arguments is not
valid, or that the TESTER_NAME_ID is a numeric field (I've written the
DLookup function in my examples with the assumption that it's a text field).

If it's a misspelling, correct it.

If the field is numeric and not text, remove the ' characters on either side
of the Me.TesterName.Value concatenation portion of the expression.

 

[FA]

I tried and i guess you are right, the now its giving me the error that
Data Type is mismatch. I tried to remove the concetenation part from
the code but i guess i must have done something wrong it is still
giving me the error.

The Data Field Name in the table is TESTER_NME_ID Data type (int(4))
The combo box name is cboTesterName

Private Sub cboTesterName_AfterUpdate()
Dim strBetween As String
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName) > 0 Then


If IsNull(DLookup("TEST_BEGIN_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName)) = False _
Or IsNull(DLookup("TEST_END_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName)) = False Then


strBetween = "#" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName & "' And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then

MsgBox "Pick another tester... this one is busy!"

End If
End If
End If
End Sub

I know i am really giving you too much trouble, but if you can resolve
this issue i have similar issues in my app that i can resolve by
myself.
Thanks alot

Moe

 

[FA]

I think you are really busy, well i will wait for your reply. I am
still stuck!

Please help

 

[John Spencer]

I didn't check all your code, but if Tester_Nme_Id is a number type then you
need to drop the apostrophes that you have after the = sign. Also, you need
to add the word " Between " to the strBetween that you are calculating.
There may be other problems, but I didn't see them.

Private Sub cboTesterName_AfterUpdate()
Dim strBetween As String
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName) > 0 Then


If IsNull(DLookup("TEST_BEGIN_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName)) = False _
Or IsNull(DLookup("TEST_END_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName)) = False Then


strBetween = " Between #" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"

If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName & " And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then

MsgBox "Pick another tester... this one is busy!"

End If
End If
End If
End Sub

 

[Ken Snell \(MVP\)]

Thanks, John, for jumping in... I was out of town for a week, and am still
catching up!

FA, let us know if John's suggestions are the solution.

--

Ken Snell
<MS ACCESS MVP>

I didn't check all your code, but if Tester_Nme_Id is a number type then
you need to drop the apostrophes that you have after the = sign. Also, you
need to add the word " Between " to the strBetween that you are
calculating. There may be other problems, but I didn't see them.

Private Sub cboTesterName_AfterUpdate()
Dim strBetween As String
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName) > 0 Then


If IsNull(DLookup("TEST_BEGIN_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName)) = False _
Or IsNull(DLookup("TEST_END_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName)) = False Then


strBetween = " Between #" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"

If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]=" & Me.cboTesterName & " And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then

MsgBox "Pick another tester... this one is busy!"

End If
End If
End If
End Sub

I tried and i guess you are right, the now its giving me the error that
Data Type is mismatch. I tried to remove the concetenation part from
the code but i guess i must have done something wrong it is still
giving me the error.

The Data Field Name in the table is TESTER_NME_ID Data type (int(4))
The combo box name is cboTesterName

Private Sub cboTesterName_AfterUpdate()
Dim strBetween As String
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName) > 0 Then


If IsNull(DLookup("TEST_BEGIN_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName)) = False _
Or IsNull(DLookup("TEST_END_DATE", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName)) = False Then


strBetween = "#" & Format(Me.TEST_BEGIN_DATE.Value, _
"mm\/dd\/yyyy") & "# And #" & _
Format(Me.TEST_END_DATE.Value, "mm\/dd\/yyyy") & "#"
If DCount("*", "dbo_SYS_INFO", _
"[TESTER_NME_ID]='" & Me.cboTesterName & "' And (" & _
"[TEST_BEGIN_DATE] " & strBetween & " Or " & _
"[TEST_END_DATE] " & strBetween & ")") > 0 Then

MsgBox "Pick another tester... this one is busy!"

End If
End If
End If
End Sub

I know i am really giving you too much trouble, but if you can resolve
this issue i have similar issues in my app that i can resolve by
myself.
Thanks alot

Moe

 

[FA]

Thanks John your correction worked fine with only one error, when i do
it once it works just fine but when i go back and change the
Me.TEST_BEGIN_DATE and Me.TEST_END_DATE its giving me error such as
Run time error '3075' Syntax error (missing operator) in query
expression '[TESTER_NME_ID]='
Other than that its working fine. if its possible you can see what
could cause this problem.

Thanks

Moe

 

[Ken Snell \(MVP\)]

Tell us more about the changes that you're making in the two textboxes? or
are you saying you're changing the code example to use other textboxes? It's
not clear what you mean, sorry!