Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Chicagoland, IL USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copying data from one table to another (Access 97 sr-2)

    All,

    I have 2 databases that were combined into one in order to reduce redundancy of data entry. It was originally engineered to copy specific patient demographic information to the second database/log when clicking a particular button. Now it is not copying that data. Nothing was altered or anything. Has anyone seen this before? Would it be helpful to paste the "on click" information to see what the routine is?

    Thanks,
    Debra

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Copying data from one table to another (Access 97 sr-2)

    Does the OnClick property for that button actually say "Event Procedure"? And are you certain the code is executing at all? Put in a breakpoint at the 1st line of the code in that procedure, then step thru the code. Don't use Resume Next to catch errors, and it masks them; put in a GOTO ON ERROR, even if it in turn just does a Return Next.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Copying data from one table to another (Access 97 sr-2)

    If you combined two databases into one, then where would you be copying data to? It sounds like it no longer has any place to go. And are you talking about databases or tables? Post the code ... and more information.
    Charlotte

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Location
    Chicagoland, IL USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one table to another (Access 97 sr-2)

    Charlotte, yes... there were two databases that one particular unit in the hospital was typing the demographic information into twice. It was made into 1 database. They start off in the first log, and if they need to progress to the second one, they click on the button to go to the next log. What this is supposed to do is copy specific fields to the next log/table so they don't have to retype the same information twice. So now, it is one database with all the tables, forms, queries and reports from both of them. Here is the Event Procedure code of what is supposed to happen when they click on the button to open a new record in the second log:

    Private Sub Command88_Click()
    Dim EmtalaLDDb As Database
    Dim PatientReturn As Recordset
    Dim PatientQry As QueryDef
    Dim PatientQryParm As Parameter

    If IsNull([Date]) Or IsNull([Last Name]) Or IsNull([First Name]) Or IsNull([Delivery Time]) Or IsNull([Doctor Name]) Then
    MsgBox ("Date, Last Name, First Name, Delivery Time and Doctor Name required to add a record to Labor and Delivery Database")
    Else
    Set EmtalaLDDb = DBEngine.Workspaces(0).Databases(0)
    Set PatientQry = EmtalaLDDb.QueryDefs("Prentice/Winfield Moody Select2")

    PatientQry.Parameters("ParmLastName") = [Last Name]
    PatientQry.Parameters("ParmFirstName") = [First Name]
    PatientQry.Parameters("ParmDateofBirth") = [Date]

    Set PatientReturn = PatientQry.OpenRecordset()

    Dim saveInit As String


    If PatientReturn.EOF Then
    DoCmd.SetWarnings False


    DoCmd.OpenForm ("Prentice Edit Form from Emtala")
    DoCmd.SetWarnings True


    Else
    'PatientReturn.MoveFirst

    DoCmd.SetWarnings False
    'DoCmd.OpenQuery ("Prentice/Winfield Moody Insert")
    DoCmd.OpenForm ("Prentice Edit Form from Emtala")
    DoCmd.SetWarnings True
    End If
    End If
    End Sub

    Private Sub Find_a_record_Click()
    On Error GoTo Err_Find_a_record_Click


    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

    Exit_Find_a_record_Click:
    Exit Sub

    Err_Find_a_record_Click:
    MsgBox Err.Description
    Resume Exit_Find_a_record_Click

    End Sub

    What happens is it DOES, in fact, open up a new record, but all the information that was supposed to be copied is not.
    I'd be happy to show the queries, too, but am unsure how to display them here.

    Thanks,
    Debra

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one table to another (Access 97 sr-2)

    From the name, I would guess that "Prentice/Winfield Moody Insert" is the name of the query that is supposed to insert a record in the second table. However, it is never executed because there is an apostrophe before the instruction
    'DoCmd.OpenQuery ("Prentice/Winfield Moody Insert")
    which makes it into a comment.

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Location
    Chicagoland, IL USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copying data from one table to another (Access 97 sr-2)

    I copied this database into a test environment, took out the ' and it still didn't copy it. Puzzling.

    Thanks,
    Debra

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copying data from one table to another (Access 97 sr-2)

    Try making the DoCmd.SetWarnings ... lines (in the test copy) into comments by putting an apostrophe ' in front of them. Set a stop on

    Private Sub Command88_Click()

    by clicking in the left margin at that line (or clicking in the line and selecting Debug/Toggle Breakpoint)
    Now see what happens if you open the form and click the button. The code should pause at the breakpoint. Step through the code using F8 (or Debug/Step Into). Do you ever get to the statement where the insert query is run? If so, do you get a warning that you are about to append nn records? Here nn is a number; if nn = 0, it means that the insert query will append no new records...

Posting Permissions

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