Combobox does not show the correct value

[AccessBeetle]

Private Sub Form_Open(Cancel As Integer)
Combo34.SetFocus
If DLookup("[Permission]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = "mngr" Then
 
    If DLookup("[RegionID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = 1 Then
        Me.Combo32.RowSourceType = "Table/Query"
        Me.Combo32.RowSource = "qryRegion1"
            Else
    If DLookup("[RegionID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = 2 Then
        Me.Combo32.RowSourceType = "Table/Query"
        Me.Combo32.RowSource = "qryRegion2"
    Else
    If DLookup("[RegionID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = 3 Then
        Me.Combo32.RowSourceType = "Table/Query"
        Me.Combo32.RowSource = "qryRegion3"
    End If
    End If
    End If
 
Else
If DLookup("[Permission]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = "spvr" Then
         Me.Combo32.RowSourceType = "Table/Query"
        Me.Combo32.RowSource = "qryHQ"
Else
If DLookup("[Permission]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = "user" Then
        Me.Combo32.RowSourceType = "table/query"
        Me.Combo32.RowSource = "qryDistrictDefaultUser"
        Me.Combo32.DefaultValue = DLookup("[DistrictID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
        Me.Combo32.Locked = True
Else
        Me.Combo32.RowSourceType = "Table/Query"
        Me.Combo32.RowSource = "RadioLog_tblValDistrict"
End If
End If
End If
 
End Sub
 

I have above code written which sets the row source property for combo34(Distric tID). I use this code because the value of this combobox should be different for different users. .The form is bound to the table called RadioCallActivi ty.
Question:
Suppose I logged in as a staff user, I can see the records entered by another people but the Combo34(Distric tID) is blank. It does not show any values in it.
How to fix it?
Thanks

[missinglinq]

Is the form frmLogIn open when this code is run?

What version/service pack are you running? Version 2003 with the SP3 service pack installed can have "disappeari ng" data if the subsequent hotfix is not applied,

Linq ;0)>

[AccessBeetle]

I am using SP 2. The form frmLogin is open but hidden. Is there anything else I have to consider to make it work?
THanks

[Megalog]

This is a bit confusing, especially all the nested If's..
So this works, except if you're a 'staff user'? What permission is that exactly, 'user'? If so, then your problem might be the 'Me.Combo32.Def aultValue' should be changed to 'Me.Combo32.Val ue' (in line 22)

Few suggestions by the way....

First, here's a modified version of what you posted, using ElseIf instead of all the extra If statements:

Private Sub Form_Open(Cancel As Integer)
    Combo34.SetFocus
    Dim txtPermission As String
    Dim lngRegion As Long
 
    txtPermission = DLookup("[Permission]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
    lngRegion = DLookup("[RegionID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
 
    Me.Combo32.RowSourceType = "Table/Query"
    If txtPermission = "mngr" Then
        If lngRegion = 1 Then
            Me.Combo32.RowSource = "qryRegion1"
        ElseIf lngRegion = 2 Then
            Me.Combo32.RowSource = "qryRegion2"
        ElseIf lngRegion = 3 Then
            Me.Combo32.RowSource = "qryRegion3"
        End If
    ElseIf txtPermission = "spvr" Then
        Me.Combo32.RowSource = "qryHQ"
    ElseIf txtPermission = "user" Then
        Me.Combo32.RowSource = "qryDistrictDefaultUser"
        Me.Combo32.DefaultValue = DLookup("[DistrictID]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")
        Me.Combo32.Locked = True
    Else
        Me.Combo32.RowSource = "RadioLog_tblValDistrict"
    End If
 
End Sub

Also, this login form should be placing all these values into global variables, instead of you having to call on the specific form itself everytime with the Dlookups.

[AccessBeetle]

user=staff user. they are same. Tried to using global variables but there was an error I could not solve so I replaced it with this chunk of code.
Also, user and staff user are same.
No, If I logged in as a mngr (of region 2: Districts 1,2,3,4,5), it will show every details of the previously entered records but the DistrictID. The combobox is blank. but it will show every detail, including DistrictID, for the records where DistrictID =1 or 2 or 3 or 4 or 5
I hope you understand.
Thanks for suggestions anyways!!

[Megalog]

I'd doublecheck the queries that you're assigning to the combo box to make sure they are correctly filtering your results.

Open them up in design view, specify the criteria manually (your region #'s), and make sure the data being returned is valid.

Also, in 'qryDistrictDef aultUser', make sure the first column (or whichever column you're binding your combo box to) is also the DistrictID

[AccessBeetle]

They are filtering records correctly.I checked it yet once more.

Also, in 'qryDistrictDef aultUser', make sure the first column (or whichever column you're binding your combo box to) is also the DistrictID

what does this mean?

[AccessBeetle]

Could it be a problem as this code is written in Form's open event?
Which event takes place when you exactly start to enter data?

[Megalog]

You can try shifting it to the Load event, instead of Open. And maybe remove the SetFocus command in the first line. Although I'm not sure either move will help.

The sequence of events goes:
Open - The form isnt bound to the recordset yet, this is only creating the controls
Load - Form is bound to the dataset
Current - This is triggered when loading the first record, AND everytime you move to a new record

[AccessBeetle]

You were right. None of it helped? I don't where should I see for it??