Solved

Few Basic Questions

Posted on 2002-04-23
21
194 Views
Last Modified: 2010-05-02
Hi Experts - Just a few basic things Im sure someone on here could handle - Im just looking for someone to check out my project, and place any code on here.

Questions :

1. On my toolbar, I have two drop down menus, I need to use the hotkeys with this menu items as you would do with a normal menu - How do I do this ?

2. This is a VB Frontend to a database, what I now need help with is a little bit of error trapping.

When you go to the last record, and then select next, it comes up with blank fields - What I need is for it to get to the end, and then just STOP - Hence allowing further navigating and or other functions.

3. When you delete a record, while it works, the data is still visible on screen. After this, you are not able to then continuing searching and or making changes - How do I fix this ?

4. How can I confirm all fields have been filled in ?


----

If you could please comment anything you add here that would be great ! You can download my project here : http://www.hypermax.net.au/~user/hollstar/paper.zip
0
Comment
Question by:hollstar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
  • 2
  • +2
21 Comments
 
LVL 8

Expert Comment

by:qfren
ID: 6962209
the hotkeys..i think at the menu editor that u create menus..u can find a combobox which ask u to inset the shortcut...
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6962232
1: Adding hotkeys is a bit tricky as these are button menus, if you set the form's keypreview property to True and add the following code (you will need to extend it for all the hotkeys).

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If Shift <> 4 Then
        Exit Sub
    Else
        Select Case Chr(KeyCode)
        Case "A", "a"
            tlbToolbarMain_ButtonMenuClick tlbToolbarMain.Buttons(1).ButtonMenus("edtAdd")
        End Select
    End If
End Sub

2:

    Case "fndNext"
        If Not mrstContact.Eof Then
          mrstContact.MoveNext
        Else
          Msgbox "At End Of File"
        End If
       
    Case "fndPrevious"
        If Not mrstContact.Bof Then
          mrstContact.MovePrevious
        Else
          Msgbox "At Beginning of file"
        End If

3: After the .Delete method, just reposition to either the next record or optionally the first/last using mrstContact.MoveNext

4: If you use the .Tag property to store a default value when you add / view the record you can use this to compare against the value of each field when you attempt to save it. If it has changed then obviously the field has been filled in, if not then it hasn't and you can handle that as appropriate.
0
 

Author Comment

by:hollstar
ID: 6962283
Your answer to 2 does not work as I still cant then get back to the other records - But I understand what your saying.

3. Same deal here as above...

Still looking into number 1...
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 43

Expert Comment

by:TimCottee
ID: 6962339
Ok, change 2 to this:

On Error Resume Next
mrstContact.MoveNext
If mrstContact.Eof Then
  mrstContact.MoveLast
End If
On Error Goto 0

And similar for the movefirst.
0
 

Author Comment

by:hollstar
ID: 6962359
Still no good mate !
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6962361
We will get there:

    Case "fndNext"
        On Error Resume Next
        If mrstContact.AbsolutePosition = mrstContact.RecordCount - 1 Then
            mrstContact.MoveLast
        Else
            mrstContact.MoveNext
        End If
        On Error GoTo 0
       
    Case "fndPrevious"
        On Error Resume Next
        If mrstContact.AbsolutePosition = 0 Then
            mrstContact.MoveFirst
        Else
            mrstContact.MovePrevious
        End If
        On Error GoTo 0

This works with your code as it stands.

As for the other problems, they basically stem from the fact that you are using data bound controls, these are inherently a PIA and it is generally accepted that you should avoid them where possible. You can probably eliminate 99.9% of your problems by eliminating the data bound textboxes on the form. When you change records, populate the textboxes explicitly and then populate the fields in your table explicitly when you save them.
0
 

Author Comment

by:hollstar
ID: 6962402
Yeah Im a newbie and the assignment requests I do it like this... So I have no choice, but understand your point.

I have to duck out for about 2 hours or so and we return to sus out your answer to question 1. Hope that is OK !
0
 

Author Comment

by:hollstar
ID: 6962736
What is it for CTRL and not ALT ?
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6962803
Ctrl is 2 instead of 4
0
 

Author Comment

by:hollstar
ID: 6962822
If you delete a record, you cant surf around - Any ideas?
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6962862
echo Tim on the PIA comment.

However, bound controls are not too difficult to deal with if you understand how the recordset (re: data control) they are bound to works.  I haven't worked much with ADO Data controls, so, if you are, some of my comments may be out-dated, as mostly I worked with DAO data controls.

If you delete a record, your recordset will still have a  pointer to that deleted record, and you'll still have the data on your screen. First, you should always try to move to the next record after a delete.  So, my code might have been:

data1.Recordset.Delete
data1.Recordset.MoveNext

