Unanswered: Linked Table Query Used in Data Entry Form
I am not sure what the best method for creating a data entry form is. I have a linked table to another database to provide a list of parts. Unfortunately the field data type for "PartNumber" is text (there are a few records that I don't care about that have actual text in their "PartNumber" value). I use a query to recreate the part list, and the query converts the text data type to number data type and records without proper part numbers are excluded from the results. I think I am clear on what I am doing up to this part.
I then have a form that I wish to use in data entry mode to input part orders. This form has fields for part number, quantity, and date; as well as a text box that displays the part description provided from the linked table (based on the part number). If I try to access the description from the linked table, I receive an error indicating that the part number from my form and from the linked table are not equivalent data types. If I try to use the part description from the part list query, the form displays all of my part order records and is not in data entry mode. The only way I can get the form in data entry mode is if I create a table from the query to access the part description from. I would prefer not to do that so that my form access a current part list from the linked table, rather than a table I generate one time from a query. Any advice? How can I use a query in a form so that it remains in data entry mode?
Instead of a textbox for the part number, you can use a 2-column combobox, with the second (invisible) column containing the text representation of the numerical part number. It would then be compatible with the original table. You could then run your query using the invisible column instead of using the combobox value.