error 3162 in text box

[Guest]

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!!

Seren

 

[Guest]

This is happening in VBA code somewhere in your form module. Only Variant
data types can be assigned the value Null. If you attempt to assign Null to
any other data type, you will get this error.

To find where it is occuring, you will need to determine in which event the
error is created. What action precipitates the error? For example, if you
click a command button and the error occurs, the error is in the code
associated with the button's Click event.

Once you have determined where the error occurs, open your form in design
view, select the offending control, click on Properties, select the events
tab, and select the event. You can click on the box at the right with the 3
dots to open the VBA editor in that event. Select the first line of
executable code. This excludes function or sub declarations and dim
statements. Press F9 to create a break point.

Now open the form and do whatever will create the error. The code will go
into debug mode. You can step through the code line by line using F8 to see
where the error happens.

My guess, based on your post, it that you first look in the Before Update
and After Update events of the control where you enter a new job number.

 

[Guest]

Ok, I'm following along and come to two questions... first of all, I know
when the error is occuring. It's once I try to type anything into that text
field. So I assume this event would be OnChange? Next, considering this is
occuring in the subform, should I be working directly with the subform, or
within the main form? Next, there is no code in that procedure... therefore,
nothing to step through...

 

[Guest]

When you type anything into which text box, the Job Number on the main form
or the Tool Number on the sub form?
If there is no code in the Change event, then it could be in either the
Before or After update events. The reason to start with the main form is
that once you move to the subform, the database will attempt to update the
current record in the main form. It could be happening during that operation.

For future reference, I always put an error handler in every sub and
function. The messsage I present includes the name of the procedure and the
module so an error is always easy to find. Here is an example:

DeleteMaster_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DeleteMaster of VBA Document Form_frmAttributetable"
GoTo DeleteMaster_Exit
End Sub

 

[Guest]

When you try to type in the Tool Number on the sub form is when it is
occuring...

 

[Guest]

Then open the sub form in design view and see what events are associated with
that control.

 

[Guest]

Does this type of error handling go in its own function and then is called in
the procedure same as a regular function? Sorry, I've not used this very
much, and I'm teaching myself VBA as I go...

Thank you!
Seren

 

[Guest]

It is part of the procedure. You can learn more in VBA Help under On Error
Statement. Here is a complete example. In this case, we are opening a
report. If you open a report and there are no records available for the
report, it will throw an error 2501 even if you handle it in the report's
NoData event, so we don't want to raise an error to the user in this case:

Private Sub cmdPreview_Click()
Dim strWhere As String

On Error GoTo cmdPreview_Click_Error

strWhere = BuildWhere()
DoCmd.OpenReport SelectReport(), acViewPreview, , strWhere

cmdPreview_Click_Exit:

On Error Resume Next
Exit Sub

cmdPreview_Click_Error:

If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cmdPreview_Click of VBA Document
Form_frmLaborPVASelection"
GoTo cmdPreview_Click_Exit
End If
End Sub