Update multiple fields from a Query

L

Leslie

I have a Form. I have two fields, and one is a Combo-Box built on a Query
and the other is a "hidden" field which I want auto-updated based on what is
chosen in the Combo-Box. When a record is selected from the Combo-Box Query,
I want the other field to be updated with another field value that is in the
Query result.

E.g. I search for a Name in the Combo-Box and I choose the name, then I
want the ID data from query to populate a hidden ID field behind the scenes
in the Form.

Any quick and dirty ideas out there?

Thanks!
Leslie
 
6

'69 Camaro

Hi, Leslie.
I have two fields, and one is a Combo-Box built on a Query
and the other is a "hidden" field which I want auto-updated based on what is
chosen in the Combo-Box. When a record is selected from the Combo-Box
Query,
I want the other field to be updated with another field value that is in the
Query result.
Click to expand...

If the hidden text box were named txtHidden, the combo box were named
cboName, and the combo box's column number of the field you want stored in
the hidden text box were the first column in the query, then you could try
the following:

Private Sub cboName_AfterUpdate()

On Error GoTo ErrHandler

Me!txtHidden.Value = Me!cboName.Column(0)

Exit Sub

ErrHandler:

MsgBox "Error in cboName_AfterUpdate( ) in" & vbCrLf & Me.Name & _
" form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear

End Sub

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
J

John Welch

If you just want to chose a name from a combo-box and be able to get the id
value associated with that name, then you don't need to have a hidden
textbox.
You can set the rowsource of the combo box to something like :
Select ID, Name from tblPeople, so that the combo-box has 2 columns.
Then set the bound column to number 1, the ID. Then on the format tab tell
it there are 2 columns and set the column widths to zero and say 2 inches,
by writing "0;2" (without quotes) in the 'column widths' box. Now when you
open the combo box, you will see only names, but the value of the combo box
will be the ID number.
Perhaps there is something more complicated that you didn't mention, but if
not this should give you want you want without the need for a hidden
textbox.
HTH
-John
 
L

Leslie

Gunny,

THANK YOU! Yes, this was exactly what I was looking for. I will give it a
try and hopefully this will work.

Thanks again!

Leslie :O)
 
L

Leslie

John,

It looks like Gunny gave me the solution I was looking for. You were headed
down the right track, and I was already there with what you suggested, I just
needed the hidden field updated, as I need that ID field to run another
"query" from another control!

Thanks!
Leslie
 
Top