1

I have a form in MS Access (datasheet view) which is based on the output of a SQL Server query. Now this is used in a front end where the user needs to be able to select from a drop down the value for one of the columns, 'NAME'. I tried adding a combo box which is mapped to take distinct names from the SQL Server table to the datasheet form but the results were not as desired.

Kindly provide advice on how this can be possible. I did this before by using a lookup on a similar MS Access database, but in a multi-user environment, the database was corrupted and now I moved my back end to SQL Server.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Manus
  • 869
  • 2
  • 10
  • 20

2 Answers2

2

Just got the answer to my question. here are the steps I followed.

a) Add a combo box to the datasheet form in design view like Johnny Bones suggested.

b) Create a drop down with values.

c) Point the Control Source property to the field in the SQL Server table which needs to be updated, in this case NAME.

d) And voila, your drop-down now shows the values in the table and you can include a drop down for the user.

Thank you for pointing me in the right direction Johnny.

Manus
  • 869
  • 2
  • 10
  • 20
1

In datasheet view, I think you'll need to set up the field in the table.

  1. Open the table in Design View
  2. Click on the specific field in question
  3. At the bottom you will see 2 tabs; General and Lookup (I apologize, my work doesn't allow me to upload pics, so I hope you can visualize it)
  4. On the Lookup tab, change the Display Control from Text Box to Combo Box

At that point, the properties should look familiar if you've worked with Combo Boxes, and you can change them to suit your needs.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Hello Johnny,Thank you for your answer. But I am not using an Access database as my back end. I know this method works when your source data is in an Access table. But my front end is in Access and my back end is in SQL Server. How can I achieve the same thing with such a setup? – Manus Sep 10 '15 at 17:17
  • 1
    Hmmm... In a linked table I don't think it can be done. You should never use a bound form anyway, it's a huge hassle to undo changes properly. But if you insist on doing it that way, could you use a Continuous Form? This way you could essentially mimic a datasheet with text boxes, and just use a combo for the specific field(s) you want. – Johnny Bones Sep 10 '15 at 17:42