Setting the default value of a Combo Box. (1 Viewer)

[three-pins]

Can anyone please help me with the following problem?

I wish to set the default value of a Combo Box on a form, whose source is a table. An example is Order Status code that can have the values Outstanding; In Progress; Completed etc. On a new order I wish the Order Status to default to “Outstanding”

Whilst I realise this can be achieved by setting the default value to the record key of the table that holds the values, doing it this way means that to change the default value, a user of the application would need to have the opportunity of amending the table properties, which I do not wish to allow. I would prefer that the user have the option to determine the default Order Status by the use of a flag or parameter held on the appropriate record by use of a table maintenance form. i.e. set a flag to a value of ”D” to the appropriate record.

 

[vbaInet]

Look on the Data tab of your Combo box's properties. You will find a field that reads "Default Value"

 

[Mr. B]

three-pins,

When you set the defautl value for any field, or any control, that "default" value is only applied to a new record. Users can then make changes to that default value at any appropriate time, based on the existance of other restrictions that may or may not be applied. In your case, you can simple enter the default value of "Outstanding" in the Default Value property of your Combo box and that default value will be applied to each new record. Your users can then select the other options as the situation demands.

If there are other criteria that determine when a record status needs to have one of the other values applied, you can even change the value of the combo box using additional VBA code as appropriate when that other criteria is met.

 

[three-pins]

Thanks for the replies. However, this approach does not work here. The data held in the form control source is the numeric key (AutoNumber) to the records held on the table of possible values. Therefore I can set the default to the appropriate key i.e. 27 and the user is presented with the value of "Outstanding", (the second field of the query behind the Combo Box, the key being the first). Although this gives me exactly what I want, I am trying to avoid using the look up table key in the default property. I was trying to achieve this by adding a third field to the look up table which could have the value, say, "Default" against the chosen record in the list of options. This would allow the user to control the default value to be applied via a maintenance process/form, should system circumstances change. I have about 30 data elements which are controlled by combo boxes and it would be a real advantage for the user to be able set the default value of many of these fields, without me having to change the underlying code/properties each time.

 

[vbaInet]

I still don't fully understand what you're after. Does the combo box change value everytime a new record is added?

 

[Mr. B]

The process that I described to you would work even when you are using a Foreign Key value from a table. You just set the value not the text.

If you want users to be able to manage the default values of multiple controls then you need to create a table where the values can be assigned by a user. If you are using a Split mothod for your applicaiton then you can have this table in the front-end for each user or have a second back-end file that resides on the users work station and the table linked to the front-end. This will allow each user to have their own setting for the controls you want. All you need then is a function that will assign the specified default values to each control as the form is opened.

 

[HiTechCoach]

Thanks for the replies. However, this approach does not work here. The data held in the form control source is the numeric key (AutoNumber) to the records held on the table of possible values. Therefore I can set the default to the appropriate key i.e. 27 and the user is presented with the value of "Outstanding", (the second field of the query behind the Combo Box, the key being the first). Although this gives me exactly what I want, I am trying to avoid using the look up table key in the default property. I was trying to achieve this by adding a third field to the look up table which could have the value, say, "Default" against the chosen record in the list of options. This would allow the user to control the default value to be applied via a maintenance process/form, should system circumstances change. I have about 30 data elements which are controlled by combo boxes and it would be a real advantage for the user to be able set the default value of many of these fields, without me having to change the underlying code/properties each time.

The way I do it is using the form's On Load event to set the default value for each combo box control by reading (Dlookup or recordset) the lookup table to get the default value.

This works great in an MDE/ACCDE!

 

[three-pins]

Thanks to everyone for all for the pointers. I have now achieved the result I needed, (possibly not in the most effective manner). I have created a subroutine in the OnOpen event of the form, and using the ListCount, ColumnCount and column properties to interrogate the the Look Up table of values for the Combo Box, have determined which record is flagged as the default record. I then used the key to set the default property of the Combo Box. All I need to do now is to turn the subroutine into a function so it can be readily used in other areas of the system, but that's another problem!

 

[HiTechCoach]

Glad to hear you are almost done.

TIP: Check out the DLookup() function

 

[three-pins]

Have now investigated the DLookUp() function and found it worked very effectively once I realised you could have more than one condition clause.

 

[HiTechCoach]

Great job!

Glad you got it figured out.