Can’t get lookup to work right (Access 2003)

Topic

New Reply

It’s very late at night and I’m at my wits end. I’m trying something very simple I’m sure, but I can’t get it to work quite right.

I have a database that I enter websites into, categorize them, then select topics that are covered on the website, describe the topic, and rate the topic. A website is limited to one category. You can select as many topics as you want, but you can only use a topic once per website.

Anyway, I’m starting to design a form (frmSite Data Entry) that has the SiteName, SiteURL, and CategoryNumber (this is all from tblSite). On this form I want to be able to type in my site name and URL, then have a combo box that lists the categories I have to choose from. The combo box looks up its information from the tblCategory which has fields: CategoryNumber, CategoryName, and CategoryDefinition. Now this part works. I can see the list of my Category Names. But what I also want is for the form (frmSite Data Entry) to show the CategoryDefinition on it when I select the CategoryName. I just can’t get it to do that.

I feel like such a dunce. It’s probably something really simple.

Reply | Quote

Replies

many ways to do this. Is this a main form subform? IF not make an unbound text box, put the description as a colum in the combo box, you can set it swidth to Zero and the after update f the combo to put that colum in the textbox

Reply | Quote

Zave gave you the basic idea, but you don’t even need code for this. Set the following properties for the combo box:
Column Count: 3
Column Widths: 0″;1″;0″
Row Source: either tblCategory or a query based on tblCategory returning all three fields.
Say that you name the combo box cboCategory.
Put a text box on the form with Control Source

=[cboCategory].[Column](2)

(The columns are numbered starting at 0, so Column(2) is the 3rd column)

Reply | Quote

I think I am having a similar problem as JenniferS. I have a form based on a query and its working fine. I have lots of rows that I am stepping through with the form to score answers to a test. I would like to put a textbox on the form that displays the results from a different query showing percentage complete. I have this query written and it return 1 row with 1 column. The query works fine on its own. I would like to run the query every time the screen repaints (such as when I go to the next record). I tried your suggestion with a combo box and text box, but so far, no joy. Neither the combo box or the text box are getting any values.
The combo box properties are:
Name: cboPctComplete
Column Count: 2
Column Widths: 2.54cm;2.54cm
List Rows: 1
Control Source: =[PolAdminScoringPctComplete]![PctComplete] (=[QueryName]![QueryColumnName])
Row/Source Type: Table/Query
Row Source: blank
Bound Column: 1
No Event values

The text box properties are:
Name: TxtPctComplete
Control Source: =[cboPctComplete].[Column](0)

Any thoughts as to what I am doing wrong?

Stu

Reply | Quote

I don’t think you need a combo box here. You can use a text box TxtPctComplete with the following control source:

=DLookup(“PctComplete”,”PolAdminScoringPctComplete”)

To make this update each time you move to a new record, you can create an event procedure for the On Current event of the form:

Private Sub Form_Current()
Me.Recalc
End Sub

Reply | Quote

Thanks Hans, that worked great. Should have sent this days ago.

Stu

Reply | Quote

Thanks, Hans and Zave. Works great! I was just missing the reference to the column.

Jen

Reply | Quote