Combobox on start form displays blank after sql server migration

[adhoustonj]

Hello,
I am facing a problem with a combobox on the start form of one of our db's after sql server migration.
When doing a debug.print the correct values are returned in the form_load procedure, but this combobox stays blank. I've added a requery on the combobox in the form_load procedure, but still blank. Do I need to go about this differently?

The row source code:

SELECT tblHS_area.hs_area_id, tblHS_area.hs_area
FROM tblHS_area
WHERE (((tblHS_area.sup)=[Forms]![frmStart]![txt_user]))
ORDER BY tblHS_area.hs_area;

I will add I feel silly asking this - The db and combobox works fine on my pc and is setup with a DSN-less connection to the sql server backend.
when distributing the FE for testing is when I noticed the combobox was not populating on other pc's.

Thanks

 

[theDBguy]

Are the other PCs also using a DSN-less connection? Can they see the table data?

 

[adhoustonj]

Yes they are also using the dsn-less connection and can see the table data.
the txt_user is a textbox on the start form that is populated in the form_load event.

 

[adhoustonj]

So I have got a little closer..

On my PC this combobox still works fine and on load contains a selected value.
The other pc's did not see any value selected or any values in the combobox dropdown.

I changed the form_load event to set the rowsource for the combobox, and instead of using [Forms]![frmStart]![txt_user] I used the 'emp' variable that I was using to set the txt_user field on the start form.

Now - It still loads blank on the other pc's, but values are there when opening the dropdown.

Me.txt_user = emp
Me.hs_area_id = hsid

'if supervisor has more than 1 hs area
strSQL = "SELECT tblEmployees.emp_id, tblEmployees.User, tblEmployees.hs_area_id, tblHS_area.sup " _
& "FROM tblEmployees INNER JOIN tblHS_area ON tblEmployees.emp_id = tblHS_area.sup " _
& "WHERE (((tblEmployees.User)=fosUserName()))"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
If rs.RecordCount > 0 Then
    rs.MoveLast
    If rs.RecordCount > 1 Then
    Me.hs_area_id.Visible = True
    End If

Me.hs_area_id.RowSource = "SELECT tblHS_area.hs_area_id, tblHS_area.hs_area FROM tblHS_area WHERE tblHS_area.sup = " & emp & " ORDER BY tblHS_area.hs_area;"