Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    4

    Unanswered: connecting to a table in access using VBA?

    I have been using access 2000 for a couple years now to edit data and write reports against an oracle db. I have just recently needed to add a module to do some of the things that I need to do. I am trying to make a barcode labor entry screen that uses an estimated time for a scanned in operation and subtracts that time from the previous time. this screen will be used in a batch mode so that the user can scan in about 30 parts at a time and walk away. the table I want to open stores one record which is the current date that will get subtracted from. from access help files this is how it shows to connect to the "date" table that I have but when I try to run it it gives me an error saying that "user defined type not defined." the code it has issue with is the "dim db as database"
    after I can view the data in the table I will need to update the record in the date table to "S"


    Public Function stime(starttime As Date, runhrs As Double) As Date

    Dim s As Date
    Dim r As Double
    Dim db As database
    Dim sdate As Recordset
    Set db = CurrentDb

    Set sdate = db.OpenRecordset("Date")
    s = sdate
    r = runhrs

    s = s - r

    stime = s
    End Function

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: connecting to a table in access using VBA?

    Originally posted by revkev6
    I have been using access 2000 for a couple years now to edit data and write reports against an oracle db. I have just recently needed to add a module to do some of the things that I need to do. I am trying to make a barcode labor entry screen that uses an estimated time for a scanned in operation and subtracts that time from the previous time. this screen will be used in a batch mode so that the user can scan in about 30 parts at a time and walk away. the table I want to open stores one record which is the current date that will get subtracted from. from access help files this is how it shows to connect to the "date" table that I have but when I try to run it it gives me an error saying that "user defined type not defined." the code it has issue with is the "dim db as database"
    after I can view the data in the table I will need to update the record in the date table to "S"


    Public Function stime(starttime As Date, runhrs As Double) As Date

    Dim s As Date
    Dim r As Double
    Dim db As database
    Dim sdate As Recordset
    Set db = CurrentDb

    Set sdate = db.OpenRecordset("Date")
    s = sdate
    r = runhrs

    s = s - r

    stime = s
    End Function
    You need to reference the DAO 3.6 Libraries ...

    Go To: Tools->References and find and select them.

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    ok thanks that worked great!
    now that it accesses the table when try to set a variable to the recordset (s = sdate)
    it gives me an incorrect type error. the record in the table is of the date type as is the variable. how do I get to the record level instead of the record set? also how to update would also be useful

  4. #4
    Join Date
    Jan 2004
    Posts
    184
    Try this:

    s= sdata.fields("NameOfField").value
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    Okay this now works but I still need to update the record to the value of "S" before I end the function

    Public Function Stime(starttime As Date, runhrs As Double) As Date

    Dim s As Date
    Dim r As Double
    Dim db As Database
    Dim sdate As DAO.Recordset
    '* Dim idate As ADODB.Recordset
    Set db = CurrentDb

    Set sdate = CurrentDb.OpenRecordset("xdate")
    s = sdate.Fields("start_time").Value
    r = runhrs

    s = s - r
    '* THIS IS WHERE i WANT TO UPDATE THE RECORD
    Stime = s
    End Function

  6. #6
    Join Date
    Jan 2004
    Posts
    184
    Do this

    CurrentDB.Execute "UPDATE Table1 SET Field1=' " & s & " ' FROM TABLE1 WHERE ..."
    In abundance of water only the fool is thirsty. Bob Marley.

  7. #7
    Join Date
    Mar 2004
    Posts
    4
    thanks worked great! you might have just gotten me a raise! almost up to minimum wage now........

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •