How can I get a PK in another table, based on searching ...

[Morpheus_UK]

How can I get a PK in another table, based on searching for it based on 2 values. By searching with 2 values you will always get only 1 record.

READ BELOW FOR SITUATION

My Problem on my form:

The user enters 2 values, after entering the 2nd value a Unbound combo box runs a query, which searches for these 2 values (unique), that appear in a separate table.

These 2 values are unique toegether, but records in this separate table have another field, its Primary Key (AutoNumber), used to identfy the pair.

So back to this Unbound Combo Box that searched for the primary key of the 2 values entered. Now Only the Primary key can be selected from the combo, the value that I'm after.

But since there will only be 1 option (being unique), how can I have the PK value aready stored in an Unbound text box, as opposed to having options in a Combo box?

 

[Morpheus_UK]

On the AfterUpdate property of the combo box where you enter the 2nd value, the Unbound textbox would have to do what? Have its control source changed?

Me.UnboundTextbox.ControlSource = "Qry1.column(0)"

This DOESN'T WORK though, is it wrong syntax or wrong method?

Qry1 is the query that searchs for the record based on the 2 values entered. The 1st field has no criteria and is the Primary Key value, which I'm after to be stored in a text box, after going through selection using 2 casading combo boxes.

The first (field) column's value in the qry needs to be stored to this textbox. I know usually queries give back more than 1 result, and but this query will always result with one record when run.

 

[Pat Hartman]

I can't make any sense out of your post but I can fix the syntax of:

Me.UnboundTextbox.ControlSource = "Qry1.column(0)"

It should be:
Me.UnboundTextbox.Value = Me.BoundCombo.column(0)

You are not changing the ControlSource of the unbound control, you are changing its VALUE. And to obtain the value you want, you reference the combo, not the query. Column(0) is the first column of the combo's RowSource which is usually the bound column. So the above statement can be reduced to:
Me.UnboundTextbox = Me.BoundCombo
since it is referencing the bound value for each control.

 

[Morpheus_UK]

Yes, I wish I could explain what I am trying to do.

Thanks for that, yeh I see how its the value that is changing and not the ControlSource.

I have a form where I use 2 cascading combo boxes, that set values/parameters needed in a query to find the PK of the record that these 2 values belong. There will only be 1 record when the query is run. So I was wondering, instead of having the option from the query in an Unbound Combo Box, and having to select it. Can the combo box, or a textbox value be the PK value from the result of the query.

I don't think its possible, sorry I'm not good at explaining it well.

I'm think I may redo my table structure, and my form another way though.

 

[Pat Hartman]

I'm pretty good at figuring out what the real question is but you've got me stumped. Usually if the search criteria is entered on the form which will show the results of the query, the combos that supply the criteria are unbound but the rest of the form is bound so when the record is found, all fields are available.

 

[pono1]

My best guess for a Sunday...

Me.UnboundTextbox.Value = DLookup("ColumnName","Qry1")

Regards,
Tim