Changing the current tabindex at runtime? 1

[MickyWork]

I need to be able to change the current tab index, while the database is running.

For Example

When a Afterupdate is called from the user either tabbing or clicking on another field, if they have entered wrong information I want the tabindex to be set back to the incorect field so they can contine typing straight away without having to click on the field.

Anyone know how to do this?

Thank you
Michael Kirk

 

[Craig0201]

Michael,

This is done using the SetFocus method of every control....

So if you want a text box called txtName to get focus....

txtName.SetFocus

That easy!!!

Craig

 

[MickyWork]

Craig thank you for the quick answer

I have done that and it still goes on to the next tab in the order

I'll put the code I have below

Private Sub damageid_AfterUpdate()
checkforduplicatedamageid
End Sub

Function checkforduplicatedamageid()
recordexists = 0
Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset&quot
stSql = "SELECT * FROM damages where damageid = """ & damageid.Value & """"
rs.Open stSql, con
Do Until rs.EOF
recordexists = 1
rs.MoveNext
Loop
rs.Close
If recordexists = 1 Then
MsgBox ("The damageID you entered already exists, please enter a new damageID&quot
damageid.SetFocus
End If
End Function

Any idea whats wrong?

 

[Craig0201]

A couple of things in general with the code but nothing specific to solve the problem....

As it is a function, it should return a value, in this case a boolean....

The SetFocus should be as a result of the function, not integral to it.....

You're looping thru a recordset to see if values exist when a look at the record count will tell you this....

You're using late binding which will slow the execution of the code....

So your code should be more like...

Private Sub damageid_AfterUpdate()

If checkforduplicatedamageid = True Then
MsgBox "Your message here....", vbOkOnly + vbInformation
DamageID.SetFocus
End If

End Sub


Function checkforduplicatedamageid() As Boolean

Dim objCon as ADODB.Connection
Dim objCmd as ADODB.Command
Dim objRS as ADODB.Recordset

Set objCon = Application.CurrentProject.Connection

Set objCmd = New ADODB.Command

With objCmd
.ActiveConnection = objCon
.CommandText = "SELECT * FROM damages where damageid = '" & damageid.Value & "'"
Set objRS = .Execute
End With

If objRS.RecordCount > 0 Then
checkforduplicatedamageid = True
End If

End Function

Also.......what is DamageID??? Couldn't it be an autonumber??

Craig

 

[MickyWork]

I knew you would comment on my horrendous coding .

Thank you for taking the time to rearrange it all for me. It is appriciated.

I knew when I was doing it that I needed to make it return a vaule as you did, but sometimes I just get lazy and do it the quick way, basically so I don't have to write the other 2 lines over and over when I want to call the function.

Just to let you know I have implemented that code and it doesn't enter into the RecordCount If command, it says the recordcount is -1 even if I enter a damageID that is already used.

Any ideas there, sorry for taking up your time.

And no damageID can't be a autonumber as they don't know what it will be, it may have some text for example bir251.

Micky

 

[Craig0201]

Micky,

Change the type of recordset returned to a dynaset and the recordcount will work...

Craig

 

[MickyWork]

Ok now I just feel thick

What is a dynaset and how do I change it to one?

Also changed it to return false just to make the 2 sub commands get called and it still doesn't setfocus to the damageid field.

Micky

P.S
I only started programming in VB 2 weeks ago so don't think I'm doing 2 bad.

Because I did some tiny databases at collage, about 6 years ago they give me this huge project, buy me a rubbish access book and say right do that

 

[Craig0201]

Well if you started two weeks ago, you're doing pretty well!!! Best thing to do is to get some books on how to write code in general....

Don't change the If in the sub to be equal to false. It will run the wrong way round if you do......

Substitute this code for the function...

Function checkforduplicatedamageid() As Boolean

Dim objCon as ADODB.Connection
Dim objCmd as ADODB.Command
Dim objRS as ADODB.Recordset

Set objCon = Application.CurrentProject.Connection

Set objCmd = New ADODB.Command

With objCmd
.ActiveConnection = objCon
.CommandText = "SELECT * FROM damages where damageid = '" & damageid.Value & "'"
End With

set objRS = New ADODB.RecordSet

objRS.Open objCmd, objCon, adOpenStatic, adLockReadOnly

If objRS.RecordCount > 0 Then
checkforduplicatedamageid = True
End If

End Function


Forward-Only recordsets don't support Recordcount but static recordsets do.

Craig

 

[MickyWork]

I put that in and get this error.

Run-time error '3797'

The application cannot change the ActiveConnection property of a Recordset object with a Command object as its source

this is the line it highlights for debugging
objRS.Open objCmd, objCon, adOpenStatic, adLockReadOnly

Sorry for turning what should have been a simple answer into a programming lesson

As for turning it to false I know that would switch it round thats why I did it just to see if the command SetFocus was working, which is wasn't. So still the original problem is not solved

And can't really get a programming book and learn proper programming techniques for this one, they want it and they want it fast. Nothing I can do about it as long as it works i'm not bothered about the code been tidy at the moment. Need to keep my job.

Any help as always appreciated.

~Micky

P.S
I am actually a website designer, so design, websites and flash are my main fortes, so been put onto this project was as much of a shock to me as my programming technique is to you

 

[Craig0201]

Try

objRS.Open objCmd, , adOpenStatic, adLockReadOnly

Forgot i'd already set a connection to the Command object.....

Craig

 

[MickyWork]

Whaoooo

that works

but still no soloution on the origional problem, the focus is still passing to the next tabindex

~Micky

 

[Craig0201]

Micky,

Can you put a breakpoint in on the damageid_afterupdate and track it thru for me. Something is changing the focus that isn't in the events/functions you've listed....

Craig

 

[MickyWork]

I put a watch on all of the lines of code including the checkforduplicatedamageid() function and it steps through just as you would expect it to.

It does not go to any other functions or procedures I'll post below exactly what I have

Private Sub damageid_AfterUpdate()
If checkforduplicatedamageid = True Then
MsgBox "The damageID you entered already exists, please enter a new damageID", vbOKOnly + vbInformation
damageid.SetFocus
End If
End Sub

Function checkforduplicatedamageid() As Boolean
Dim objCon As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRS As ADODB.Recordset

Set objCon = Application.CurrentProject.Connection

Set objCmd = New ADODB.Command

With objCmd
.ActiveConnection = objCon
.CommandText = "SELECT * FROM damages where damageid = '" & damageid.Value & "'"
End With

Set objRS = New ADODB.Recordset

objRS.Open objCmd, , adOpenStatic, adLockReadOnly

If objRS.RecordCount > 0 Then
checkforduplicatedamageid = True
End If
End Function

 

[Craig0201]

Hmmmm......

Try this then....

Put this in the BeforeUpdate envent

If checkforduplicatedamageid = True Then
MsgBox "The damageID you entered already exists, please enter a new damageID", vbOKOnly + vbInformation
Cancel = True
End If

and delete the other procedure.....

This will check before it writes to the db and stop the control losing the focus in the first place.....


Craig

 

[MickyWork]

Craig

Good News

Thats works

Bit anoying that the other thing doesn't work as expected, as I'm sure I will need to use it at some later date. Oh well if it doesn't work I will have to accept it and figure a way around.

Thank you so much for all your help, and sticking with it instead of tell me to go away and stop bothering you.

Micky

Once again thank you