Error on first keystroke

G

Guest

I have a subform that I am trying to get to look up a tool number. the user
enters a tool number, then if it already exists, they are redirected to the
existing record. If not, they can continue to fill out the info and create a
new record. at the first keystroke, I get an error that says "You tried to
assign a NULL value to a variable that is not a variant data type." Now, I
know dealing with the error is one thing... and if anyone has suggestions on
that, I'd be more than happy to take them. But the question *for now* is why
is it doing this on the first keystroke? I want to be able to type in a
whole tool number, then when the user tabs off or clicks somewhere else, if
the record already exists in the tool table, it redirects them to the record.

The following code is what I'm trying to use to look up the tool number in
the subform, and is located in the code-behind page for the subform. SHould
it be in the subform control Sub in the Form3 (main form) page? (Subform
name = subTool2; subform *control* name = subTool)

Private Sub ToolNum_AfterUpdate()
Dim TNum As Variant
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
TNum = Form_subTool2.txtToolNum.Value

stDup = "ToolNum = " & "'" & TNum & "'"

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

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

'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.")
txtToolNum.SetFocus
End If
'Set rsc = Nothing
DoCmd.Save
End Sub


Thanks!!
Seren
 
A

Allen Browne

The timing of this error (first keystroke in a *new* record) sounds like a
Default Value problem.

Any chance this subform's RecordSource is a query that contains more than
one table? If so, you probably have the table you are trying to add a record
to as well as one or more lookup tables. And one of the lookups has a field
that has a Default Value set. When you try to add the new record, Access is
not smart enough to realize that you are not adding a field to the lookup
table, and so it tries to assign the value to that field. The attempt fails.

If that is the case, you can solve the issue by removing the Default Value
from the table, and also the Default Value from any text boxes on your form
that are not from the table you are trying to create the record in.

I have not examined your code in detail: since the Afterupdate event of the
text box should not fire on the first keystroke, I am assuming the problem
is elsewhere. If not the above, is there anything in the form's BeforeInsert
event?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Seren said:
I have a subform that I am trying to get to look up a tool number. the
user
enters a tool number, then if it already exists, they are redirected to
the
existing record. If not, they can continue to fill out the info and
create a
new record. at the first keystroke, I get an error that says "You tried
to
assign a NULL value to a variable that is not a variant data type." Now,
I
know dealing with the error is one thing... and if anyone has suggestions
on
that, I'd be more than happy to take them. But the question *for now* is
why
is it doing this on the first keystroke? I want to be able to type in a
whole tool number, then when the user tabs off or clicks somewhere else,
if
the record already exists in the tool table, it redirects them to the
record.

The following code is what I'm trying to use to look up the tool number in
the subform, and is located in the code-behind page for the subform.
SHould
it be in the subform control Sub in the Form3 (main form) page? (Subform
name = subTool2; subform *control* name = subTool)

Private Sub ToolNum_AfterUpdate()
Dim TNum As Variant
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
TNum = Form_subTool2.txtToolNum.Value

stDup = "ToolNum = " & "'" & TNum & "'"

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

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

'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.")
txtToolNum.SetFocus
End If
'Set rsc = Nothing
DoCmd.Save
End Sub


Thanks!!
Seren
Click to expand...
 
G

Guest

Nope, the Record Source for the subform is a single table. There is nothing
in the subTool2 Form's BeforeInsert. This is the only Sub on the page.
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Allen Browne said:
The timing of this error (first keystroke in a *new* record) sounds like a
Default Value problem.

Any chance this subform's RecordSource is a query that contains more than
one table? If so, you probably have the table you are trying to add a record
to as well as one or more lookup tables. And one of the lookups has a field
that has a Default Value set. When you try to add the new record, Access is
not smart enough to realize that you are not adding a field to the lookup
table, and so it tries to assign the value to that field. The attempt fails.

If that is the case, you can solve the issue by removing the Default Value
from the table, and also the Default Value from any text boxes on your form
that are not from the table you are trying to create the record in.

I have not examined your code in detail: since the Afterupdate event of the
text box should not fire on the first keystroke, I am assuming the problem
is elsewhere. If not the above, is there anything in the form's BeforeInsert
event?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Seren said:
I have a subform that I am trying to get to look up a tool number. the
user
enters a tool number, then if it already exists, they are redirected to
the
existing record. If not, they can continue to fill out the info and
create a
new record. at the first keystroke, I get an error that says "You tried
to
assign a NULL value to a variable that is not a variant data type." Now,
I
know dealing with the error is one thing... and if anyone has suggestions
on
that, I'd be more than happy to take them. But the question *for now* is
why
is it doing this on the first keystroke? I want to be able to type in a
whole tool number, then when the user tabs off or clicks somewhere else,
if
the record already exists in the tool table, it redirects them to the
record.

The following code is what I'm trying to use to look up the tool number in
the subform, and is located in the code-behind page for the subform.
SHould
it be in the subform control Sub in the Form3 (main form) page? (Subform
name = subTool2; subform *control* name = subTool)

Private Sub ToolNum_AfterUpdate()
Dim TNum As Variant
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
TNum = Form_subTool2.txtToolNum.Value

stDup = "ToolNum = " & "'" & TNum & "'"

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

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

'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.")
txtToolNum.SetFocus
End If
'Set rsc = Nothing
DoCmd.Save
End Sub


