Solved

CurrentDb syntax

Posted on 2004-10-19
20
1,258 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
  • 7
  • 4
  • 3
  • +3
20 Comments
 
LVL 44

Expert Comment

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

AW
0
 
LVL 10

Expert Comment

by:calpurnia
Comment Utility
Are you using DAO or ADO?
0
 
LVL 4

Author Comment

by:Die-Tech
Comment Utility
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
 
LVL 10

Expert Comment

by:calpurnia
Comment Utility
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
Comment Utility
Ok... now I get the following error.

Run-time error '3464':

Data type mismatch in criteria expression.



Thanks,
Dan


0
 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 100 total points
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 50

Expert Comment

by:Steve Bink
Comment Utility
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
Comment Utility
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 50

Accepted Solution

by:
Steve Bink earned 400 total points
Comment Utility
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 50

Assisted Solution

by:Steve Bink
Steve Bink earned 400 total points
Comment Utility
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
Comment Utility
Silly question perhaps, but why not use a bound form instead?
0
 
LVL 4

Author Comment

by:Die-Tech
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
>    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
Comment Utility
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
Comment Utility
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 49

Expert Comment

by:Gustav Brock
Comment Utility
Dan, I second that recommendation.

/gustav
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now