Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time error 3075 (Access 2000)

    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!

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Run-time error 3075 (Access 2000)

    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
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error 3075 (Access 2000)

    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
    <code>
    sSQL = "INSERT INTO tblAssociateList (username, manager, AssocType, " & _
    "shiftHrs, ActiveDt, InactiveDt) " & _
    "VALUES ('" & Me.txtOperator & "', '" & Me.cboManager & "', '" & Me.cboAssocType & _
    "', " & Me.txtShiftHours & ", #" & Me.txtActvieDt & "#, #" & Me.txtInactiveDt & "#)"
    </code>

    and the SQL for the Update part should be
    <code>
    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 & "'"
    </code>

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

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error 3075 (Access 2000)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

    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!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error 3075 (Access 2000)

    You're treating dates as strings in that code. You should change
    <code>
    sSQL = sSQL & "InActiveDt = '" & Me.txtInactiveDate & "', "
    </code>
    to
    <code>
    If IsNull(Me.txtInactiveDate) Then
    sSQL = sSQL & "InActiveDt = Null, "
    Else
    sSQL = sSQL & "InActiveDt = #" & Me.txtInactiveDate & "#, "
    End If</code>

  8. #8
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

    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 & "'"

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error 3075 (Access 2000)

    It would resemble the other code. Say that ShiftHrs and InactiveDate can be null:
    <code>
    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 & "'"</code>

  10. #10
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

    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 <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

    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

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Run-time error 3075 (Access 2000)

    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

    <code>If Trim(Me.txtInactiveDt) & "" = "" Then</code>

    This will handle both Null and "".

  13. #13
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error 3075 (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •