Navigation Buttons

[RuralGuy]

Your certainly welcome and I'll stay tuned and watch what happens.

 

[ghudson]

I just changed my resolution to fit your form. My everything is so small. What size monitors do you use at work?

Why can't you place the "static" parts of the frmTONumber form in the form header section of the frmTOItems form? That would keep everything within the same form. The static parts can be unbound so that they do not change with the current record. You can then link the static Employee value to another text box within the frmTOItems form. Using the same theory you can link any of the static parts to the bounds fields in your form. To create your record source you need to build the forms record source as a SQL within your form. Currently your form is directly linked to your table is that is a not advised and another reason you are having a difficult time getting your form to work.

Since I do not know exactly what you are trying to do I am only guessing but I think my method will simplify your form and solve your navigation problems. The look of your form is very well done and I will give you an A- for the appearance of your form. It is so refreshing to see somebody else use the Arial font. Although not all of your objects are in Arial for consistency.

 

[jrjr]

Thanks gh for the assistance in getting the secured mdb open. I would still be hacking around. I happen to have the runtime package for ac2003 on my system and the mdb opens just fine with your shortcut. Of course I can't exit to anything but Windows since it is a RunTime package. My full ac2002 does *not* like very well. It opens for a second and then wants to send Bill Gates a failure report. It probably has something to do with the fact that the mdb was secured with ac2003 and it throws ac2002 for a loop. I'm giving up for now. If I continue to assist on this thread it will have to be with my psychic ability to see the code.

Sorry Sara, but I'll keep watching and pitch when I can.

Happy Monday morning!

Just for confirmation, I am using A02 as well. Even with the correct info in the shortcut the DB still does the same for me as RG. I do not have the 03 runtime either.

 

[sara82]

jrjr and RuralGuy the database did the same for me when I attempted to open it from home and I have A02 at home. Try this and let me know if it works:

Go to TOOLS > OPTIONS
Click on the GENERAL tab
Uncheck TRACK NAME AUTOCORRECT INFO
Uncheck PERFORM NAME AUTOCORRECT

Go to FILE > GET EXTERNAL DATA > IMPORT
Import my database.

That is how I got mine to work. Let me know if it works with you.

 

[sara82]

This database is really stressing me out. I worked on it all Friday morning and night. I was going to try to work on it the weekend as well but I had to spend some time away from it to regain sanity.

ghudson: I'm not to experienced with access so I didn't know it was the preferred method to create unbound fields. I will try to work on your suggestion since anything I do to try to correct the navigation buttons on that subform just won't work. I may have gotten it to work finally, but the label reads 1 of 1 when it loads just like on the main form.

I don't know how successful I will be in trying to create what you explained in your post but I'll try.

 

[ghudson]

Here is an updated version of my Better Mouse Trap sample. It is basically the same except I have added navigation buttons to the main form and the subform. For simplicity I did not create a relationship between the records in the subform and the current record in the main form. I have been using this basic code with my navigation buttons for years and I have never had a complaint that the first record button or last record button is not disabled when the user is on the last or first record. Hopefully this will show you how simplicity is usually the better way to go when coding an application.

 

[jrjr]

GHudson, nice job on the mousetrap

Question though. Did you use A03 for this? Opening with 2000 gave me the need to close error. Opening with XP would not allow me to edit anything. I was able to convert to both 2000 and XP versions with my XP install though and they work ok. I was just curious......

 

[RuralGuy]

jrjr, it looks like a97 to me!

 

[jrjr]

Duh yea, thats the only one I didnt try....

 

[ghudson]

There was something funky with that file. It was in Access 97 but had the properties of Access 2003 for the buttons were rounded when I opended it in Access 2003. I have replaced the zip file in my previous post with a pure Access 97 version. That way more users can open and use the file.

 

[jrjr]

I thought it was odd that it did not demand to convert on opening.

 

[sara82]

ghudson:

Thanks for that sample. I like it very much. In fact I'm using it now on my main form and on the subform. Thanks I appreciate it. It is great for error trapping.

One quick question, would it be possible to have a text box where the user inputs the record number, and upon hitting enter it'll take you to that record? The reason I ask is because this is going to be a fairly large database with 5,000+ records in it. And *just in case* the user wants to browse through the records that way it would be available.

What would you advise?

 

[ghudson]

