Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    accFormAdd opens blank form (2003)

    I have a form that loads with a subform set to continuous mode. When i used the command "DoCmd.OpenForm strDocName, acNormal, , , acFormReadOnly" the form opens in read only mode as I would expect. However when I open the form with "DoCmd.OpenForm strDocName, acNormal, , , acFormAdd", the form opens but there is nothing present. The form is just a blank white form. Does anyone have any suggestions as to what might be causing this?

    Thanks,
    BZ

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

    Re: accFormAdd opens blank form (2003)

    This happens if the record source of the form is not updatable.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    The database used to work fine. What would have caused this to change? The record source is based off a query.

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

    Re: accFormAdd opens blank form (2003)

    What happens if you open that query directly from the database window? Can you add a new record (i.e. is the New Record button enabled)?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    If the query is the following statement, I can add new records:

    SELECT tblEntry.[Emp#], tbljobnumbers.[Customer Name], tblEntry.[Job Number], tblEntry.[Activity Code], tblEntry.[Week#], tblEntry.MR, tblEntry.TUR, tblEntry.WR, tblEntry.THR, tblEntry.FR, tblEntry.SAR, tblEntry.SUR, tblEntry.MO, tblEntry.TUO, tblEntry.WO, tblEntry.THO, tblEntry.FO, tblEntry.SAO, tblEntry.SUO, tblEntry.Year, tbljobnumbers.[Billing Code], tbljobnumbers.Description, [Billing Code] & [Description] AS combine, Left([Billing Code],7) AS [Part of Billing Code]
    FROM tblEntry INNER JOIN tbljobnumbers ON tblEntry.[Job Number] = tbljobnumbers.[Job Number]
    WHERE (((tblEntry.[Emp#])=[Forms]![frmCalendar]![txtEmpNo]) AND ((tblEntry.[Week#])=[Forms]![frmCalendar]![txtCurWkSt]) AND ((tblEntry.Year)=[forms]![frmcalendar]![txtyear]))
    ORDER BY tbljobnumbers.[Customer Name], tblEntry.[Job Number];

    I wanted to changed the table. Here is the modified version that won't allow me to add records:
    SELECT tblEntry.[Emp#], tbljobnumbersLoc.[Customer Name], tblEntry.[Job Number], tblEntry.[Activity Code], tblEntry.[Week#], tblEntry.MR, tblEntry.TUR, tblEntry.WR, tblEntry.THR, tblEntry.FR, tblEntry.SAR, tblEntry.SUR, tblEntry.MO, tblEntry.TUO, tblEntry.WO, tblEntry.THO, tblEntry.FO, tblEntry.SAO, tblEntry.SUO, tblEntry.Year, tbljobnumbersLoc.[Billing Code], tbljobnumbersLoc.Description, [Billing Code] & [Description] AS combine, Left([Billing Code],7) AS [Part of Billing Code]
    FROM tblEntry INNER JOIN tbljobnumbersLoc ON tblEntry.[Job Number] = tbljobnumbersLoc.[Job Number]
    WHERE (((tblEntry.[Emp#])=[Forms]![frmCalendar]![txtEmpNo]) AND ((tblEntry.[Week#])=[Forms]![frmCalendar]![txtCurWkSt]) AND ((tblEntry.Year)=[forms]![frmcalendar]![txtyear]))
    ORDER BY tbljobnumbersLoc.[Customer Name], tblEntry.[Job Number];

    The only thing I changed was tblJobNumbers. I changed it to tblJobNumbersLoc. I created tblJobNumbersLoc just like I created the first table.
    Thanks,

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

    Re: accFormAdd opens blank form (2003)

    Is there a unique index on the Job Number field in tblJobNumbers? And in tblJobNumbersLoc? If the answers are Yes and No, respectively, create a unique index on Job Number in tblJobNumbersLoc.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    They are both linked tables... when I changed the indexed property to Yes in the home database, it did not update in the datebase i have the tabled linked to. Am I forgetting to do something?

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

    Re: accFormAdd opens blank form (2003)

    Perhaps you should close and reopen the frontend database - linked tables take their indexes from the source tables in the backend.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    Here is my setup. I have two backend databases and a frontend. When the main form opens on the frontend, a make table query runs and copies a table from the one backend database on the network to the other backend database that resides on the local. I believe that every time the make table query runs it overwrites the table thus resetting the index field to no. Is there a way to force the backend to make the jobnumber field indexed?

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

    Re: accFormAdd opens blank form (2003)

    You are correct: a make-table query overwrites the target table if it exists. You could do the following instead:

    One-time action:
    - Import the table from the network backend into the local backend.

    Each time the main form in the frontend is opened:
    - Run a delete query to delete all records from the local backend table.
    - Run an append query to add all records from the network backend table to the local backend table.
    Or simply import the network backend table again.

  11. #11
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    Which method will take the least time? I would think the import would. Do I have to perform the import in VBA? If so, could you give me an example? I will search the help file while i wait for a reply. Thanks Hans...you never let me down!!

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

    Re: accFormAdd opens blank form (2003)

    If you now run the make-table query using VBA, you should also run the delete+append queries or the import using VBA. If you import, you'd have to delete the target table first. Here is some dummy code:

    ' Delete target table if it exists
    On Error Resume Next
    DoCmd.DeleteObject acTable, "tblSomething"
    On Error GoTo 0 ' or use an error handler
    ' Import table from network backend
    DoCmd.TransferDatabase acImport, "Microsoft Access", "serversharefolderBackend.mdb", acTable, "tblSomething", "tblSomething"

  13. #13
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    I need to import the database everytime the main form is loaded. Will it append the data or will it overwrite?

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

    Re: accFormAdd opens blank form (2003)

    DoCmd.TransferDatabase acImport ... neither overwrites nor appends the data. If the target table already exists, it will create a new table with a sequence number after the name, e.g. if you import to tblData and if tblData already exists, it will create tblData1, just like when you import using File | Get External Data | Import...
    So you need to delete the existing table before importing.
    Alternatively, you can run a delete query and an append query.

  15. #15
    2 Star Lounger
    Join Date
    Apr 2002
    Location
    Evansville, Indiana, USA
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: accFormAdd opens blank form (2003)

    Is there a way to set the primary key of a table when I run a maketable query? I need this table to be created on the users computer that the database will be deployed on. I can't just have them copy the new database cause that would overwrite the data in the frontend stored on their local. What I need is to have a new table created in the frontend with a primary key defined.
    Is this possible?

Page 1 of 2 12 LastLast

Posting Permissions

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