Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    3

    Unanswered: Need to pass date from form to different table

    I have a database that includes the tables Exam and Patient. I would like to pass the ExamDate off of the Exam form to the LastEyeExam field in the Patient table. How do I go about doing this?

    I have tried code out of the Access 2002 Bible, and got the error message "The database has been placed in a state by (User) that prevents it from being.....". Are there any suggestions on how to do this using VB or maybe an expression that will do it when the form is updated? Any help would be greatly appreciated.

    Ben

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Need to pass date from form to different table

    Originally posted by blars
    I have a database that includes the tables Exam and Patient. I would like to pass the ExamDate off of the Exam form to the LastEyeExam field in the Patient table. How do I go about doing this?

    I have tried code out of the Access 2002 Bible, and got the error message "The database has been placed in a state by (User) that prevents it from being.....". Are there any suggestions on how to do this using VB or maybe an expression that will do it when the form is updated? Any help would be greatly appreciated.

    Ben
    See if this works for you:

    You will need a reference to the DAO 3.6 library which is not the default in Access 2002. ADO is the default. Shift the DAO reference up higher in the list than the ADO reference and you won't have to reference the library name before this code. (DAO.Recordset, ADO.Recordset etc...)

    Dim rs as recordset, db as database
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblPatient", dbOpenDynaset)
    rs.FindFirst "PatientID = " & Me.PatientID
    rs.Edit
    rs.Fields ("LastEyeExam") = Me.ExamDate
    rs.Update
    Set rs = Nothing
    Set db = Nothing

    You could also use a subform with the patient data displayed. Link the forms with the PatientID and add the date through code there.

    Hope this helps.

    Gregg

  3. #3
    Join Date
    Nov 2003
    Posts
    3
    I am no Access wiz, just doing a project for a college class. I don't know what you mean by shifting the DAO reference above the ADO reference. But, I will try this when I get off work tonight. Thank you very much for your help.

    Ben

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by blars
    I am no Access wiz, just doing a project for a college class. I don't know what you mean by shifting the DAO reference above the ADO reference. But, I will try this when I get off work tonight. Thank you very much for your help.

    Ben
    Sorry man. There's probably something easier that someone can suggest. I've gotten so used to relying on programming code that I forget some of the easy stuff built in to Access.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Posts
    3
    Many, many thanks to you Basicmek. I couldn't figure out what you were talking about with the priority thing at work because the reference libraries are diabled for view on work computers. Got home, figured it out, put in your code, and it worked! Thanks again!

    Ben

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by blars
    Many, many thanks to you Basicmek. I couldn't figure out what you were talking about with the priority thing at work because the reference libraries are diabled for view on work computers. Got home, figured it out, put in your code, and it worked! Thanks again!

    Ben

    Excellent. That's what we like to hear.

    Gregg

Posting Permissions

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