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??

[Megalog]

Dont have a lot of time today to help, so will try to get right to the point:

This is the only part that's still failing, correct?

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

Can you post the SQL code for "qryDistrictDef aultUser", AND also post these properties for Combo32:

Control Source:
Bound Column:
Column Count:

[AccessBeetle]

SELECT RadioLog_tblValDistrict.DistrictID, RadioLog_tblValDistrict.DistrictName, RadioLog_tblValDistrict.RegionID
FROM RadioLog_tblValDistrict
WHERE (((RadioLog_tblValDistrict.DistrictID)=DLookUp("[DistrictID]","RadioLog_tblValUsers","[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'")));

ControlSource:D istrictID
Bound Column:1
Column Count: 2

[Megalog]

It seems that your control is bound to the DistrictID field (which is what I was asking earlier). So the way that your code was set up, if a 'User' was logged in you would be setting that data to a default value, and then locking the field. This would change the data stored, which I'm guessing you wouldnt want to happen. You already have the filtering in place by using the WHERE statement inside the actual query, so my guess would be you could use this (based on the code you originally posted) to replace lines 25-30:

If DLookup("[Permission]", "RadioLog_tblValUsers", "[UserName]= '" & [Forms]![frmLogin]![txtUsername] & "'") = "user" Then  
        Me.Combo32.RowSourceType = "table/query"  
        Me.Combo32.RowSource = "qryDistrictDefaultUser"  
        Me.Combo32.Locked = True  
Else

If you're using the code I rewrote for you, then delete line 22

[AccessBeetle]

Sorry but that didn't work either. I don't know that if it is even possible. Same thing happens. It shows all the records when the form opens but when you hit on "Add Record", it will clears every record's DistrictId field (every other details are there showing up BIG). It does shows DistrictId for the records where DistrictID = currently logged in user's districtID.

[Megalog]

Ok I'm starting to lose track of what your problems exactly are. Now you're saying it works except for when you add a new record. Either I'm too dense to figure out what your scenario is exactly here, or you arent giving a good enough breakdown on your situation. So please try to back up a step and explain CLEARLY:

The form's main function
How this function differs for each user type that accesses it
What exactly are you doing with this combo box that is different from the rest of the controls

If anyone else sees the problem or understands the situation feel free to jump in here...