Open a form to search for a record using a command button which prompts input box

[RachelleB]

I'm trying to find the best way to do the following

1. From a main form (my own switchboard) press a command button to open a form
2. Before the form opens a Input Box appears asking for the ID number of the record you want to look at / edit
3. The forms opens at the record ID based on the value in the input box

My Main form is called "Master OFI Details" and the ID I want users to seach for is "OFI ID"

I'm not sure if the best way is through a query, a macro or code but I can't get any of them to work!

Thank you!

[jimatqsi]

Rachelle,
I would suggest you avoid leaving spaces in the names of your table columns. It makes extra work for you, you always have to surround references to them, as so ...[OFI ID].

Add a textbox beside your button and call the textbox id_txt.
In the command button onclick event try this:

DoCmd.OpenForm "formname", acNormal, , "OFI ID=" & Me.id_txt"

You'll have to supple the correct formname, of course.

After that works, add some validation to make sure there was an ID entered before the button was clicked.

if nz(me.id_txt,0)=0 the exit sub

Jim

Jim

[RachelleB]

Thanks Jim for the advise about the spaces. Unfortunatly the database is now too far gone for me to go back and change it.

I've previously tried the text box method as I use that code a lot to open related forms for specific records with the Master OFI details but the way my "swtichboard" (not Access switchboard, own creation) is set out I'd end up with massive amount of text boxes next to buttons and it wouldn't be particually user friendly.

Ideally, I need the command button to generate a Input Box where the user enters the ID and then takes them to the record in the form. Saves a lot of mess on the switchboard.

Rachelle

[jimatqsi]

What does the ID represent? Since they have to enter something why not open the form and let them enter the identifier there?

Jim

[RachelleB]

This part of the databse records customer complaints.

Master OFI Details is a tabbed form where the complaint is recorded and then depending on what the problem is various other actions can be done.

Once the complaint is origionally recorded (and assigned an OFI ID) users will need to go back and add more details at a later date.

Ideally, when the user goes back to add details I don't want other records showing incase the wrong record is amended.

I know it sound like I've created a monster that's quite messy but the database does so many things that is just sounds that way!

Do I have to open the form and then on the onload event of the form have an input box pop up rather than on the on click of the command button?

[Seth Schrock]

This might not be very cosmetically appealing, but it would be simple. Just take Jim's code and instead of using the Me.ID_txt, make it InputBox("Record ID:"). If you want a "prettier" version, then you would have to create your own form, set its Modal property to Yes and then have your button first open this form as a dialog box, get the ID value from it and then open your form.

[zmbd]

RachelleB

Seth has a a good option, you could even just use a parameter at the query level that would popup an input box for the user if asking for a single value.

Sounds like you've plunged in the deeper end of the VBA programing pool.

I'm with jimatqsi on the overall form design; thus, you might find the following information to be helpfull:
-filtering-

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
  
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.

[NeoPa]

I agree that filtering would work more neatly for you Rachelle. However, bearing in mind some of your comments, I would make some suggestions on how to fit it better to your purposes :

1. Typically, when the form isn't filtered, it shows all records (IE. No filter means no restriction). Change this so that when there is no filter specified then no records show.
2. If you want to give no clues as to what else may be available then make sure the filter control is a TextBox and not a ComboBox.

If you like these ideas and need help taking them further then please let us know as I'm sure any of us here would be happy to provide more detail as it pertains to your particular system.

[RachelleB]

Thanks all for your responces. I'm still a little unsure as to what to go with though.

The text box on the swtichboard then using a comand button to open the form based on the text box would be messy because I've got a least 4 other forms I need to do the same thing with plus other buttons linking to other forms, so there would be a lot of text boxes and buttons.
I've already got a tabbed switchboard because each tab will have different links on it so all the above text boxes and buttons would have to be on the same tab.

I've tried adding the below onto the on click on the command button:

DoCmd.OpenForm "[Master OFI Details]", acNormal, , "OFI ID=" & InputBox("Record ID:")

But it doesn't work, errors that it can't find the form but I c+p'd the name so it must be spelt right. Not sure if there's something I've missed?

Nore sure if this has something to do with the filtering of the form like Neopa suggested?

