Help with linking a combobox

[rywello]

I have the below code and to link a subform and a form based on the combobox value. I get a data type mismatch on the code highlighted in orange. It is a number field so I am not sure why the error. Also the subform is based on a query. Any help is appreciated.

Private Sub cboEmployee_AfterUpdate()

strSQL = "Select * from dbo_EMPLOYEES1"
Me.tblDays_subform.Form.RecordSource = strSQL

'On Error GoTo combo_cust_AfterUpdate_Err

DoCmd.SearchForRecord , "", acFirst, "[EMP_NB] = " & Str(Nz(Screen.ActiveControl, 0))



Dim rs As DAO.Recordset

Set rs = Me.tblDays_subform.Form.RecordsetClone

If rs.RecordCount = 0 Then

MsgBox ("No overtime for this analyst.")

End If



Me.cboEmployee.Value = Null

cboEmployee.SetFocus
End Sub

 

[Minty]

By using the Str() command you are changing the number to a string field. Hence the type mis-match.

Also it's far more usual to refer to the actual combo than screen.activecontrol

So I would use something like

 DoCmd.SearchForRecord , "", acFirst, "[EMP_NB] = " & [COLOR="Red"]Me.YourComboControlName[/COLOR]

In fact I would also restrict the combo to also only list people who have results to find - then you don't need the second part at all.

 

[rywello]

Thanks! I no longer get the type mismatch or any errors but the subform is not populating. Is Me.tblDays_subform.Form.RecordSource = strSQL correct when referring to the subform?

 

[Minty]

It certainly looks about right, however you can probably do this without code;

Permanently set the subform recordsource to the employees1 table.
Then set the Parent child properties in your sub form container to refer to the combo as the parent link.

(Always worry when I see a table like employees1 - hope there isn't an employees2.... )

 

[arnelgp]

Private Sub cboEmployee_AfterUpdate()

strSQL = "Select * from dbo_EMPLOYEES1"
with Me.tblDays_subform.Form
   .RecordSource = strSQL
   .Requery
   Set rs = .RecordsetClone
   If rs.RecordCount = 0
     Msgbox "No overtime for this analyst"
  Else
      rs.FindFirst "[EMP_NB] = " & Me.cboName
      If Not rs.NoMatch Then
          .Bookmark = rs.Bookmark
      Else
          Msgbox "No overtime for this analyst"
      End If
  End If
   Set rs = Nothing
End With

Me.cboEmployee.Value = Null

cboEmployee.SetFocus
End Sub

 

[rywello]

I changed the table name to Employees.


Now I am I am getting the error Syntax error (missing Operator) in expression on the below line:

rs.FindFirst "[EMP_NB] = " & Me.cboEmployee

I have the 2 forms linked by EMP_NB.

I tried to attach the DB but keep getting an error.

 

[rywello]

Here it is. I am also trying to get the shift types in each date across kind of like a calendar but not sure if that is possible with these forms. Thanks!

 

[arnelgp]

Did you change the combo name too?

 

[Minty]

Your data is not normalised and you aren't storing the correct information in the right places in order to produce the information you need.

You have committed what someone once helpfully called "A Spreadsheet" - a sin in the Access world.

You have a table of employees - this is good.
What you should then store is simply the unique EmployeeId and the StartDate and EndDate of each shift they work, possibly with a WorkTypeID if they maybe work on different things on different shifts.

This would give you enough to create almost any data report you could want, you can calculate working hours, days, etc etc.

If you need to produce a Calendar type view they are a little tricky for a new Access user, as they really need to be dynamic, without you necessarily having data for every point in the period you are look at.

Create the simple table for the shifts worked, add some dummy data, and we can show a simple method for displaying that information , almost entirely without code.

Your current table design is only going to give you a million problems, that will multiply out over time.

 

[rywello]

Ok, sounds good.

arnelgp : No I did not change the combobox name. Why would I be getting the below error?


Now I am I am getting the error Syntax error (missing Operator) in expression on the below line:

rs.FindFirst "[EMP_NB] = " & Me.cboEmployee

I have the 2 forms linked by EMP_NB.

 

[Minty]

Honestly the code is virtually unnecessary if you store the data and create the forms correctly.
If the forms are joined by emp_ID the recordsource and find are redundant.