Copy a Value from a Query

[RaunLGoode]

Could somebody give me a simple example of the script to copy a value from a query (qryA)onto a text box in a form(frmB)?

 

[dcx693]

Why not just use a Dcount, Dlookup, Dmax, or Dmin to get the data from the query. No coding involved. If you want a code example, let us know what version of Access you are using.

 

[RaunLGoode]

I am using 2000

 

[RaunLGoode]

I am trying to look up an employees name based on their ID #.
I have a combo box that has the employees Names and ID #s and stores the ID #.
I need the ID # because it is a primary key, but I want to display the name of the person selected in the combo box for verification. What made a query that looks up the employee based on the ID #, but I can’t get the result form the query back into the text box in my form. If there is an easier way to do this, please let me know, but as a rookie that’s trying to learn Access and VBA, I would also like to know how to write a script too-for next time.

 

[dcx693]

I have a combo box that has the employees Names and ID #s and stores the ID #.

You're already most of the way there. The combo box already has the names that you want. Even if the combo box is storing the ID, the name information is still available. Combo boxes have a Column property, beginning with Column(0) for the 1st column, that gets you the data from that column of the selected entry.

If you don't want to show the ID, but would rather have the combo box display the name, you can still have ID as the bound column, but show the name field. If you still want to display the ID in the combo box, but show the name corresponding to that ID in another text field on the form, here's some sample code:
Me.txtName=Me.comboBox1.Column(0)

In that example, I assume the text field you want to display the name in is called txtName. I also assume that your combo box is called comboBox1, and that the name is in the first column. Place that code into the AfterUpdate event of the combo box.

 

[RaunLGoode]

DCX693- Thanks and a brief follow up

Thanks, I finally got that to work, and I REALLY appreciate it.

One final question. If I wanted to fill in a field in a subform with the updated value of txtName I assume I could add another line to the on Afterdate event.
Insted of :
Me.txtName=Me.comboBox1.Column(0)
I would need to point to the correct field in the subform, Right?
I tried This and got an error:

SubForm1.txtName = MainForm1.comboBox1.Column(0)

Where MainForm1 is the original form & SubForm1 is the (obvious) subform.
Could you possibly give me a bit more help?

 

[dcx693]

You need to follow a specific syntax when referring to subforms from the main form. Try this:
Me.SubForm1.Form.txtName=Me.comboBox1.Column(0)
or the equivalent syntax:
Me("SubForm1")("txtName")=Me("comboBox1").Column(0)
which should also work.