Results 1 to 3 of 3

Thread: Error 3162

  1. #1
    Join Date
    May 2006

    Unanswered: Error 3162

    I have a form with a subform. The form holds job info, the subform holds tool info. When creating a record for a new job, the user should be able to type in a tool number in the subform and, if a record for that tool number already exists, the remaining fields in the subform are populated with info from that record. If there is not a record for that tool number that already exists, the user can enter the new data and create the record for it. My problem is this: When i try to add a new job number and then click in the ToolNum field to enter a tool number, I get the error "You tried to assign the NULL value to a variable that is not a Variant data type." I have seen this error, as I have searched for an answer before posting. But what I'm finding is that most of the time this question seems to pertain to combo boxes and occurs after something is entered and then deleted. That is not the case with my issue.

    Anyone have any suggestions for me as to how to fix this?

    Would be much appreciated.

    Thank you!!


  2. #2
    Join Date
    Sep 2003
    Is your subform a continuos form (have many rows possible)? How are you triggering the tool search upon typing in the tool #? I think that is where you're hitting the brick wall ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    I think so too. Actually, one thing is that in the following code, it's getting hung up on the TNum at the top, saying that an object is required... As far as how I'm triggering... before, it was once the tool number is typed in and then toolnum loses focus. it automatically searched through the code. My form and my subform are both in single form..

    Private Sub ToolNum_BeforeUpdate(Cancel As Integer)
    Dim TNum As String
    Dim stDup As String
    Dim ToolID As Integer
    Dim rsc As DAO.Recordset

    Set rsc = Form_Form3.subTool.Form.RecordsetClone

    Set TNum = Form_Form3.subTool.Form.ToolNum.Value
    stDup = "ToolNum = " & "'" & TNum & "'"

    ' check if toolnum already exists in tblTool
    'If DCount("ToolNum", "tblTool", stDup) > 0 Then
    If Trim(ToolNum.Text) = "" Then
    MsgBox ("You must enter a tool number")
    ElseIf DCount("ToolNum", "tblTool", stDup) > 0 Then
    MsgBox ("corresponding tool number was found")
    ' clear toolNum field
    ' set form to existing matching record
    ToolID = Nz(DLookup("[TOOLID]", "tblTool", "ToolNum ='" & TNum & "'"))

    Form_Form3.ToolID = ToolID
    Form_Form3.subTool.Form.RecordSource = "Select * from tblTool where ToolNum = '" & TNum & "'"

    'rsc.FindFirst stDup
    ' DoCmd.FindRecord TNum
    ' DoCmd.GoToRecord acTable, "subTool", acGoTo, TNum
    'Form_Form2.subTool.Form.Bookmark = rsc.Bookmark
    ' MsgBox ("you were taken to the corresponding tool number- Tool Number: " & ToolNum & _
    ", Boards Per Panel: " & BdsPerPanel)
    End If
    If IsNull(ToolNum) Then
    MsgBox ("You must enter a tool number to save this record.")
    End If
    'Set rsc = Nothing
    End Sub

    The irritating thing is that I know I got this working at one point. So I'm not sure why it's not now. And, even though it gives the error, it still allows me to attach the toolnum record to the job num record.

Posting Permissions

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