Do while loop and if else problem

[Ultra King]

HELLO ALL...

I have table name tbl_TimeRecord. It has 3 fields that name EmpID, Sign_Time, and Mark. I use a form name FormUser to add new record into tbl_TimeRecord..
In this form it has 3 text box by name txtEmpID, txtSign_Time, and txtMark. User only need to key in their ID in txtEmpID and the other will store automatically using vba code. But the result is not like what I expect it should be, because there is no value of Mark at all in tbl_TimeRecord. Can anyone help me in this matter, below is an example of my vba code

Private Sub txtEmpID_AfterUpdate()
    Me.txtSign_Time = Now()
    Dim tdyDate As Date
    Dim k As Integer
    Dim m As Integer
    m = 1
    k = 1
    Do While tdyDate = Now()
        If Me.txtEmpID = DLookup("EmpID", "tbl_TimeRecord") Then
            Me.txtMark = Int(m)
        Else
            Me.txtMark = Int(k)
        End If
        m = m + 1
    Loop
    
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNewRec
End Sub

 

[pbaldy]

I think nobody is answering because they don't know where to start. I have no idea what the loop is supposed to accomplish. You declare but never set tdyDate, so it will never be equal to Now(), which contains the time by the way. Without a criteria your DLookup() will always return the first record in the table. What is the value in Mark supposed to represent?

 

[JHB]

First, you declare tdyDate variable, but you never added a value to it, so Do While tdyDate = Now() will never become true.

Second, you need a criteria in the DLookup, (take a look in the MS-Access Help file, how to use DLookup).

If Me.txtEmpID = DLookup("EmpID", "tbl_TimeRecord","EmpID=" & Me.txtEmpID) Then

I would use an Update query instead of DoCmd.RunCommand acCmdSaveRecord, DoCmd.GoToRecord , "", acNewRec.
But what exactly is it you want to achieve with the Do While tdyDate = Now() loop?

 

[Ultra King]

I am sorry that I did not explain it clearly... Actually I want my table result look like this.
As you can see, at difference date, Mark start from 1 for any user...
Actually, the Do While tdyDate = Now() is meant to set the date for today date only... or do I need to use Do While tdyDate = Date or I got it all wrong from the start..

For the record, I am new user for vba. Please teach me..

 

[Mihail]

Database Normalization and Table Structures - Microsoft Access / VBA
Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com

The first link is for your information.
One DB rule is to not store something that you can calculate. And your field "Mark" has calculated (somehow) values.

The second one show you how to work with data in tables (and not only).

For now, I think that the best you can do is to inform us about what you have (at all) and what you wish to obtain. Why you need those values in the Mark field ? Where / How you intend to use it after you will have it ?

 

[JHB]

Actually, the Do While tdyDate = Now() is meant to set the date for today date only...

No it is totally wrong you are not setting the tdyDate, you are comparing if tdyDate = Now().
When you use Do While tdyDate = Now(), then the program understand it so:
Do the loop until the value of tdyDate is equal to the value returned from the Now() function, it is quite elementary. Use the MS-Help file when you are implementing something, until you are aware of how things work.
If you want to assign a value to tdyDate then it must separated like below:

    Dim tdyDate As Date
   ...
   tdyDate = Now() 
   ...