Default data in a combo 2

[Appollo14]

Hi all,

I have a combo box on a form and would like the user to be able to select the default data to display. The combo picks data from a table (ID, Code, Desc, Default(Y/N)). My question is how do i get the control to pick the Code with the Default setting of Y?

Regards,
Noel.

 

[randy700]

In the combo box row source, add a WHERE clause....
SELECT ID, Code, Desc, Default FROM MyTable WHERE Default = Y

 

[Appollo14]

Hi Randy,

Thought of that, but that would limit my selection. I want the user to be able to ultimately be able to select from the combo. What i'm trying to do is just allow the user to be able to set the default selection.

Regards,
Noel.

 

[AmritPaulSingh]

In the properties windows of the combo box, enter ="Y"

 

[Appollo14]

Hi Amrit,

Not entirely sure what your post meant. would you be a little more specific please.

Regards,
Noel.

 

[AmritPaulSingh]

sorry.

What i meant was that in the properties windows of the combo box, there isa field for default value....you can enter whatever default value you want in this field.

hope thats clearer enough

 

[scriverb]

Use the following in the combobox's Default property:

=DLookUp("[Code], "[[red]YourTableName[/red]]", "[Default] = 'Y'")

This code looks up the single record with a value of Yes in the Default field of your table. It then returns the Code value of that record. Now this is dependent upon the code column being the Bound column for your combobox. If the ID is the Bound column then adjust the statement accordingly.

Post back if you have any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Appollo14]

Hi All,
still cant get this working so i must be doing something wrong somewhere.
Here's the SQL that queries my combo at the minute.

SELECT ServiceLevel.SLevelid, ServiceLevel.ServiceLevelCode, ServiceLevel.ServiceLevelDesc
FROM ServiceLevel;

The combo is bound to column 1. So i tried to use the following lookup in the default data field.

=dlookup("[ServiceLevelCode]","[ServiceLevel]","[Default]='Yes'")
The field is still returning data but not giving any default data.

Any help appreciated.
Regards,
Noel.

 

[RoyVidar]

Hi!

The point here is to use the value from the bound column (the "ID" field?) to set the default value from the correct row (where somefield = somevalue).

If your bound column is column 1, then the id field should contain the default value, change the Dlookup to:

[tt]=dlookup("[SLevelid]",...[/tt]

Or do the really quick and dirty thingie, find the value from the table, and enter it;-)

It doesn't seem like your table has a field named "Default", you need to use a field from the rowsource.

Do you have a value "Yes" in any of the fields? If not, use some value that exists in the rowsource.

In stead of trying to understand your data, consider a two column combo containing the following field/values:

[tt]id desc
1 Blah
2 Yikes
3 Ouch
4 Etc[/tt]

To make the "Ouch" choice default, id being the bound column, I'd either stick the number 3 in the defaultvalue property, or to make it a bit more dynamic, try:

[tt]=DLookup("id","MyTbl","desc = 'Ouch'")[/tt]

Roy-Vidar

 

[Appollo14]

Hi Roy, thanks for the quick response.

Firstly, well done for spotting the deliberate mistake and noticing that i wasnt including the Default field in the query ;-)

The query now looks like
SELECT ServiceLevel.SLevelid, ServiceLevel.ServiceLevelCode, ServiceLevel.ServiceLevelDesc, ServiceLevel.Default
FROM ServiceLevel;

My dlookup now looks like this

=DLookUp("SLevelId","ServiceLevel","Default=yes")

and it works he he.. thanks a million

I think my main problem was not fully understanding the bound column control thingy.
I had already toyed with the idea of using the ID and forcing the client to use a specific default record but i wanted to do something more dynamic - and now i can.
Now, the only other problem that i can envisage is that a user may have multiple defaults set, i've tried setting the yes/no to be unique but as you are probably aware, you cant. Can you suggest a better way of handling this?

Regards,
Noel.

 

[scriverb]

After your posting of your select statement please use the following code:

=DLookUp("[SLevelid], "[ServiceLevel]", "[Default] = 'Y'")

You see the Combobox has a setting column 1 as the Bound Column. This means that the value of the combobox being stored or selected is actually the SLevelid value. I am assuming that you have your column widths set to something like this:

0";1";2"

With this type of setting you will not see the [blue]SLevelid[/b] but you will see two columns for the [blue]ServiceLevelCode[/blue] and [blue]ServiceLevelDesc[/blue].

You are only returning 3 fields with the SQL which is in your Row Source. That's okay as the Default boolean field doesn't have to be displayed here. The DLookUp function will search the table([blue]ServiceLevel[/blue]) for the record with its [blue]Default[/blue] field value set to [red]Yes[/red] and return the value of its corresponding [blue]SLevelid[/blue].

Post back with the results of this entry.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[RoyVidar]

I don't quite understand your question. More than one default, how do you determine which default goes to which person/record ? The where clause/criteria of the dlookup would then have to include more fields. Perhaps seing some sample data/tables involved...

(scriverb is correct of course, the field doesn't have to be in the combos rowsource, but have to be in the table/query it's querying)

Perhaps a more programmatic approach can be used, here's a little sample on changing a controls default value thread702-772209

Roy-Vidar

 

[Appollo14]

Sorry, wasnt very clear was i.

I dont mean that i want there to be multiple defaults, i want to limit the default setting to a single record, obviously i cant do this with a yes/no field. I was just wondering if you had a better way of doing this.

Regards,
Noel.

 

[scriverb]

Apollo14: Yes, you can limit the default selection of the combobox to one record with the code I provided. If you have only one record in the table with a Y in the field default then you will have that record selected as the default for the combobox. Now, how you make the determination in the rest of your program as to setting the Y value for that record you have yet to specify. Do you have the user setting this value? Can multiple users choose and set different records to this value? If you can better describe the table and its uses we can probably come up with a way to help you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Appollo14]

Hi Bob,

I've got exactly what i want appearing as the default data in the combo now, and the combo is still displaying all of the other records. Now the only problem that i see is that, at the moment the user can set multiple dfaults due to the fact that i'm using a yes/no field to allow the user to designate the record. the table is as follows:

[SLevelId] (autonumber & primary field), [ServiceLevelCode] (Text field), [ServiceLevelDesc] (Text field), [Default] (Yes/No field).

I'm guessing that what i actually need to do is set an on click command that will basically verify that there is no other record with "yes" as the value.

Regards,
Noel.

 

[scriverb]

You can use the following SQL to make sure that you only have one record with the Yes value selected. In the AfterUpdate of the check box for Default on your form execute the running of of a query:

If Me.Default = True then
   DoCmd.SetWarnings False
   DoCmd.OpenQuery "qryClearDefault"
   DoCmd.SetWarnings True
else
   MsgBox "You are removing the only default setting"
end if

Now here is the query SQL for the saved query [blue]qryClearDefault[/blue]:

UPDATE ServiceLevel as A SET A.[Default] = False WHERE A.[SLevelID] <> Forms![YourFormName]![SLevelID];

The running of this query sets all records in the table back to a Default field value of false or NO. I made a reference to a form control in the query. I am assuming that you have a form opened and that there is a control called SLevelID. If there is not a control for this ID that just create one. It doesn't have to be visible. Just set the visible property to false and you can actually shrink its size down to just a small box and tuck it away in the corner of the form. But, it is required so that the query knows which record not to update to False/NO.

Post back with any questions that you may have.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Appollo14]

Thanks guys, you have been great. it's doing what i need now and i think i even understood a bit of it too...



Regards,
Noel.