Run-time error 3075 (Access 2000)

Topic

New Reply

I have tried to figure this out for myself, but am stumped. I have added some additional fields to this form (cboAssocType,ActiveDt,InactiveDt), and the additional code to update the cells in this form to the fields in the corresponding fields in the table, but keep getting an error with the ActiveDt field. I know what this is somehow tied to the ShfitHrs field, but cannot seem to fix it.

Private Sub cmdAction_Click()
If Not IsNull(Me.txtOperator) Then
Select Case Me.cmdAction.Caption
Case “&Add”
If DCount(“username”, “tblAssociateList”, “username='” & Me.txtOperator & “‘”) > 0 Then
MsgBox “You have entered a duplicate operator”
Else
sSQL = “INSERT INTO tblAssociateList (username, manager, AssocType, shiftHrs, ActiveDt, InactiveDt) ” & _
“VALUES (‘” & Me.txtOperator & “‘, ‘” & Me.cboManager & “‘, ‘” & Me.cboAssocType & “‘, ‘” & Me.txtShiftHours & “‘, ‘” & Me.txtActvieDt & “)”
CurrentDb.Execute sSQL
Call cmdClear_Click
End If
Case “&Update”
sSQL = “UPDATE tblAssociateList ” & _
“SET username = ‘” & Me.txtOperator.Value & “‘, ” & _
“Manager='” & Me.cboManager.Value & “‘, ” & _
“AssocType='” & Me.cboAssocType.Value & “‘, ” & _
“ShiftHrs='” & Me.txtShiftHours.Value & “‘ ” & _
“ActiveDt='” & Me.txtActvieDt.Value & “‘ ” & _
“WHERE username = ‘” & Me.lstOperators & “‘”

CurrentDb.Execute sSQL
Call cmdClear_Click
Me.cmdAction.Caption = “&Add”
End Select

Would someone take a look at this and tell me where my error is.

Thanks!

Reply | Quote

Replies

The second block of sql has two errors in it:

sSQL = “UPDATE tblAssociateList ” & _
“SET username = ‘” & Me.txtOperator.Value & “‘, ” & _
“Manager='” & Me.cboManager.Value & “‘, ” & _
“AssocType='” & Me.cboAssocType.Value & “‘, ” & _
“ShiftHrs=” & Me.txtShiftHours.Value & ” , ” & _
“ActiveDt='” & Me.txtActvieDt.Value & “‘ ” & _
“WHERE username = ‘” & Me.lstOperators & “‘”

* Shifthrs is a number field so you don’t need to surround it with single quotes
* You left out the comma between ShiftHrs and ActiveDt

Reply | Quote

In a SQL statement, literal string values must be enclosed in quotes and literal date/time values must be enclosed in # characters (to avoid the date from being interpreted as a calculation). Literal numeric values should NOT be enclosed in anything.
You have included InactiveDt in the list of fields to be inserted, but not in the list of values.

The SQL for the Add part should be

sSQL = "INSERT INTO tblAssociateList (username, manager, AssocType, " & _
"shiftHrs, ActiveDt, InactiveDt) " & _
"VALUES ('" & Me.txtOperator & "', '" & Me.cboManager & "', '" & Me.cboAssocType & _
"', " & Me.txtShiftHours & ", #" & Me.txtActvieDt & "#, #" & Me.txtInactiveDt & "#)"


and the SQL for the Update part should be

sSQL = "UPDATE tblAssociateList " & _
"SET username = '" & Me.txtOperator.Value & "', " & _
"Manager='" & Me.cboManager.Value & "', " & _
"AssocType='" & Me.cboAssocType.Value & "', " & _
"ShiftHrs=" & Me.txtShiftHours.Value & ", " & _
"ActiveDt=#" & Me.txtActvieDt.Value & "#, " & _
"InActiveDt=#" & Me.txtInactiveDt.Value & "# " & _
"WHERE username = '" & Me.lstOperators & "'"


BTW You may want to correct txtActvieDt to txtActiveDt on the form and in the code.

Reply | Quote

Hans,

I am getting a Compile error, Method or data member not found. It seems to be hanging up on Me.txtShiftHours. Any ideas?

“VALUES (‘” & Me.txtOperator & “‘, ‘” & Me.cboManager & “‘, ‘” & Me.cboAssocType & _
“‘, ” & Me.txtShiftHours & “, #” & Me.txtActvieDt & “#, #” & Me.txtInactiveDt & “#)”

Thanks for finding that typo

Reply | Quote

Make sure that you copied the code correctly.
Also make sure that you have filled in all the data. If you want to allow the user to leave some data blank, the code will have to be adapted.

Reply | Quote

Had to fix those typos again. It is working correctly now. How much modification is needed to leave the InactiveDT field blank? the reason that I ask is because I have another simular form that has this same type of field (see code below) and this same field can remain blank and not cause any errors.

Select Case Me.cmdAction.Caption
Case “&Add”
sSQL = “INSERT INTO tblUserLog (UserID, UserName, NmFirst, NmLast, ActiveDt, AccessLevel, Password) VALUES (”
sSQL = sSQL & Me.txtUserID & “, ‘”
sSQL = sSQL & Me.txtUsername & “‘, ‘”
sSQL = sSQL & Me.txtFirstName & “‘, ‘”
sSQL = sSQL & Me.txtLastName & “‘, ‘”
sSQL = sSQL & Me.txtActiveDate & “‘, ”
sSQL = sSQL & Me.cboAccessLevel & “, ‘howitzer1’)”
Case “&Update”
sSQL = “UPDATE tblUserLog SET username = ‘” & Me.txtUsername & “‘, ”
sSQL = sSQL & “NmFirst = ‘” & Me.txtFirstName & “‘, ”
sSQL = sSQL & “NmLast = ‘” & Me.txtLastName & “‘, ”
sSQL = sSQL & “InActiveDt = ‘” & Me.txtInactiveDate & “‘, ”
sSQL = sSQL & “AccessLevel = ” & Me.cboAccessLevel
sSQL = sSQL & ” WHERE UserID = ” & Me.lstUsers
End Select

thanks!

Reply | Quote

You’re treating dates as strings in that code. You should change

sSQL = sSQL & "InActiveDt = '" & Me.txtInactiveDate & "', "

to

If IsNull(Me.txtInactiveDate) Then
sSQL = sSQL & "InActiveDt = Null, "
Else
sSQL = sSQL & "InActiveDt = #" & Me.txtInactiveDate & "#, "
End If

Reply | Quote

Hans,

Can that same If IsNull stmt be inserted in the code corrected code that you originally gave me?

Case “&Update”
sSQL = “UPDATE tblAssociateList ” & _
“SET username = ‘” & Me.txtOperator.Value & “‘, ” & _
“Manager='” & Me.cboManager.Value & “‘, ” & _
“AssocType='” & Me.cboAssocType.Value & “‘, ” & _
“ShiftHrs=” & Me.txtShiftHours.Value & “, ” & _
“ActiveDt=#” & Me.txtActiveDt.Value & “#, ” & _
“InActiveDt=#” & Me.txtInactiveDt.Value & “# ” & _
“WHERE username = ‘” & Me.lstOperators & “‘”

Reply | Quote

It would resemble the other code. Say that ShiftHrs and InactiveDate can be null:

Case "&Update"
sSQL = "UPDATE tblAssociateList " & _
"SET username = '" & Me.txtOperator.Value & "', " & _
"Manager='" & Me.cboManager.Value & "', " & _
"AssocType='" & Me.cboAssocType.Value & "', "
If IsNull(Me.txtShiftHours) Then
sSQL = sSQL & "ShiftHrs=Null, "
Else
sSQL = sSQL & "ShiftHrs=" & Me.txtShiftHours.Value & ", "
End If
ssQL = sSQL & "ActiveDt=#" & Me.txtActiveDt.Value & "#, "
If IsNull(Me.txtInactiveDt) Then
sSQL = sSQL & "InActiveDt=Null "
Else
sSQL = sSQL & "InActiveDt=#" & Me.txtInactiveDt.Value & "# "
End If
sSQL = sSQL & "WHERE username = '" & Me.lstOperators & "'"

Reply | Quote

Thanks again! It works beautifully, and with that I am calling it a night. I really appreciate all of the help that you give me.

nite

Reply | Quote

Back again! Seem to be running into one little problem. If I try to change update/change only the manager field for an associate, I get a run-time error…unless I put in an InactiveDt. I can then update the records and go back remove the InactiveDt and update again. Any ideas why this is happening? The db is now in Access 97 format.

Thanks

Reply | Quote

The txtInactiveDt text box isn’t really null, it contains an empty string “”. This is not the same from the viewpoint of Access. I don’t know what causes this, but there is an easy workaround: change the IsNull test to

If Trim(Me.txtInactiveDt) & "" = "" Then

This will handle both Null and “”.

Reply | Quote

The man who never sleeps. Thanks again!!!!

Reply | Quote