1

I have been working with Access 2010 and creation of forms.

I have a table1 and related form1.
I have another table2 with values for yes,no and missing (lookupyesnomissing).

I want a particular field in the form1 to show the options "Yes", "No" and "Missing". I also want the same field to be stored as 0,1,or -1 in the table( 0-No, 1-Yes, -1 Missing, --- lookupyesnomissing table values).

I am not able to have different values for same field in 2 places form and table. I either should have both as numbers or both as text.

Please help me out.

Renaud Bompuis
  • 16,596
  • 4
  • 56
  • 86
user1804254
  • 59
  • 4
  • 5
  • 13
  • Include both columns in the list, but set the value column to zero width. Then set the bound column to the one with the value you want to store. (I think there's also a wizard to accomplish this -- have you tried following along after you drag/drop a combo box onto your form?) – McGarnagle Nov 06 '12 at 20:19
  • Thank you for the reply. Could you please clarify when you say include both columns in the list. I am very new to Access. I am not aware of the wizard. – user1804254 Nov 06 '12 at 20:35
  • See below -- hopefully that helps. I don't have Access in front of me now, so had to go from memory. – McGarnagle Nov 06 '12 at 20:54

1 Answers1

1

You can include multiple columns in your combo box, but bind its selected value to a field in the current form. Just set its properties correctly.

  1. Make sure the RowSource query is correct, and pulls both columns from table2.
  2. Set ColumnCount to 2 (or however many you want to display, plus 1).
  3. Set the ColumnWidths to 0;1 (ie, hide the first column, I'm assuming that the first column is the value that you want to bind to)
  4. Set the BoundColumn to 0 (bind it to the first column in the data set)
  5. Finally, set the bound field property to the field in the form's data source that you want to bind to
McGarnagle
  • 101,349
  • 31
  • 229
  • 260
  • 1
    Thank you very much. I was able to do these steps and solved the problem. – user1804254 Nov 06 '12 at 21:19
  • While I was doing this, I could see that some of the rows of data are getting deleted as I keep on adding the data. Can you please guide me as where might the error have been? – user1804254 Nov 06 '12 at 22:22
  • @user1804254 rows of data? Do you mean in the table, or in the form properties? – McGarnagle Nov 06 '12 at 22:28
  • It was in the table. I mean to say that records of data are getting deleted. – user1804254 Nov 06 '12 at 22:41
  • @user1804254 I'm drawing a blank -- can't say I've encountered that before. – McGarnagle Nov 06 '12 at 22:43
  • Hmm..No problem. But thanks a lot for helping me out. I have been struggling with this for more than 2 days now. – user1804254 Nov 06 '12 at 23:11
  • I am using "Add New Record" to add the data. So whenever I am opening the form as new I could see that the last record gets updated instead of being added as anew record. Does this give you any idea of what the problem might have been. – user1804254 Nov 06 '12 at 23:36
  • @user1804254 Oh, that makes more sense. You'll want to open the form in add mode (there are properties you can hard code in the form, or you can set them when you open the form from a macro or Visual Basic). Look at these propeties (I think acFormAdd is the one you want): http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm#constants_DataMode – McGarnagle Nov 06 '12 at 23:40
  • @user1804254 to help troubleshoot, you might want to add the autoid field to the form -- for a new record it should show "(New)" instead of a number. – McGarnagle Nov 06 '12 at 23:44
  • Information at this link solved my problem - http://stackoverflow.com/questions/9417843/how-to-open-form-in-add-record – user1804254 Nov 06 '12 at 23:53
  • dbaseman: I was able to do a table to not to edit the information in it. I can't have auto numbered unique id as I have another primary key. The issue now I have is, I need to open the table both in add mode and edit mode. When I need to enter data I have to do it in add mode and when I need to check data, I should be able to edit them. How do I deal with such a requirement. – user1804254 Nov 07 '12 at 19:48