populating field in form based on input field

[BrandonFinn]

Hi All,



I have done some searching with no luck in regards to what I am trying to do. I am still quite a novice when it comes to VBA code. I will be inputting a set of numbers into the field "shop Order #". I want the ANSI field to then populate from another linked table.
The form you see here is attached to a table that I am inputting values into for use in other places. The field that I want to read from is linked Excel file on our network drive; which I know that I have access to. I want to populate this field upon entry of the Shop Order #, but then save it to a field in the input table. Any help that can be provided would be greatly appreciated.

[June7]

Options:

1. multi-column combobox for the entry of Shop Order number then ANSI textbox can reference column of the combobox - the combobox RowSource would be a query that joins the related tables so associated information is available for reference

2. include the Excel link in the form RecordSource - join type "Retrieve all records from {table name} and only those from {Excel link} that match"

3. DLookup()

[BrandonFinn]

Hi June,

Thank you very much for the direction. I set the control source of the ANSI textbox =DLookUp([Open Prod Orders]![Mat# descriptn],[Open Prod Orders]," [Open Prod Orders]![Order] = " & [Forms]![FPY Table - Cladding]![ShopWorkOrder#])

As stated above I want to match up the value input into the Shop Order # field with a matching value in the Open Prod Orders table and spit the Mat# Descriptn (txt string) back at me.

I am not getting any errors, however I am also not getting any values in the ANSI tab. what am I missing here? I will also want to store this value after I create the record. I was going to set the two values equal after I get the value displayed.

[June7]

DLookup would be my personal last choice.

The syntax of DLookup is:

DLookup("fieldname", "tablename", "some field=" & variable)

The quote marks are required.

Access Help has more guidelines on using domain aggregate functions.

[BrandonFinn]

Ok, then I will scrap that idea. I regrouped a little and decided to go the combo box route for filling in the desired field. I found this http://www.baldyweb.com/Autofill.htm that states I can set the ANSI field equal to the combo box value, but cannot get that to work. I keep getting the #Name?; what am I missing?

[June7]

What are properties of combobox:
Name
RowSource
ColumnCount
ColumnWidths
BoundColumn

[BrandonFinn]

Nevermind, I figured out how to get the combo box to populate the ANSI field. However I do not want my operators to be able to choose or select the drop down portion of the combo box. They are currently scanning in the values, but i do not want them to have the functionality to select from a drop down menu.

Thank you so much for your help so far!

[June7]

I don't know how to prevent use of the dropdown and still allow input to the combobox. Maybe best could do is 'capture' the click event and cancel the input.