If Exist Update (1 Viewer)

Pavczech

Registered User.
Local time
Today, 09:06
Joined
Jul 11, 2012
Messages
41
Hi guys

Could anybody help. I need a code to update my tables. Basicaly I have 2 forms.
1 table with Name, Surname, DOB etc.
2 table with Date, TimeIn and TimeOut.
1 query with all details

I have Dlookup code which finds out the ID
DLookup("[ID]", "[Table1]", "[FirstName] ='" & Forms![Form1]![FirstName] & "' AND [Surname] ='" & Forms![Form1]![Surname] & "' AND [DoB] ='" & Forms![Form1]![DOB] & "' ")

I have text box with "TimeOut" which needed to be update right record.

Does this make sense.

Thanks very much
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 02:06
Joined
May 3, 2012
Messages
636
Not sure what you are trying to do but if DOB is a datetime field you need to change your criteria to read ..." AND [dob] = #" & Forms!Form1]!DOB] & "#")

date criteria should be enclosed by pound signs in Access.
 

Pavczech

Registered User.
Local time
Today, 09:06
Joined
Jul 11, 2012
Messages
41
I have attached short version of db.

What I need to happen is when you open query2 form and input surname and Dob and click submit it will just update the "TimeOut" for that record. If nothing found there would be message box.

Thanks for the previous post.

I am sort of learning in my own pace but it is not easy.

Thanks very much
 

Attachments

  • Database5.accdb
    1.1 MB · Views: 77

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 02:06
Joined
May 3, 2012
Messages
636
You have it working already but you are setting timeout = some dlookup function. Change it to this:
Code:
Private Sub Command9_Click()
If DCount("[Surname]", "[Query1]", "[Surname] ='" & Forms![Query2]![Surname] & "' AND [DoB] =#" & Forms![Query2]![DoB] & "# ") > 0 Then
[COLOR=red][COLOR=seagreen]'Me!TimeOut = DLookup("[ID]", "[Query1]", "[Surname] ='" & Forms![Query2]![Surname] & "' AND [DoB] =#" & Forms![Query2]![DoB] & "# ")[/COLOR]
[/COLOR]Me.TimeOut = Now()
Else
MsgBox "Details Not Found, Try again!"
DoCmd.Close
End If
End Sub
 

Pavczech

Registered User.
Local time
Today, 09:06
Joined
Jul 11, 2012
Messages
41
Hi thanks, but
If i do that it give me error you can't assign value to the object.
If i take it to immediate window and take it apart
?dlookup gives me the right ID, Time () gives me the time but how do i put it all in to the TimeOut?
If i do in the immediate window Me!TimeOut = Dlookup(... It just give me null.
Any idea
Thanks
 

AccessMSSQL

Seasoned Programmer-ette
Local time
Today, 02:06
Joined
May 3, 2012
Messages
636
You are trying to put the value if ID in the TimeOut field. TimeOut is a date time field. You can't put the integer value in a datetime field. This is why you are getting an error. What value are you trying to put in TimeOut? I'm not understanding what you are trying to accomplish. I took your db and modified the code as above and it works fine for me. See screenshot.
 

Attachments

  • TestACCDB.jpg
    TestACCDB.jpg
    17.5 KB · Views: 109

Pavczech

Registered User.
Local time
Today, 09:06
Joined
Jul 11, 2012
Messages
41
Hi Thanks sorry I was away.

Basically what I need to do is when I click the button it would update existing record.

I not sure why but when I put the ?Dlookup... it gives me right ID but it doesn't work with Me.TimeOut = DLookup

It only creates new records instead updating the old ones
 

Pavczech

Registered User.
Local time
Today, 09:06
Joined
Jul 11, 2012
Messages
41
Don't worries I have used docmd.runsql instead thanks for the other tips anyway
 

Users who are viewing this thread

Top Bottom