unbound and null

[kaib]

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

[MMcCarthy]

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

[ADezii]

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.

Me![txtTest].Text = vbNullString 
                 OR
Me![txtTest].Text = ""

Hope this helps..

[NeoPa]

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.

[kaib]

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)

Private Sub cmd_add_Click()
On Error GoTo Err_cmd_Add_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
    Set db = CurrentDb
    Set rs = db.OpenRecordset("time_entry", dbOpenDynaset)
    rs.AddNew
        'rs!ref_id = Me.txt_ref
        rs!EMP_CODE = Me.EMP_CODE
        rs!WORK_DATE = Me.Calendar2
        rs!START = Me.txt_start
        rs!LUNCH_OUT = Me.txt_lunchout
        rs!LUNCH_IN = Me.txt_lunchin
        rs!L_END = Me.txt_L_END
        rs!TOT_HRS = Me.txt_totalhrs
        rs!WORK_HRS = Me.tot_wkd_hrs
        rs!REG_HRS = Me.txt_reg_hrs
        rs!OT_HRS = Me.OTHRS
 
 
    rs.Update
 
    'Me.txt_ref.Text = ""
 
    Me.txt_start.Text = ""
    Me.txt_lunchout.Text = ""
    Me.txt_lunchin.Text = ""
    Me.txt_L_END.Text = ""
    Me.txt_totalhrs.Text = ""
    Me.tot_wkd_hrs.Text = ""
    Me.txt_reg_hrs.Text = ""
    Me.OTHRS.Text = ""
 
 
    Me.Requery
 
Exit_cmd_Add_Click:
    Exit Sub
 
Err_cmd_Add_Click:
    If Err.Number = 3315 Then 'zero-length string
        Resume Exit_cmd_Add_Click
    Else
        MsgBox Err.description
        Resume Exit_cmd_Add_Click
    End If
 
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;
-----------------------------------------------------------------------

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
 
If IsNull(Me.EMP_CODE) Or IsNull(Me.WORK_DATE) Or _
(Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
Me.Calendar2 = Me.Calendar2.OldValue) Then
'do nothing
Else
 
strWhere = "([WORK_DATE] = " & Format(Me.Calendar2, "\#mm/dd/yyyy\#") & ") " & _
" AND ([emp_code] = " & Me.EMP_CODE & ")"
 
'strWhere = "([calendar5] = """ & Me.Calendar5 & _
'""") AND ([emp_code] = """ & Me.EMP_CODE & """)"
 
varResult = DLookup("ref_id", "time_entry", strWhere)
 
If Not IsNull(varResult) Then
strMsg = "Record " & varResult & " in the table has the same name." & _
vbCrLf & "Undo record?"
 
If MsgBox(strMsg, vbOKOnly, "Dulicate Entry") = vbOK Then
 
'If MsgBox(strMsg, vbYesNo + vbDefaultButton2, _
'"Possible duplicate") <> vbYes Then
Cancel = True
Me.Undo
End If
End If
End If
End Sub

[NeoPa]

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.

If IsNull(Me.EMP_CODE) Or IsNull(Me.WORK_DATE) Or _
(Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
Me.Calendar2 = Me.Calendar2.OldValue) Then
'do nothing

[kaib]

yes work_date is a table field; so how can i get rid of this error
help appreciated
thanks

[NeoPa]

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.

[kaib]

is it because the values are cleared! the validation doesn't work; i'm lost
help appreciated
thanks

[NeoPa]

You need to replace Or IsNull(Me.WORK_DATE) with Or IsNull(Me.Calendar2).

If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
(Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
Me.Calendar2 = Me.Calendar2.OldValue) Then
'do nothing

[kaib]

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

[NeoPa]

You mean that error's fixed but you have another one?

[MMcCarthy]

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

[kaib]

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

[NeoPa]

Try putting the

If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
(Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
Me.Calendar2 = Me.Calendar2.OldValue) Then
...

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.

[kaib]

Try putting the

If IsNull(Me.EMP_CODE) Or IsNull(Me.Calendar2) Or _
(Me.EMP_CODE  = Me.EMP_CODE.OldValue And _
Me.Calendar2 = Me.Calendar2.OldValue) Then
...

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

[NeoPa]

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.

[kaib]

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