By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

unbound and null

P: 23
i've used for data entry some unbound text boxes
they are of number, date/time



user will enter data into these fields; when click 'Add New' button,
rs will update and these unbound controls to be cleared by assinging 'null' value to it; but when i click 'add new' button i get an error like this:

"you tried to assign the Null value to a variable that is not a Variant data type"

pls help me
thanks
Dec 9 '06 #1
Share this Question
Share on Google+
17 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry Kaib

You will have to post the code behind the add new button and tell us what line is highlighted when the code stops.

Mary
Dec 9 '06 #2

ADezii
Expert 5K+
P: 8,669
i've used for data entry some unbound text boxes
they are of number, date/time



user will enter data into these fields; when click 'Add New' button,
rs will update and these unbound controls to be cleared by assinging 'null' value to it; but when i click 'add new' button i get an error like this:

"you tried to assign the Null value to a variable that is not a Variant data type"

pls help me
thanks
'I'll assume the Unbound Controls into which you are entering data are Text
'Boxes. Do not attempt to clear them out by assigning the Null value to them, but rather the vbNullStyring or the empty string ("") e.g.
Expand|Select|Wrap|Line Numbers
  1. Me![txtTest].Text = vbNullString 
  2.                  OR
  3. Me![txtTest].Text = ""
Hope this helps..
Dec 10 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
I'm not sure that's right ADezii.
I think this is referring to a DIMmed variable in the code. Most form controls take Nulls ok. Even Record Fields are likely to (depending on set up rather simply their type).
We need to go back to Mary's post and request the user posts the relevant code used.
Dec 10 '06 #4

P: 23
scenario is like this:

