?
Solved

CurrentDb syntax

Posted on 2004-10-19
20
Medium Priority
?
1,278 Views
Last Modified: 2011-08-18
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
Comment
Question by:Die-Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
  • +3
20 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12349542
what problems are you having, as you failed to indicate in your posting.

AW
0
 
LVL 10

Expert Comment

by:calpurnia
ID: 12349627
Are you using DAO or ADO?
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350072
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 10

Expert Comment

by:calpurnia
ID: 12350456
Well, try being explicit about using DAO.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350555
Ok... now I get the following error.

Run-time error '3464':

Data type mismatch in criteria expression.



Thanks,
Dan


0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12350706
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350796
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12350931
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12351001
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
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 400 total points
ID: 12351062
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
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12351087
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
 
LVL 4

Author Comment

by:Die-Tech
ID: 12351355
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
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1600 total points
ID: 12351631
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
 
LVL 51

Assisted Solution

by:Steve Bink
Steve Bink earned 1600 total points
ID: 12351637
Oops...forgot the other example.  This is also the same:

rs.AddNew
rs!f1 = 1
rs!f2 = 2
rs.Update
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12355333
Silly question perhaps, but why not use a bound form instead?
0
 
LVL 4

Author Comment

by:Die-Tech
ID: 12356852
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 12356909
>    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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12363564
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
 
LVL 10

Expert Comment

by:calpurnia
ID: 12364314
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
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 12367652
Dan, I second that recommendation.

/gustav
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question