Help!

[Dan-goodridge]

i have this error using vb6 :
run-time error '3075'; syntax error (missing operator) in query expression 'Employee ID = '4000

Private Sub cmdin_Click()
Me.Data1.RecordSource = "select *  FROM Employee Clock WHERE Employee ID = '" & Me.txtin & "'"
Me.Data1.Refresh

Select Case Data1.Recordset.RecordCount

Case Is < 1 'no record found
MsgBox "Not an Employee", vbCritical + vbOKOnly, "user error"

Case Is > 1 'record found
If Data1.Recordset.Fields(1).Value = Me.txtin.Text Then
Select Case Data1.Recordset.Fields(2).Value
Case "Mechanic"
Load frmmech
frmmech.Show
Case Else
MsgBox "Your Have Not Been Set A Role Please Contact Administrater"
End
End Select
Unload Me
Else
MsgBox "Tra", vbCritical + vbOKOnly, "bye!"

End If

End Select

[gibra]

your SQL command:

"select *  FROM Employee Clock WHERE Employee ID = '" & Me.txtin & "'"

is wrong.
Names with space should be enclosed into brackets []

Then you must use:
[Employee Clock]
[Employee ID]

[Hack]

Also, if Employee ID is a numeric value there should be no surrounding single quotes and the textbox entry would need to be converted to a numeric.

[Ron Weller]

Actually because you are inserting the value of the text box into a string you don't need to convert it to a number. You are correct though that you don't want the surrounding single quote marks.
Again the is only if [Employee ID] in numeric, not text.


I noticed that you use Select Case statements for all of your checks. The Select Case is programatically a more complex type of statement. It should be used when you have multipule values for which each different value would trigger different code.

In your case all of your select case statements are simply testing for a specific value and if it is not that value it does something else. These types of checks would be better if you used the IF/THEN statements.

You just need to arrange them in the proper order. In each case you want to exit out if there is a wrong value is found. Here an example of your code using IF/THEN statements.

  Me.Data1.RecordSource = "select *  FROM [Employee Clock] WHERE [Employee ID] = '" & Me.txtin & "'"
  Me.Data1.Refresh

  If Data1.Recordset.RecordCount < 1 Then
    MsgBox "Not an Employee", vbCritical + vbOKOnly, "user error"
    Exit Sub
  End If

  'if your code gets this far the RecordCount must be >= 1

  If Data1.Recordset.Fields(1).value <> Me.txtin.Text Then
    MsgBox "Tra", vbCritical + vbOKOnly, "bye!"
    Exit Sub
  End If

  'if your code gets this far then Field(1) must equal the text box txin

  If Data1.Recordset.Fields(2).value <> "Mechanic" Then
    MsgBox "Your Have Not Been Set A Role Please Contact Administrater"
    Exit Sub
  End If

  'if your code gets this far then Field(2) must be "Mechanic"

  Load frmmech
  frmmech.Show
  Unload Me

[Dan-goodridge]

Actually because you are inserting the value of the text box into a string you don't need to convert it to a number. You are correct though that you don't want the surrounding single quote marks.
Again the is only if [Employee ID] in numeric, not text.


I noticed that you use Select Case statements for all of your checks. The Select Case is programatically a more complex type of statement. It should be used when you have multipule values for which each different value would trigger different code.

In your case all of your select case statements are simply testing for a specific value and if it is not that value it does something else. These types of checks would be better if you used the IF/THEN statements.

You just need to arrange them in the proper order. In each case you want to exit out if there is a wrong value is found. Here an example of your code using IF/THEN statements.

  Me.Data1.RecordSource = "select *  FROM [Employee Clock] WHERE [Employee ID] = '" & Me.txtin & "'"
  Me.Data1.Refresh

  If Data1.Recordset.RecordCount < 1 Then
    MsgBox "Not an Employee", vbCritical + vbOKOnly, "user error"
    Exit Sub
  End If

  'if your code gets this far the RecordCount must be >= 1

  If Data1.Recordset.Fields(1).value <> Me.txtin.Text Then
    MsgBox "Tra", vbCritical + vbOKOnly, "bye!"
    Exit Sub
  End If

  'if your code gets this far then Field(1) must equal the text box txin

  If Data1.Recordset.Fields(2).value <> "Mechanic" Then
    MsgBox "Your Have Not Been Set A Role Please Contact Administrater"
    Exit Sub
  End If

  'if your code gets this far then Field(2) must be "Mechanic"

  Load frmmech
  frmmech.Show
  Unload Me

when i try this peace of code i get error messagee saying too few parameter expected 1.
thanks

[Hack]

when i try this peace of code i get error messagee saying too few parameter expected 1.
thanks

On what line of code?

[Dan-goodridge]

the highlighted error is me.data1.refresh

[Ron Weller]

That line of code was in your original.
You might try:
Me.Data1.Recordset.Refresh

If that does not work try Requery instead of Refresh.
Of course changing the RecordSource may trigger a Requery automatically, so you may not even need that line of code.