[RESOLVED] Problem with Code

[ielashi]

Hello,
The following piece of code I am having an error in. I divided it to two parts to simplify it.

Dim sConnString As String
    Dim rsTempRecordSet As New ADODB.Recordset
    Dim cnTeamCMI As New ADODB.Connection
    Dim sSQl As String
    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\ÅÓáÇã\ÓØÍ ÇáãßÊÈ\ÈÑäÇãÌ ÇáÍÖæÑ æ ÇáÇäÕÑÇÝ\database.mdb;Jet OLEDB:Engine Type=5;"
    With cnTeamCMI
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open strConnString
    End With
        sSQl = "SELECT [ÑÕíÏ ÇáÅÌÇÒÇÊ] FROM [ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "'"
        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly
    With rsTempRecordSet
 
        If .Fields("ÑÕíÏ ÇáÅÌÇÒÇÊ") >= 1 Then
        cmdMGTHoliday.Enabled = True
        Else
        cmdMGTHoliday.Enabled = False
        End If
    End With
rsTempRecordSet.Close
Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

This part is fine. But I get the error in his part of code:

With cnTeamCMI
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open strConnString
    End With
        sSQl = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [ÊÇÑíÎ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"
        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly
    With rsTempRecordSet
        If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
        cmdMGTHoliday.Enabled = False
        Else
        cmdMGTHoliday.Enabled = True
        End If
    End With
rsTempRecordSet.Close
End Sub

It highlights this line: " If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then" with an error message telling me that either BOF or EIF is True, or the current record has been deleted, the process needs a current record. (The error message is in Arabic I tried to translate as accurate as I could). Any suggestions on how to solve this problem?
Thanx in advance !

[Devion]

It mostly means that the query didn't return any results, so before you actually start fetching data check if .EOF isn't true.

[ielashi]

How can I check it?

[Devion]

If not rsTempRecordSet.EOF then
 ' Do your data fetches here.
End if

[dee-u]

sSQL = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [ÊÇÑíÎ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"
With cnTeamCMI
    .ConnectionString = sConnString
    .ConnectionTimeout = 0
    .CursorLocation = adUseClient
    .Open strConnString
End With
With rsTempRecordSet
    .Open sSQL, cnTeamCMI, adOpenStatic, adLockReadOnly
    If Not (.EOF And .BOF) Then
        If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
            cmdMGTHoliday.Enabled = False
        Else
            cmdMGTHoliday.Enabled = True
        End If
    End If
    .Close
End With

[ielashi]

But where is the execution statement for your code dee-u? And shouldn't we put this at the end of your code?

Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

[Devion]

not to mention .MoveNext if you have multiple records.

[ielashi]

OK, here is the code that I have at the moment. But it doesn't do anything. (NO errors, nothing at all). By the way could you tell me where would I put the .movenext?

With cnTeamCMI
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open strConnString
    End With
        sSQl = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [ÊÇÑíÎ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"
        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly
    With rsTempRecordSet
       If Not (.EOF And .BOF) Then
        If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
        cmdMGTHoliday.Enabled = False
        Else
        cmdMGTHoliday.Enabled = True
        End If
        End If
    End With
rsTempRecordSet.Close
Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

[Devion]

Don't check on BOF, Begin of File is always true at the start

With rsTempRecordSet
       While not (.EOF)
           If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
                cmdMGTHoliday.Enabled = False
           Else
                cmdMGTHoliday.Enabled = True
           End If
 
           .MoveNext
        Wend
    End with

This query is a bit weird as it probably never will have more then 1 result, it's only useful for more records.. like:

With rsTempRecordSet
       While not (.EOF)
           lstView.Listitems.Add,, .Fields("Username")
           .MoveNext
        Wend
    End with

Kinda like that, it just moves the record up or down by one notch (depending how you look at it hehe). As soon as it hits the end EOF is set to true and thus the While loop ends.

[dee-u]

But where is the execution statement for your code dee-u? And shouldn't we put this at the end of your code?

Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

I just tried to fix your code, so it is still your code and not mine...

[ielashi]

But I was wondering how come the first one worked? Anyways, here is the code I have right now. Still it doesn't function. I think it's because EOF is true so it doesn't process the If statement. Is there like a command to set it to false or something else that would fix this?

With cnTeamCMI
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open strConnString
    End With
        sSQl = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [ÊÇÑíÎ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"
        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly
    With rsTempRecordSet
       While Not (.EOF)
           If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
                cmdMGTHoliday.Enabled = False
           Else
                cmdMGTHoliday.Enabled = True
           End If
           .MoveNext
        Wend
    End With
rsTempRecordSet.Close
Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

[Devion]

Ehh don't use the While statement for 1 record, just use the IF statement.

Something like this:

With rsTempRecordSet
       If Not (.EOF)
           If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
                cmdMGTHoliday.Enabled = False
           Else
                cmdMGTHoliday.Enabled = True
           End If
       Else
             cmdMGTHoliday.Enabled = True
       End if 
    End With

[ielashi]

Sorry for late reply, I tried what you said, I am not getting any errors now, but it's not doing what I want. It skips the if statement that I have as EOF is always true. Here's the code I have at the moment:

With cnTeamCMI
        .ConnectionString = sConnString
        .ConnectionTimeout = 0
        .CursorLocation = adUseClient
        .Open strConnString
    End With
        sSQl = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [ÊÇÑíÎ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"
        rsTempRecordSet.Open sSQl, cnTeamCMI, adOpenStatic, adLockReadOnly
    With rsTempRecordSet
       If Not (.EOF) Then
           If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" Then
                cmdMGTHoliday.Enabled = False
           Else
                cmdMGTHoliday.Enabled = True
           End If
       Else
             cmdMGTHoliday.Enabled = True
       End If
    End With
 
 
rsTempRecordSet.Close
Set rsTempRecordSet = Nothing
Set cnTeamCMI = Nothing

[Devion]

It skips the if statement that I have as EOF is always true

That's why there is a Else... statement. If there is no record returned you can put the code in the else statement. I put cmdMGTHoliday.enabled = true there as I assumed that would be the default.

[ielashi]

I understand what you mean. But the problem is that I'm POSITIVE there are records matching my query.

[demotivater]

Why don't you stick a

MsgBox rsTempRecordSet.recordcount

after you run the sql statement just to see if it is returning a record or not.

[ielashi]

What if we use WHERE EXISTS in the SQL SELECT statement, would that work?

[ielashi]

Thanx for the advice demotivator, I'll give it a shot

[ielashi]

OK, I seem to understand the problem and it's almost fixed. I'm trying now to make an if statement for multiple database fields, I think my syntax is incorrect. Here it is:

If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" And .Fields("ÊÇÑíÎ ÇáÏÎæá") = "'" & Format(Now, "dd/mm/yy") & "'" Then
            cmdMGTHoliday.Enabled = True
        Else
            cmdMGTHoliday.Enabled = False
        End If

Any ideas about the basic syntax for the if statement above would be greatly appreciated. By the way, do all the fields in the if statement above must be in the SELECT SQL statement?

[demotivater]

That looks ok to me. And yes, the fields have to be in the sql statement.

You may also want to check if the fields you have retreived are null before doing any work with them.

ie:

If isnull("youfield") = false then
'do you stuff
Else
'can't do your stuff, the field is null
End If

[Dennis DVR]

Hope you guys don't mind if I hop in

Don't check on BOF, Begin of File is always true at the start

Do you mind explaining this one?

BTW IMHO the proper way of determining whether the result returned by a query is empty is by cheking both EOF and BOF not just BOF nor EOF


To ielashi

if you are assigning the value of your fields to a textbox or other control then demotivater is quite correct you should check the value of your fields whether it is NULL or not before passing it to a textbox control etc.., but another method would be to just add a vbNullString or "" (empty string) and eliminate the If block.

BTW Format will already return string why do you need to add a single quote (') in the result?

[ielashi]

Thank you. Actually one of the fields of my if statement wasn't in the SELECT SQL statement, so I had to write it again. But there seems to be a problem in it which puzzles me. It just tells me that are no records for it although there is in the database data matching my query. Here is my statement:

sSQl = "SELECT [ÍÖæÑ] FROM [ÊæÞíÊÇÊ ÇáÅÏÇÑí] WHERE [ÇÓã ÇáÚÇãá] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [æÞÊ ÇáÏÎæá] = #" & Format(Now, "dd/mm/yy") & "#"

Any suggestions?

[ielashi]

DENNIS DVR, I can't really understand what you mean. Can you put it an example code to make more clearer?

BTW Do the fields have to actually be null so I can work with them?

[demotivater]

Are you sure you want to be searching the database for the List Index?

[edit]Whoops, nevermind, misread your code[/edit]

[Dennis DVR]

Are you sure that [æÞÊ ÇáÏÎæá] is a date datatype? and the format is dd/mm/yy?

BTW you are asking example? to what?

[ielashi]

No no, you see, it's looking for the name I clicked on in the listbox. This part's fine. The problem I think is in the last part, with the date thing.

[ielashi]

Yes, it is a DateTime field. I was asking example regarding the vbnullstring. And do the fields have to be null so that I can work them?

[ielashi]

I substituted the fields in the SQL statement with relevant ones in English to make things a little clearer.

sSQl = "SELECT [Holiday] FROM [Employee Timings] WHERE [Employee Name] = '" & lstMngment.List(lstMngment.ListIndex) & "' AND [Punchin Time] = #" & Format(Now, "dd/mm/yy") & "#"

[ielashi]

Could someone please tell me what's wrong with this if statement, been trying to work on it forever, I just don't know what's wrong. There are no errors formaed, but the value of the field I entered did not match. Needs to be rephrased I guess. Anyone have any ideas?

If .Fields("ÊÇÑíÎ ÇáÏÎæá") = "#" & Format(Now, "dd/mm/yyyy") & "#" Then
            cmdMGTHoliday.Enabled = False
        Else
            cmdMGTHoliday.Enabled = True
        End If

[Dennis DVR]

Yes, it is a DateTime field

But how about the format is it really dd/mm/yy?

I was asking example regarding the vbnullstring

If you are passing the value of your fields to a textbox control etc.. then you could do something like

Text1.Text = vbNullString & .Fields("YourField").Value 'It will not give you an error even if .Fields("YourField").Value is NULL

This one will throw an error if .Fields("YourField").Value is NULL

Text1.Text = .Fields("YourField").Value

But as far as I can see it you are not passing the value of your fields to a textbox control etc.

And do the fields have to be null so that I can work them?

No, it doesn't have to be NULL to be able for you to work with then

and did you removed the single quote in your IF statement? i.e.

If .Fields("ÍÖæÑ") = "ÅÌÇÒÉ" And .Fields("ÊÇÑíÎ ÇáÏÎæá") = Format(Now, "dd/mm/yy") Then

[ielashi]

Actually, I modified the SELECT SQL statement minorly and therefore the if statement has changed. Please take a look at my previous post and inform me if you see any error in it.

[Dennis DVR]

I don't think you need a pound or number sign there

[ielashi]

OK, I removed them and the line now looks like this:

If .Fields("ÊÇÑíÎ ÇáÏÎæá") = " & Format(Now, "dd/mm/yyyy") & " Then

But do you know that ASCI thing which replaced this double apostrophe " because it's giving me an error because of that.

[techgnome]

Take the quotes out.... you do not need them....

If .Fields("ÊÇÑíÎ ÇáÏÎæá") = Format(Now, "dd/mm/yyyy")  Then

Tg

[Dennis DVR]

Post #30

[ielashi]

Tried removing quotes but still doesn't work

[techgnome]

What doesn't work? Why? What? Where?
[snarky=on]Saying it doesn't work does the rest of us no good. That's walking into the doctor's office and say it hurts. He's going to ask what hurts. Ok, so it doesn't work? Well, why not? What kind of errors do you get? HOW do you know it isn't working?[/snarky]

Tg

[ielashi]

What I meant was that when I removed the quotes from the If statement it doesn't work. It doesn't show any errors because it is skipped by another if statement because it doesn't math the field database. But no problem, I made another alternative. Instead of making the if statement on this datetime field, I made it based on another string field. It works now, but thank you people so much for your help.

[dee-u]

Usually I do it this way...

If DateValue(.Fields("ÊÇÑíÎ ÇáÏÎæá")) = DateValue(Date) Then