Sorry for asking so many questions and being so demanding!

I picked up access from scratch 3 weeks ago and have learnt A LOT in that time but there are massive gaps in my knowledge so I can't see the bigger picture of how it all works together.

Thanks all,
Rachelle

[NeoPa]

Your code will never work exactly as it is due to the space in the name. Here's a version that may :

DoCmd.OpenForm "[Master OFI Details]", acNormal, , "[OFI ID]=" & InputBox("Record ID:")

As a very basic way of doing it that should work for you (Assuming the Record ID is numeric. If not then further changes are required).
If you want something that has a less clumsy feel to it then spend some time looking through one of the linked articles on filtering. When you're ready, and this can be happily working the current way until you are, come back and post a question about how to use what it is you have learned there but with some amendments that make it work more appropriately for what you need in your current project. Take it at your own pace. There's no rush as what you have now should make it work at least.

[Seth Schrock]

If you want to continue with the inputbox method, then here is something to try.

'This code assumes [OFI ID] is a long integer number type
Dim lngID As Long
 
lngID = InputBox("Record ID:")
DoCmd.OpenForm "[Master OFI Details]", acNormal, , "[OFI ID]=" & lngID

Disclaimer: This is not a recommendation to not follow what Jim, ZMBD, and NeoPa have suggested. On the contrary, they are very experienced developers and have very good suggestions. My solution is just a method to achieve exactly what you originally requested, but there may be a better alternative.

[RachelleB]

If there's an alternative without adding text boxes onto the swtichboard form then that'd be perfect.

I'm trying to add an image to show what the switch board looks like . . at least what it would look like with the text boxes and buttons.
There are still 5+ buttons to go on yet

[NeoPa]

All three of the suggestions below :

1. Seth's first - Use an parameter in the query that causes a prompt.
2. Seth's second - Use an InputBox to get the data from the user after the switchboard has been used but before the form is opened.
3. NeoPa's - Go straight to a form which will show nothing until a TextBox on the form is filled to select the filter for the required record.

will give you the facility to use the switchboard without adding another control to it to enter the record ID.

[jimatqsi]

I'd like to give RachelleB a big hand. She picked up Access just 3 weeks ago and is plunging ahead fearlessly and apparently doing well with it.

I personally like Seth's first suggestion because it is so simple - if the need is to run the form for one single edit. I like NeoPa's idea if there will be multiple edits to make.

What we don't know anything about is the flow of information and processes throughout a day. It seems clunky to me to have to open the form from the menu every time I want to record an edit for an OFI, a Cert or a Supplier. Might it not be useful to open the form and leave it open for use for multiple entries? If so, choice C is the way to go.

Always be thinking to how you can limit clicks and mouse movement.

Jim

[RachelleB]

Thanks Jim.

I'm still struggling to get Seths code to work. It's erroring with "unable to find form [Master OFI Details].


To explain a little more about the database:

It won't be used by everyone all of the time. In its basic form it is a place to collect all customer complaints, product defects and recording certificates any suppliers have.

So everytime we have a customer complaint, the person taking that complaint can log it, deal with it (outside of the database) and then notify QAHSE dept (me). At which point I go in and close out that particuar complaint with any relvanet preventive actions and costs etc.- this is is why I need to be able to easily search for a record. Or say the person logging the OFI didn't have customer contact details at the time, they are able to go back and find that record easily to add the details.
I just need the form to open at a spesified record without making the swtichboard boxy :)

Similarlly the database can hold supplier details and if we log all product defects, we can track the number of defects per supplier.

Same deal with certtificate errors.

The databse can handle all the above with relation to quality, environmental and h&s issues.

It wouldn't happen that someone will go into the database, log an OFI and then need to edit a supplier. They're completely separate things just located in the one place for ease of use and reporting requirements.



I can't tell if I'm just making everything overly complicated because I don't know that much about Access

[Seth Schrock]

Try removing the square brackets.

[RachelleB]

Also sorry if I'm coming accross as ungrateful / awkward, that's absolutely not the case. I appreciate all the help and suggestions!

[RachelleB]

Seth - amazing that did it. Thank you. Exactly what I was after!