However, after moving next, you may have hit the end of the recordset.  So, typically, I'd  have a cmdMoveNext with code like this:

if not data1.Recordset.EOF then
   data1.Recordset.MoveNext
   if data1.Recordset.EOF then
      data1.Recordset.MoveLast
   End if
End if

If data1.Recordset.EOF then
   cmdMoveNext.Enabled = False
End if

If data1.Recordset.BOF then
   cmdMovePrev.Enabled = False
End if

This covers all cases of moving to the next record.  So, after deleting, you can simply say:

data1.recordset.delete
cmdMoveNext_Click

And even if you delete the last record in the recordset everything will be fine.

Now, about the empty record at the end of the recordset.  There may be a property you can set on the data control that specifies what the default action should be when it reaches the EOF.  I think that the default is to AddNew.  If you can, you should change the default for this (I know I've seen that somewhere, but I can't think specifically where).  If you can't find out anything about that, then I think that the data control fires a Validate or Validation event whenever any repositioning of the recordset occurs.  And, one of the parameters that the system passes to this Validate/Validation event is an Action flag (I think that is what it was called), and this flag will tell you what happened to cause this event.  If the Action was an AddNew (there will be a special code assigned to an AddNew action), then you can set the Cancel parameter to True, and this will cancel the AddNew, which should just leave you on the last record of the recordset.

You can confirm that all fields have been filled in in the same place.  In the Validate/Validation event, you can Cancel the action if not all of the textboxes have been filled in.
0
 

Author Comment

by:hollstar
ID: 6962894
Yeppa - Sounds good guys - Just played with the delete, while it does delete, and does keep the data on screen like I tested before and you confirmed, it will not (for me) move to another record - I have to restart the app.




Will not be able to check this for another 8 hours or so from time of post.
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 50 total points
ID: 6962907
mdougan, actually this is DAO (which is why I have been a bit slow as I am far more familiar with ADO). Anyway the problem can be resolved as follows:

Private Sub datContact_Reposition()
' ******************************
' Contact Main Database Function
' Code Start :
' ******************************
    Static pblnFirst As Boolean
'    If pblnFirst = False Then
        Set mrstContact = datContact.Recordset  ' Config variable for use in wider program.
        pblnFirst = True
'    End If
' ********
' Code End
' ********
End Sub

    On Error GoTo mnuEditDelete_Error
    Me.datContact.UpdateControls
    mrstContact.Delete
    datContact.Refresh
    mrstContact.MoveLast

The movelast / movenext is up to you.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6962908
Sleep well hollstar!
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6963417
Tim's got another good point.  Generally they suggest that you refresh a data control if you delete a record.  As I said, this is because the recordset still has a pointer to the deleted record, and every time you navigate over it by clicking next or back you will hit the record and probably generate a runtime error.  That's true, by the way, of records deleted by other users connected to the same database too.  So, it's always good to have error handling in each of your move procedures.  Hitting a deleted record throws a particular error number which you can trap and when you hit that, just refresh the data control's recordset.

I'm not sure why Tim's doing the UpdateControls before the delete, actually, I think that you could do a data1.UpdateControls after the .Delete and/or after the .MoveNext to solve the problem of not seeing the next records values in the textboxes. You might try that.

When you say that you have to restart the app, that sounds like you're not handling the error properly, and possibly you are getting into a loop in your error handler?  Step through the code while doing a delete and let us know what instruction you hang on.
0
 

Expert Comment

by:jhamlett
ID: 6963725
1. On my toolbar, I have two drop down menus, I need to use the hotkeys with this menu items as you
would do with a normal menu - How do I do this ?

on the form that was the menu, goto menu editor, then in the "caption" field, put a "&" before the letter that you want to be a hotkey.

i.e "caption = &File" will result in the word "File" appearing with the "F" underlined, and "Alt-F" will activate it.

Hope this helps
0
 

Author Comment

by:hollstar
ID: 6964269
Tim - You sure that is correct code ? Do I have to change something ?


jhamlett - I have done that, but for some rerason, it did not work hence I used Tims answer !
0
 

Author Comment

by:hollstar
ID: 6964299
Also NOW, when you go to the next record when you first start the app, it goes to the end record !!!!!!!
0
 

Author Comment

by:hollstar
ID: 6968105
More problems than answers lol ! :)
0
 

Author Comment

by:hollstar
ID: 6990111
First of all thank you all for responding to my question. Inregard to the delete issue, I did a lot of testing and found that it was a data control issue and not that of code as nothing I attempted, worked.

I will accept TimCottes answer however, should anyone feel they should get points, please let me know.

Hollstar
0
 

Author Comment

by:hollstar
ID: 6990116
Not your fault the dumb ass data control doesnt work ! ;)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question