Select key with command button

[juriemagic]

Hi good people!,



me again..the newbie.. . I am making very slow progress with learning Access, but I will get there. Anyways, could someone please help me with the following:. I have deleted the ID column of my database, and made the rooms (numbers), the primary keys. (It's for a hotel). Then I have a command button on a form. When the form opens it shows the primary key top left, which happens to be a room number, and the fields are populated a room inventory. Each inventory item has a box for entering a 0-2 code, depending on the status of the item. Now, I know that at the bottom of the form, I can click to go to the next record, but I sometimes want to jump 20 rooms (records), so I need to click 20 times. Please help me with showing me what I need to do to accomplish clicking the command button, have a textbox popping up, manually insert the primary key (room number) I want to jump to, and then press "okay" or "go" or whatever. The form must now display the record for the key as entered. If this can be done, please help me?...all and any help will be accepted with great humbleness and appreciation. Thank you very much..

[ranman256]

You need a unbound box in the header. TxtFind
in the afterUpdate event of the box, go right to the room.

me.filter = "[room]=" & txtFind
me.filterOn = true

[ItsMe]

Can you, manually, add 20 to the record index number displayed at the bottom of the form, where the navigation arrows are?

[juriemagic]

Okay, I have no clue what you're telling me, but I'll google on that and see how things turn out. Thanx a lot..

[juriemagic]

The first reply was for Ranman256..sorry

[juriemagic]

Hi ItsMe, No I have tried but it doesn't look like one can..unless I just don't know exactly how to do it...

[ItsMe]

Hi ItsMe, No I have tried but it doesn't look like one can..unless I just don't know exactly how to do it...

At the bottom of the form there is a built in feature called the Navigation Buttons. You mentioned you were clicking the arrows 20 times. I interpret this to mean you were using the Navigation Buttons of the form. You can hide the Navigation Buttons, using the Form's properties.

In Access 2010 there is an option to type the index of the record you want. The little field displays the current record's index within the Form's Recordset. There is also a search field there. I believe it is the same for 2013.

[juriemagic]

Yep, I can manually insert the room number there (In my case), but Access returns the row. You see, the rooms start at 101. So room 101 is on line (row) 1, in my database. If I select 101, room number 509 is displayed, because room 509 resides in row 101. Also the search functionality does not seem to work, BUT, I anyways do not really want to jump to the next wanted key by selecting at the bottom as it seems to me a little "tacky". I would really like a command button that will give me the option to either manually insert the room number I want, or give a drop down list of available rooms, so I can select one.

[ItsMe]

Yup, I typically hide the Navigation Buttons and use VBA for navigation. If you do not want to use VBA, like ranman mentioned, you can use the Old School search function via a keyboard shortcut.

The trick to using the intrinsic search function is to set focus in the desired field to search, prior to employing the intrinsic search function. Ctrl + F will produce the old search function/option.

To create your own search, you will need to drop an unbound Text Box Control onto your form. Then, create code behind the form, like previously mentioned.

[juriemagic]

Well, I have in the meantime stumbled upon the List box. I inserted that, followed the little prompts and it's doing just what I wanted. Thank you for your time anyways, I really appreciate your efforts..