Changing Records via a combo box.

I am creating a Access database that keeps tracks of our tools. I have created the table that has all of the current tools in it.  Built on top of that table I have a form that allows the user to add a new tool, delete an old tool or check out a tool to a technician.  The problem is that to find a particular tool you have to go record by record until you find the tool that you want.  I thought that it would be easy to create a combo box that would list all the tools in the table and by selecting that particular tool it would change to that record so you could check it out, check it in, or what ever.  I have the combo box created but when you select the description it changes the description of the current record.  Any ideas on how I can accomplish this task?

Thank you in advance.
ToyotaAsked:
Who is Participating?
 
brewdogConnect With a Mentor Commented:
Yes, you'll need to do a couple things:

1. Make sure that your combo box has no ControlSource -- that means it will be directly updating the table (what it's doing now). Instead, you'll want one for just a "lookup"

2. On the AfterUpdate event of the combo box, do this:
        [ToolTextBox].setfocus
        doCmd.FindRecord [LookupComboBoxName], etc., etc.

You'll want to check out the different options of the FindRecord argument to see exactly what you want. That should work fine.

brewdog
0
 
ToyotaAuthor Commented:
brewdog,

I'm not real up on the whole programming in code thing.  Can you be a little bit more descriptive on what you want me to do.  I guess I don't uderstand if I'm creating a new combo box or just updating the one I have.  And I'm not exactly sure how to put the stuff in code.

Thank you again.
0
 
brewdogCommented:
Sure. Here's what I'm envisioning:

You have a text box that simply displays the Tool -- just like the rest of the fields. You have a combo box, maybe at the top or bottom of the screen with the label "Search for" or something next to it, so users know how to use it.

Open the Properties sheet for the new combo box (you can right-click on it to get there). Scroll down until you see AfterUpdate. Click in that line, and you'll see a drop-down arrow. Choose [Event Procedure], and then click the ... button next to the drop-down arrow. That will take you into the code window. Then type or paste the code I posted before. Obviously, you'll want to make sure the "LookupComboBoxName" is changed (unless you like that name) -- it's just the name of the combo box.

Does that help more?

brewdog
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ToyotaAuthor Commented:
brewdog,

I tried what you said and I get an error that reads:
The expression after update you entered as the event property setting produced an error: Compile Error.

Any ideas what I did wrong?  Thanks!!!
0
 
brewdogCommented:
In your code window, go to the Debug menu and choose Compile and Save All Modules. Which line gets highlighted? Have you checked to make sure that the names of the text box and combo box in design view of your form are the same as the names you're referencing in your code?
0
 
ToyotaAuthor Commented:
brewdog,

This is how the code reads:
[Tool Description].SetFocus
            DoCmd.FindRecord [Lookup]

I am running Access 95 so under the "Run" menu I select compile all modules.  It highlights "[Tool Description]" and gives an error box that says "Invalid Outside Procedure".  I have checked all the names and they are all okay, ie. Tool Description is the name of the Text Box and Lookup is the name of the Combo Box.

I also noticed that the Object Name at the top of the code window changed to [General] and the Proc. changed to [declerations].

Again I know nothing about Code so I don't know anything about what we just did.  But I believe I am doing what you say.  Thanks again for the ideas!!!

Toyota
0
 
brewdogCommented:
somehow, it sounds like we got the code in the general declaration section of the form -- kind of like the Foreword of a book. Cut those two lines of code. Then, from the first drop-down list (where it says "General"), choose the name of your combo box ("Lookup"). the second drop-down list will probably switch to BeforeUpdate. Instead, choose "AfterUpdate". Then paste your code and try to compile again.
0
 
ToyotaAuthor Commented:
brewdog,

I cut the code out of the "General Declerations" section of the form and made sure it was still in the "After Update" section of the "Lookup" field and then tried the form again and got the same error.  So, I went back into the Code Builder and told it to compile all modules and it highlights ".SetFocus" and gives an error that says "Method or Data Member Not Found."  Any ideas?

Thank you one more time, you have been a huge help!!!

Toyota
0
 
brewdogCommented:
I'm not sure if you're still working on this or not, since you accepted my answer. It sounds like the last problem was that Access didn't recognize the name of your Tool Description text box. Make sure that is really what it is called. If the name is right, when you hit the period at the end of the name, Access should pop up a list of appropriate things you can do with it -- setfocus should be one.
0
 
ToyotaAuthor Commented:
brewdog,
I got it working...Thank you for your support!!!
Toyota
0
All Courses

From novice to tech pro — start learning today.