Selecting information in a combo box showing info in a table 1

[jalge2]

Hey all. I've got a table that has 3 different fields. The three fields are Part Number, Description, and Qty on Hand.

I'm trying to make a form that has a combo box and 2 text boxes in it. What I want the user to be able to do is to select which part they are sending back with the combo box. Once they have selected which part description, I want the part number and the qty on hand to show up in the two other text boxes. Can access do this?

Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'

 

[BobStubbs]

Yes, this should be straightforward.

Create two unbound text boxes on your form.

In each, set the Control Source to look like this:

=DLookUp("[Description]","tblYourTableName","[PartNumber]=forms!frmYourFormName![PartNumber]")

Put your real table and form names in place of my example values.

Set these properties as well:

Enabled = No
Locked = Yes

You might also change the background colour to show the user that these fields are 'view only' and cannot be changed.

I hope that this helps.


Bob Stubbs

 

[yduckfr]

Make a combo box with 3 columns Part Number; Description; Qty on hand then in the after update event of the combo box enter the following code:

[i]txtBox1[/i] = [i]combo box[/i].column(1)
[i]txtbox2 = combo box[/i].column(2)

 

[jalge2]

The table name that I am referring to is tblInventory, which I changed your string to say. The form that I am using is called subfrmInventory, which I also changed. When I switch to form view, it is giving me a #error. What is causing this?
=DLookUp("[Description]","tblInventory","[PartNumber]=forms!subfrmInventory![PartNumber]")

Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'

 

[BobStubbs]

I'm guessing from the name that you are using a sub-form. If so, you need slightly different DLookup syntax, as in this example:

=DLookUp("[FieldToLookup]","tblTableName","[FieldName]=forms!frmMainFormName!subfrmSubFormName![FieldName]")

Thus you need something like:

=DLookUp("[Description]","tblInventory","[PartNumber]=forms!frmYourMainFormName!subfrmInventory![PartNumber]")

Bob Stubbs

 

[jalge2]

Hi Bob, first of all, thanks for all of your help. I feel that I'm close but I'm still getting the #error. Here is the line that I have.

=DLookUp("[Description]","tblInventory","[Part Number]=forms!frmservice!subfrmInventory![txtPartNumber]")

The names of my tables and forms seem correct. If you don't mind I would like to go through this string.

It appears to me that it is looking for whatever is in the description field of the table "tblInventory". Whatever is selected in the description combo box, it will match that by taking the part number out of that same table "tblinventory" and apply it to the subform "subfrminventory" in the text box "txtPartNumber". Is this a correct statement?

I'm not exactly strong with access so I'm trying to break it down and learn a little here also. Thanks a lot for the help, I really do appreciate it.

Jason Alge
Jason.M.Alge@lowes.com

'There are three kinds of people: Those who can count and those who can't'