[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Changing Records via a combo box.

Posted on 1999-01-19
10
Medium Priority
?
272 Views
Last Modified: 2012-05-04
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.
0
Comment
Question by:Toyota
  • 5
  • 5
10 Comments
 
LVL 10

Accepted Solution

by:
brewdog earned 400 total points
ID: 1977214
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
 

Author Comment

by:Toyota
ID: 1977215
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
 
LVL 10

Expert Comment

by:brewdog
ID: 1977216
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Toyota
ID: 1977217
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
 
LVL 10

Expert Comment

by:brewdog
ID: 1977218
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
 

Author Comment

by:Toyota
ID: 1977219
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
 
LVL 10

Expert Comment

by:brewdog
ID: 1977220
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
 

Author Comment

by:Toyota
ID: 1977221
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
 
LVL 10

Expert Comment

by:brewdog
ID: 1977222
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
 

Author Comment

by:Toyota
ID: 1977223
brewdog,
I got it working...Thank you for your support!!!
Toyota
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question