Next Record and EOF (1 Viewer)

[PRD]

Hello, I’m sure this must be a common problem but I can’t seem to find a simple solution…


I created a Form to display the record details of a Query. I have four command buttons (First Record, Next Record, Prev Record, Last Record) to scroll through the records. The problem is when the EOF is reached and the user clicks the Next Record button a blank record appears and if they click the Next Record button a second time the program goes into Debug. And so my questions are…


1. How can I detect the EOF condition?


2. How can I disable (or hide) the Next Record command button so it won’t go into Debug if it is clicked again?


Thank you for your time.

 

[missinglinq]

Here's how I handle it:

Private Sub Next_Click()
  If CurrentRecord = RecordsetClone.RecordCount Then
    MsgBox "You are on the Last Record!"
  Else
    DoCmd.GoToRecord , , acNext
  End If
End Sub

You'll need to handle the same kind of thing on the first record so that the user doesn't hit the 'previous' button and pop an error:

Private Sub Previous_Click()
  If CurrentRecord = 1 Then
    MsgBox "You are on the First Record!"
  Else
   DoCmd.GoToRecord , , acPrevious
  End If
End Sub

Linq ;0)>

 

[PRD]

missinglinq -

Great, thanks, can't wait to try it on Monday.

Is there a way though to actually hide a command button on a Form (make the "Visible" property = "No")? I have a Form which allows users to Add, Change and Delete records but I want to limit these actions depending on the user. It would be nice if I could use the same Form for all users and just hide the command buttons I don't want them to have access to. Just curious. Thanks again.

 

[r.harrison]

It would depend on how your users log on, If the Username / UserLevel are stored as variable or actually visible on the form then you could use the OnCurrent event for the form:

If UserLevel = "Admin" Then
        Cmd_Add.Visible = True
        Cmd_Delete.Visible = True
    Else
        Cmd_Add.Visible = False
        Cmd_Delete.Visible = False
    End If

Or something similar.

 

[missinglinq]

As r.harrison said, one way would be to conditionally set the Visibilty of the Command Buttons, depending on the logon IDs, assuming that you actually have your users log on. If the Form will always be closed brtween users, you could place his code in the Form_Load event, rather than in the Form_Current; both will work, but the comparison would only be done once using Form_Load, rather than every time a different Record is accessed.

Another way would be to

1. Set the buttons Visible = False, thru the Properties Pane for the buttons
2. Have a Textbox for entering a Password
3. If the Password is correct, set Visible = True for the buttons

From that point until the Form is closed, the buttons would be Visible.

Linq ;0)>

 

[PRD]

Great tips, thx! I will certainly give them a try and let you know what I find.

 

[PRD]

Thanks again for all of your suggestions - they worked perfectly! I even figured that there must be a 'Locked' keyword and was able to lock all of the fields on my Record Detail Form so users with Read-only access could view the data. This saved me from having to create a separate Form for Read-only users.

This Forum is by far the most useful forum I have ever used. The moderators are professional, prompt with their responses, give detailed solutions and most importantly the tips actually work. Thanks again for your input, it is really appreciated.

 

[gemma-the-husky]

i suspect a lot of people dont have to deal with navigation issues, because they accept the standard navigation buttons, instead of trying to add their own.

why do you need your own? is it an unbound form?

 

[Galaxiom]

I add my own navigation buttons because those provided in Access are part of the rather poxy navigation bar, which I generally hide. Instead of the tiny little buttons I much prefer to provide large buttons with obvious, meanful captions.

The Search box in that bar is also not particularly useful and much better functionality can be provided with a custom search box.

 

[PRD]

gemma-the-husky -

I like to use my own navigation buttons because I often do more than just go to the Next or Previous record. For example, the user can select up to five plant names each with its own field/dropdown box. Before I save the record I concatenate all five names into a sixth field. This way if I want to search for a particular plant name I search the sixth field instead of the five fields individually. I may also want to clear some fields before moving on to the next record. So creating my own navigation buttons gives me more control and flexibility.

 

[Galaxiom]

Before I save the record I concatenate all five names into a sixth field. This way if I want to search for a particular plant name I search the sixth field instead of the five fields individually.

That is a breach of normalization.

 

[PRD]

Galaxiom -

Not sure what that means.

Actually I capture a total of 20 fields of information on my Detail Record form (including the five plant names) and I created a 21st field where I concatenate all 20 fields into this one 'super' field. Bob Larson showed me how to do a string search on any field and so on my Main Menu I have an option to search the entire database for any string (by searching this super field). I can even search for two strings using an 'AND' search to narrow down the results. It works great. I realize that the superfield doubles the size of the Table but the advantages of the string search seem well worth it (I only have 8,000 records in this Table).

 

[spikepl]

Galaxiom has a point - there is no point in concatenating data in the Table when you can do it on the fly in a query and use that query as the record source for your form (or was it subform)?

 

[PRD]

spikepl -

As I am new to the world of Access I tend to do things the 'brute force' way. If you can show me a more elegant way to search my database I would certainly be interested in what such a query would look like. Thx.

 

[spikepl]

You make a query taht grabs all the fields form your table. And then you add one calculated field:

MyField: [Fieldname1] + [FieldName2] ....

where FieldName1 is the name of the firstextt field in your table and so on. An MyFiled is should be the same name as you are using in your search routine.

Then in the form that currently has the table as record source (See Form's Properties-Data->Record Source) you now select the query instead of the table. Done.

 

[PRD]

spikepl -

That certainly is elegant alright. I will give it a try. Thx.

 

[PRD]

spikepl -

Sorry for not responding sooner. I could not get this query to work until I changed the plus '+' operator to the concatenate '&' operator....

MyField: [Fieldname1] & [FieldName2] & ...

Thanks for the tip, this is much cleaner and will save me some diskspace to boot.