• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1285
  • Last Modified:

CurrentDb syntax

I am having problems with the code below.

 Dim db As Database
 Dim rs As Recordset
 Dim tblname As String

 tblname = "tblTimeClock"  'change as needed
 Set db = CurrentDb

'find the master record
 Set rs = db.OpenRecordset("select EmployeeID, Date, ClockIN from " &
tblname & " where EmployeeID=" & Me!txtEmployeeID, DB_OPEN_DYNASET)

If rs.EOF And rs.BOF Then
  MsgBox "Master Record Deleted."
Else
  rs.Edit
  rs!Date = Me!txtDate 'match your field name
  rs!ClockIN = Me!txtTime  'match your field name
  rs.Update
End If
rs.Close

I would like to use this approach to add a new record with the employee's ID, current date and current time.
Although the form has the current date (txtDate) and current time(txtTime), would I be better off doing the following?
   rs!Date = Today()
   rs!ClockIN = Time()


TiA,
Die-Tech
0
Die-Tech
Asked:
Die-Tech
  • 7
  • 4
  • 3
  • +3
3 Solutions
 
Arthur_WoodCommented:
what problems are you having, as you failed to indicate in your posting.

AW
0
 
calpurniaCommented:
Are you using DAO or ADO?
0
 
Die-TechAuthor Commented:
I'm getting the following error message

     Method or data member not found

When you click on debug, it highlights the ".Edit" in rs.Edit under the "Else"


I think I'm using DAO, but I couldn't tell you for sure.

Below is the whole code for the command button....

Private Sub cmdOK_Click()
    Me!txtMarquee1.Visible = True
    Me!txtMarquee2.Visible = True
   
    Dim db As Database
    Dim rs As Recordset
    Dim tblname As String

    tblname = "tblTimeClockTEMP"  'change as needed
    Set db = CurrentDb

    'find the master record
    Set rs = db.OpenRecordset("select EmployeeID, Date, ClockIN from " &
tblname & " where EmployeeID=" & Me!txtEmployeeID, DB_OPEN_DYNASET)

    If rs.EOF And rs.BOF Then
        MsgBox "Master Record Deleted."
    Else
        rs.Edit
        rs!Date = Date   'match your field name
        rs!ClockIN = Time()  'match your field name
        rs.Update
    End If
    rs.Close
   
    Me!txtMarquee2 = "In for the day at " & Format(Now, "hh:mm AMPM")
   
End Sub



Thanks,
Dan
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
calpurniaCommented:
Well, try being explicit about using DAO.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
0
 
Die-TechAuthor Commented:
Ok... now I get the following error.

Run-time error '3464':

Data type mismatch in criteria expression.



Thanks,
Dan


0
 
Steve BinkCommented:
What line is being highlighted with the error?  Here's a couple guesses:

rs!Date = Date   'match your field name                  <---- is [Date] a Date/Time field?  Make sure you put the [] around Date!!  OR change the name of the field.
rs!ClockIN = Time()  'match your field name              <---- is ClockIN a Date/Time field?

You will have to either keep [Date] inside brackets, or change the name of the field.  I recommend changing the name of the field.  Date() is a built-in VBA function, and it is a good idea to not use the names of these functions or reserved words as field names.  Since Date() and Time() both return Date/Time variant objects, you should define those fields as such.  If you need it as text instead of Date/Time, use the following replacements:

rs![Date] = Date$
rs!ClockIN = Time$
0
 
Die-TechAuthor Commented:
After some discussions from another post I changed the code to the following.
After this change, I started getting the "Run-time error '3464': Data type mismatch in criteria expression"

Private Sub cmdOK_Click()
   
    Me!txtMarquee1.Visible = True
    Me!txtMarquee2.Visible = True
   
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tblname As String
   
    tblname = "tblTimeClockTEMP"  'change as needed
    Set db = CurrentDb

    'find the master record
    Set rs = db.OpenRecordset("select EmployeeID, ClockIN, LunchOUT, LunchIN, ClockOUT from " & tblname & " where EmployeeID=" & Me!txtEmployeeID, DB_OPEN_DYNASET)

    If rs.EOF And rs.BOF Then
        MsgBox "Master Record Deleted."
    Else
        rs.Edit
        rs!LunchOUT = Now()  'match your field name
        rs.Update
    End If
    rs.Close
   
    Me!txtMarquee2 = "Out to lunch at " & Format(Now, "hh:mm AMPM")

End Sub


I added calpurnia's suggestion:
    Well, try being explicit about using DAO.
       Dim db As DAO.Database
       Dim rs As DAO.Recordset

And all I'm really adding is the Now() for the LunchOUT field of this record,
so I took out the Date and Time stuff and added    rs!LunchOUT = Now()







