Record search bar at bottom of form (1 Viewer)

[jpl458]

I created a data entry form from a table. When I first created the form and added some data, I could scroll through that dat using the record bar at the bottom of the form, which looks like this:

After making cosmetic changes that bar no longer allows me to cruise through the data one record at a time, and each time I hit either next or previous it creates a blank record in the table. I had the form set to

DoCmd.GoToRecord record:=acLast

, so when the user opened the form they were ready to enter data. Since they they to change data in a record I would like to have the < > work. I am missing something simple, but can't find it.

Thanks

 

[June7]

For me, acLast puts focus on last existing record, not new record row.

As I suggested in response to your Stackoverflow question, might provide your db for analysis. Follow instructions at bottom of my post.

 

[theDBguy]

Just curious, did you happen to change the setting for the Form's Data Entry property? What does it say now?

 

[June7]

Apparently that's exactly what OP did as indicated in their Stackoverflow posting. https://stackoverflow.com/questions/73628902/acces-data-entry-scrolling-buttons

 

[jpl458]

Data Entry is set to Yes. Maybe I'm expecting too much from this form. After data enter, the users may want to make changes to records in the table. I was thinking that they could use the same form for both purposes. Early on I tried to make changes via the data entry form and it worked, now I can't even see entered records. Do I need to have a different form for changes? A form that is not data entry but has the same record source and controls to move through the table, and be able to search for items in the table. Or am I carrying coals to Newcastle to have a separate form.

 

[theDBguy]

Apparently that's exactly what OP did as indicated in their Stackoverflow posting. https://stackoverflow.com/questions/73628902/acces-data-entry-scrolling-buttons

Mystery solved!

 

[theDBguy]

Data Entry is set to Yes. Maybe I'm expecting too much from this form. After data enter, the users may want to make changes to records in the table. I was thinking that they could use the same form for both purposes. Early on I tried to make changes via the data entry form and it worked, now I can't even see entered records. Do I need to have a different form for changes? A form that is not data entry but has the same record source and controls to move through the table, and be able to search for items in the table. Or am I carrying coals to Newcastle to have a separate form.

As @June7 suggested, use acNewRec instead of acLast in your code.

 

[jpl458]

Mystery solved!

As you can tell, I'm no expert at this, but it seem to me that the way you can change properties, and pretty much everything else, that you should be able to change the form to display and switch back to entry. I'm sure they don't do that because of other considerations. I'll just creat a new form. Thanks for a the help.

 

[MajP]

that you should be able to change the form to display and switch back to entry.

Maybe we did not understand you, since you definitely can switch between data entry view and all record view. To demo put a button on any form and in the click event put.

Me.DataEntry = Not Me.DataEntry

This will toggle the form back and forth between data entry and all record view. Can you explain exactly how you would like it to work?

 

[jpl458]

Just thought it over, and talked to a possible user, who said she would rather stay on the same screen and hit buttons to enter data or to search and make changes. That will require some more vba code, but there only be one screen to deal with for the users. I don't know what is the acceptable way of designing this, what is standard? Or is it freedom to do it the way you want because there is no standard.

 

[Gasman]

As you can tell, I'm no expert at this, but it seem to me that the way you can change properties, and pretty much everything else, that you should be able to change the form to display and switch back to entry. I'm sure they don't do that because of other considerations. I'll just creat a new form. Thanks for a the help.

I never bothered with dataentry = yes, I just used the * button at the bottom of the form.
That is a very badly named property IMO as all you can do is ADD new records. Never what newbies think it does.

 

[theDBguy]

As you can tell, I'm no expert at this, but it seem to me that the way you can change properties, and pretty much everything else, that you should be able to change the form to display and switch back to entry. I'm sure they don't do that because of other considerations. I'll just creat a new form. Thanks for a the help.

