Form Problem with Field

[devarts]

Our company likes to keep track of when we calibrate certain pieces of machinery. A form was created to log that data in a table. I was asked if I could add another piece of machinery to the list because it was just purchased. I found out that someone manually put in the Row Source (looking at the property sheet). My thoughts were to create a table so if they need to add another piece of machinery that all they needed to do was add it to the table. But come to find out, the form field is connected to a table field that only accepts numbers. I ended up writing over what was previously there and cannot fix it. I need to be able to choose a number for that field but also display the machine name.

Example:

1/Tool 1
2/Tool 2
etc

If you need pictures or something to help visualize please let me know.

[Seth Schrock]

A combo box allows you to do to exactly that. Just set the column count property to 2, and then set the column widths to 0; 1 (assuming measurements are in inches). This hides the first column, but the controls will still be bound to the first column (this can be changed, but the default is bound to column 1). So the value stored will be the number, but the value shown will be the tool name. This works for both value lists and data sources. So if you have a table of tools, then just make the row source be a query that gets the tool's ID and the name.

[devarts]

Thank you so much! I completely forgot that one could do that! It solved all my issues!

Thanks!

[zmbd]

OK, this is why we stress normalization so strongly here!

The rowsource for your tools should have been a table and then you would have had a history or log type table.

BACKUP YOUR DATABASE! Never ever develop on the production database.

Provided you haven't altered your control's rowsource.
list the data so that you have the numeric and text (as you've done for the examples 1,tool1...

This may not be the best fix; however, it should get you back up and running.

Now, create a table with an autnumber field and enter the correct name against the autonumber

tbl_tools
[tools_pk] autonumber, primary key
[tools_name] text(30)

so the first new record would be: 1, tools 1... etc...

Go back to your form.
Open the properties on the control
data tab
Row Source Type: Table/Query
Row Soucce: tbl_tools
Bound Column: 1

format tab
Column Count: 2
Column Widths: 0,1
Column Heads: no
List Width: Auto \
(if not option then 1.5 ... add 0.5 to the column widths lagest value)

Close and save the form

You should be back up and running.

I would also go into database tools and set the relationship between the tbl_tools and the table where this information is setup.


Now if you are using sharepoint list then there's a whole other monkey we need to shoot instead (please say no, no, no, .... happy place...)

[Seth Schrock]

No problem. Glad to help.

[zmbd]

opps.... cross posted with Seth.

[devarts]

I usually do back my databases up but it seemed like an easy fix from the beginning because I've seen people do this before. But I have learned my lesson.