0
 
Die-TechAuthor Commented:
Oh... on the what line is being highligted with the error?

The "Set rs = db.OpenRecordset("select EmployeeID, ClockIN, LunchOUT......." line is the one highlighted.

0
 
Die-TechAuthor Commented:
Ok... I tried something new and I think I've narrowed down the syntax problem.

I changed the following line
From
Set rs = db.OpenRecordset("select EmployeeID, ClockIN, LunchOUT, LunchIN, ClockOUT from " & tblname & " where EmployeeID=" & Me!txtEmployeeID, DB_OPEN_DYNASET)

to
Set rs = db.OpenRecordset("select EmployeeID, ClockIN, LunchOUT, LunchIN, ClockOUT from " & tblname & " where EmployeeID='123'", DB_OPEN_DYNASET)

By hardcoding the employeeID I was able to update the record with the LunchOUT time.

Since Me!txtEmployeeID is a text field on my form, should there be some kind of ' or " around it?  (like the '123' above)

Thanks,
Dan


0
 
Gustav BrockCIOCommented:
Then your ID is possibly a string. Add two single quotes:

    'find the master record
    Set rs = db.OpenRecordset("select EmployeeID, ClockIN, LunchOUT, LunchIN, ClockOUT from " & tblname & " where EmployeeID = '" & Me!txtEmployeeID & "'", DB_OPEN_DYNASET)

/gustav
0
 
Steve BinkCommented:
The relevence of the quotes is determined by the field data type, not the data type of the control on the form.  In other words, VBA considers all textbox controls to be text, whether or not they contain numbers.  SQL, however, only considers it to be text if it is delimited by quotes.  If the EmployeeID field in the table is text, use the quotes.  If it is a numeric type (integer, long, etc), do not use quotes.

Have you tried replacing DB_OPEN_DYNASET with dbOpenDynaset?  The constant you are using is a backwards compatible recordset type...change it to the correct type.  If you are still getting the error, remove the constant completely.  If you continue getting an error after that, we'll have to experiment with your SQL.
0
 
Die-TechAuthor Commented:
cactus_data,

You were correct, I changed the line to what you wrote (adding the two single quotes)
Now it's finding the record and adding the LunchOUT, LunchIN and ClockOUT times.

What if I want to create a new record.... such as when the employee clocks in for the day.

So far I'm using the DoCmd.RunSQL command to create the new record and then using the CurrentDb code to add the other times to the same record.


Thanks!
0
 
Steve BinkCommented:
If you are using a recordset, use rs.AddNew instead of rs.Edit.  Note that you will have to fill in all necessary fields before using the .Update method.  Check your table for any fields you have marked as required, without a default value, and not accepting zero-length strings.

Under most conditions, you can substitute "CurrentDB.Execute" for the "DoCmd.RunSQL" command, with no other necessary changes.  These two do the same thing:

DoCmd.RunSQL "INSERT INTO MyTable (field1, field2) VALUES (1,2)"
CurrentDB.Execute "INSERT INTO MyTable (field1, field2) VALUES (1,2)"
0
 
Steve BinkCommented:
Oops...forgot the other example.  This is also the same:

rs.AddNew
rs!f1 = 1
rs!f2 = 2
rs.Update
0
 
shanesuebsahakarnCommented:
Silly question perhaps, but why not use a bound form instead?
0
 
Die-TechAuthor Commented:
shanesuebsahakarn,

I'm going this route because I need to do some error checking along the way.
I want to make sure they have clocked in for the day before clocking out for lunch.
If they try to clock out for lunch, clock back in from lunch or clock out for the day without clocking in for the day they get an error on the screen telling then they missed a punch and need to see their supervisor about it.

I also have to pull up the "clocked in for the day" record and add the other punches to that record for that employee.... on this day.

Each employee should have one record in the table for each day they are at work.

Thanks,
Dan
0
 
Gustav BrockCIOCommented:
>    Me!txtMarquee2 = "Out to lunch at " & Format(Now, "hh:mm AMPM")

If you wish AM/PM display then this should read:

   Me!txtMarquee2 = "Out to lunch at " & Format(Now, "hh:mm AM/PM")

/gustav
0
 
shanesuebsahakarnCommented:
You can do all of the things you need with a bound form :) You might find it easier as you won't need any additional code to update the record (just a couple of lines of VBA code to locate the employee's record for the day).
0
 
calpurniaCommented:
Hi Dan,

Shane has a very good point here. You seem to be making things unecessarily hard for yourself. I can't see that there's anything so unusual about your project that it calls for a very non-standard approach.
0
 
Gustav BrockCIOCommented:
Dan, I second that recommendation.

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now