Default combo box based on default table value

[Robert88]

Hi Everyone,

I hope someone can help.

I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).

Currently in order to set the default value, I have used the following code for each default;

Private Sub Form_Load()
    Forms!frmMediaLabeller!CboDriveName.DefaultValue = """[COLOR="Red"]D[/COLOR]"""

End Sub

However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D:\ Drive. I have tried to replace the D above with an SQL statement but with no success.

Private Sub Form_Load()

    Dim Drivename As String

    Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));

    Forms!frmMediaLabeller!CboDriveName.DefaultValue = """[COLOR="Red"]Drivename[/COLOR]"""

End Sub

This is definetly not working, can anybody help, I have a feeling it is syntax but not sure where?

Robert88

 

[Robert88]

Hi All,

Am I going down a completely wrong path?

If so any input would be valuable.

Robert88

 

[ansentry]

Maybe I am not understanding your problem, however have a look at the attached sample. If I'm on the wrong track just just ignore this post.

 

[Robert88]

Hi ansentry,

Since CD player drive letters vary between computers dependant on how people allocate thier hard disk's (partitions and/or a series of hard drives) there is a need for me to allow the user to go into a table (in your example tblCDDrive) and select a default (yes/no) selection for thier drive letter for thier CD Drive. I suppose in this case select only one default for thier machine.

I have added the database you attached with one field extra fldCDDefaultDrive, with the D Drive selected as default. Based on this selection for the user this then becomes their default in the combo box, so they do not have to keep changing it unless they have more than one CD/DVD Drive.

Hence the reason for trying to incorporate a SQL statement to look for the selected fldCDDefaultDrive that is set to "YES", in this case it is the D Drive in the tblCDDrive. In this case selected by the user. I was hoping to incorporate the response form this into the default of the combo box?

I hope this makes it clearer otherwise happy to clarify my problem.

Robert88

 

[ansentry]

Are we getting close to the correct answer.

 

[Robert88]

Hi ansentry,

I am trying to work out how you have done this.

the only code is;

Private Sub Combo3_AfterUpdate()
DoCmd.GoToControl "cmdDud"
End Sub

So where is cmdDud?

Robert88

 

[ansentry]

The cmdDud is just that a dub command button that does nothing but except the focus of the tab.

When a form opens as you know the tab goes to the first control on the tab list,you will see that I have cmdDud first on the list.

After the combo box is updated the tab focus would stay on the combo the code sends it to cmdDud. To make the button create a command button Format/Transparent = Yes call it cmdDud and put it at the top of the list for the "Details" tab order. I also could have used Me.cmdDud.SetFocus.

I have changed how it works ("much more correct") have a look and see what you think. Make sure you have a look at the query.

Go Pies! (That will make the "others" wonder)

 

[Robert88]

Hi ansentry,

Firstly, not another f.......g collingwood supporter, but wait a minute, Go the Pies!!!! (That will make the others wonder). But the AFL (Australia Football League) is a wonderful thing!!!!

Now back to the problem at hand......

After fosicking on this site for a while I managed to come across a solution, within the default property on the CboDriveName I managed to find a DLookup command.

=DLookUp("[fldDrivename]","[tblMediaDrive]","[fldDefaultDrive]= -1")

This seems to have done the trick.

I am now intrigued in your method as it might be easier if I get my users to make changes via forms rather than get them to adjust tables, probably have more control. Getting users to check to make sure they have not marked two "yes/no" fields as a default in order for the DLookup to work.

I will investigate your comments further in a couple of days and get back if I have any problems. Thanks for your help to date, appreciate it.

Robert88
Go the Pies!!!!