Record numbers would [should] be useless to a user. Instead you should offer the user a search combo box where the combo box is filled with all the unique values from a table [like an employee number, vendor name or something of significance]. Using the AfterUpdate event of the combo box you would then jump to the first record that matched the search criteria based on the users selection in the combo box. When setup correctly the user can begin typing a value in the search combo box and the values that match what the user has typed so far will auto populate in the search combo box.

Here is the SQL of a combo box which will only display the unique "Vendors" [SELECT DISTINCT] in the table...

SELECT DISTINCT tVendors.VendorName, tVendors.VendorNumber
FROM tVendors
ORDER BY tVendors.VendorName;

Here is the code in the AfterUpdate event of the cbSearch combo box...

Private Sub cbSearch_AfterUpdate()
On Error GoTo cbSearch_AfterUpdate_Err

    Me.tbHidden.SetFocus
        
    If Me.Dirty Then
        Beep
        MsgBox "Please Save This Record!" & vbCrLf & vbLf & "You can not advance to another record until you either 'Save' the changes made to this record or 'Undo' your changes.", vbExclamation, "Save Required"
        Exit Sub
    Else
        'MsgBox "[VendorName] = " & "'" & Me![cbSearch] & "'" 'used just for testing
        Me.RecordsetClone.FindFirst "[VendorName] = " & "'" & Me![cbSearch] & "'"
        Me.Bookmark = Me.RecordsetClone.Bookmark
        Beep
    End If

cbSearch_AfterUpdate_Exit:
    Exit Sub

cbSearch_AfterUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume cbSearch_AfterUpdate_Exit
    
End Sub

Notice that the Me![cbSearch] search value is enclosed in single quotes because the search is for a text value.

While testing your search 'value' you should test what is being used with a message box.
MsgBox "[VendorName] = " & "'" & Me![cbSearch] & "'"

Search around for I am sure that there are other ways to code a search function but this is the only one I had available to post.

Good luck!

 

[ghudson]

Four ways to move to a record from a Combo Box selection

FindFirst, FindLast, FindNext, FindPrevious Methods Example (MDB)

NoMatch Property Example (MDB)

OpenArgs Property Example (Microsoft Office 2000)

 

[sara82]

ghudson what is the significance of tbhidden? Trying to understand how that comes into play.

Also thank you for the combo box example I tried it out and it worked on my form. Thanks again.

Something that I noticed is that when I have more than one record on the subform, it says Record 1 of 1 but when I click on the next it goes on to say Record 2 of 3 etc. That may be a little deceiving when a user first takes a look at that. Is that something that can be fixed have a refresh or something so that it will depict the correct number of records?

 

[ghudson]

I use my custom tbHidden text box the throw the focus back to the tbHidden text box at the beginning of most of my action events. Mostly for visual reasons but it also prevents a user from firing an OnClick event if a control still has the focus and they hit the Enter key or accidently removing the contents of a text box or changing the value of a combo box if it has the focus and the users scrolls with their mouse wheel. It is placed in the upper left corner of my mouse trap sample [it is very very small].

You need to add this code to the OnOpen event of your subform to get your Record X of Y to display the correct 1 of X totals...

RecordsetClone.MoveLast
RecordsetClone.MoveFirst

Look closer at the events and code I am using through out my mouse trap sample.

 

[sara82]

ghudson:

That code is already present in the subform OnOpen event.

Main form:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    
    RecordsetClone.MoveLast
    RecordsetClone.MoveFirst

'    MsgBox "InsideHeight = " & InsideHeight & vbCrLf & vbLf & "InsideWidth = " & InsideWidth
    InsideHeight = 4000
    InsideWidth = 5790

Exit_Form_Open:
    Exit Sub
    
Err_Form_Open:
    MsgBox Err.Number, Err.Description
    Resume Exit_Form_Open

End Sub

Subform:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
    
    RecordsetClone.MoveLast
    RecordsetClone.MoveFirst

Exit_Form_Open:
    Exit Sub
    
Err_Form_Open:
    MsgBox Err.Number, Err.Description
    Resume Exit_Form_Open

End Sub

Yet I still get the 1 of 1 in the label.

 

[RuralGuy]

I think the Open event is too early in the process. Try the same code in the Load event.

 

[sara82]

Thank you RG for the suggestion. Unfortunately that didn't solve the problem.

When the form loads it does at first show the record number of records 1 of x but then when I click the next button or the previous button it goes back to saying 1 of 1

 

[ghudson]

That is odd because my MouseTrap_NavigationButtons.zip sample I posted above does not have that problem. Are you sure that you have everything in the right place? Do you have the right code in the OnCurrent event of each form and subform?