Did you see my last post? (Post #7)

 

[jpl458]

Maybe we did not understand you, since you definitely can switch between data entry view and all record view. To demo put a button on any form and in the click event put.

Me.DataEntry = Not Me.DataEntry

This will toggle the form back and forth between data entry and all record view. Can you explain exactly how you would like it to work?

Pretty much the way you described it. But, don't you need 2 lines of code to toggle back and forth. I have noticed that when you set the data entry property to NO the property no longer is in the list., does your snippet of code actually toggle back and forth? I understand going from data entry to not data entry, but don't see how it would toggle back if you hit the button again. Could you briefly explain that, eager to learn.

 

[MajP]

The data entry property is a true/false value.
The above could have been written as

If me.dataEntry = true then
  me.dataEntry = false
end if
If me.DataEntry = false then
  me.dataEntry = true
end if

Assume the data entry is True then the expression
Me.DataEntry = Not Me.DataEntry
would resolve to
Me.DataEntry = Not (True)
which resolves to
Me.DataEntry = False

If DataEntry is false then
Me.DataEntry = not me.DataEntry
resolves to
Me.dataEntry = Not (False)
resolves to
Me.dataEntry = True

These toggles can work with all boolean properties
Me.TxtBoxAbsenceReason.visible = me.absent
So if a person is checked absent then the absence reason text box shows visible, hides if not absent.

 

[jpl458]

Maybe we did not understand you, since you definitely can switch between data entry view and all record view. To demo put a button on any form and in the click event put.

Me.DataEntry = Not Me.DataEntry

This will toggle the form back and forth between data entry and all record view. Can you explain exactly how you would like it to work?

Just tried it and it works. Really cool, but I don't understand how the code works both ways. Have to change the label on the button to indicate what mode it's in. Thanks.......

 

[Pat Hartman]

This is more relevant when your BE is SQL Server but since it works regardless of what your BE is, I use ONE method always. Less brain strain for me.

Most forms have only one or two search options so I add text boxes or combos to the form's header and if the options can be used in combination, I also add a button to requery the form. Otherwise, if the search fields/combos are independent, I requery the form in the afterUpdate event of the search field.

to make this work, I base ALL forms on a query. The query includes Order By criteria so if multiple records are returned, they are in a logical order rather than random. The query has a WHERE clause that references the search field on the header.

Select ...
From ..
Where MyID = Forms!myform!cboMyID;

When the form initially opens, there is no value in the combo so the form opens to a new record. Then if the user wants to go to a specific record, he picks a value from the combo or types it into the search field and hits tab. The only line of code is:

Me.Requery

So, the process is very simple. A query with criteria, a control on the form to search with, and a single line of code

The reason this is more relevant when your BE is ODBC is because it allows the server to do the heavy lifting. No user is going to need to see thousands of rows in a form so there is no value in binding a form to a table or even to an unfiltered query. Either the user wants to enter a new record or he wants to go to a specific record, my suggestion handles both in the most efficient way. You don't download rows that will never be looked at. The old Access style method where the BE is Jet or ACE, assumes the form is bound to a table and then filtered locally. That's not terrible when the BE is Jet/ACE because they are not server side databases. All processing happens on your PC anyway. But when your data is on a server, you want the server to do the selection and then return to you only ONE record whenever possible.

Occasionally, you have situations where you have complex search criteria, sometimes even for the same form. In this case, I create a separate form with a dozen or more options for selecting data. The user picks what he wants and the code creates a custom where clause. It does a dcount() to determine how many rows will be returned. If the answer is 1, it uses the criteria to open the regular single record edit form. If no records satisfy the criteria, I give the user a message to that effect. Otherwise, I open an intermediate datasheet view form with the most significant columns showing. Then the user double clicks on the record he wants to work with and that form opens the regular single record edit form. So, in all cases, a single form is used to add/edit a record but other forms might be used in the drill down process. It is poor practice and even downright dangerous to create separate forms to update the same table. When requirements change, you have to make the same change, with the same effect to all the "duplicate" data entry forms.

Just FYI, this simple efficiency for all forms means that I can almost always convert applications that I built from Jet/ACE to SQL Server in a few hours and that is mostly because I have to test everything just to make sure I didn't miss something. If you use DAO code, you also have to be cognizant of the differences with SQL Server. Since the SQL Server method also works for Jet/ACE, I always use the SQL Server method. In all the years I've been doing this, I have found very last minute changes that cropped up.