Finally when I thought I could create something myself, I've ran into an error again.

Private Sub Command1151_Click()

DoCmd.Close

DoCmd.OpenForm "Call center"
Me.Knop1051.SetFocus
Event Enter()

End Sub


Error: Compile Error: Invalid inside procedure

What is wrong here?

Um where did you get this Event Enter() from?

Uhm, from the object browser? But I think I misread it somewhere

Is it just

Enter

?

Sometimes you have to think simple, I must agree.

EDIT: no that's not it... Dang it, this must be simple!

Edited by: MrSiezen on Fri Apr 15 9:04:54 EDT 2005.

Once you set focus on something, you are in it.

What I think you need is code attached to the OnEnter event of Knop1051.

Or possibly, the GotFocus event of Knop1051

Ah never mind,

found a better way to do it.

---
Private Sub Command1151_Click()

DoCmd.Close
DoCmd.OpenForm "Call center"
Me.Knop1051_Click

End Sub
---

But this leads to another error (yeah I'm good at this).

Here we are:
---
Run-time error '2467':

The expression you entered refers to an object that is closed or
doesn't exist.'
---

Which is weird, because if I run the last line alone straight from a command button, it works perfectly.

What are you trying to do?

Close the current form, reopen it, and immediately run Knop1051_Click.

Why? Because else I have to rewrite a five page long code, which is too complicated for me being a VBA noob.


Edited by: MrSiezen on Fri Apr 15 9:22:17 EDT 2005.

Still don't understand why you have to close and reopen a form. What is that accomplishing for you? My point is instead of asking how to do a specific thing, explain the whole situation. I suspect there is a better alternative if we understood the problem.

However, what I think you need is:

Private Sub Command1151_Click()

DoCmd.Close
DoCmd.OpenForm "Call center"
Call Knop1051_Click()

End Sub

Okay! You're right. But this is quite a story. I've added a sample database which makes it much easier to understand. I'm sorry but this might be a long story.

Our employees (Call center agents) work with the form CallCenter(-Start).

All they have to do is click on the button "Volgende" (=Next) which takes them to the first or next contact they have to call. Check the code behind this button to see how complex this is. But sometimes, they forget something, and want to go back to the last visited record.

For this I've implemented the button "Vorige" (=Last). This button uses the table "TblSys" where the ID and another code of the last visited record is saved everytime they click the "Volgende" button. This works fine.

Also, if they close the form, and reopen it later, and click "Volgende" again, it takes them back where they stopped the last time ( this is quite essential ).

But if you want to continue again after you went back to the last visited record, you can't just click "Volgende" again. Plz try this: Click "Volgende" a couple of times (You can see how it selects and filters a Branchecode every time), and then click "Vorige". This takes you to the last visited record (also, it saves the data from the last visited record again).

Now, here is where I run into the problem. If you click "Volgende" after this, it should take you back to the normal sequence of selecting a branchecode and filtering on it. But it doesn't. Hard to explain what it exactly does, so it's best to just try it.

So, instead of fighting my way through the long code behind "Volgende" I decided to use the earlier function which remembers the last visited record. Just reopen the form, and it takes you back to the last visited record again...well, it should.

Oi I hope this makes sense to you guys. I'm in this story too long

I hope you guys can help me, but I can understand if this takes you too much research...


Grtz, a desperate VB noob...

Well this works fine, but now the activated code returns the same error. So it hasn't fully opened the form yet or something?

Again, you need to explain what the real issue is here. Not ask for a bandaid

Okay,

but it seems like I'm forced to post the whole code here. Using colors seems a good idea now.

The purple code retrieves his starting point from the red part. This red part reads from tblSys which record was visited last, and which branchecode it has. After this the purple part devides the found branchecode into two parts which is then used by the rest of the code to find it's way through the table.

Everytime it opens the next record, the blue part saves the ID and branchecode of the current record in TblSys.

Now, if you go to another record WITHOUT using this code (numerous ways to do this, pressing the "Vorige" button is just one of them, but the most common one), the purple part of this code doesn't work properly.

It doesn't continue with the branchecode which is stored in TblSys. I don't know exactly what it does, but it doesn't work the way it should.

But, if you close the form, open it, and then run this code again, the purple part does it's job again. So instead of going through this whole code again, I considered automating the previous sentence would be much easier. Don't you agree?

Well, you asked why hehe, this is why. I'm sorry, but this whole thing is driving me mad right now. I didn't create this code, as you might have noticed, but since the guy who created this is gone, I have to fix it. Oh well, the red and blue part are mine actually.

I have to go home now (office is closing) but when I'm home I'll check up on this again to recheck this story.

Cya soon guys, hope you can help...

-----------------------
Public Sub Knop1051_Click()

Dim HBC As Integer

Dim sStartcode As String
Dim sGevondenBranche As String
Dim sCurrentBranche As String
Dim sLaatsteBranche As String


sLaatsteBranche = DLookup("[BrCode]", "tblSys", "Solid='1'")



'******************************************************
'SQL benodigdheden
'******************************************************
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strVeld As String
'******************************************************


'******************************************************
'intHoofdbranche zetten als die leeg is
'******************************************************
If intHoofdbranche = Empty Then
intHoofdbranche = Val(Trim(Left$(sLaatsteBranche, 2)))
End If
'******************************************************

'******************************************************
'intSubbranche zetten als die leeg is
'******************************************************
If intSubbranche = Empty Then
intSubbranche = Val(Trim(Right$(sLaatsteBranche, 2)))
End If
'******************************************************



'******************************************************
'Registratie laatstbezochte ID
'******************************************************
Dim rs As DAO.Recordset

If Not IsNull(Me.IDnummer) Then
Set rs = CurrentDb().OpenRecordset("tblSys", dbOpenDynaset)
With rs
.FindFirst "[Variable] = 'IDLast'"
If .NoMatch Then
.AddNew 'Create the entry if not found.
![Variable] = "IDLast"
![Value] = Me.IDnummer
![BrCode] = Me.Branchecode
![Description] = "Last customerID, for form " & Me.Bedrijfsnaam
.Update
Else
.Edit 'Save the current record's primary key.
![Value] = Me.IDnummer
![BrCode] = Me.Branchecode
.Update
End If
End With
rs.Close
End If
Set rs = Nothing
'******************************************************


If blnSearch = True Then
If Me.CurrentRecord <> Me.Recordset.Recordcount Then
If Me.Recordset.Recordcount <> 0 Then
DoCmd.GoToRecord acDataForm, "Call center", acNext
'******************************************************
'Controle op wijzigingen naar bijvoorbeeld Status = '1'
'******************************************************
strSQL = "SELECT [Postcode district proef].[Status] " _
& "FROM [Postcode district proef] " _
& "WHERE (([Postcode district proef].[Status] Is Not Null) AND " _
& "([Postcode district proef].[Status] <> '0') AND " _
& "([Postcode district proef].[Status] <> 'VK') AND " _
& "([Postcode district proef].[Status] <> 'WC') AND " _
& "([Postcode district proef].[Status] <> 'WR') AND " _
& "([Postcode district proef].[Status] <> 'AF') AND " _
& "([Postcode district proef].[Status] <> 'NA') AND " _
& "([Postcode district proef].[Status] <> 'EX')) " _
& "AND ([Postcode district proef].Branchecode = '" & Me.Form.Branchecode & "')"

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
result = rst.EOF
rst.Close
If result = False Then
'******************************************************
'Als de branchegeblokkeerd is(true) dan naar de volgende hoofdbranche
'******************************************************
intHoofdbranche = intHoofdbranche + 1
intSubbranche = 1
Else
Exit Sub
End If
End If
Else
temp = Me.Form.Branchecode
DoCmd.GoToRecord acDataForm, "Call center", acNext
'******************************************************
'Controle op wijzigingen naar bijvoorbeeld Status = '1'
'******************************************************
strSQL = "SELECT [Postcode district proef].[Status] " _
& "FROM [Postcode district proef] " _
& "WHERE (([Postcode district proef].[Status] Is Not Null) AND " _
& "([Postcode district proef].[Status] <> '0') AND " _
& "([Postcode district proef].[Status] <> 'WC') AND " _
& "([Postcode district proef].[Status] <> 'VK') AND " _
& "([Postcode district proef].[Status] <> 'WR') AND " _
& "([Postcode district proef].[Status] <> 'AF') AND " _
& "([Postcode district proef].[Status] <> 'EX') AND " _
& "([Postcode district proef].[Status] <> 'NA')) " _
& "AND ([Postcode district proef].Branchecode = '" & temp & "')"
rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
result = rst.EOF
rst.Close
If result = False Then
'******************************************************
'Als de branchegeblokkeerd is(true) dan naar de volgende hoofdbranche
'******************************************************
intHoofdbranche = intHoofdbranche + 1
intSubbranche = 1
Else
intSubbranche = intSubbranche + 1
End If
End If
End If

next_record:

'Controle op de sub en hoofdbranche
'Als subbranche leeg is naar 1 zetten
If intSubbranche = 0 Then
intSubbranche = Val(Trim(Left$(sLaatsteBranche, 2)))

End If

'Als hoofdbranche leeg is naar 1 zetten
If intHoofdbranche = 0 Then
intHoofdbranche = Val(Trim(Right$(sLaatsteBranche, 2)))
End If

'Als subbranche boven de 34 uitkomt naar 1 zetten en hoofdbranche ophogen.
If intSubbranche > 34 Then
intSubbranche = 1
intHoofdbranche = intHoofdbranche + 1
End If

'Als hoofdbranche boven de 6 naar 1 zetten
If intHoofdbranche > 6 Then
intHoofdbranche = 1
End If

'Maken van de startcode
If Len(Trim(intSubbranche)) = 2 Then
sStartcode = "0" & Trim(Str(intHoofdbranche)) & Trim(Str(intSubbranche))
Else
sStartcode = "0" & Trim(Str(intHoofdbranche)) & "0" & Trim(Str(intSubbranche))
End If


'**************************************************
'SQL code uitvoeren!
'**************************************************
strSQL = "SELECT TOP 1 " _
& " a.Branchecode , a.Status " _
& "FROM " _
& " [Postcode district proef] AS A " _
& "WHERE " _
& " ((A.Status is Null) OR (A.Status IN ('0','WC','VK','WR','AF','EX','NA'))) And (A.Branchecode >= '" & sStartcode & "') AND " _
& " ((SELECT " _
& " Count (b.Branchecode) " _
& " FROM " _
& " [Postcode district proef] AS B " _
& " WHERE ((B.Status is NOT Null) AND (B.Status NOT IN ('0','WC','VK','WR','AF','EX','NA'))) AND (B.Branchecode = A.Branchecode)) = '0') " _
& "ORDER BY A.Branchecode; " _

rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

'*************************************************
'Zetten van de huidige Hoofdbranche
'*************************************************
intHoofdbranche = Val(Trim(Left$(rst!Branchecode, 2)))
intSubbranche = Val(Trim(Right$(rst!Branchecode, 2)))

If Len(Trim(intSubbranche)) = 2 Then
freeBranche = "0" & Trim(Str(intHoofdbranche)) & Trim(Str(intSubbranche))
Else
freeBranche = "0" & Trim(Str(intHoofdbranche)) & "0" & Trim(Str(intSubbranche))
End If


rst.Close

'--- Filters toepassen
'--- Nog toevoegen 0, WC, AF A, AF B
Me.Filter = "([Postcode district proef].Branchecode = '" & freeBranche & "') AND " _
& "(([Postcode district proef].Status IS NULL) OR " _
& "(([Postcode district proef].Status <> '0') AND " _
& "([Postcode district proef].Status <> 'WC') AND " _
& "([Postcode district proef].Status <> 'WR') AND " _
& "([Postcode district proef].Status <> 'VK') AND " _
& "([Postcode district proef].[Status] <> 'EX') AND " _
& "([Postcode district proef].Status <> 'AF')))"

Me.FilterOn = True


If Me.Recordset.Recordcount = 0 Then
intSubbranche = intSubbranche + 1
GoTo next_record
End If

'--- Aangeven dat we zijn gaan zoeken
blnSearch = True



End Sub

First let me apologize. I didn't see your note from 10:14, I only saw the 10:25 one. That's why I asked for an explanation.

I've looked at this and between language issues and the complexity of the code I can't follow the code enough to pinpoint the solution. But I may be able to provide some pointers so you can track it down. From what I gathered the user should see a list of something (not sure whether its companies or locations or what) and should be able to go back and forth within that filtered list. For some reason a complex setup was created to do this.

My first suggestion is for you to step thru the code in Debug mode keeping track of the recordID as it moves. You should be able to spot the problem that way. If not, can you explain what, exactly is entered on the CallCenter form or is it just used to provide info to the user?

No problems with that, I'm glad enough you're trying to help.

But I figured it out myself already! Yahoo! Uhm...sorry

I forgot to add these declarations in my earlier post.
---
Public blnSearch As Boolean
Public intHoofdbranche As Integer
Public intSubbranche As Integer
---

Now it seems that blnsearch is the value which decides if the proces is already started.
So, I made a button with this code.

Private Sub Command1151_Click()

blnSearch = False

Call Knop1051_Click

End Sub

So now it knows that it has to restart again.
Which does the trick. How simple can it be...

Yep, sometimes it can be that simple. Glad you got it solved.