Error saying my subform is empty when it is not

[haerion]

Hi guys,

I was just wondering if someone could help me on that:

I got a form which have a subform on it, and when I click on a command, it take the info on my subform and copy it in another program, problem is sometime, even if I have 1 or more line in my subform, it give me the no entry msgbox with the following code.

Set rs = Me.ItemCustWant1_subform.Form.RecordsetClone

If rs.BOF And rs.EOF Then
   MsgBox ("no entries")
   Exit Sub
End If

I'm trying to find how I could correct this so it will not give me the "no entrie" when there IS entries

Thank you.

 

[seaport]

It depends on which event that code is running at.

I prefer using the recordset.RecordCount method to determine the number of records in the form. That method is more reliable.

 

[haerion]

Problem is that I use a loop with .movenext to copy my data on my other program. so if the rs.BOF And rs.EOF doesn't work, then the following code will not work:

    rs.MoveFirst
    Do Until rs.EOF
    With rs
    Check = !Check
    If Check = True Then
        Item = !Itemno & ""
          SendKeys Item & "{enter}"
    End If
          rs.MoveNext
    End With
    Loop

 

[seaport]

When using ADO form recordset, the EOF and BOF is a little complicated because it depends on what you are currently doing on a record. That's why I said "depending on the event".

When using a recordset from a query or table, the EOF and BOF works as they are supposed to be. So your second example should work fine. To avoid complication, do not use form recordset. Create the recordset from a query/table instead.

 

[haerion]

Excellent suggestion, I change my code as you suggested, will see if the error come up again, be back in 2 days to update you

 

[TheAceMan1]

How are ya haerion . . .

Your biggest problem is wether [blue]rs[/blue] was declared as DAO or ADO (assuming you have A2K or greater). According to you:

[blue] ... even if I have 1 or more line in my subform, it give me the no entry msgbox ...[/blue]

This indicitive of ADO! Which means either you declared [blue]rs[/blue] as ADO, or you don't have DAO and didn't declare [blue]rs[/blue].

This is easily fixed by declaring rs as [blue]DAO[/blue]. But first ... the code requires [purple]microsoft dao 3.6 object library[/purple] to run. to [blue]check/install[/blue] the library, in any code window click [blue]tools[/blue] - [blue]references...[/blue] in the listing find the library and [blue]make sure its checked.[/blue] then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. click ok.

Your modified code becomes:

[blue][purple][b]Dim rs As DAO.Recordset[/b][/purple]

Set rs = Me.ItemCustWant1_subform.Form.RecordsetClone

If rs.BOF And rs.EOF Then
   MsgBox ("no entries")
   Exit Sub
End If[/blue]

Should work just fine!

There's bigger problems in the use of your sendkeys code:
[ol][li]Your other program where your sending the keys, needs to have the focus ... making it the active application. You don't switch focus![/li]
[li]If your going to use sendkeys you should at least set the 2nd arguement to [blue]True[/blue]. This enters a wait state to allow the keys to be sent ... otherwise problens can occur:

[blue]   SendKeys Item & "{enter}", True[/blue]

[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Gents,
The only way that this code

Set rs = Me.ItemCustWant1_subform.Form.RecordsetClone

Returns and ADO recordset is if the user explicitly bound it to an ADO recordset. Although that is possible, it seems pretty unlikely and there is not suggestion by the OP that was done.

So unless that is the case there is no way that the above code returns an ADO recordset regardless of what references are set. If by chance the OP only had ADODB set as a reference then this would throw a type mismatch error, but not return an ADO recordset.

 

[TheAceMan1]

Howdy MajP . . .

Yes your right ... however in response to the actions of BOF/EOF I did say:

[blue] ... This is [purple]indicitive[/purple] of ADO![/blue]

... I didn't say it was! I'm well aware that a recordset clone can't be perscribed as an ADODB type.

In any case, my point is to specifically try [purple]DAO[/purple] ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[haerion]

Hi TheAceMan

Been a while hehe. I have been good, and you?

As for your code, I think the DAO recordset would work, but I seriously need to read on that, as for now, I use them but I don't understand them (I mean the recordset). It is like trying to decipher a text when someone splatter it with coffee You can understand the big picture but missing on a lot of little thing.

For the sendkeys, I have this code right before the sendkeys:

[/code]AppActivate ("Versys:2 - NITerm")
Wait 2[/code]

This should relieve your worry on the focus

About the sendkeys itself, I'm not sure I understand completely the 2nd argument. What is the difference between them false and true.

The way I implemented it is as follow:

AppActivate ("Versys:2 - NITerm")
Wait 2

SendKeys "^x"
SendKeys StoreNo

SendKeys "{ENTER}"
SendKeys Acronym & "{ENTER 4}"

If DCount("*", "qryItemCustWant1", "[Check] = -1") = 0 Then
    rs.MoveFirst
    Do Until rs.EOF
    With rs
        Item = !Itemno & ""
          SendKeys Item & "{enter}"
          rs.MoveNext
    End With
    Loop
Else
    rs.MoveFirst
    Do Until rs.EOF
    With rs
    Check = !Check
    If Check = True Then
        Item = !Itemno & ""
          SendKeys Item & "{enter}"
    End If
          rs.MoveNext
    End With
    Loop

End If

    rs.Close
    Set rs = Nothing
    
SendKeys "{ENTER}"
SendKeys "END" & "{ENTER}"

btw, thanks a lot for your help Ace, always appreciated

 

[TheAceMan1]

[blue] ... I think the DAO recordset would work, but I seriously need to read on that, as for now, I use them but I don't understand them (I mean the recordset)[/blue]

Well ... do you to study DAO or solve your problem? It takes no time to set the DAO reference and declare rs as DAO. Yoy just need to see if it works! ... If not its easy enough to remove the DAO delcaration line and remove the DAO reference (a simple checkbox). You can study DAO any other time you want.

Try it ... what do you have to loose!

BTW, for those following this thread, [blue]what version access[/blue] are you using?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

haerion . . .

Hit submitt too soon! ... To read about BOF/EOF ... :
[ol][li]Open any module in the modules window.[/li]
[li]Hit [blue]Ctrl+G[/blue] to call up the Immediate Window.[/li]
[li]Enter [blue]BOF[/blue]. Backspace to put the cursor on the word and hit [blue]F1[/blue] to read about it![/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[haerion]

Hey Ace

I tried the DAO recordset, still get the same error as before, there is 2 lines but he come up empty.
and for those following, my version is Access 2003