Combo Box

[JoseSharp]

Hi, I am trying to automate the options that appear in a drop down box. For Example if the user is an Approver (1) then I what them to see only Approver option in the drop down and if they are a Normal User (0) then, again, they would only see what is relevant to them. Below is the code I have entered but for some reason I keep getting data type mismatch errors. The 0 and 1 are classed as text within the table, so I have no idea why this is happening.

Does anyone know what is going wrong or even another way of doing this.

Any help is much appreciated

CODE:

Set frm = Forms!frmMain
Set dbs = CurrentDb
Dim varX As Variant
Dim varX2 As Variant
Dim strSQL As String

varX = DLookup("[Approver]", "Login", "[UserName] = Forms!frmMain!txtUser")

If varX = 1 Then

Set strSQL = "SELECT tblStatus.Status FROM tblStatus WHERE User = '" & 1 & "'"
Set rst = dbs.OpenRecordset(strSQL)
With rst
!Status.RowSource = rst
.Requery
End With
Else
Set rst = dbs.OpenRecordset("SELECT tblStatus.Status FROM tblStatus WHERE User = 0")
With frm
!Status.RowSource = rst
.Requery
End With
End If

 

[stopher]

Probably here:

        Set rst = dbs.OpenRecordset("SELECT tblStatus.Status FROM tblStatus WHERE User = 0")

If your field Status in tblStatus is a string then this will cause an error.

Suggest you use:

        Set rst = dbs.OpenRecordset("SELECT tblStatus.Status FROM tblStatus WHERE User = '0'")

Note the single quotes

I think I'd do the whole thing differently though. I'd avoid code altogether and just make the source query for your combo read:

"SELECT tblStatus.Status FROM tblStatus WHERE User ='" & Forms!frmMain!txtUser & "'"

Stopher

 

[stopher]

"SELECT tblStatus.Status FROM tblStatus WHERE User ='" & Forms!frmMain!txtUser & "'"

Sorry, I don't think the single quotes are needed here so your combo row source is just:
SELECT tblStatus.Status FROM tblStatus WHERE User = Forms!frmMain!txtUser

hth
Stopher