Thanks!!
Seren
Click to expand...
Click to expand...
 
A

Allen Browne

Okay, you need to isolate what is causing the error.

Which line is highlighed when the error occurs?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Seren said:
Nope, the Record Source for the subform is a single table. There is
nothing
in the subTool2 Form's BeforeInsert. This is the only Sub on the page.
--
Always behave like a duck- keep calm and unruffled on the surface but
paddle
like the devil underneath.


Allen Browne said:
The timing of this error (first keystroke in a *new* record) sounds like
a
Default Value problem.

Any chance this subform's RecordSource is a query that contains more than
one table? If so, you probably have the table you are trying to add a
record
to as well as one or more lookup tables. And one of the lookups has a
field
that has a Default Value set. When you try to add the new record, Access
is
not smart enough to realize that you are not adding a field to the lookup
table, and so it tries to assign the value to that field. The attempt
fails.

If that is the case, you can solve the issue by removing the Default
Value
from the table, and also the Default Value from any text boxes on your
form
that are not from the table you are trying to create the record in.

I have not examined your code in detail: since the Afterupdate event of
the
text box should not fire on the first keystroke, I am assuming the
problem
is elsewhere. If not the above, is there anything in the form's
BeforeInsert
event?

Seren said:
I have a subform that I am trying to get to look up a tool number. the
user
enters a tool number, then if it already exists, they are redirected to
the
existing record. If not, they can continue to fill out the info and
create a
new record. at the first keystroke, I get an error that says "You
tried
to
assign a NULL value to a variable that is not a variant data type."
Now,
I
know dealing with the error is one thing... and if anyone has
suggestions
on
that, I'd be more than happy to take them. But the question *for now*
is
why
is it doing this on the first keystroke? I want to be able to type in
a
whole tool number, then when the user tabs off or clicks somewhere
else,
if
the record already exists in the tool table, it redirects them to the
record.

The following code is what I'm trying to use to look up the tool number
in
the subform, and is located in the code-behind page for the subform.
SHould
it be in the subform control Sub in the Form3 (main form) page?
(Subform
name = subTool2; subform *control* name = subTool)

Private Sub ToolNum_AfterUpdate()
Dim TNum As Variant
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
TNum = Form_subTool2.txtToolNum.Value

stDup = "ToolNum = " & "'" & TNum & "'"

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

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

'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.")
txtToolNum.SetFocus
End If
'Set rsc = Nothing
DoCmd.Save
End Sub


Thanks!!
Seren
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

it doesn't give me an option to debug... so I'm working on figuring out how
to find the problem.
--
Always behave like a duck- keep calm and unruffled on the surface but paddle
like the devil underneath.


Allen Browne said:
Okay, you need to isolate what is causing the error.

Which line is highlighed when the error occurs?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Seren said:
Nope, the Record Source for the subform is a single table. There is
nothing
in the subTool2 Form's BeforeInsert. This is the only Sub on the page.
--
Always behave like a duck- keep calm and unruffled on the surface but
paddle
like the devil underneath.


Allen Browne said:
The timing of this error (first keystroke in a *new* record) sounds like
a
Default Value problem.

Any chance this subform's RecordSource is a query that contains more than
one table? If so, you probably have the table you are trying to add a
record
to as well as one or more lookup tables. And one of the lookups has a
field
that has a Default Value set. When you try to add the new record, Access
is
not smart enough to realize that you are not adding a field to the lookup
table, and so it tries to assign the value to that field. The attempt
fails.

If that is the case, you can solve the issue by removing the Default
Value
from the table, and also the Default Value from any text boxes on your
form
that are not from the table you are trying to create the record in.

I have not examined your code in detail: since the Afterupdate event of
the
text box should not fire on the first keystroke, I am assuming the
problem
is elsewhere. If not the above, is there anything in the form's
BeforeInsert
event?

I have a subform that I am trying to get to look up a tool number. the
user
enters a tool number, then if it already exists, they are redirected to
the
existing record. If not, they can continue to fill out the info and
create a
new record. at the first keystroke, I get an error that says "You
tried
to
assign a NULL value to a variable that is not a variant data type."
Now,
I
know dealing with the error is one thing... and if anyone has
suggestions
on
that, I'd be more than happy to take them. But the question *for now*
is
why
is it doing this on the first keystroke? I want to be able to type in
a
whole tool number, then when the user tabs off or clicks somewhere
else,
if
the record already exists in the tool table, it redirects them to the
record.

The following code is what I'm trying to use to look up the tool number
in
the subform, and is located in the code-behind page for the subform.
SHould
it be in the subform control Sub in the Form3 (main form) page?
(Subform
name = subTool2; subform *control* name = subTool)

Private Sub ToolNum_AfterUpdate()
Dim TNum As Variant
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
TNum = Form_subTool2.txtToolNum.Value

stDup = "ToolNum = " & "'" & TNum & "'"

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

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

'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.")
txtToolNum.SetFocus
End If
'Set rsc = Nothing
DoCmd.Save
End Sub


Thanks!!
Seren
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

search for existing record 1
Bookmark issues 6
Problems referencing controls on subform 4
second form opens w/ no records 1
saving wrong record when subform is blank 2
error 3162 in text box 7
if duplicate goto record 3
Run time error 2001 3

Top