Default Combo Box Based On Default Table Value

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 = """D"""

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 = """Drivename"""

End Sub

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

Robert88