Combo Box, Losing my mind

[cbramsey]

I took 3 years off from Access work and I feel like I can't do anything now.

I have the following

"Ownership" - Table
-owner number
-owner name
-tract ownership
-royalty decimal

"Active Owners" - Query - This query filters the master owner information table to display on the active owners


-Owner Number
-Owner Name

"Active Owners_Name" - Query - This uses the owner number to return a name
-Owner Number
-Owner Name

I have a form setup to enter data into the OWNERSHIP table, what I want to do is to input the owner number in the form and have the name autopopulate. I also want to ensure that the number entered into the Owner Number is valid.

What I did was enter a Combo box that is tied to the ACTIVE OWNERS Query, this only shows me the owner number, ideally I would like to see the Owner Number and Owner Name in the box when selecting. Any help would be great, I know this is fairly easy, I just can't seem to get it.

Thank you

[June7]

Why do users need to see Owner Number? Only one value can display in the box but multiple values can display in the dropdown list. Do you want users to select owner by typing number or name?

Set combobox properties:

ControlSource: the field where you want to save the [Owner Number]
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0.25"; 2" (or use 0";2" to hide the number and type name)


Advise no spaces or punctuation/special characters (underscore only exception) in naming convention. Better would be OwnerNumber, Owner_Number, OwnerNum, Owner_Num.

Is [Owner Name] names of individuals like John Smith? Name parts should be in separate fields: LastName, FirstName, MidName, Title, Suffix.

Don't really need multiple query objects to accomplish. An SQL statement can be typed directly into RowSource (can even open the query design by clicking the ellipses (...))

At least instead of the second query, do whatever you do to pull in name in that query in the first one.

[cbramsey]

The field names do have the underscore in the name, unfortunately, the owner name data is coming from a linked database table to our main system and it is set up with the entire name in one field. As for needing the number and the name, we are working to do an export of the data and require both data points. I think my problem was that I was starting by building a form using the table fields as opposed to creating the combo box field and tying it back to the table field. Once I select the Owner Number, how can I get that to populate the query that will automatically populate my owner name field? I have done this in the past but like I said, I was away from Access for awhile and now feel like I am treading water to get back into all of the little quirks.

[June7]

Review post 3 in https://www.accessforums.net/showthread.php?t=66009

For your export, build a query that includes both tables (basically option 1 in the referenced thread)

[cbramsey]

When I do that I get the #name? error

I have the following formula under the Control Source - =[Active_Owners_Name]![Owner_Name]
Locked = Yes
Tab Stop = No

In the Active_Owners_Name Query I have the following
under Owner_Number
[FORMS]![NRI_Entry]!
[List2]

List 2 is the list box that is storing my owner number, once I get this figured out I will name more appropriately

In the List2 box on the form, it is set requery the owner_name afterupdate

[June7]

Do not use a query object as a form RecordSource if that query references the form for dynamic filter criteria. I NEVER use dynamic parameterized query objects.

Cannot reference a table or query object directly in a textbox. The table or query has to be included in the form or report RecordSource. Then just reference the field name. So form RecordSource could be an SQL statement typed directly into the property (or open the query designer by clicking the ellipses ...). The SQL would be like:

SELECT Ownership.*, MasterOwnerTable.* FROM MasterOwnerTable RIGHT JOIN Ownership ON MasterOwnerTable.OwnerID = Ownership.OwnerID;

Then build a query object for your export or build a report for export.