i put some unbound controls in header of a continous form; after user enters the data when he [press 'add new' unbound cntrols are cleared and down records are added; but two controls one is combo which look the em[ployee names into
em[p.master file; another is a calendar ocx; the rest are normal text boxes;
below the code given under 'add new'; how can i clear the value of combo and calendar;(both used for the easy data entry)

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_add_Click()
  2. On Error GoTo Err_cmd_Add_Click
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Dim strsql As String
  6.     Set db = CurrentDb
  7.     Set rs = db.OpenRecordset("time_entry", dbOpenDynaset)
  8.     rs.AddNew
  9.         'rs!ref_id = Me.txt_ref
  10.         rs!EMP_CODE = Me.EMP_CODE
  11.         rs!WORK_DATE = Me.Calendar2
  12.         rs!START = Me.txt_start
  13.         rs!LUNCH_OUT = Me.txt_lunchout
  14.         rs!LUNCH_IN = Me.txt_lunchin
  15.         rs!L_END = Me.txt_L_END
  16.         rs!TOT_HRS = Me.txt_totalhrs
  17.         rs!WORK_HRS = Me.tot_wkd_hrs
  18.         rs!REG_HRS = Me.txt_reg_hrs
  19.         rs!OT_HRS = Me.OTHRS
  20.  
  21.  
  22.     rs.Update
  23.  
  24.     'Me.txt_ref.Text = ""
  25.  
  26.     Me.txt_start.Text = ""
  27.     Me.txt_lunchout.Text = ""
  28.     Me.txt_lunchin.Text = ""
  29.     Me.txt_L_END.Text = ""
  30.     Me.txt_totalhrs.Text = ""
  31.     Me.tot_wkd_hrs.Text = ""
  32.     Me.txt_reg_hrs.Text = ""
  33.     Me.OTHRS.Text = ""
  34.  
  35.  
  36.     Me.Requery
  37.  
  38. Exit_cmd_Add_Click:
  39.     Exit Sub
  40.  
  41. Err_cmd_Add_Click:
  42.     If Err.Number = 3315 Then 'zero-length string
  43.         Resume Exit_cmd_Add_Click
  44.     Else
  45.         MsgBox Err.description
  46.         Resume Exit_cmd_Add_Click
  47.     End If
  48.  
  49. End Sub
now when click add new button i get the following error:
"you cannot reference a [pro[perty or method for a control unless the control has the focus"

i did n't get it [pro[perly!
below the code for the validation so that user cannot enter duplicate time and attendance for a particular employee on same workdate; the thing is getting highlighted is in italics in code;
-----------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. Dim strWhere As String
  3. Dim varResult As Variant
  4. Dim strMsg As String
  5.  
  6. If IsNull(Me.EMP_CODE) Or IsNull(Me.WORK_DATE) Or _
  7. (Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
  8. Me.Calendar2 = Me.Calendar2.OldValue) Then
  9. 'do nothing
  10. Else
  11.  
  12. strWhere = "([WORK_DATE] = " & Format(Me.Calendar2, "\#mm/dd/yyyy\#") & ") " & _
  13. " AND ([emp_code] = " & Me.EMP_CODE & ")"
  14.  
  15. 'strWhere = "([calendar5] = """ & Me.Calendar5 & _
  16. '""") AND ([emp_code] = """ & Me.EMP_CODE & """)"
  17.  
  18. varResult = DLookup("ref_id", "time_entry", strWhere)
  19.  
  20. If Not IsNull(varResult) Then
  21. strMsg = "Record " & varResult & " in the table has the same name." & _
  22. vbCrLf & "Undo record?"
  23.  
  24. If MsgBox(strMsg, vbOKOnly, "Dulicate Entry") = vbOK Then
  25.  
  26. 'If MsgBox(strMsg, vbYesNo + vbDefaultButton2, _
  27. '"Possible duplicate") <> vbYes Then
  28. Cancel = True
  29. Me.Undo
  30. End If
  31. End If
  32. End If
  33. End Sub
Dec 10 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
You refer to Me.WORK_DATE on that line but nowhere else.
I would guess that WORK_DATE is a table field and not an entry on your form.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.EMP_CODE) Or IsNull(Me.WORK_DATE) Or _
  2. (Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
  3. Me.Calendar2 = Me.Calendar2.OldValue) Then
  4. 'do nothing
Dec 10 '06 #6

P: 23
yes work_date is a table field; so how can i get rid of this error
help appreciated
thanks
Dec 11 '06 #7

NeoPa
Expert Mod 15k+
P: 31,660
yes work_date is a table field; so how can i get rid of this error
help appreciated
thanks
So let me get this straight, you know the problem but you can't work out what to do to fix it?
How far have you got?
I want to avoid spoon-feeding you, you see. This does seem a little basic.
Dec 11 '06 #8

P: 23
is it because the values are cleared! the validation doesn't work; i'm lost
help appreciated
thanks
Dec 11 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
You need to replace Or IsNull(Me.WORK_DATE) with Or IsNull(Me.Calendar2).
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
  2. (Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
  3. Me.Calendar2 = Me.Calendar2.OldValue) Then
  4. 'do nothing
Dec 11 '06 #10

P: 23
i tried but it doesn't work; i understand something; the error "you cannot reference a property or method for a control unless the control has the focus"

i remmed all the validation codes and still this error showing up! is it anywhere the ME is losing its focus! id don't understand...it doesn't come as highlighted instead with a heading micrsoft access;
i'm stuck
Dec 11 '06 #11

NeoPa
Expert Mod 15k+
P: 31,660
You mean that error's fixed but you have another one?
Dec 11 '06 #12

MMcCarthy
Expert Mod 10K+
P: 14,534
i tried but it doesn't work; i understand something; the error "you cannot reference a property or method for a control unless the control has the focus"

i remmed all the validation codes and still this error showing up! is it anywhere the ME is losing its focus! id don't understand...it doesn't come as highlighted instead with a heading micrsoft access;
i'm stuck
If you click debug it should show the line of code highlighted in yellow that has the problem.

Mary
Dec 11 '06 #13

P: 23
You mean that error's fixed but you have another one?
in fact i want a validataion before rs.update if for one employee duplicate workdates are entered; i remmed my code under Form_BeforeUpdate so there are no errors; but i lost my validation
could u assisit me validating these dates....!
thanks
Dec 12 '06 #14

NeoPa
Expert Mod 15k+
P: 31,660
Try putting the
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
  2. (Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
  3. Me.Calendar2 = Me.Calendar2.OldValue) Then
  4. ...
before your rs.AddNew line.
YOU will have to work out how to do that properly. It's not difficult.
BTW your Form_BeforeUpdate was never triggered because you weren't doing the add via the bound form.
Dec 12 '06 #15

P: 23
Try putting the
Expand|Select|Wrap|Line Numbers
  1. If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
  2. (Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
  3. Me.Calendar2 = Me.Calendar2.OldValue) Then
  4. ...
before your rs.AddNew line.
YOU will have to work out how to do that properly. It's not difficult.
BTW your Form_BeforeUpdate was never triggered because you weren't doing the add via the bound form.
i keep trying but nothing work out
Dec 14 '06 #16

NeoPa
Expert Mod 15k+
P: 31,660
i keep trying but nothing work out
Kaib,

You keep asking questions but you never seem to pass any info back.
What have you tried and where did it fail.
You seem to think it is our job to do all the chasing while you sit back and just pick up the results.
Without some signs that you are doing some work to help your own situation I'm not prepared to run around after you like one of my children when they were young enough to need it.
This is your problem and we are helpers. Not the other way around.
Show what you are doing and where your understanding needs help and we will help.
Dec 14 '06 #17

P: 23
Kaib,

You keep asking questions but you never seem to pass any info back.
What have you tried and where did it fail.
You seem to think it is our job to do all the chasing while you sit back and just pick up the results.
Without some signs that you are doing some work to help your own situation I'm not prepared to run around after you like one of my children when they were young enough to need it.
This is your problem and we are helpers. Not the other way around.
Show what you are doing and where your understanding needs help and we will help.
thanks for ur comments; it's working now; but i gave up the DAO method
ur help is not misused; rather respected & valued
thanks again
Dec 14 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.